Batch Insert using Linq To SQL (2)

The BatchInsert command is now capable of generating a DbCommand which INSERTs INTO a table from a SELECT statement defined by a Linq query.

However, the current solution does not allow to assign columns of the inserted table to columns of the SELECT statement – the columns are mapping in order of the table definition and query definition.

An additional List<> and a column assignment method is necessary:

public class BatchInsert<T, S>
{
    List<Tuple<LambdaExpression, LambdaExpression>> setClauses = 
        new List<Tuple<LambdaExpression, LambdaExpression>>();

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

We also need to extend the ToMSSqlString() method to handle the new collection:

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

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

        return sb.ToString();
    }

The ToDbCommand() method needs to be extended to SELECT the columns from the query in the order defined by the setClauses collection:

    public DbCommand ToDbCommand(System.Data.Linq.DataContext context)
    {
        var command = context.GetCommand(source);
        //var command = context.Connection.CreateCommand();
        var sb = new StringBuilder();
        sb.Append(this.ToMSSqlString());

        if (insertClauses.Count > 0)
            sb.AppendLine(command.CommandText);
        else if (setClauses.Count > 0)
        {
            var sep = "SELECT ";
            foreach (var set in setClauses)
            {
                sb.Append(sep + set.Item2.ToMSSqlString());
                sep = ", ";
            }
            sb.AppendLine();
            sb.AppendLine("FROM (");
            sb.AppendLine(command.CommandText);
            sb.AppendLine(") AS T");
        }
        else
            throw new ArgumentException("no columns defined");

        command.CommandText = sb.ToString();

        return command;
    }

Using the FooMax table for some examples:

Creating a BatchInsert command without column mapping

var database = new DataContext();
var id = Guid.NewGuid().ToString();

var query = database.FooMaxes.Where(fm => fm.OID == 2)
    .Select(fm => 
        new { newID = fm.ID + " " + id, newText = fm.SomeText + " upd" });

var insert = BatchInsert<FooMax>.From(query)
    .Insert(fm => fm.ID)
    .Insert(fm => fm.SomeText);

var cmd = insert.ToDbCommand(database);

Console.WriteLine(cmd.CommandText);
foreach (System.Data.Common.DbParameter p in cmd.Parameters)
    Console.WriteLine(p.ParameterName + ": " + p.Value.ToString());
Console.WriteLine();

results in:

INSERT INTO dbo.FooMax
    ([ID], [SomeText])
SELECT ([t0].[ID] + @p1) + @p2 AS [newID], [t0].[SomeText] + @p3 AS [newText]
FROM [dbo].[FooMax] AS [t0]
WHERE [t0].[OID] = @p0

@p0: 2
@p1:
@p2: 55125f61-d96a-4ef8-8185-9419ffa2a80b
@p3:  upd

Creating a BatchInsert command with column mapping

var insert = BatchInsert<FooMax>.From(query)
    .Set(fm => fm.SomeText, q => q.newText)
    .Set(fm => fm.ID, q => q.newID);

cmd = insert.ToDbCommand(database);

Console.WriteLine(cmd.CommandText);
foreach (System.Data.Common.DbParameter p in cmd.Parameters)
    Console.WriteLine(p.ParameterName + ": " + p.Value.ToString());
Console.WriteLine();

var inserted = database.Execute(insert);

results in:

INSERT INTO dbo.FooMax
    ([SomeText], [ID])
SELECT [newText], [newID]
FROM (
SELECT ([t0].[ID] + @p1) + @p2 AS [newID], [t0].[SomeText] + @p3 AS [newText]
FROM [dbo].[FooMax] AS [t0]
WHERE [t0].[OID] = @p0
) AS T

@p0: 2
@p1:
@p2: 55125f61-d96a-4ef8-8185-9419ffa2a80b
@p3:  upd