Partager via

Rolling your own SQL Update on top of the Entity Framework - Part 4

  • Article

Okay so we've got to the interesting bit at last.

We've hooked up our extension method, we've got a query for filtering the records we are updating, we've got a map from object Properties to database Columns. So now comes the interesting bit...

We now have to build the UPDATE [Table] SET Column = value, .... WHERE Key IN (...) query.

Basically we need to convert the Expression<Func<T,T>>

public SqlCommand GetUpdateCommand(Expression<Func<T, T>> updator){
    //Get the information we need from the QUERY
    QueryInfo info = GetQueryInfo();

In Part 3 we walked through GetQueryInfo() so lets just move on:

  //Make sure that we are doing a new T(){Column1=Value1,Column2=Value2} in the Updator expression
    MemberInitExpression expr = updator.Body as MemberInitExpression;
    if (expr == null) throw new InvalidOperationException("Attempt to use an unsupported expression: " + updator.ToString());

This bit of code, basically does what it says, it makes sure that the update expression is a MemberInitExpression, i.e. a constructor call, with C# 3.0 style property initialization.

Then we build a list of set statements (one for each Property initialized in the constructor).

To do that we loop through the Bindings, the name of the binding, is the Property name. We then use our fieldMap to lookup the Column name corresponding to that Property. Then we just need a way of converting the MemberBinding to a T-SQL SET statement and a SqlParameter.

  List<string> setStatements = new List<string>();
  SqlCommand command = new SqlCommand();

    foreach (MemberBinding binding in expr.Bindings)
    {
        string name = binding.Member.Name;

        Utilities.Assert(info.C_S_fieldMap.Keys.Contains(name), () => new InvalidOperationException("Attempt to update a Property that is not part of the primary Extent: " + name));

Utilities.Assert(binding.BindingType == MemberBindingType.Assignment, () => new InvalidOperationException("You can only use assignments: " + binding.Member.ToString()));

        MemberAssignment assignment = binding as MemberAssignment;
        if (assignment.Expression.NodeType == ExpressionType.Constant)
        {
            ConstantExpression constant = assignment.Expression as ConstantExpression;
            command.Parameters.AddWithValue("@" + name, constant.Value);
        }
        else if (assignment.Expression.NodeType == ExpressionType.Convert)
        {
            command.Parameters.AddWithValue("@" + name, Utilities.Invoke(assignment.Expression));
        }
        else
        {
            throw new NotImplementedException("Currently only constant values are supported: " + assignment.Expression.ToString());
        }
        setStatements.Add(string.Format("{0}=@{0}", name));
    }

As you can see, here I only handle a couple of types of assignment expressions (Constant and Convert expressions), but you can easily add support for more yourself.

Now are are almost done.

We just need to borrow the StoreConnection for our SqlCommand, and assembly the update statement.

    command.Connection = Context.StoreConnection;
    //Check we are actually doing something:
    Utilities.Assert(command.Parameters.Count != 0, () => new InvalidOperationException("You have to update something"));

    //Build the update
    command.CommandText = string.Format(
           "UPDATE {0} SET {1} WHERE {2} IN ({3})",
           info.TableName,
           string.Join(",",
setStatements.ToArray()),
Context.Keys[0],
info.RestrictingSQL
);
   return command;
}

Tada...

And it only took 4 rambling posts and 10 weeks to describe it!

Comments