Insert Select for mass inserts

Insert Select for mass inserts

Postby zsessary » Fri 20 May 2011 23:10

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?

Posts: 10
Joined: Thu 25 Mar 2010 20:05

Postby StanislavK » Mon 23 May 2011 14:41

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.
Devart Team
Posts: 1710
Joined: Thu 03 Dec 2009 10:48

Postby zsessary » Mon 23 May 2011 17:11

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.
Posts: 10
Joined: Thu 25 Mar 2010 20:05

Return to dotConnect for Oracle