Is library or code available to create SQL Update statements from lambda expressions? We would like to use strongly-typed lambda expressions to do updates instead of calling the object before hand, or using strings. I'm thinking of something like this.
Update<Task>(
u => u.UserID = 1, u.TaskCount += 1, //Update
w => w.Priority != "High" && (w.Status != "Complete" || w.Status == null) //Where
);
Which would roughly translate to..
UPDATE Tasks SET UserID = 1, TaskCount = TaskCount + 1
WHERE Priority <> "High" AND (Status <> "Complete" OR Status = null)
I should mention we are currently using the Entity Framework and Postgres.
I finally figured out a way to do this. Basically, get the generated SQL from the Entity Framework, LINQ-to-SQL, or another ORM, then parse the WHERE clause. That way I don't have to parse the lambda manually. Then create an UPDATE clause from an anonymous type. The result looks like:
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 first.
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\"");
}
You can do something like this, but there will be limitations on what can be translated into SQL and what needs to be pulled back to your application.
What you need to do is give your Update
method both an Action
(this is the 'update' part) and an Expression
(as the 'where' clause).
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 call your Update with something like
repository.Update(s => s.Name = "Me", w => w.Id == 4);
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);
}
Usage:
await context.Organisations.Update(s => s.LastDateBasicEvent = LastDayOfSchool, null);
context.SaveChanges();
Tested on EF6
I found this article about building and executing an "SQL update on-top of the Entity Framework". Maybe it's 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
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With