LINQ to Entities - Three table join queries

Question

I have a little problem with a query in Linq to Entities, which I hope someone can shed some light on :-) I'm trying to create a query that joins three tables.

So far this works, but since the last table I'm trying to join is empty, the query result does not contain any records. When I delete the last connection, it gives me the correct results.

My query looks like this:

var query = from p in db.QuizParticipants
            join points in db.ParticipantPoints on p.id 
            equals points.participantId into participantsGroup
            from po in participantsGroup
            join winners in db.Winners on p.id 
            equals winners.participantId into winnersGroup
            from w in winnersGroup
            where p.hasAttended == 1 && p.weeknumber == weeknumber
            select new
            {
                ParticipantId = p.id,
                HasAttended = p.hasAttended,
                Weeknumber = p.weeknumber, 
                UmbracoMemberId = p.umbMemberId,
                Points = po.points,
                HasWonFirstPrize = w.hasWonFirstPrize,
                HasWonVoucher = w.hasWonVoucher                                    
            };

I would like to get some records even if the Winners table is empty or there is no match in it.

Any help / hint on this is much appreciated !:-)

Thank you very much in advance.

/ Bo

Answer 1

, , , .

var query = from p in db.QuizParticipants
            where p.hasAttended == 1 && p.weeknumber == weeknumber
            select new
            {
                ParticipantId = p.id,
                HasAttended = p.hasAttended,
                Weeknumber = p.weeknumber, 
                UmbracoMemberId = p.umbMemberId,
                Points = p.ParticipantPoints.Sum(pts => pts.points),
                HasWonFirstPrize = p.Winners.Any(w => w.hasWonFirstPrize),
                HasWonVoucher = p.Winners.Any(w => w.hasWonVoucher)
            };

, hasWonFirstPrize hasWonVoucher , , p.Winners.Any(w => w.hasWonFirstPrize == 1)

Answer 2

, , from w in winnersGroup from w in winnersGroup.DefaultIfEmpty()