CODEWITHSUNDEEP
sqllinqentity-framework
Yusuf Uzun
Yusuf Uzun

Reputation: 1511

Entity Framework and LINQ together Batch Update

Afaik Entity Framework 6 doesn't support for batch insert/update/delete. Is there anyway to make an batch update over an IQueryable object. As an example I have

var query = _db.People.Where(x=>x.Name.Contains(parameter));

an IQueryable (query) object and I want to get the generated sql. Then I hope I can create an update command with this select query like this

Update filteredPerson
Set filteredPerson.Status = 'Updated'
from (here it comes IQueryable Generated SQL :) ) as filteredPerson

over DbContext raw sql execution commands. BTW I don't need EF properties like change tracking and auto detecting. It is just a batch operation.

I know it is pretty risky but I am going to use it for a small piece of code.

Some other logics are appricated. If you know something better, I would like to hear it.

REASON: Why I want to do it this way, because I don't want to spoil the seperation of layers. And there is some validation and filtering comes into the queryable object from other layers. So it is hard to convert it to stored procedure. At the other hand it must be faster than other standard queries.

Again I know there is no support in Entity Framework 6 for batch operations. But other questions are bit outdated. That's another reason why I want to ask this again.

Upvotes: 3

Views: 1551

Answers (1)

Yusuf Uzun
Yusuf Uzun

Reputation: 1511

While I was writing the question, I was guessing how I am going to solve it. But I was looking for some more proper way of it. In the end, I know what am I doing and tried to be simple for my colleagues who looking to the same code after me. I know it has some risky usages but I let the exceptions to CLR to handle it. After this excuse :) , I wrote the code like this:

Let's say I have an IQueryable object which is generated with this way:

string parameter = "John";

AdventureWorks2012Entities _db = new AdventureWorks2012Entities();
var query = _db.People.AsQueryable();
//Some parameters added from different layers
query = query.Where(x => x.FirstName.Contains(parameter));

Then I want a batch update over this IQueryable object.

var sqlFrom = query.ToString(); //This is the query which becomes "from table"
var dbq = query.AsDbQuery().GetObjectQuery(); //This does some magic with reflection

var linqParams = dbq.Parameters
    .Select(x => new System.Data.SqlClient.SqlParameter(x.Name, x.Value)).ToList();
linqParams.Add(new System.Data.SqlClient.SqlParameter("@ModDate", DateTime.Now));

var sqlBatchUpdate = @"Update filteredPerson Set ModifiedDate = @ModDate From (@FilteredPerson) as filteredPerson"
    .Replace("@FilteredPerson", sqlFrom);
var affectedRows = _db.Database.ExecuteSqlCommand(sqlBatchUpdate, linqParams.ToArray());

That's it! Now I don't have to repeat same business logic in stored procedure again. And it is more faster than a foreach and SaveChanges combo.

So I ended up with this for very basic usage. As a fast solution It brings more problems no doubt! But I know I can easily wrap around it for new purposes. So It is up to programmer who wants to use it with more preferences.

Also the code which does the reflection and casting is below and I added a gist for full code:

public static ObjectQuery<T> GetObjectQuery<T>(this DbQuery<T> query)
{
    var internalQueryField = query.GetType()
        .GetFields(BindingFlags.NonPublic | BindingFlags.Instance)
        .Where(f => f.Name.Equals("_internalQuery"))
        .FirstOrDefault();
    var internalQuery = internalQueryField.GetValue(query);
    var objectQueryField = internalQuery.GetType()
        .GetFields(BindingFlags.NonPublic | BindingFlags.Instance)
        .Where(f => f.Name.Equals("_objectQuery"))
        .FirstOrDefault();
    var objectQuery = objectQueryField.GetValue(internalQuery) as ObjectQuery<T>;
    return objectQuery;
}

Here is the Gist file. Hope It helps somebody out there.

Upvotes: 3

Related Questions