Multiple rows update without select

Question

An old question for Linq 2 Entities. I'm just asking it again, in case someone has came up with the solution.

I want to perform query that does this:

UPDATE dbo.Products WHERE Category = 1 SET Category = 5

And I want to do it with Entity Framework 4.3.1.

This is just an example, I have a tons of records I just want 1 column to change value, nothing else. Loading to DbContext with Where(...).Select(...), changing all elements, and then saving with SaveChanges() does not work well for me.

Should I stick with ExecuteCommand and send direct query as it is written above (of course make it reusable) or is there another nice way to do it from Linq 2 Entities / Fluent.

Thanks!

can you describe what exactly doesn't work for you or how it does not work ? Maybe sample code of what you tried and how it failed ? — Joanna Derks, May 07 '12 at 19:26
There's nothing that's "failed". This is a question about performance, something that I'm not sure is doable with EF. I'm asking for confirmation if I'm right, or there's something I've missed :) — Admir Tuzović, May 07 '12 at 20:34

score 1 · Answer 1 · answered May 07 '12 at 20:10

What you are describing isnt actually possible with Entity Framework. You have a few options,

You can write it as a string and execute it via EF with .ExecuteSqlCommand (on the context)
You can use something like Entity Framework Extended (however from what ive seen this doesnt have great performance)

score 0 · Answer 2 · answered May 07 '12 at 20:03

0

You can update an entity without first fetching it from db like below

using (var context = new DBContext())
{
    context.YourEntitySet.Attach(yourExistingEntity);

    // Update fields

    context.SaveChanges();
}

answered May 07 '12 at 20:03

Yucel

2,603
5
28
40

sorry, this isn't a solution for your question, i didn't undestand question well – Yucel May 07 '12 at 20:13

score 0 · Answer 3 · answered May 07 '12 at 20:08

0

If you have set-based operations, then SQL is better suited than EF.

So, yes - in this case you should stick with ExecuteCommand.

answered May 07 '12 at 20:08

Nick Butler

24,045
4
49
70

score 0 · Answer 4 · answered May 08 '12 at 10:31

I don't know if this suits you but you can try creating a stored procedure that will perform the update and then add that procedure to your model as a function import. Then you can perform the update in a single database call:

using(var dc = new YourDataContext())
{
    dc.UpdateProductsCategory(1, 5);
}

where UpdateProductsCategory would be the name of the imported stored procedure.

score 0 · Answer 5 · answered Jul 07 '17 at 11:48

Yes, ExecuteCommand() is definitely the way to do it without fetching all the rows' data and letting ChangeTracker sort it out. Just to provide an example:

Will result in all rows being fetched and an update performed for each row changed:

    using (YourDBContext yourDB = new YourDBContext()) {
        yourDB.Products.Where(p => p.Category = 1).ToList().ForEach(p => p.Category = 5);
        yourDB.SaveChanges();
    }

Just a single update:

    using (YourDBContext yourDB = new YourDBContext()) {
        var sql = "UPDATE dbo.Products WHERE Category = @oldcategory SET Category = @newcategory";
        var oldcp = new SqlParameter { ParameterName = "oldcategory", DbType = DbType.Int32, Value = 1 };
        var newcp = new SqlParameter { ParameterName = "newcategory", DbType = DbType.Int32, Value = 5 };
        yourDB.Database.ExecuteSqlCommand(sql, oldcp, newcp);
    }

Multiple rows update without select

5 Answers5