Create a single Query from EF 4.0 to SQL Server 2008 R2 instead of multiple queries

Question

In following code, multiple queries will be run against the database for inserting one row at a time.

EF cause Multiple Database Trips: When I call c.SaveChanges, then EF 4.0 will run multiple UPDATE statements against the database, but will execute only one UPDATE per database trip. This means if we have 1000 UPDATE statements generated by EF 4.0, then we will end up making 1000 database trips, which is NOT a GOOD and OPTIMAL data approach in my view.

Is there any way of making EF 4.0 run a single batched query for all records updates in a single database trip? May be, if I could get the UPDATE query ( I mean the SQL query) for each record update, then I could just put all these queries into a single batched query and send the batched query to database, but I don't know if the UPDATE query for each record can be obtained in EF 4.0.

 public void SaveVendorOrders(int vendorId, int parentVendorId, string tag,
                              string desc, int productSegment, int productCategory)     
 {  
  using (Vendor c = new Vendor())
        {
            var query = (from p in c.Orders
                         where (p.VendorID == vendorId ||
                                p.ParentVendorID == parentVendorId)
                         select p);

            if (query.ToList().Count() > 0)
            {
                for (int i = 0; i <= query.ToList().Count() - 1; i++)
                {
                    query.ToList()[i].OrderTag = tag;
                    query.ToList()[i].OrderDescription = desc;
                    query.ToList()[i].ProductSegment = productSegment;
                    query.ToList()[i].ProductSegmentCategory = productCategory;
                }

                c.SaveChanges();
            }
        }
 }

EDIT 1:

As pointed out by SJuan76 and Hamlet Hakobyan, calling ToList repeatedly will execute a query repeatedly. But even that is solved by cleaning the code so ToList is called only once, my question remains: How to combine multiple updates into a single batched update query?

Wow.... just wow. Do you know that each `ToList()` is at least one SQL query, don't you? — SJuan76, Apr 11 '14 at 20:06
You mean query.ToList should be called only once in this method? I get it now. This is some code that is causing a lot of performance issues in an existing app and I have been given the task to improve its performance. I am new to EF, so never knew that query.ToList() would execute a query against the database, but makes sense now. — Sunil, Apr 11 '14 at 20:10
No offence, but this is a good example of how NOT to use EF. — Kunukn, Apr 11 '14 at 20:19
@Kunukn, I am in 100% agreement with you on this. Why make multiple database trips especially when there is potential for thousands of updates in future. If it was always going to be something like 10 or so, then I would be fine but in this case a vendor could have thousands of orders in database meaning thousands of database trips. — Sunil, Apr 11 '14 at 20:28

score 3 · Answer 1 · answered Apr 11 '14 at 20:06

3

The query intended to deferred execution. It really executes whey you enumerate your query or, for instance, call ToList(). To avoid multiple enumerations you must call ToList() one time the your in-memory data in you method.

using (Vendor c = new Vendor())
{
  var query = (from p in c.Orders
               where (p.VendorID == vendorId ||
                      p.ParentVendorID == parentVendorId)
               select p).ToList();

  if (query.Count() > 0)
  {
      for (int i = 0; i <= query.Count() - 1; i++)
      {
          query[i].OrderTag = tag;
          query[i].OrderDescription = desc;
          query[i].ProductSegment = productSegment;
          query[i].ProductSegmentCategory = productCategory;
      }

      c.SaveChanges();
  }
}

answered Apr 11 '14 at 20:06

Hamlet Hakobyan

32,965
6
52
68

Thanks. That makes perfect sense and I think that could be causing the performance issues being reported by users, especially if a vendor has thousands of orders. – Sunil Apr 11 '14 at 20:15
My question of combining multiple updates into a single query is still not answered. Do you know how that is done? – Sunil Apr 11 '14 at 20:16
What you mean by `combining multiple updates into a single query`? Can you explain in more details? – Hamlet Hakobyan Apr 11 '14 at 20:19
When I call c.SaveChanges, it results in multiple update queries being sent to database, but ONE AT A TIME. So this results in many database trips. I want to make only one database trip in which I send all UPDATE queries at a time. You can see my explanation of this under 'EF cause Multiple Database Trips' of my original post. – Sunil Apr 11 '14 at 20:23
@Sunil What you want is bulk update, maybe this can help you http://stackoverflow.com/questions/12751258/batch-update-delete-ef5 or search for entity framework bulk update, or lightweight ORM which support bulk update – Kunukn Apr 11 '14 at 20:40

Create a single Query from EF 4.0 to SQL Server 2008 R2 instead of multiple queries

1 Answers1