using System.ComponentModel.DataAnnotations.Schema;

using System.Diagnostics;

using System.Diagnostics.CodeAnalysis;

using System.Linq;

using Microsoft.EntityFrameworkCore;

using Microsoft.Extensions.Logging;

​

namespace IssueConsoleTemplate

{

    [Table("Contacts")]

    public class WAContact

    {

        public int Id { get; set; }

        public string Phone { get; set; }

        public string Name { get; set; }

        [NotNull]

        public int UserId { get; set; }

        public WAUser User { get; set; }

    }

​

    [Table("Users")]

    public class WAUser

    {

        public int Id { get; set; }

        public string Name { get; set; }

    }

​

    // 

    // DbContext:

    // 

​

    public class Context : DbContext

    {

        public DbSet<WAContact> Contacts { get; set; }

        public DbSet<WAUser> Users { get; set; }

​

        protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)

        {

            optionsBuilder

                .UseSqlServer(

                    FiddleHelper.GetConnectionStringSqlServer())

                .UseLoggerFactory(

                    LoggerFactory.Create(

                        b => b

                            .AddConsole()

                            .AddFilter(level => level >= LogLevel.Information)))

                .EnableSensitiveDataLogging()

                .EnableDetailedErrors();

        }

​

        protected override void OnModelCreating(ModelBuilder modelBuilder)

        {

            modelBuilder.Entity<WAUser>()

                .HasData(

                    new WAUser {Id = 1, Name = "John"},

                    new WAUser {Id = 2, Name = "Jane"},

                    new WAUser {Id = 3, Name = "Mike"});

​

            modelBuilder.Entity<WAContact>()

                .HasData(

                    new WAContact {Id = 11, Name = "John's First Contact", Phone = "12345", UserId = 1},

                    new WAContact {Id = 12, Name = "John's Second Contact", Phone = "23456", UserId = 1},

                    new WAContact {Id = 21, Name = "Jane's Only Contact", Phone = "09876", UserId = 2});

        }

    }

​

    internal class Program

    {

        private static void Main()

        {

            using var context = new Context();

​

            //context.Database.EnsureDeleted();

            context.Database.EnsureCreated();

            // Return all WAContact entities. Because every entity must have a UserId,

            // there is no need to actually query the WAUsers table.

            // Just performs a SELECT, then switches to client-evaluation to group the returned

            // data in memory:

            //     SELECT `c`.`Id`, `c`.`Name`, `c`.`Phone`, `c`.`UserId`

            //     FROM `Contacts` AS `c`

​

            var userIdsWithContactsWithoutJoin = context.Contacts

                .AsEnumerable()

                .GroupBy(c => c.UserId)

                .ToList();

​

            Debug.Assert(userIdsWithContactsWithoutJoin.Count == 2);

            Debug.Assert(userIdsWithContactsWithoutJoin[0].Key == 1);

            Debug.Assert(userIdsWithContactsWithoutJoin[0].Count() == 2);

            Debug.Assert(userIdsWithContactsWithoutJoin[0].First().Name == "John's First Contact");

​

            // Return all WAUser Ids only with related WAContact entities (full).

            // First performs an INNER JOIN, then switches to client-evaluation to group the

            // returned data in memory:

            //     SELECT `c`.`UserId`, `c`.`Id`, `c`.`Name`, `c`.`Phone`

            //     FROM `Users` AS `u`

            //     INNER JOIN `Contacts` AS `c` ON `u`.`Id` = `c`.`UserId`

​

            var userIdsWithContacts = context.Users

                .SelectMany(

                    u => context.Contacts

                        .Where(c => u.Id == c.UserId),

                    (u, c) => new

                    {

                        c.UserId,

                        Contact = c

                    })

                .AsEnumerable()

                .GroupBy(j => j.UserId, j => j.Contact)

                .ToList();

​

            Debug.Assert(userIdsWithContacts.Count == 2);

            Debug.Assert(userIdsWithContacts[0].Key == 1);

            Debug.Assert(userIdsWithContacts[0].Count() == 2);

            Debug.Assert(userIdsWithContacts[0].First().Name == "John's First Contact");

​

            // Return all WAUser entities (full) with or without related WAContact entities (full).

            // First performs a LEFT JOIN, then switches to client-evaluation to group the returned

            // data in memory:

            //     SELECT `u`.`Id`, `u`.`Name`, `c`.`Id`, `c`.`Name`, `c`.`Phone`, `c`.`UserId`

            //     FROM `Users` AS `u`

            //     LEFT JOIN `Contacts` AS `c` ON `u`.`Id` = `c`.`UserId`

​

            var usersWithOrWithoutContacts = context.Users

                .SelectMany(

                    u => context.Contacts

                        .Where(c => u.Id == c.UserId)

                        .DefaultIfEmpty(),

                    (u, c) => new

                    {

                        User = u,

                        Contact = c

                    })

                .AsEnumerable()

                .GroupBy(j => j.User, j => j.Contact)

                .ToList();

​

            Debug.Assert(usersWithOrWithoutContacts.Count == 3);

            Debug.Assert(usersWithOrWithoutContacts[0].Key.Name == "John");

            Debug.Assert(usersWithOrWithoutContacts[0].Count() == 2);

            Debug.Assert(usersWithOrWithoutContacts[0].First().Name == "John's First Contact");

        }

    }

}