c# - LINQ Entity Framework Select into a non-primary-key relationship -

i have 3 class models overlapping relationships:

class worder {   int workerid }     class shift {   int shiftid,   int workerid,   icollection tasks //tasks shift , worker } class task {   int workerid   int shiftid }     

how populate collection of shift objects include respective .tasks? like:

var q = shift in db.shifts   .include(s => s.tasks.where(t=>t.shiftid == s.shiftid && t.workerid = s.workerid)) 

edit

i have used projection create anonymous type suggested jethro. return typed shift classes instead. possible? or poor design of shift model?

you can join tables.

var joinedtables = c in db.shifts                    join p in db.tasks on c.shiftid equals p.shiftid & c.workerid equals p.workerid select new {shifts = c, tasks = p }; 

please check link additional examples on linq joins.

var shifts = db.shifts; var tasks = db.tasks;  foreach(var shift in shifts) {     var shiftid = shift.shiftid;     var workerid = shift.workerid;     shift.tasks = tasks.where(p=>p.shiftid == shiftid & p.workerid == workerid); } 

something above feels "not right" me, think best if @ design again, tasks of shifts should populated automatically when pull shifts.