entity framework - EF Linq query with conditional include -

so have following linq query:

var member = (from mem in                context.members.include(m =>                m.memberprojects.select(mp => mp.project))               mem.memberid == memberid              select mem).firstordefault(); 

this returns member entity, set of memberprojects have project child. limit memberprojects project child has property projectidparent == null.

one of failed attempts might make intent clearer:

var member = (from mem in context.members              .include(m => m.memberprojects                             .where(mp =>                                     mp.project.projectidparent == null)              .select(proj => proj.project))              mem.memberid == memberid              select mem).firstordefault(); 

this of course complains of invalid include expression because of clause.

any thoughts on how great :)

disclaimer: havent tested this. idea. if let me know results, update accordingly. (skip update part tested solutions)

var member = (from mps in context.memberprojects                                 .include(m => m.members)                                 .include(m => m.projects)                 mps.project.projectidparent == null               select mps)               .firstordefault(mprojs => mprojs.member.memberid == memberid);      

i'd analyze queries using efprofiler make sure generated queries dont leave realm of sanity.

you can take @ this post jimmy bogard on many many relationships orms.

update

i came multiple tested solutions ef 6.1.3. edmx looked below:

the setup data below:

i able run code below memberfive correctly

var member = context.members.firstordefault                      (m => m.memberid == memberid                           && m.projects.any(p => p.projectparentid == null)); 

the generated sql looked this:

select top (1) [extent1].[memberid]   [memberid],            [extent1].[membername] [membername]   [dbo].[members] [extent1]  ([extent1].[memberid] = 1)    , (exists (select 1 [c1]                   (select [memberprojects].[memberid]  [memberid],                                [memberprojects].[projectid] [projectid]                           [dbo].[memberprojects] [memberprojects])                 [extent2]                        inner join [dbo].[projects] [extent3]                          on [extent3].[projectid] = [extent2].[projectid]                  ([extent1].[memberid] = [extent2].[memberid])                        , ([extent3].[projectparentid] null))) 

if dont generated query can use this:

 var memberquery = @"select m.* members m                             inner join memberprojects mp on m.memberid = mp.projectid                             inner join projects p on mp.projectid = p.projectid                             m.memberid = @memberid , p.projectparentid null";  var memberparams = new[]                      {                       new sqlparameter("@memberid", 1)                      };  var member3 = context.members.sqlquery(memberquery, memberparams)                .firstordefault(); 

the later consistently returned under 20ms vs other 1 hovered around 60ms (if matters you).

i hope helps.