Geek asks and answers

Update multiple rows without selecting

An old question for Linq 2 objects. I am just asking again if someone came up with a solution.

I want to execute a query that does this:

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

And I want to do this with Entity Framework 4.3.1.

This is just an example, I have tons of records, I just want 1 column to change the value, nothing more. Download to DbContext using Where (...). Select (...) by changing all the elements and then save using SaveChanges (), for me this will not work.

Should I stick to ExecuteCommand and send a direct request as written above (of course, make it reusable) or is there another good way to do this from Linq 2 Entities / Fluent.

Thank!

+3
source share
5

, , Entity Framework. ,

+1

db,

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

    // Update fields

    context.SaveChanges();
}
0

, SQL , EF.

, - ExecuteCommand.

0

, , , , . :

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

UpdateProductsCategory .

0

Yes, ExecuteCommand () is definitely a way to do this without fetching all row data and not allowing ChangeTracker to sort it. Just to give an example:

This will cause all rows to be extracted, and the update for each row will change:

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

Just one 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);
    }
0
source

More articles:


All Articles