Entity Framework: update inside LINQ query

Question

I came across this idea of ​​updating a table inside a LINQ query instead of the first query and updating every object returned by this query.

For example, you can change the value of any property associated with x inside this query:

var Query = from x in EFContext.SomeTable where x.id == 1 // SET X = Model or x.Name = "NewName" select SaveChanges(); 

Can something like this do at all?

Answer 1

I believe that the best way to do this would be to write an extension method that can be done by creating a static class:

 public static class Extensions { public static IEnumerable<T> Remove<T>(this DbSet<T> Input, Func<T, Boolean> Objects) where T : class { var I = Input.Where(Objects).ToList(); for (int i = 0; i < I.Count; i++) { Input.Remove(I[i]); } return Input; } public static IEnumerable<T> Update<T>(this DbSet<T> Input, Func<T, Boolean> Objects, Action<T> UpdateAction) where T : class { var I = Input.Where(Objects).ToList(); I.ForEach(UpdateAction); return I; } } 

Then you can do:

 var Context = new EFContext(); Context.YourTable.Remove(x=> x.Id == 1); Context.SaveChanges(); // OR Context.Update((x=> x.Id == 1), (y)=> {y.Title = "something"}); Context.SaveChanges(); 

Answer 2

From MSDN :

In a query that returns a sequence of values, the query variable itself never contains the query results and only stores the query commands. The query is delayed until the query variable is repeated in a foreach or for foreach . This is called deferred execution ; that is, the execution of the request occurs some time after the request is built. This means that you can complete the request as often as you want. This is useful if, for example, you have a database that is updated by other applications. In your application, you can create a request to receive the latest information and repeat the request, returning updated information each time.

So, your request will be executed when you execute foreach to update your objects. As @recursive said, LINQ is useful when you need to query a collection, rather than specifically updating data.

As additional information, you can also force . This is useful if you want to cache query results, for example, when you want to use some functions that Linq to Entities does not support. To force a query that does not give a single value, you can call the ToList method, the ToDictionary method ToDictionary or the ToArray method in a query or query variable.

Answer 3

You can use method calls and write a ForEach or ForEachWithContinue method that lets you change each element, but EF doesn't know what to do with it anyway, and you will need to use ToList to pull items from EF before you can do anything them to do.

ForEach example (functional purists will not like this, of course):

 public static void ForEach<T>(this IEnumerable<T> pEnumerable, Action<T> pAction) { foreach (var item in pEnumerable) pAction(item); } public static IEnumerable<T> ForEachWithContinue<T>( this IEnumerable<T> pEnumerable, Action<T> pAction ) { foreach (var item in pEnumerable) pAction(item); return pEnumerable; } 

Then:

 EFContext .SomeTable .Where(x => x .id == 1) .ToList() // come out of EF .ForEach(x => x.Name = "NewName"); EFContext.SaveChanges(); 

_{(In fact, List<T> already has a ForEach method, so writing IEnumerable extensions in this case is not absolutely necessary.)}

In principle, EF needs to pull data into memory in order to know that you have changed something, to find out what your changes are, and to know what needs to be saved in order to return to the database. I would also like to consider what you are trying to do when you are overwriting data that neither the user nor the program even considered. How did you determine that it was the data that you would like to overwrite first?

In addition, you can directly write direct SQL queries directly to the database using the ExecuteStoreCommand method, which will be the “normal” way to do this. Sort of:

 EFContext.ExecuteStoreCommand( "UPDATE SomeTable SET Name = {0} WHERE ID = {1};", "NewName", 1 );