-2

when I use 'var select = from st in context.student where st.college=="ABC" select st;'

context.student.DeleteAllonSubmit(select); context.student.SubmitChanges();

if there are 10 records containing college name as "ABC" then 10 delete query are fired.

Piyush
  • 15
  • 4
  • 1
    Possible duplicate of http://stackoverflow.com/questions/445033/use-linq-to-generate-direct-update-without-select – Crab Bucket Feb 16 '12 at 11:59
  • But the exact reason for firing of select query is still not cleared. How can we avoid select statement in update and delete queries using Linq – Piyush Feb 16 '12 at 12:06
  • I think because as the answer says - LINQ nor LINQ to SQL has set-based update capabilities. The second 'answer' gives a way around. But see if anyone else has anything to add. I'd be interested – Crab Bucket Feb 16 '12 at 12:08
  • Its fine for update but when I am using the following Query for delete operation then also select query is fired. var delet= from remov in context.student where remov.college=="ABC" select remov; context.student.DeleteAllonSubmit(delet); context.SubmitChanges(); – Piyush Feb 16 '12 at 12:11

1 Answers1

1

Well my only explanation is that Linq first has to select the row you want to update/delete. After it knows which rows are affected by the update/delete Linq makes the most sufficient query to update/delete the rows you want to and executes it.

Please correct me if I'm wrong, this is what I think is happening I don't know it for sure.

Rick Hoving
  • 3,585
  • 3
  • 29
  • 49
  • Well what actually happens is the following. When u create an object contect the entity framework makes objects from the database, using your edmx file (or another file that handles the conversion from database to objects). These objects are not in sync with your database. So when you delete/update objects from your object context and you say you want to save the object context the entity framework compares your objects with the objects in the database (also using the edmx (or other)file) you have. Then the entity framework updates the database (with optimal queries) according to your changes. – Rick Hoving Feb 16 '12 at 12:26
  • Glad to help (don't forget to up-vote :)). What I'd suggest is open your object context do a single Linq query that removes all the rows you want to remove. Then save the changes and close your object context (you should use the using statement but that's another thing). Linq should be that smart that it detects that it is possible in a single query. You did so Linq can do practically the same. Then all you have to do is trust Linq icm the entity framework executes the correct query. You can also monitor the send queries in SQL Server 2008 I beleve. – Rick Hoving Feb 16 '12 at 12:44
  • Could you post the Linq query you are doing? And perhaps figure out what those 10 queries are exactly? Table scans table seeks ect – Rick Hoving Feb 17 '12 at 08:28
  • Try something like this. using (var oc = new ObjectContextName()) { var sel = oc.students.Where(x => x.college == "XYZ").ToList(); foreach (var item in sel) { oc.DeleteObject(item); } oc.SaveChanges(); } – Rick Hoving Feb 17 '12 at 09:19