linq - EF 4.3 Code First - Querying a navigation property backwards -

i trying query 1 many relationship cannot figure out how this. problem have id of field want filter lives in join table (not main table)...

its easier illustrate rather explain!!

the 2 classes have are

public class dbuserclient {     public virtual string userid { get; set; }     public virtual int clientid { get; set; }     public virtual datetime assignedon { get; set; }     public virtual datetime? clearedon { get; set; }      // navigation properties     public virtual dbuser user { get; set; }     public virtual dbclient client { get; set; } } 

and

public class dbclient {     public virtual int clientid {get;set;}     public virtual string entityname { get; set; }     public virtual bool deleted { get; set; }      // navigation properties     public icollection<dbuserclient> userclients { get; set; } } 

in program have repository exposes clients i.e.

    public observablecollection<dbclient> clients     {         { return context.clients.local; }     } 

i binding why keen on querying via client refresh "local" collection. can't seem figure out way include userclients add "where" clause.

i have tried like

context.clients.include(c => c.userclients.where(uc => uc.userid == "me"));

but results in following exception "the include path expression must refer navigation property defined on type. use dotted paths reference navigation properties , select operator collection navigation properties. parameter name: path"

this works unfortunately not update "local" collection

from c in context.clients uc in c.userclients uc.clientid == uc.clientid && uc.userid == "me" select new { c.clientid, c.entityname, uc.assignedon }; 

any suggestions on have gone wrong?

cheers abs

edit : looking @ sql profiler above query generates following sql

select  [extent1].[clientid] [clientid],  [extent1].[entityname] [entityname],  [extent2].[assignedon] [assignedon]  [dbo].[client] [extent1] inner join [dbo].[userclient] [extent2] on [extent1].[clientid] = [extent2].  [clientid] ([extent2].[clientid] = [extent2].[clientid]) , (n'me' = [extent2].[userid]) 

this pretty simple , more or less along lines of have written myself if handcrafting sql

however although suggested expression below works , pointed out populates local cache

context.clients   .where(c => c.userclients.any(uc => uc.userid == userid))   .select(c => new { dbclient = c, dbuser = c.userclients.where(uc => uc.userid == userid).firstordefault() }).tolist(); 

it produces following sql. looks alot more complicated needs , assuming have performance implications

exec sp_executesql n'select  [filter2].[clientid] [clientid],  [filter2].[entityname] [entityname],  [filter2].[deleted] [deleted],  [limit1].[userid] [userid],  [limit1].[clientid] [clientid1],  [limit1].[assignedon] [assignedon],  [limit1].[clearedon] [clearedon]   (select [extent1].[clientid] [clientid], [extent1].[entityname] [entityname], [extent1].[deleted] [deleted]      [dbo].[client] [extent1]      exists (select          1 [c1]         [dbo].[userclient] [extent2]         ([extent1].[clientid] = [extent2].[clientid]) , ([extent2].[userid] = @p__linq__0)     ) ) [filter2] outer apply  (select top (1)      [extent3].[userid] [userid],      [extent3].[clientid] [clientid],      [extent3].[assignedon] [assignedon],      [extent3].[clearedon] [clearedon]     [dbo].[userclient] [extent3]     ([filter2].[clientid] = [extent3].[clientid]) , ([extent3].[userid] = @p__linq__1) ) [limit1]',n'@p__linq__0 nvarchar(4000),@p__linq__1 nvarchar(4000)',@p__linq__0=n'me',@p__linq__1=n'me'   

edit ii : after playing around more, have found solution seems fulfill requirement. looking @ sql profiler, happy generated sql. similar of orginal query.

exec sp_executesql n'select  [extent1].[clientid] [clientid],  [extent1].[entityname] [entityname],  [extent1].[deleted] [deleted],  [extent2].[userid] [userid],  [extent2].[clientid] [clientid1],  [extent2].[assignedon] [assignedon],  [extent2].[clearedon] [clearedon]  [dbo].[client] [extent1] inner join [dbo].[userclient] [extent2] on [extent1].[clientid] = [extent2].[clientid] [extent2].[userid] = @p__linq__0',n'@p__linq__0 nvarchar(4000)',@p__linq__0=n'me' 

i assuming there no lazy loading involved here. if confirm grateful

context.clients.join   (     context.userclients,      c => c.clientid,      uc => uc.clientid,      (user, usrclient) => new { dbclient = user, dbuserclient = usrclient }   ).where(uc => uc.dbuserclient.userid == userid).load(); 

you can load clients have @ least 1 user userid = "me":

var clients = context.clients     .where(c => c.userclients.any(uc => uc.userid == "me"))     .tolist(); 

this loads correct clients no user included.

if include users...

var clients = context.clients.include(c => c.userclients)     .where(c => c.userclients.any(uc => uc.userid == "me"))     .tolist(); 

... you'll correctly filtered clients include all users, not user "me".

in order users filtered last approach, projection, best way:

var clientswithuser = context.clients     .where(c => c.userclients.any(uc => uc.userid == "me"))     .select(c => new     {         client = c,         user = c.userclients.where(uc => uc.userid == "me").firstordefault()     })     .tolist(); 

this should update local collection because loading full entities (client , user) in anonymous object list.

edit

the last query in question fine, although it's not ef way write join manually when have navigation properties. sql , query result identical to:

context.userclients.include(uc => uc.client)     .where(uc => uc.userid == userid)     .load(); 

the include in query should translate same inner join hand-written linq join produces.