Insert/Select with Linq-To-SQL

Question

Is there a way to do an insert/select with Linq that translates to this sql:

INSERT INTO TableA (...)
SELECT ...
FROM TableB
WHERE ...

Answer 1

Yes @bzlm covered it first, but if you prefer something a bit more verbose:

// dc = DataContext, assumes TableA contains items of type A
var toInsert = from b in TableB
               where ...
               select new A 
               {
                   ...
               };

TableA.InsertAllOnSubmit(toInsert);
dc.SubmitChanges();

I kind of prefer this from a review/maintenance point of view as I think its a bit more obvious what's going on in the select.

---

In response to the observation by @JfBeaulac :

Please note that this will not generate the SQL shown - so far as I'm aware it's not actually possible to generate directly using Linq (to SQL), you'd have to bypass linq and go straight to the database. Functionally its should achieve the same result in that it will perform the select and will then insert the data - but it will round-trip the data from the server to the client and back so may not be optimal for large volumes of data.

Answer 2

context
  .TableA
  .InsertAllOnSubmit(
    context
      .TableB
      .Where( ... )
      .Select(b => new A { ... })
  );