How to do INSERT INTO X(Y,Z) SELECT(Y,Z FROM X WHERE ...)

[jpdomenge]

Hi,

I want to perform the followinq sql query in LinqToSql

INSERT INTO X(Y,Z) SELECT(Y,Z FROM X WHERE ...)

I tried the following :


var queryToInsert = from c in ObjectContext.EntiteGroupes
where c.IdGroupe == oldId
select new EntiteGroupe
{
IdGroupe = groupe.IdGroupe,
IdEntite = c.IdEntite,
TypeDroit = c.TypeDroit
};
ObjectContext.EntiteGroupes.InsertAllOnSubmit(queryToInsert);

Or the same version with another syntax :

ObjectContext.EntiteGroupes.InsertAllOnSubmit(
ObjectContext
.EntiteGroupes
.Where(c => c.IdGroupe == oldId)
.Select(b => new EntiteGroupe { IdGroupe = groupe.IdGroupe, IdEntite = b.IdEntite, TypeDroit = b.TypeDroit })
);

but I got an exception : Explicit construction of entity type 'EntiteGroupe' in query is not allowed.


The aim of the query is to copy rows of EntiteGroupe of a group to a new one without having x queries insert to perform.

This is really important because it's a huge performance limitation.

I know that the alternative is to use ObjectContext.ExecuteCommand, like "ObjectContext.ExecuteCommand("INSERT INTO ENTITE_GROUPE(ID_ENTITE, ID_GROUPE, TYPE_DROIT, RM, EM)(SELECT ID_ENTITE, {0}, TYPE_DROIT, {1}, {2} FROM ENTITE_GROUPE WHERE ID_GROUPE = {3})", groupe.IdGroupe, groupe.Rm, groupe.Em, ancienId);
"
but we lose the benefit of Linq controlling type, name and so on.

I read somewhere that this syntax was okay at the beginning of Linq but was denied cause it could "pollutes the cache with potentially malformed objects" but I'm not interested in using the object created by the request.

With devart, is there the same limitation (it seems), is there an alternative having linq generates the sql query expected ?


Best regards.

[crazypit]

I would also like to see this supported by LinqConnect. All my bulk inserts are performed through ExecuteQuery now and this is not good.

[StanislavK]

Thank you for your suggestion, we will consider supporting 'insert into ... (select ...)' commands. We will post here when our investigation is completed.

Also, you can suggest this feature at our UserVoice:
http://devart.uservoice.com/forums/80267-linqconnect

JIC: LinqConnect supports batch inserts, and copying you've described can actually be executed with only two calls to the server:

Code: Select all

var queryToInsert = from c in ObjectContext.EntiteGroupes 
  where c.IdGroupe == oldId 
  select c;

List list = new List();
foreach (EntiteGroupe eg in queryToInsert)
 list.Add(new EntiteGroupe { ... });

ObjectContext.EntiteGroupes.InsertAllOnSubmit(list);

[jpdomenge]

JIC: LinqConnect supports batch inserts, and copying you've described can actually be executed with only two calls to the server:

Code: Select all

var queryToInsert = from c in ObjectContext.EntiteGroupes 
  where c.IdGroupe == oldId 
  select c;

List list = new List();
foreach (EntiteGroupe eg in queryToInsert)
 list.Add(new EntiteGroupe { ... });

ObjectContext.EntiteGroupes.InsertAllOnSubmit(list);

Yes at the end the inserted datas are the same, but your method generates x queries Insert instead of 1 which is really bad for performance.

[StanislavK]

LinqConnect should execute these insert commands in batches, i.e., several insert statements should be wrapped in a single command and executed during one call to the server:
http://www.devart.com/linqconnect/docs/ ... dates.html
Please tell us if you are experiencing different behaviour.

This gives better performance than merely executing multiple insert commands one-by-one, though, of course, this is slower than 'insert into ... (select ...)' statements. We will inform you when any new information regarding the latter feature is available.