Using C#, convert a lambda expression to a SQL UPDATE query.

c# entity-framework lambda

Question

Is it possible to produce SQL Update statements using lambda expressions using a library or code? To do changes, we would like to utilize strongly-typed lambda expressions as opposed to calling the object beforehand or using strings. I have an idea along these lines.

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

This closely corresponds to.

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

I should add that Postgres and the Entity Framework are presently being used.

1
4
7/4/2017 7:50:49 AM

Popular Answer

I at last discovered how to do this. Basically, parse the WHERE clause after obtaining the produced SQL from Entity Framework, LINQ-to-SQL, or another ORM. I won't need to manually parse the lambda this way. Then, from an anonymous type, build a UPDATE clause. The outcome 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");

I can change and remove values using this technique without first extracting them.

And here is how the code for it appears.

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\"");
}
7
3/9/2011 12:31:51 AM


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow