Return results from a two joined tables even if the link to the child table data does not exist using LINQ join

Question

var TblPolls = new[]
{
    new { Guid = 1, Question = "question 1", UserId = 1 },
    new { Guid = 2, Question = "question 2", UserId = 2 },
    new { Guid = 3, Question = "question 3", UserId = 1 },
};

var TblVotes = new[]
{
    new { VoteId = 1, UserId = 1, PollGuid = 1, Value = "No" },
    new { VoteId = 2, UserId = 2, PollGuid = 1, Value = "Yes" },
    new { VoteId = 3, UserId = 3, PollGuid = 1, Value = "Yes" },
    new { VoteId = 4, UserId = 1, PollGuid = 2, Value = "No" },
};

I have these tables above, but as you can see that question 3 has not been voted. So how do i still pull out the data for TblPolls row for Question 3, taking into account that there are no votes for this Poll, so the result should be that the row question 3 is returned but the values in votes are null. Im guessing this is kind of a left join in linq. I have tried this but im not getting back the results i want:

poll = (
        from polls in ctx.TblPolls
        join votes in ctx.TblVotes on polls.Guid equals votes.PollGuid into gg
        where polls.Guid == guid
        from ff in gg.DefaultIfEmpty()
        where (ff.UserId == userId || ff.UserId == null) && ff.PollGuid == guid
        select new
        {
            polls.UserId,
            polls.Id,
            polls.Guid,
            polls.Question,
            polls.Description,
            polls.Date,
            polls.VideoId,
            value = gg.Where(x => x.UserId == userId && x.PollGuid == guid).FirstOrDefault().Value,
            VoteCount = gg.Count(),
        }
        ).ToList();

Whats happening is the if a users vote does not exist for a TblPoll Question im getting empty array when the question should be returned even if there are not votes for that user.

So how would the linq query work for the reuslts to occur as i suggested?

Answer 1

You have two tables, TblPolls with a property Guid and TblVotes with properties UserId and PollGuid, and you want to find all polls for a given poll guid, and tally up all votes for that poll for a given user id -- or none, if no votes were case.

You can do this with a left outer join as follows:

var selectedPolls = (
    from poll in ctx.TblPolls
    // Filter on polls.Guid before the join, for performance and simplicity.
    where poll.Guid == guid
    // Filter on vote.UserId and vote.PollGuid before the join, for performance and simplicity.
    join vote in ctx.TblVotes.Where(v => v.UserId == userId && v.PollGuid == guid) on poll.Guid equals vote.PollGuid into gg
    from vote in gg.DefaultIfEmpty() // At this point vote will be null if gg was empty.
    select new
    {
        poll.UserId,
        poll.Id, // Not shown in question.
        poll.Guid,
        poll.Question,
        poll.Description, // Not shown in question.
        poll.Date, // Not shown in question.
        poll.VideoId, // Not shown in question.
        //What is vote.Value?  It isn't shown in the question so I don't know how to fix this line:
        //value = gg.Where(x => x.UserId == userId && x.PollGuid == guid).FirstOrDefault().Value,
        //But possibly this is correct:
        value = (vote != null ? vote.Value : -1), // Substitute whatever default value you want here.
        VoteCount = gg.Count(),
    }
).ToList();

Notes:

• For performance and simplicity, you should filter the Guid of the polls and the UserId and PollGuid of the votes before doing the join.

• Since the contents of TblVotes are classes not structs, from ff in gg.DefaultIfEmpty() will return null if gg is empty. Then ff.UserId will cause an exception.

• Your TblVotes does not have a property Value so I don't know how to fix

  value = gg.Where(x => x.UserId == userId && x.PollGuid == guid).FirstOrDefault().Value
  

  Since you are already looping through gg anyway, maybe you just want:

  value = (vote != null ? vote.Value : -1),
  

• I modified some of your variable names for clarity.

Demo fiddle here.

Update from redoc01:

The solution was almost there, i just modified this line:

join vote in ctx.TblVotes.Where(v => v.UserId == userId && v.PollGuid == guid) on poll.Guid equals vote.PollGuid into gg
where poll.Guid == guid <----- to here
from vote in gg.DefaultIfEmpty() // At this point vote will be null if gg was empty.

and this line:

from value = vote?.Value, to value = vote.Value,

And now works!...