// 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

{   

    static readonly Random Rnd = new Random(1);

    public static void Main()

    {

        GenerateData(5);

        GroupBy_SingleKey();

        GroupBy_MultiKey();

        GroupBy_SingleKey_SingleResult();

        GroupBy_SingleKey_MultiResult();

        GroupBy_SingleKey_Count();

        GroupBy_SingleKey_Sum();

    }

    private static void GroupBy_SingleKey()

    {

        Console.WriteLine(nameof(GroupBy_SingleKey));

        var context = new EntityContext();

        var result = context.Posts.GroupBy("BlogId").ToDynamicArray();

​

        for (int i = 0; i < result.Length; i++)

        {

            var expectedRow = result[i];

​

            // The DynamicBinder doesn't allow us to access values of the Group object, so we have to cast first

            var testRow = (IGrouping<int, Post>) result[i];

​

            Console.WriteLine("Key (BlogId) = {0}", testRow.Key);

            Console.WriteLine("PostTitles   = {0}", string.Join(", ", testRow.Select(x => $"'{x.Title}'")));

            Console.WriteLine();

        }

    }

    private static void GroupBy_MultiKey()

    {

        Console.WriteLine(nameof(GroupBy_MultiKey));

        var context = new EntityContext();

        var result = context.Posts.GroupBy("new (BlogId, PostDate)").OrderBy("Key.PostDate").ToDynamicArray();

        for (int i = 0; i < result.Length; i++)

        {

            var expectedRow = result[i];

​

            // The DynamicBinder doesn't allow us to access values of the Group object, so we have to cast first

            var testRow = (IGrouping<DynamicClass, Post>) result[i];

​

            Console.WriteLine("Key.BlogId   = {0}", ((dynamic)testRow.Key).BlogId);

            Console.WriteLine("Key.PostDate = {0}", ((dynamic)testRow.Key).PostDate);

            Console.WriteLine("PostTitles   = {0}", string.Join(", ", testRow.Select(x => $"'{x.Title}'")));

            Console.WriteLine();

        }

    }

    private static void GroupBy_SingleKey_SingleResult()

    {

        Console.WriteLine(nameof(GroupBy_SingleKey_SingleResult));

        var context = new EntityContext();

        var result = context.Posts.GroupBy("PostDate", "Title").ToDynamicArray();

​

        for (int i = 0; i < result.Length; i++)

        {

            var expectedRow = result[i];

​

            //For some reason, the DynamicBinder doesn't allow us to access values of the Group object, so we have to cast first

            var testRow = (IGrouping<DateTime, string>) result[i];

​

            Console.WriteLine("Key (PostDate) = {0}", testRow.Key);

            Console.WriteLine("PostTitles     = {0}", string.Join(", ", testRow.Select(x => $"'{x}'")));

            Console.WriteLine();

        }

    }

    private static void GroupBy_SingleKey_MultiResult()

    {

        Console.WriteLine(nameof(GroupBy_SingleKey_MultiResult));

        var context = new EntityContext();

        var result = context.Posts.GroupBy("PostDate", "new (Title, Content)").ToDynamicArray();

​

        for (int i = 0; i < result.Length; i++)

        {

            var expectedRow = result[i];

​

            //For some reason, the DynamicBinder doesn't allow us to access values of the Group object, so we have to cast first

            var testRow = (IGrouping<DateTime, DynamicClass>) result[i];

​

            Console.WriteLine("Key (PostDate)          = {0}", testRow.Key);

            Console.WriteLine("PostTitles with Content = {0}", string.Join(", ", testRow.Cast<dynamic>().Select(x => $"'{x.Title}' : '{x.Content}'")));

            Console.WriteLine();

        }

    }

    private static void GroupBy_SingleKey_Count()

    {

        Console.WriteLine(nameof(GroupBy_SingleKey_Count));

        var context = new EntityContext();

        var result = context.Posts.GroupBy("BlogId").Select("new(Key, Count() AS Count)").ToDynamicArray();

​

        for (int i = 0; i < result.Length; i++)

        {

            var expectedRow = result[i];

            var testRow = result[i];

            Console.WriteLine("Key (BlogId) = {0}, Count = {1}", testRow.Key, testRow.Count);

            Console.WriteLine();

        }

    }

    private static void GroupBy_SingleKey_Sum()

    {

        Console.WriteLine(nameof(GroupBy_SingleKey_Sum));

        var context = new EntityContext();

        var result = context.Posts.GroupBy("BlogId").Select("new(Key, Sum(NumberOfReads) AS TotalReads)").ToDynamicArray();

​

        for (int i = 0; i < result.Length; i++)

        {

            var expectedRow = result[i];

            var testRow = result[i];

            Console.WriteLine("Key (BlogId) = {0}, TotalReads = {1}", testRow.Key, testRow.TotalReads);

            Console.WriteLine();

        }

    }

    private static void GenerateData(int blogCount)

    {

        using (var context = new EntityContext())

        {

            for (int i = 0; i < blogCount; i++)

            {

                var blog = new Blog { Name = "Blog" + (i + 1), Created = DateTime.Now.AddDays(-Rnd.Next(0, 100)) };

​

                // context.Blogs.Add(blog);

​

                for (int j = 0; j < Rnd.Next(2, 5); j++)

                {

                    var post = new Post

                    {

                        Blog = blog,

                        Title = $"Blog {i + 1} - Post {j + 1}",

                        Content = "My Content " + Rnd.Next(100, 200),

                        PostDate = DateTime.Today.AddDays(-Rnd.Next(0, 100)).AddSeconds(Rnd.Next(0, 30000)),

                        NumberOfReads = Rnd.Next(0, 100)

                    };

                    context.Posts.Add(post);

                }

            }

            context.SaveChanges();

        }

    }

    public class EntityContext : DbContext

    {

        public EntityContext() : base(FiddleHelper.GetConnectionStringSqlServer())

        {

        }

        public DbSet<Post> Posts { get; set; }

    }

    public class Blog

    {

        public int Id { get; set; }

        public string Name { get; set; }

        public int? NullableInt { get; set; }

        public virtual ICollection<Post> Posts { get; set; }

        public DateTime Created { get; set; }

    }

    public class Post

    {

        public int Id { get; set; }

        public string Title { get; set; }

        public string Content { get; set; }

​

        public int BlogId { get; set; }

        public virtual Blog Blog { get; set; }

​

        public int NumberOfReads { get; set; }

​

        public DateTime PostDate { get; set; }

    }

}