c# - Filter linq/entity query results by related data -
i'm using mvc5 ef6 , identity 2.1.
i have 2 classes:
public class incident { public int incidentid {get; set;} ...//title, description, etc public virtual icollection<followedincident> followedincidents { get; set; } public virtual applicationuser user { get; set; } } public class followedincident { public int followedincidentid { get; set; } public string userid { get; set; } public int incidentid { get; set; } public virtual incident incident { get; set; } public virtual applicationuser user { get; set; } }
so, users have ability follow incident. (for starters, i'm not entirely sure if need icollection , public virtual relationship references, added them in case time being.)
i'm trying create query show users results of followed incidents. in controller, query starts (i'm using troy goode's paging package... i.e. listunpaged):
iqueryable<incident> listunpaged = db.incidents.orderbydescending(d => d.incidentdate);
then want filter followed incidents. so, want show incidents userid (parameter pass it) equal userid in followedincident. i've tried (error conversion bool ienumerable):
listunpaged = listunpaged.where(s => s.followedincidents.where(t => t.userid == userid));
and (no error, doesn't filter @ all):
listunpaged = listunpaged.where(s => s.followedincidents.all(t => t.userid == userid));
to me, seems should simple this:
listunpaged = listunpaged.where(s => s.followedincidents.userid == userid));
but, linq extensions don't seem related data child properties? (i apologize programming terminology haven't quite pieced names yet.)
anyone know how accomplish this? seems may not thinking correct? (...since in past, i've used related data supplement or add properties result. first time want narrow results related data.)
thank you.
actually you're going getting incidents wrong way.. since incident navigation property of followedincident should use
iqueryable<incident> listunpaged = db.followedincidents .where(a => a.userid == userid) .select(a => a.incident) .orderbydescending(d => d.incidentdate);
another option use any()
iqueryable<incident> listunpaged = db.incidents .where(a => a.followedincidents.any(b => b.userid == userid) .orderbydescending(d => d.incidentdate);
which saying
select * incidents id in (select incidentid followedincident userid = @userid)
Comments
Post a Comment