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

3 Responses to Batch Insert using Linq To SQL

  1. […] BatchInsert command is now capable of generating a DbCommand which INSERTs INTO a table from a SELECT statement […]

  2. Another rather simple approach to bulk inserting using your linq-to-sql entities is described at http://blog.tanneryd.com/2011/11/speeding-up-inserts-using-linq-to-sql.html

Leave a Reply

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

WordPress.com Logo

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

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s

%d bloggers like this: