Lambda expression for SQL UPDATE statement using C #

Question

Is library or code available to create SQL Update statements from lambda expressions? We would like to use strongly typed lambda expressions to perform updates instead of calling the object before hand or using strings. I think of something like that.

Update<Task>( u => u.UserID = 1, u.TaskCount += 1, //Update w => w.Priority != "High" && (w.Status != "Complete" || w.Status == null) //Where ); 

What roughly translate into ..

 UPDATE Tasks SET UserID = 1, TaskCount = TaskCount + 1 WHERE Priority <> "High" AND (Status <> "Complete" OR Status = null) 

I should mention that we are currently using Entity Framework and Postgres.

Answer 1

I finally figured out how to do this. Basically, get the generated SQL from Entity Framework, LINQ-to-SQL, or another ORM, then parse the WHERE clause. This way, I don't have to manually parse the lambda. Then create an UPDATE clause from an anonymous type. The result is as follows:

 Update<Task>( new { UserID = 1, TaskCount = IncrementOf(1), SomeOtherField = DdNull } //Update w => w.Priority != "High" && (w.Status != "Complete" || w.Status == null) //Where ); Delete<Task>(w => w.UserID == userID && w.Status != "Complete"); 

This allows me to update / delete values ​​WITHOUT pulling them out.

And the code for it looks like this ...

 protected void Update<T>(object values, Expression<Func<T, bool>> where) where T : class { Domain.ExecuteStoreCommand( "UPDATE {0} SET {1} WHERE {2};", GetTableString<T>(), GetUpdateClauseString(values), GetWhereClauseString(where) ); } protected string GetUpdateClauseString(object obj) { string update = ""; var items = obj.ToDictionary(); foreach (var item in items) { //Null if (item.Value is DBNull) update += string.Format("{0} = NULL", GetFieldString(item.Key)); //Increment else if (item.Value is IncrementExpression) update += string.Format("{0} = {0} + {1}", GetFieldString(item.Key), ((IncrementExpression)item.Value).Value.ToString()); //Decrement else if (item.Value is DecrementExpression) update += string.Format("{0} = {0} - {1}", GetFieldString(item.Key), ((DecrementExpression)item.Value).Value.ToString()); //Set value else update += string.Format("{0} = {1}", GetFieldString(item.Key), GetValueString(item.Value)); if (item.Key != items.Last().Key) update += ", "; } return update; } protected string GetWhereClauseString<T>(Expression<Func<T, bool>> where) where T : class { //Get query var query = ((IQueryable<T>)Domain.CreateObjectSet<T>()); query = query.Where(where); ObjectQuery queryObj = (ObjectQuery)query; //Parse where clause string queryStr = queryObj.ToTraceString(); string whereStr = queryStr.Remove(0, queryStr.IndexOf("WHERE") + 5); //Replace params foreach (ObjectParameter param in queryObj.Parameters) { whereStr = whereStr.Replace(":" + param.Name, GetValueString(param.Value)); } //Replace schema name return whereStr.Replace("\"Extent1\"", "\"Primary\""); } 

Answer 2

You can do something similar, but there will be restrictions on what can be translated into SQL and what needs to be returned to your application.

What you need to do is give your Update method both Action (this is part of the "update") and Expression (like the clause "where").

 public void Update(Action<T> updateStatement, Expression<Func<T, bool>> where) { // get your object context & objectset, cast to IQueryable<T> var table = (IQueryable<T>)objectContext.CreateObjectSet<T>(); // filter with the Expression var items = table.Where(where); // perform the Action on each item foreach (var item in items) { updateStatement(item); } // save changes. } 

Then you can invoke your update with

 repository.Update(s => s.Name = "Me", w => w.Id == 4); 

Answer 3

And for those who like extensions:

 public static async Task Update<T>(this DbSet<T> objectContext, Action<T> updateStatement, Expression<Func<T, bool>> where) where T : class { var items = objectContext.AsQueryable(); // filter with the Expression if exist if (where != null) items = items.Where(where); // perform the Action on each item await items.ForEachAsync(updateStatement); } 

Using:

 await context.Organisations.Update(s => s.LastDateBasicEvent = LastDayOfSchool, null); context.SaveChanges(); 

Tested on EF6

Answer 4

I found this article about creating and executing "SQL updates on top of Entity Framework". Perhaps this is useful for you.

http://blogs.msdn.com/b/alexj/archive/2007/12/07/rolling-your-own-sql-update-on-top-of-the-entity-framework-part-1.aspx