Batch Insert using Linq To SQL

Whereas Batch Update operates on a single table, and therefore on a single C# class, Batch Insert operates on two C# tables and types:

• the type to be inserted, which is known from the class representing the table definition
• the type of the result set of a query, which is either an IQueryable<table-type>,
• or an IQueryable<anonymous-type> in case of select new { }

Since we have to deal with two types, the BatchInsert command class has to be declared as BatchInsert<Table, Query>.

However, since the type of the query may be an anonymous type, we need to use type inference to create a BatchInsert object using a helper class BatchInsert<Table>:

public static class BatchInsert<T>
{
    public static BatchInsert<T, S> From<S>(IQueryable<S> source)
    {
        return new BatchInsert<T, S>(source);
    }
}

The BatchInsert class looks similar to the BatchUpdate class:

public class BatchInsert<T, S>
{
    IQueryable<S> source;
    List<LambdaExpression> insertClauses = new List<LambdaExpression>();

    public BatchInsert(IQueryable<S> source)
    {
        this.source = source;
    }

    public BatchInsert<T, S> Insert<P>(Expression<Func<T, P>> columnClause)
    {
        insertClauses.Add(columnClause);
        return this;
    }

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

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

        if (insertClauses.Count > 0)
        {
            foreach (var set in insertClauses)
            {
                sb.Append(sep + set.ToMSSqlString());    
                sep = ", ";
            }
            sb.AppendLine(")");

        }
        return sb.ToString();
    }

The ToMSSqlString() method generates the INSERT INTO statement with the column declaration, but we still miss the SELECT part of the statement, which can be retrieved using the DataContext.GetCommand() method

    public DbCommand ToDbCommand(System.Data.Linq.DataContext context)
    {
        var command = context.GetCommand(source);
        var sb = new StringBuilder();
        sb.Append(this.ToMSSqlString());
        sb.AppendLine(command.CommandText);
        command.CommandText = sb.ToString();
        return command;
    }
}

Since we now have a DbCommand with a valid Connection property, we can simply execute it:

public static class DataContextExtensions
{
    public static int Execute<T, S>(
        this System.Data.Linq.DataContext context, BatchInsert<T, S> insert)
    {
        var cmd = insert.ToDbCommand(context);
        cmd.Connection.Open();
        int result;
        try
        {
            result = cmd.ExecuteNonQuery();
        }
        finally
        {
            cmd.Connection.Close();
        }
        return result;
    }
}