I've came across this idea of updating a table inside of a LINQ query instead of making the query first, and updating each object returned by that query.
For instance, it is possible to change the value of any property associated with x inside of this query:
var Query = from x in EFContext.SomeTable
where x.id == 1
// SET X = Model or x.Name = "NewName"
select SaveChanges();
Could something like this be done at all?
From MSDN:
In a query that returns a sequence of values, the query variable itself never holds the query results and only stores the query commands. Execution of the query is deferred until the query variable is iterated over in a
foreachorforloop. This is known as deferred execution; that is, query execution occurs some time after the query is constructed. This means that you can execute a query as frequently as you want to. This is useful when, for example, you have a database that is being updated by other applications. In your application, you can create a query to retrieve the latest information and repeatedly execute the query, returning the updated information every time.
So, your query will be executed when you do the foreach to update your entities. As @recursive said, LINQ is useful when you need to do a query over a collection, not to update data specifically.
As an aditional info, you can also force immediate execution. This is useful when you want to cache the results of a query,for example, when you want to use some functionalities that Linq to Entities doesn't support. To force immediate execution of a query that does not produce a singleton value, you can call the ToList method, the ToDictionary method, or the ToArray method on a query or query variable.
I believe the best possible way to do so would be to write an extension method which 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();
Expression<Func<T, Boolean>> Objects (preferably selector, and updateAction, camel case for parameters), then we're getting somewhere.You could use method calls, and write a ForEach or ForEachWithContinue method that lets you modify each element, but EF wouldn't know what to do with it anyway, and you'd have to use ToList to pull the items out of EF before you could do anything to them.
Example of ForEach (functional purists won't 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();
(Actually, List<T> even already has a ForEach method, too, so writing the IEnumerable extensions is not strictly necessary in this case.)
Basically, EF needs to pull the data into memory to know that you have changed anything, to know what your changes are, and to know what to save to back to the DB. I would also consider what it is you're trying to do, where you are overwriting data that neither the user nor the program has even looked at. How did you determine that this was data you wanted to overwrite in the first place?
Also, you can write direct SQL queries straight to the DB as well, using the ExecuteStoreCommand method, which would be the "normal" way of accomplishing this. Something like:
EFContext.ExecuteStoreCommand(
"UPDATE SomeTable SET Name = {0} WHERE ID = {1};",
"NewName",
1
);
