Hi
I am trying to use Linq-to-SQL to select some values from a db and insert a new record based on those values. In SQL I would write it like:
INSERT INTO RunPlatform(
platform_id,
build_env,
go_args,
)SELECT
pp.platform_id,
pp.build_environment,
pp.go_args,
FROM
DecoPlatforms dp
INNER JOIN ProductPlatforms pp ON dp.prodplat_id = pp.prodplat_id
WHERE dp.deco_id = 555
I tried writing this using linq extensions like so:
db.RunPlatforms.InsertAllOnSubmit
(
DecoPlatform.QueryByDecoId(Run.GetDecoIdFromRun(runId, db),db)
.Join(
ProductPlatform.Query(db),
dp => dp.prodplat_id,
pp => pp.prodplat_id,
(dp, pp) => pp
)
.Where(pp => buildOnlyNotTestOnly == null || pp.build_environment == ((bool)buildOnlyNotTestOnly ? "T" : "F"))
.Select(pp => new RunPlatform
{
platform_id = pp.platform_id,
build_env = pp.build_environment,
go_arg = pp.go_arg,
bits = pp.bits,
baseline_id = (int)pp.baseline_id,
t3_osmap = pp.t3_osmap
})
);
Each [object].Query(db) simply checks if context (db) is null, creates a new db instance only if it is null, and returns the db.[object]s as an IQueriable<[object]>. Any [object].Query...() may perform some actions to narrow the IQueryable<[object]> set but each checks the db for null and always passes it's own instance down the line finally to [object].Query(db). I have included the DecoPlatform class below as an example of this.
[DecoPlatform class]
partial class DecoPlatform
{
public static IQueryable Query
(
CarmaContext db = null
)
{
if (db == null)
db = new CarmaContext();
return db.DecoPlatforms;
}
public static IQueryable QueryByDecoId
(
int decoId,
CarmaContext db = null
)
{
if (db == null)
db = new CarmaContext();
return Query(db).Where(dp => dp.deco_id == decoId);
}
}
But I get the error 'Explicit construction of entity type 'SQLDataAccess.CarmaEntityFramework.RunPlatform' in query is not allowed.'.
Question: How do I manage to get the IEnumerable<runplatform> list from the linq?
For now I have two phases; First a IEnumerable<productplatform> select and then the context insert all with the new RunPlatform class'.
I could use this solution but I hoped the problem was simple / easy enough to investigate. I mean, I gotta learn sometime, right? ^_^
Thanks in advance
G