effective linq delete

[Lagrange]

Hi,

Suppose that I have a table named 'Image' with primary key 'ID' (of
int type) and binary data column named 'Data' (of Image type) on MS
SQL database.

I am using Linq to SQL.

Then if I want to delete items that satisfy some condition I write:


DefaultDataContext dataContext = new DefaultDataContext ();

var itemsToDelete = dataContext.Images.Where(item => item.ID > 10)

dataContext.Images.DeleteAllOnSubmit(itemsToDelete);


If I understand this correctly, whenever an implicitly typed variable
(var) is casted to IEnumerable the query is executed (and depending on
query type data retrieved from DB).


As DeleteAllOnSubmit accepts a IEnumerable<Images> parameter, my
itemsToDelete linq query is, indeed, casted to IEnumerable, hence, the
query is executed and all the Images that satisfy the condition ID >
10 are fetched instantiated in my app's memory prior.

For what? Only to tell the DB ID's of Images to delete in the
following step?

Did I catch the point?

Now suppose, that my records are really big peaces of data. Why should
I have my application filling the memory with such a volume of
information when all it needs to do is, in fact, to tell MS SQL the
condition for items to be deleted?


I know that I can execute direct SQL statement via data-context
object. But tell me, does some more elegant, more "linquish" way
exist?

Thanks in advance.

Peter.

 

[bruce barker]

you jut missed the point.

in linq to sql, the query is an expression tree rather than an linq
query. see the Linq.Expressions namespace

when IEnumerable is called the the sql expression tree, the tree is
converted to a sql select statement, then executed.

when you do a delete via .RemoveAll or .DeleteAllOnSubmit, the
expression tree is used to generate the delete statement and executed,
so no data is returned to your app.

-- bruce (sqlwork.com)

 

[Mr. Arnold]

Now suppose, that my records are really big peaces of data. Why should
I have my application filling the memory with such a volume of
information when all it needs to do is, in fact, to tell MS SQL the
condition for items to be deleted?


I know that I can execute direct SQL statement via data-context
object. But tell me, does some more elegant, more "linquish" way
exist?

So why can't you use a List<int> and do a Linq query where you return the
key of the records to be deleted?

Then you would walk the List and for each ID, you get the Linq-2-Sql entity
and submit it for delete.


__________ Information from ESET NOD32 Antivirus, version of virus signature database 4498 (20091011) __________

The message was checked by ESET NOD32 Antivirus.

http://www.eset.com

 

[Lagrange]

Hi Bruce,

(this is Peter again, I just used a different account)

thanks for your insight, I realize that my terminology was a bit
vague.

However, I used SQL Profiler to verify your statement "so no data is
returned to your app.".



Again, the code is:

---
DefaultDataContext dataContext = new DefaultDataContext();

var query = dataContext.Images.Where(item => item.ID > 6);
dataContext.Images.DeleteAllOnSubmit(query);


dataContext.SubmitChanges();
---

which is translated to the following SQL statements:

---
exec sp_reset_connection
go
exec sp_executesql N'SELECT [t0].[ID], [t0].[Data]
FROM [dbo].[Image] AS [t0]
WHERE [t0].[ID] > @p0',N'@p0 int',@p0=6
go
exec sp_reset_connection
go
exec sp_executesql N'DELETE FROM [dbo].[Image] WHERE ([UserID] = @p0)
AND ([ID] = @p1)',N'@p0 int,@p1 int',@p0=8,@p1=111
go
exec sp_executesql N'DELETE FROM [dbo].[Image] WHERE ([UserID] = @p0)
AND ([ID] = @p1)',N'@p0 int,@p1 int',@p0=8,@p1=113
go
exec sp_executesql N'DELETE FROM [dbo].[Image] WHERE ([UserID] = @p0)
AND ([ID] = @p1)',N'@p0 int,@p1 int',@p0=8,@p1=115
go
exec sp_executesql N'DELETE FROM [dbo].[Image] WHERE ([UserID] = @p0)
AND ([ID] = @p1)',N'@p0 int,@p1 int',@p0=8,@p1=145
go
exec sp_executesql N'DELETE FROM [dbo].[Image] WHERE ([UserID] = @p0)
AND ([ID] = @p1)',N'@p0 int,@p1 int',@p0=15,@p1=103
go
exec sp_executesql N'DELETE FROM [dbo].[Image] WHERE ([UserID] = @p0)
AND ([ID] = @p1)',N'@p0 int,@p1 int',@p0=15,@p1=104
go
exec sp_executesql N'DELETE FROM [dbo].[Image] WHERE ([UserID] = @p0)
AND ([ID] = @p1)',N'@p0 int,@p1 int',@p0=15,@p1=105
go
---

I suppose, the result of the select statement is returned to the
application and than Linq engine (or whatever do we call it)
constructs delete statements for each record one by one and executes
it on SQL.

I'm affraid this supports my original theory rather than yours.
If I am wrong again, please, correct my reasoning.


Be it so or not it is still far from more efective single SQL
statement:

DELETE FROM Image Where....


I must insist on my original question:
Does some more effective but still Linq to SQL based way of DELETE
exist?

Thanks in advance

Peter

 

[Lagrange]

Here's Peter again.

After inspecting several resources, it appears to me that this is
simply 'status quo' regarding deleting multiple rows in Linq to SQL.


This is just one example:

http://www.rocksthoughts.com/blog/archive/2008/03/03/delete-multiple-rows-with-linq-to-sql.aspx

 

[Lagrange]

Another entry about this topic:

http://weblogs.asp.net/jeffreyzhao/...sion-batch-deletion-by-lambda-expression.aspx