// Dynamic LINQ - stef

​

// @nuget: System.Linq.Dynamic.Core

// @nuget: Z.EntityFramework.Classic

​

using System;

using System.Collections.Generic;

using System.Linq;

using System.Data.Entity;

using System.Linq.Dynamic.Core;

public class Program

{   

    public static void Main()

    {

        GroupJoin_Example_1();

        GroupJoin_Example_2();

        GroupJoin_Example_3();

        GroupJoinOnNullableType_RightNullable();

        GroupJoinOnNullableType_LeftNullable();

    }

​

    public static void GroupJoin_Example_1()

    {

        Console.WriteLine(nameof(GroupJoin_Example_1)); 

        Person magnus = new Person { Name = "Hedlund, Magnus" };

        Person terry = new Person { Name = "Adams, Terry" };

        Person charlotte = new Person { Name = "Weiss, Charlotte" };

​

        Pet barley = new Pet { Name = "Barley", Owner = terry };

        Pet boots = new Pet { Name = "Boots", Owner = terry };

        Pet whiskers = new Pet { Name = "Whiskers", Owner = charlotte };

        Pet daisy = new Pet { Name = "Daisy", Owner = magnus };

​

        var people = new List<Person> { magnus, terry, charlotte };

        var petsList = new List<Pet> { barley, boots, whiskers, daisy };

        var query = people.AsQueryable().GroupJoin(

                petsList,

                person => person,

                pet => pet.Owner,

                (person, pets) => new { OwnerName = person.Name, Pets = pets, NumberOfPets = pets.Count() });

        FiddleHelper.WriteTable(query);

​

        var dynamicQuery = people.AsQueryable().GroupJoin(

            petsList,

            "it",

            "Owner",

            "new(outer.Name as OwnerName, inner as Pets, inner.Count() as NumberOfPets)");

        var dynamicResults = new List<object>();

        foreach (dynamic item in dynamicQuery)

        {

            var dynamicResult = new

            {

                OwnerName = item.OwnerName,

                Pets = item.Pets,

                NumberOfPets = item.NumberOfPets

            };

            dynamicResults.Add(dynamicResult);

        }

        FiddleHelper.WriteTable(dynamicResults);

    }

​

    public static void GroupJoin_Example_2()

    {

        Console.WriteLine(nameof(GroupJoin_Example_2));

        Person magnus = new Person { Name = "Hedlund, Magnus" };

        Person terry = new Person { Name = "Adams, Terry" };

        Person charlotte = new Person { Name = "Weiss, Charlotte" };

​

        Pet barley = new Pet { Name = "Barley", Owner = terry };

        Pet boots = new Pet { Name = "Boots", Owner = terry };

        Pet whiskers = new Pet { Name = "Whiskers", Owner = charlotte };

        Pet daisy = new Pet { Name = "Daisy", Owner = magnus };

​

        var people = new List<Person> { magnus, terry, charlotte };

        var petsList = new List<Pet> { barley, boots, whiskers, daisy };

        var query = people.AsQueryable().GroupJoin(

                petsList,

                person => person.Id,

                pet => pet.OwnerId,

                (person, pets) => new { OwnerName = person.Name, Pets = pets, NumberOfPets = pets.Count() });

        FiddleHelper.WriteTable(query);

​

        var dynamicQuery = people.AsQueryable().GroupJoin(

            petsList,

            "it.Id",

            "OwnerId",

            "new(outer.Name as OwnerName, inner as Pets, inner.Count() as NumberOfPets)");

        var dynamicResults = new List<object>();

        foreach (dynamic item in dynamicQuery)

        {

            var dynamicResult = new

            {

                OwnerName = item.OwnerName,

                Pets = item.Pets,

                NumberOfPets = item.NumberOfPets

            };

            dynamicResults.Add(dynamicResult);

        }

        FiddleHelper.WriteTable(dynamicResults);

    }

​

    public static void GroupJoin_Example_3()

    {

        Console.WriteLine(nameof(GroupJoin_Example_3));

        var stef = new Employee

        {

            EmployeeId = 1,

            FirstName = "Stef",

            LastName = "abc"

        };

        var jon = new Employee

        {

            EmployeeId = 2,

            FirstName = "Jon",

            LastName = "xyz"

        };

        var paycheckStef1 = new Paycheck

        {

            PaycheckId = 1001,

            EmployeeId = 1,

            Employee = stef,

            DateCreated = DateTime.Now

        };

        var paycheckStef2 = new Paycheck

        {

            PaycheckId = 1002,

            EmployeeId = 1,

            Employee = stef,

            DateCreated = DateTime.Now

        };

        var paycheckJon1 = new Paycheck

        {

            PaycheckId = 2001,

            EmployeeId = 2,

            Employee = jon,

            DateCreated = DateTime.Now

        };

        var employees = new List<Employee> { stef, jon };

        var paychecks = new List<Paycheck> { paycheckStef1, paycheckStef2, paycheckJon1 };

​

        // Act

        var query = employees.AsQueryable().GroupJoin(

            paychecks,

            employee => employee.EmployeeId,

            paycheck => paycheck.EmployeeId,

            (emp, paycheckList) => new { Name = emp.FirstName + " " + emp.LastName, NumberOfPaychecks = paycheckList.Count() });

        FiddleHelper.WriteTable(query);

​

        var dynamicQuery = employees.AsQueryable().GroupJoin(

            paychecks,

            "it.EmployeeId",

            "EmployeeId",

            "new(outer.FirstName + \" \" + outer.LastName as Name, inner.Count() as NumberOfPaychecks)");

        var dynamicResults = new List<object>();

        foreach (dynamic item in dynamicQuery)

        {

            var dynamicResult = new

            {

                Name = item.Name,

                NumberOfPaychecks = item.NumberOfPaychecks

            };

            dynamicResults.Add(dynamicResult);

        }

        FiddleHelper.WriteTable(dynamicResults);

    }

​

    public static void GroupJoinOnNullableType_RightNullable()

    {

        Console.WriteLine(nameof(GroupJoinOnNullableType_RightNullable));

        Person magnus = new Person { Id = 1, Name = "Hedlund, Magnus" };

        Person terry = new Person { Id = 2, Name = "Adams, Terry" };

        Person charlotte = new Person { Id = 3, Name = "Weiss, Charlotte" };

​

        Pet barley = new Pet { Name = "Barley", NullableOwnerId = terry.Id };

        Pet boots = new Pet { Name = "Boots", NullableOwnerId = terry.Id };

        Pet whiskers = new Pet { Name = "Whiskers", NullableOwnerId = charlotte.Id };

        Pet daisy = new Pet { Name = "Daisy", NullableOwnerId = magnus.Id };

​

        var people = new List<Person> { magnus, terry, charlotte };

        var petsList = new List<Pet> { barley, boots, whiskers, daisy };

​

        var query = people.AsQueryable().GroupJoin(

            petsList,

            person => person.Id,

            pet => pet.NullableOwnerId,

            (person, pets) => new { OwnerName = person.Name, Pets = pets });

        FiddleHelper.WriteTable(query);

​

        var dynamicQuery = people.AsQueryable().GroupJoin(

            petsList,

            "it.Id",

            "NullableOwnerId",

            "new(outer.Name as OwnerName, inner as Pets)");

        var dynamicResults = new List<object>();

        foreach (dynamic item in dynamicQuery)

        {

            var dynamicResult = new

            {

                OwnerName = item.OwnerName,

                Pets = item.Pets

            };

            dynamicResults.Add(dynamicResult);

        }

        FiddleHelper.WriteTable(dynamicResults);

    }

​

    public static void GroupJoinOnNullableType_LeftNullable()

    {

        Console.WriteLine(nameof(GroupJoinOnNullableType_LeftNullable));

        Person magnus = new Person { NullableId = 1, Name = "Hedlund, Magnus" };

        Person terry = new Person { NullableId = 2, Name = "Adams, Terry" };

        Person charlotte = new Person { NullableId = 3, Name = "Weiss, Charlotte" };

​

        Pet barley = new Pet { Name = "Barley", OwnerId = terry.Id };

        Pet boots = new Pet { Name = "Boots", OwnerId = terry.Id };

        Pet whiskers = new Pet { Name = "Whiskers", OwnerId = charlotte.Id };

        Pet daisy = new Pet { Name = "Daisy", OwnerId = magnus.Id };

​

        var people = new List<Person> { magnus, terry, charlotte };

        var petsList = new List<Pet> { barley, boots, whiskers, daisy };

​

        var query = people.AsQueryable().GroupJoin(

            petsList,

            person => person.NullableId,

            pet => pet.OwnerId,

            (person, pets) => new { OwnerName = person.Name, Pets = pets });

        FiddleHelper.WriteTable(query);

​

        var dynamicQuery = people.AsQueryable().GroupJoin(

            petsList,

            "it.NullableId",

            "OwnerId",

            "new(outer.Name as OwnerName, inner as Pets)");

        var dynamicResults = new List<object>();

        foreach (dynamic item in dynamicQuery)

        {

            var dynamicResult = new

            {

                OwnerName = item.OwnerName,

                Pets = item.Pets

            };

            dynamicResults.Add(dynamicResult);

        }

        FiddleHelper.WriteTable(dynamicResults);

    }

}

​

public class Person

{

    public int Id { get; set; }

    public int? NullableId { get; set; }

    public string Name { get; set; }

}

​

public class Pet

{

    public int Id { get; set; }

    public string Name { get; set; }

    public Person Owner { get; set; }

    public int OwnerId { get; set; }

    public int? NullableOwnerId { get; set; }

}

​

public class Employee

{

    // [Key]

    public int EmployeeId { get; set; }

    public string FirstName { get; set; }

    public string LastName { get; set; }

    public int Age { get; set; }

​

    public ICollection<Paycheck> Paychecks { get; set; } = new List<Paycheck>();

}

​

public class Paycheck

{

    // [Key]

    public int PaycheckId { get; set; }

    public decimal HourlyWage { get; set; }

    public int HoursWorked { get; set; }

    public DateTimeOffset DateCreated { get; set; }

​

    // [ForeignKey("EmployeeId")]

    public Employee Employee { get; set; }

    public int EmployeeId { get; set; }

}