Dynamic LINQ - GroupBy

CSharp · Console

/Program.cs

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