Batch Update using Linq To SQL

Given the definition of a Batch Update command

var update = new BatchUpdate<MyTable>()
        .Set(t => t.SomeText, t => t.SomeText + " updated")
        .Set(t => t.AnotherText, t => t.AnotherText + " modified")
        .Where(t => t.ID < 10);

we can start implementing the class like this:

public class BatchUpdate<T>
{
    List<Tuple<LambdaExpression, LambdaExpression>> setClauses =
        new List<Tuple<LambdaExpression, LambdaExpression>>();
    List<Expression<Func<T, bool>>> whereClauses =
        new List<Expression<Func<T, bool>>>();

    public BatchUpdate<T> Set<P>(
        Expression<Func<T, P>> columnClause,
        Expression<Func<T, P>> valueClause)
    {
        setClauses.Add(
            new Tuple<LambdaExpression, LambdaExpression>(
                columnClause, valueClause));
        return this;
    }

    public BatchUpdate<T> Where(Expression<Func<T, bool>> whereClause)
    {
        whereClauses.Add(whereClause);
        return this;
    }

Note that the lambda expressions to reference the table columns are passed as typed Expression<Func<>>, but stored as LambdaExpressions. This is necessary to store the assignments of columns of different data types.

The methods always return the batch command object to allow for a concatenation of method calls in fluent notation.

From the definition of columns assignments and optional WHERE clause, we can generate the T-SQL UPDATE statement:

    public string ToMSSqlString()
    {
        var attr = typeof(T).GetCustomAttributes(
            typeof(global::System.Data.Linq.Mapping.TableAttribute), true);

        var sb = new StringBuilder();
        sb.AppendLine("UPDATE " +
            ((System.Data.Linq.Mapping.TableAttribute)attr[0]).Name);

        var sep = "SET ";
        foreach (var set in setClauses)
        {
            sb.AppendLine(sep + set.Item1.ToMSSqlString() +
                " = " + set.Item2.ToMSSqlString());
            sep = ",   ";
        }

        sep = "WHERE ";
        foreach (var w in whereClauses)
        {
            sb.AppendLine(sep + "(" + w.ToMSSqlString() + ")");
            sep = "AND   ";
        }

        return sb.ToString();
    }

For example, using the FooMax table of one of my previous blogs

var update = new BatchUpdate<FooMax>()
    .Set(fm => fm.SomeText, fm => fm.SomeText + " upd")
    .Set(fm => fm.ID, fm => fm.ID + " id")
    .Where(fm => fm.OID == 1);

Console.WriteLine(update.ToMSSqlString());

will output the following UPDATE statement:

UPDATE dbo.FooMax
SET [SomeText] = [SomeText] + ' upd'
,   [ID] = [ID] + ' id'
WHERE ([OID] = 1)

This statement can be executed using the DataContext.ExecuteCommand() method:

public static class DataContextExtensions
{
    public static int Execute<T>(
        this System.Data.Linq.DataContext context, BatchUpdate<T> update)
    {
        return context.ExecuteCommand(update.ToMSSqlString());
    }
}

by simply calling

var database = new DataContext();
database.Execute(update);