c# - How to avoid N+1 in EF generated queries -

i used generate helper tables usergroupusers many-to-many relations or relational ids in poco myself want ef take care of them. don't think it's such idea after all.

problem

when try usergroupdynamicfield particular user generates n+1 query every usergroup user in.

here overcommed problem stating usergroups iqueriable instead of ienumerable. cannot because ef won't map it, has icollection.

code

public class user {     ...     public virtual icollection<usergroup> usergroups { get; set; }     public ienumerable<userfield> fields     {                 {             var fields = this.usergroups.selectmany(x => x.usergroupdynamicfields); // n + 1 every usergroup              foreach (var field in fields)             {                 yield return new userfield                 {                     name = field.name                 };             }         }     } } 

database

here overcommed problem stating usergroups iqueriable instead of ienumerable. cannot because ef won't map it, has icollection.

but class ends implementing icollection entitycollection<t>. collection has createsourcequery() function can use:

var usergroupsquery = ((entitycollection<usergroup>)this.usergroups).createsourcequery(); var fields = usergroupsquery.selectmany(x => x.usergroupdynamicfields); 

update: pointed out in comments, icollection<t> implemented using entitycollection<t> when change tracking possible , enabled (non-sealed classes, , relevant properties virtual). can create query way:

var usergroupsquery = db.entry(this).collection(u => u.usergroups).query(); var fields = usergroupsquery.selectmany(x => x.usergroupdynamicfields); 

note requires have access db somehow.