LINQ to Entities three table join query?

If you set these up as related entities instead of doing joins, I think it will be easier to do what you're trying to do.

Up vote 3 down vote favorite share g+ share fb share tw.

I'm having a bit trouble with a query in Linq to Entities which I hope someone can shed a light on :-) What I'm trying to do is to create a query that joins three tables. So far it works, but since the last table I'm trying to join is empty, the result of the query doesn't contain any records. When I remove the last join, it gives me the right 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 }; What I would like is to get some records even if the Winners table is empty or there is no match in it. Any help/hint on this is greatly appreciated!

:-) Thanks a lot in advance. / Bo linq query entity-framework linq-to-entities link|improve this question asked Jan 16 at 19:35bomortensen632415 98% accept rate.

If you set these up as related entities instead of doing joins, I think it will be easier to do what you're trying to do. 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) }; This is assuming hasWonFirstPrize and hasWonVoucher are boolean fields, but you can use any aggregate function to get the results you need, such as p.Winners. Any(w => w. HasWonFirstPrize == 1).

Joel, that's just so awesome and clean! And it works :-) thanks a lot. Now onto the hard part: both your answer and cadrell0's actually helped me, so which answer to mark as accepted?

What's the policy on stackoverflow? – bomortensen Jan 16 at 20:04 If I were the asker, I would upvote both and accept whichever one I ended up using. – cadrell0 Jan 16 at 21:24.

I don't use query syntax a lot but I believe you need to change from w in winnersGroup to from w in winnersGroup.DefaultIfEmpty().

I agree :) I tried it, but it gives me this error: The cast to value type 'Byte' failed because the materialized value is null. Either the result type's generic parameter or the query must use a nullable type. HasWonFirstPrize and HasWonVoucher is indeed byte datatypes, but I don't want them to be null in the database to be honest :-)?

– bomortensen Jan 16 at 19:45 You will probably then need to change the assignment of HasWonFirstPrize to something like HasWonFirstPrize = w == null? 0 : w. HasWonFirstPrize, – cadrell0 Jan 16 at 19:50.

I cant really gove you an answer,but what I can give you is a way to a solution, that is you have to find the anglde that you relate to or peaks your interest. A good paper is one that people get drawn into because it reaches them ln some way.As for me WW11 to me, I think of the holocaust and the effect it had on the survivors, their families and those who stood by and did nothing until it was too late.