Performing a INSERT INTO ... SELECT with LINQ to SQL

Question

I'm trying to figure out if it's possible to perform an "insert into ... select" type command with LINQ to SQL. A bit of LINQ to SQL code that would allow me to send one single SQL command to the database that would insert multiple rows into a given table.

For example, how to make LINQ to SQL send the following T-SQL statement to an SQL Server database?

INSERT INTO Table1 
SELECT Table2.column1 + 1 AS column1, Table2.column2 + 2 AS column2
WHERE Table2.column3 > 100

I could of course achieve this by using the DataContext.ExecuteCommand function but this would be executed immediately without taking advantage of the automatic transaction handling you get with DataContext.SubmitChanges. I have a series of updates besides this one and I would like them all rolledbacked in case of an error.

Any ideas?

UPDATE: Here's the actual code:

        var bs_prep =
            from b in dc.T_EDR_FILEBODies
            join
            unpaid in dc.V_UNPAIDs
            on
                b.NUM_ADC.Substring(1, 9) equals unpaid.NOCONT
            join
            acordo in dc.T_ACORDOS_RECOM_APREs
            on
                Convert.ToInt32(b.NUM_ADC.Substring(1, 9)) equals acordo.ID_Contrato
            where
                b.ID_EDR == id_edr
                &&
                (
                    unpaid.NUM_INCUMPRIMENTOS <= max_unpaid_consec
                    &&
                    unpaid.TOTAL_NUM_INCUPRIMENTOS <= max_unpaid_nonconsec
                )
                ||
                (
                    acordo.Activo == true
                    &&
                    acordo.Data_Recomeco <= now
                )
            select new
                {
                    ID_EDR = id_edr_filt,
                    NUM_LINHA = b.NUM_LINHA,
                    CODREJ = b.CODREJ,
                    HDT = b.HDT,
                    IMPORT = b.IMPORT,
                    NIB_DEV = b.NIB_DEV,
                    NUM_ADC = b.NUM_ADC,
                    REF_DD_BC = b.REF_DD_BC,
                    REF_MOV = b.REF_MOV
                }
            ;


        dc.T_EDR_FILEBODies.InsertAllOnSubmit(
            bs_prep.Select(
                b => new T_EDR_FILEBODY{
                    CODREJ = b.CODREJ,
                    HDT = b.HDT,
                    ID_EDR = b.ID_EDR,
                    IMPORT = b.IMPORT,
                    NIB_DEV = b.NIB_DEV,
                    NUM_ADC = b.NUM_ADC,
                    NUM_LINHA = b.NUM_LINHA,
                    REF_DD_BC = b.REF_DD_BC,
                    REF_MOV = b.REF_MOV
                }
            )
        );

Quick explanation: The T_EDR_FILEBODies entity maps to a database table which basically stores the contents of some text files we import. One record corresponds to one line in the text file.

What I'm trying to do is create a filtered version of the file content by copying the records from one file, giving them a new file ID (ID_EDR=id_edr_filt) but filtering out some of the lines. The LINQ to SQL entities are direct mappings to the database tables. I have added no code to my datacontext so far. They do have primary keys, or otherwise I would not be able to do inserts on them (I read somewhere I would be able to get rid of that exception if I got rid of the primary keys but, as you can see, that would not work in my case).

When I run it I get the following exception thrown by InsertAllOnSubmit:

Explicit construction of entity type 'T_EDR_FILEBODY' in query is not allowed.

I guess I understand that explicitally constructing an entity inside a query would be problematic. Entities returned by queries have change tracking, changes are translated into the database when submitchanges is called. But how could you translate, into the database, changes on an entity created on the client side? But does this really mean you can't ever perform an INSERT INTO...SELECT type command using LINQ to SQL?

Answer 1

You can use

ctx.Table1.InsertAllOnSubmit(
    mySelectEnumeration.Select(x => new Table1DT { ... })
);

• InsertAllOnSubmit inserts a number of entries into a Linq to SQL table.
• mySelectEnumeration is the query that selects the items to be inserted.
• Select(new Table1DT { ... }) is the transform required to convert the data type of you select query into the data type of the table.

Alternatively you can use the ExecuteCommand method and manage the transaction manually.

using (var ctx = new DataClasses1DataContext()) {
    ctx.Connection.Open();
    using (ctx.Transaction = ctx.Connection.BeginTransaction()) {
        ctx.ExecuteCommand("sqlcommand");
        ctx.Transaction.Commit();
    }
}

Or using the Transaction scope:

using (var ctx = new DataClasses1DataContext()) {
    using (var scope = new TransactionScope()) {
        ctx.ExecuteCommand("sqlcommand");
        scope.Complete();
    }
}