using System;

using System.Collections.Generic;

using System.Linq;

​

using System.Text.Json;

​

//https://stackoverflow.com/questions/76509996/return-results-from-a-two-joined-tables-even-if-the-link-to-the-child-table-data

​

public class Program

{

    public static void Main()

    {

        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 = 1010.1 }, // Value not shown in question

            new { VoteId = 2, UserId = 2, PollGuid = 1, Value = 2010.1 },

            new { VoteId = 3, UserId = 3, PollGuid = 1, Value = 3010.1},

            new { VoteId = 4, UserId = 1, PollGuid = 2, Value = 4010.1 },

        };

​

        var ctx = new 

        {

            TblPolls = TblPolls.AsQueryable(),

            TblVotes = TblVotes.AsQueryable(),

        };

​

        foreach (var userId in new [] { 1, 2, 3, 30303030 })

        foreach (var guid in new [] { 1, 2, 3, 101010 })

        {

            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();

​

            Console.WriteLine($"\nFor {new { guid, userId }}:");

            selectedPolls.Select(p => p.ToString()).ToArray().Dump();

        }

    }

}

​

public class Poll

{

    public int Guid { get; set; }

    public string Question { get; set; }

    public int UserId { get; set; }

}

​

public class Vote

{

    public int VoteId { get; set; }

    public int UserId { get; set; }

    public int PollGuid { get; set; }

}

​

public static class Extensions

{

    public static TOut As<TOut>(this TOut o) => o;

}

​

​