Lambda表达式到SQL UPDATE语句使用C# [英] Lambda expression to SQL UPDATE statement using C#
问题描述
库或代码是否可用于从lambda表达式创建SQL Update语句?我们希望使用强类型的lambda表达式来进行更新,而不是在手之前或使用字符串调用对象。我正在想这样的事情。
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)
我应该提到我们正在使用实体框架和Postgres。
I should mention we are currently using the Entity Framework and Postgres.
推荐答案
我终于想出了一个办法。基本上,从实体框架,LINQ到SQL或其他ORM获取生成的SQL,然后解析WHERE子句。这样我就不必手动解析lambda了。然后从匿名类型创建UPDATE子句。结果如下:
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\"");
}
这篇关于Lambda表达式到SQL UPDATE语句使用C#的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!