LINQ-to-SQL equivalent to Insert ... Select ... From ... ?

Question

Is it possible to do this in LINQ to SQL in a single command?

/* Hello, everyone */
Insert into Messages (
   Message,
   ContactID
) 
Select 
   Message='Hello',
   ContactID=ContactID
From Contacts

(I know I could iterate through Contacts, InsertOnSubmit many times, and SubmitChanges at the end; but this generates one Insert command per contact, and repeats the message text each time; it's bulky and slower.)

Answer 1

no, even if you use insertAllOnSubmit and give it the IQueryable with the select, it'll still generate the multiple insert commands.

There is someone that made something like that, but I don't have the link at hand (that'll generate the insert from sql). That said, if you have a simple requirement, I'd just send that sql query directly.

Answer 2

Freddy is right.

In terms of simplifying the Linq what you're looking for is InsertAllOnSubmit() as below, but in terms of not generating a tonne of insert statements Linq to SQL is currently deficient.

InsertAllOnSubmit to avoid a coded loop:

var selquery = from c in Contacts 
               select new Message 
               { 
                 MessageText = "Hello", 
                 ContactID = c.ContactID 
               };
Messages.InsertAllOnSubmit(selquery);

I changed Message to MessageText to avoid naming conflicts...

Answer 3

You could try storing the results of the Select statement into a List and then call InsertAllOnSubmit(List).

Randy

Answer 4

As an alternative way, if you are not using client-side objects, you can try using DataContext.ExecuteCommand.