Insert Select for mass inserts

[zsessary]

Hi, wondering if there is a good way to perform a mass insert without submitting a single insert statement for each row using LINQ.

Code: Select all

insert into Table1 (Table1.Col1, Table1.Col2)
select Table2.Col1, Table2.Col2
from Table2

I've tried a few ways of doing this myself using reflection and getting the real DB table names from the attributes, but was ultimately foiled by the way constants aren't included in the SQL generated from an IQeryable object.

Code: Select all

dim dc as new dataContext
dim aDate as DateTime = #1/1/2001#

dim query = From t in dc.Table1 _
                  Select new with {.ID = t.ID, .Date = aDate}

dim sql = dc.GetCommand(query).CommandText

variable "sql" will actually be "Select id from table1" and not include the date... I'm guessing that way of achieving my goal is not going to work.

Any possible solutions?

Thanks,
zach

[StanislavK]

You can execute plain SQL commands (in particular, 'insert into ... select ...' statements) via the DataContext.ExecuteCommand method.

As for retrieving entity-table mapping at runtime, you can use the DataContext.Mapping property. For example, to get the name of the table associated with a particular entity, you can execute the following code:

Code: Select all

string tableName = context.Mapping.GetTable(typeof(MyEntity)).TableName;

Please tell us if this helps.

[zsessary]

the tablename property is much cleaner than the way i was doing it, so that will be helpful. I was hoping for something a little nicer than string SQL, but didn't have high hopes.

Thanks for the help.