c# - EntityFramework Linq Left Join Parsing Error - DotNet Core 1.0 -
i'm attempting explicitly join 3 tables using left outer join in linq query , running linq parsing issues. performing inner join parses correctly , returns data using left outer fails.
example:
var query = p in databasecontext.products p.clientid == clientid join l in databasecontext.licenses on p.productid equals l.productid pl pli in pl.defaultifempty() join in databasecontext.articles on p.articleid equals a.articleid pa pai in pa.defaultifempty() select new someentitydto { somethingfromp = p.something, somethingfroml = pli.something, somethingfroma = pai.something };
as both joined tables key off of first table, can test each individually removing other join, e.g., test query p l , p a. these test queries function perfectly. it's possible remove left outer rule , receive proper result.
var query = p in databasecontext.products p.clientid == clientid join l in databasecontext.licenses on p.productid equals l.productid join in databasecontext.articles on p.articleid equals a.articleid select new someentitydto ... rest ...
viewing offending query in sql profiler (top code example) see first 2 tables joined, e.g.:
select p.something, l.something products p left join licenses l on p.productid = l.productid p.clientid = 5 order p.productid
and, right after successful query, 2 queries (identical each other):
select a.articleid, a.something, <all fields, when not specified in query> articles order a.articleid
the outer joined 3 tables return object, long don't attempt access field "a" table. when doing that, recieve null exception error, table never joined.
as stated, removing outer join rule brings joined query.
i have attempted adjust linq query figuring linq parser had issue, no avail:
var query = p in databasecontext.products l in databasecontext.licenses.where(g => g.produktid == p.produktid).defaultifempty() in databasecontext.articles.where(g => g.articleid == p.articleid).defaultifempty() ....
this parses set of cross applys doesn't function @ , profiled query, when copied query editor window, doesn't run @ (as opposed 3 individual queries seen in profiler first code example). have attempted more complicated lambdas, doesn't work.
is error in linq parser? doing wrong? (according multiple answered questions here on explicit left outer joins (as opposed natural associations), i'm doing correctly. but, doesn't parse correctly. i've avoided creating associations can join them without explicitly defining join. potentially required here , won't work without it?
note: each table has complex keys need join based on single key values (the db part of product can't change).
using. dotnet core, entityframework, entityframeworkcore.sqlserver, etc., version 1.0.1.
help?
the short answer use ef6 instead of efcore if absolutely have have complex linq queries on entites, after 1.1 release. there still many things missing in efcore compared ef6.
roadmap here.
in case, kept efcore , used context.entity.fromsql(query) method in order results. allowed me utilize efcore of ef entities, , thereby keeping forward-looking approach application, while allowing special exceptions complicated queries not based on actual entity. plan replace fromsql queries ef core matures.
prior deciding on .fromsql, tested query on view , on stored procedure. in both instances, failed. stored procedures, named parameters not yet implemented, , views not supported unless attempt trick ef thinking view table (which brings own issues).
in order access ef core .fromsql, need install following package: microsoft.entityframeworkcore.relational
Comments
Post a Comment