CODEWITHSUNDEEP
c#sql-serverentity-frameworklinq
AliOsat Mostafavi
AliOsat Mostafavi

Reputation: 373

How can i write SQL update query with where clause in Entity Framework in C#

I know that for update a model in EF i must get model then change it then dbContext.SaveChanges() but sometimes in my Asp.net mvc project , i want update a field in my table and i don't know it 's id and must be get it with where clause. but i don't want connect twice to database , because in ADO.net i can write:

UPDATE MyTable SET Field3 = "NewValue" WHERE Active = 1

and now i want write a linq to sql for EF that work like that. Is exist any way for that? thanks

Upvotes: 10

Views: 24748

Answers (3)

Gilden00
Gilden00

Reputation: 21

This is something you can do as of EF 7, take a look at Efficient Updating for more details.

To do what you're looking for would be

dbContext.MyTable
    .Where(mt => mt.Active == 1)
    .ExecuteUpdate(mt => mt.SetProperty(e => e.Field3, e => "New Value"));

Unlike older solutions, this will operate as a single database operation. The Efficient Update article also discusses other uses.

Upvotes: 2

Backs
Backs

Reputation: 24903

You can't. EF is ORM, that means, you need to work with separate objects (update them one by one).

Look at EntityFramework.Extended library for that:

//update all tasks with status of 1 to status of 2
context.Tasks
    .Where(t => t.StatusId == 1)
    .Update(t => new Task { StatusId = 2 });

For EF Core: EntityFramework-Plus

Upvotes: 11

IvanJazz
IvanJazz

Reputation: 773

You can use the raw query function in EF.

var query = "UPDATE MyTable SET Field3 = 'NewValue' WHERE Active = 1";
using (var context = new YourContext()) 
{ 
    context.Database.ExecuteSqlCommand(query); 
}

Upvotes: 5

Related Questions