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)
            new Tuple<LambdaExpression, LambdaExpression>(
                columnClause, valueClause));
        return this;

    public BatchUpdate<T> Where(Expression<Func<T, bool>> 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 " +

        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);


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();

3 thoughts on “Batch Update using Linq To SQL

  1. Pingback: Converting Linq Expressions to T-SQL « devioblog

  2. Pingback: Batch Insert using Linq To SQL « devioblog

  3. Pingback: Series: Batch Insert and Batch Update using Linq To SQL « devioblog

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.