sql server - Linq to Entities with multiple related entities performance and UNIONs -

sql server - Linq to Entities with multiple related entities performance and UNIONs -

i'm building search logic (for mvc application) , set - i'm stuck!

i've spent few days building linq entities query utilize projection. on surface until looked @ generated sql statement underneath. gasp!

i next spent few more days trying rid these union statements sql statements, trying joins, froms, includes, give me inner/left outer joins related entities loaded.

but haven't been able , still stuck @ point.

some points consider:

l2e query contains multiple related (child) entities projection (no issue here unless bad design, i.e. causing unions) created summary classes fit anonymous types, e.g. studentsummary summary classes contain few of columns of entity object's columns performance reasons - don't need homecoming columns on initial search recently started using predicatebuilder homecoming students search, e.g. male students specialization... in area (so far).

here i've done far (code changed hide proprietary property - may have typos):

query = s in context.students select new studentsummary { studentid = s.studentid, title = s.title, fullname = s.fullname, qualifications = s.qualifications, gender = s.gender, campussummaries = ( c in s.campuses select new campussummary { name = c.name, locationsummary = new locationsummary { state = c.location.state } }), specializationsummaries = ( sp in s.studentspecializations select new studentspecializationsummary { studentspecializationid = sp.studentspecializationid, specializationsummary = new specializationsummary { name = s.specialization.name } }), subspecializationsummaries = ( ssp in s.studentsubspecializations select new studentsubspecializationsummary { studentsubspecializationid = ssp.studentsubspecializationid, subspecializationsummary = new subspecializationsummary { name = s.subspecialization.name } }) };

my projection statements appear create entity framework generate next sql union statements, below snippet (code changed hide proprietary property - may have typos):

{select [unionall1].[studentid] [c1], [unionall1].[studentid1] [c2], [unionall1].[title] [c3], [unionall1].[fullname] [c4], [unionall1].[qualifications] [c5], [unionall1].[gender] [c6], [unionall1].[c1] [c9], [unionall1].[studentspecializationid] [c10], [unionall1].[studentspecializationid1] [c11], [unionall1].[studentspecializationid2] [c12], [unionall1].[name] [c13], [unionall1].[c2] [c14], [unionall1].[c3] [c15], [unionall1].[c4] [c16], [unionall1].[c5] [c17] (select case when ([join1].[studentspecializationid] null) cast(null int) else 1 end [c1], [extent1].[studentid] [studentid], [extent1].[studentid] [studentid1], [extent1].[title] [title], [extent1].[fullname] [fullname], [extent1].[qualifications] [qualifications], [extent1].[gender] [gender], [join1].[studentspecializationid] [studentspecializationid], [join1].[studentspecializationid] [studentspecializationid1], [join1].[studentspecializationid] [studentspecializationid2], [join1].[name] [name], cast(null int) [c2], cast(null int) [c3], cast(null int) [c4], cast(null varchar(1)) [c5] [dbo].[student] [extent1] left outer bring together (select [extent2].[studentspecializationid] [studentspecializationid], [extent2].[studentid] [studentid], [extent3].[name] [name] [dbo].[studentspecialization] [extent2] inner bring together [dbo].[specialization] [extent3] on [extent2].[specializationid] = [extent3].[specializationid] ) [join1] on [extent1].[studentid] = [join1].[studentid] union select 2 [c1], [extent4].[studentid] [studentid], [extent4].[studentid] [studentid1], [extent4].[title] [title], [extent4].[fullname] [fullname], [extent4].[qualifications] [qualifications], [extent4].[gender] [gender], cast(null int) [c2], cast(null int) [c3], cast(null int) [c4], cast(null varchar(1)) [c5], [join3].[studentsubspecializationid] [studentsubspecializationid], [join3].[studentsubspecializationid] [studentsubspecializationid1], [join3].[studentsubspecializationid] [studentsubspecializationid2], [join3].[name] [name] [dbo].[student] [extent4] inner bring together (select [extent5].[studentsubspecializationid] [studentsubspecializationid], [extent5].[studentid] [studentid], [extent6].[name] [name] [dbo].[studentsubspecialization] [extent5] inner bring together [dbo].[subspecialization] [extent6] on [extent5].[subspecializationid] = [extent6].[subspecializationid] ) [join3] on [extent4].[studentid] = [join3].[studentid]) [unionall1] order [unionall1].[studentid1] asc, [unionall1].[c1] asc}

i'm not sure why unions beingness used here. perhaps i'm using projection poorly. although have noticed when 2 or more projections included in l2e statement unions appears in sql.

i've tried going in direction removing projections unsuccessfully tried using joins, froms... can't seem related kid entities beingness returned.

i can't utilize include i'm using summary objects performance reasons.

i'm questioning if i'm going correctly now, i.e. going in right direction of returning nested related (child) entities?!?! perhaps should returning flat results, e.g. students, campuses, specializations... objects @ same level, i.e. remove hierarchy.

question:

if built t-sql using inner & left outer joins homecoming related kid nested entities , filter students. i'm trying accomplish linq entities. how can this? e.g. joins, projection, or whatever... (without unions , cast(null)s...

appreciate help, if it's pointing me in right direction, i'm stuck here. thanks.

is query performing badly? if not, don't worry generated sql. using union all may in fact best way accomplish this. creating 1 complex query multiple joins may slower multiple simple queries combined union all

sql-server linq entity-framework projection