Stop LINQ to SQL from executing select statements

问题:

I'm using LINQ to SQL to update my database. I'm inserting a lot of records, and when I call SubmitChanges(), LINQ to SQL executes an insert and a select statement for each object. I don't really care to update my objects after they are inserted into the database.

Do you know I can prevent LINQ to SQL from issuing the select statements after the insert statements? This should make my app much faster.

回答1:

You're looking for ColumnAttribute.AutoSync. If you're using the designer, check each column for an Auto-Sync property and set it to Never.

Edit: Ok, that didn't work for you. Brace yourself for some mapping hackery!

When I Insert with some autogenerated primary key column, I get this SQL:

INSERT INTO [dbo].[TableName]( fieldlist )
VALUES (@p0, @p1, @p2, @p3, @p4)

SELECT CONVERT(Int,SCOPE_IDENTITY()) AS [value]

As I understand your request, you don't want that SELECT

Attempt 1: I went to the primary key field and set auto-generated to false. This caused a Sql Exception "Cannot insert explicit value for identity column in table 'TableName' when IDENTITY_INSERT is set to OFF." In other words, linq specified a value for that column.

Attempt 2: I deleted the autogenerated columns from the designer. This caused Linq to give me an Invalid Operation Exception: "Can't perform Create, Update or Delete operations on 'Table(TableName)' because it has no primary key."

Attempt 3: I deleted the autogenerated columns from the designer, then I marked another column as primary key. Even though this column is not a primary key in the database, LINQ's DataContext will use it to track row identity. It must be unique for observed records of a given DataContext.

This third attempt generated the following SQL (which is what you ask for)

INSERT INTO [dbo].[TableName]( fieldlist )
VALUES (@p0, @p1, @p2, @p3, @p4)

回答2:

I cant recall the setting now, but in the designer, on a column's properties, you have some 'refresh' setting(s).

回答3:

I had this problem with a project once. I simply used the context.ExecuteCommand({sql string here.}). This was for one insert that was causing performance problems. Easiest fix, easy to see and maintain rather than digging through the model in my opinion.