CODEWITHSUNDEEP
sqllinqlinq-to-sql
Herb Caudill
Herb Caudill

Reputation: 50002

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

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.)

Upvotes: 1

Views: 742

Answers (4)

Devart
Devart

Reputation: 122032

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

Upvotes: 0

eglasius
eglasius

Reputation: 36035

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.

Upvotes: 3

Randy Minder
Randy Minder

Reputation: 48522

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

Randy

Upvotes: 1

Murph
Murph

Reputation: 10190

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...

Upvotes: 1

Related Questions