Workaround for problems with LINQ to SQL Delete _ practical Tips

1.1,

Code1:

using (Pubsdatacontext pubscontent = new Pubsdatacontext ())

{

PubsContent.Log = Console.Out;

Author Author = PubsContent.Authors.Single (a => a.au_id = "111-11-1111");

PubsContent.Authors.DeleteOnSubmit (author);

Pubscontent.submitchanges ();

}

However, immediately my program support here will not run, the second line has an exception.

Why, then?

Take a look at the MSDN instructions for the Signle method:

Well, here's the problem, single requires a single line of records, or it's going to freak out.

From the previous study I know that when the single method is invoked, DataContent immediately obtains the database from the database, and this time if a record of auid= "111-11-1111" is not obtained, the returned recordset is empty and the above exception is thrown.

1.2,

I'm looking for a way to get LINQ to delete directly from the Select, search the MSDN, finish the LINQ in Action, No.

Then I thought, now that LINQ to SQL has a "deferred load" feature, can it be "delayed" when you delete it, I try this:

Code 2:

using (Pubsdatacontext pubscontent = new Pubsdatacontext ())

{

var q = from a in pubscontent.authors

where a.au_id = = "111-11-1111"

Select A;

PubsContent.Log = Console.Out;

PubsContent.Authors.DeleteAllOnSubmit (q);

Pubscontent.submitchanges ();

}

My program has been tamed and not mad here.

does LINQ to SQL actually execute the delete from Authors where au_id= ' 111-11-1111 ' as I think?

2.1

With the above questions, follow the DataContent log step-by-step. I found that in the single () method to delete, if there is no exception, the submitted SQL statement is the case.

It is clear from here that LINQ first takes the record out of the database and then deletes it. We know that the primary key can determine the only record in the table, but why delete the condition to add all the columns? Lao Zhao in this post ( the conflict when managing concurrent updates in LINQ to SQL [1],[2],[3] ) explains the problem in detail.

My goal is simply to delete a row of records, but it is superfluous to take the LINQ to SQL out of the database and delete it first. How does the method in code 2 work? We're going to follow it.

2.2

To better illustrate the problem, I'll change the code 1 and add a two-line record to the database, au_id 111-11-1111, 111-11-1112

Code 3:

using (Pubsdatacontext Pubscont ent = new Pubsdatacontext ())

{

& nbsp;  pubsContent.Log = console.out;

var q = from a in pubscontent.authors

where a.au_id. StartsWith (" 111-11-111")

&nbs p;   Select A;

pubsContent.Authors.DeleteAllOnSubmit (q);

pubscontent.submitchanges ();

}

Replace the = = Condition with startswith ( when generating SQL statements, Startwith will generate like ' 111-11-111% ' matches ).

Now again, this code executes the log:

SELECT [T0]. [au_id], [t0]. [au_lname], [t0]. [au_fname], [t0]. [Phone], [T0]. [Address], [t0]. [City], [t0]. [State], [t0]. [Zip], [t0]. [Contract]

from [dbo]. [Authors] As [t0]

WHERE [T0]. [au_id] Like @p0

--@p0: Input VarChar (Size = 11; Prec = 0; Scale = 0) [111-11-111%]

--Context:sqlprovider (Sql2000) Model:attributedmetamodel build:3.5.21022.8

DELETE from [dbo]. [Authors] WHERE ([au_id] = @p0) and ([au_lname] = @p1) and ([au_fname] = @p2) and ([phone] = @p3) and ([address] = @p4) and ([City] = @p5) and ([state] = @p6) and ([zip] = @p7) and ([contract] = 1)

--@p0: Input VarChar (Size = 11; Prec = 0; Scale = 0) [111-11-1111]

--@p1: Input VarChar (Size = 3; Prec = 0; Scale = 0) [QQQ]

--@p2: Input VarChar (Size = 3; Prec = 0; Scale = 0) [QQQ]

--@p3: Input Char (Size = 12; Prec = 0; Scale = 0) [QQQ]

--@p4: Input VarChar (Size = 3; Prec = 0; Scale = 0) [QQQ]

--@p5: Input VarChar (Size = 3; Prec = 0; Scale = 0) [QQQ]

--@p6: Input Char (Size = 2; Prec = 0; Scale = 0) [QQ]

--@p7: Input Char (Size = 5; Prec = 0; Scale = 0) [22222]

--Context:sqlprovider (Sql2000) Model:attributedmetamodel build:3.5.21022.8

DELETE from [dbo]. [Authors] WHERE ([au_id] = @p0) and ([au_lname] = @p1) and ([au_fname] = @p2) and ([phone] = @p3) and ([address] = @p4) and ([City] = @p5) and ([state] = @p6) and ([zip] = @p7) and ([contract] = 1)

--@p0: Input VarChar (Size = 11; Prec = 0; Scale = 0) [111-11-1112]

--@p1: Input VarChar (Size = 3; Prec = 0; Scale = 0) [QQQ]

--@p2: Input VarChar (Size = 3; Prec = 0; Scale = 0) [QQQ]

--@p3: Input Char (Size = 12; Prec = 0; Scale = 0) [QQQ]

--@p4: Input VarChar (Size = 3; Prec = 0; Scale = 0) [QQQ]

--@p5: Input VarChar (Size = 3; Prec = 0; Scale = 0) [QQQ]

--@p6: Input Char (Size = 2; Prec = 0; Scale = 0) [QQ]

--@p7: Input Char (Size = 5; Prec = 0; Scale = 0) [22222]

Very disappointed, and I expect the result is not the same.

In this test, datacontent first retrieve all the records that match the criteria, and then one delete.

If there are 10,000 records to be deleted, the sky is dark ...

This, I have to say, LINQ to SQL is a bit stupid.

3

Solve?

We can only circle around.

DataContext provides a executecommend method that might use this method to execute SQL commands directly. Like this:

Code 4:

using (Pubsdatacontext pubscontent = new Pubsdatacontext ())

{

PubsContent.Log = Console.Out;

Pubscontent.executecommand ("delete from Authors where au_id like ' 111-11-111% '");

}

You can also get the current database connection through Datacontext.connection, and then submit your own SQL statements via Dbcommend.

or write a stored procedure for deletion.

4

LINQ, language-level Integrated query (Language in tegrated Query)

Obvious, strong in query, delete on weak weakness;-) ...