using Microsoft.EntityFrameworkCore;

using Microsoft.Data.SqlClient;

using System;

using System.Collections.Generic;

using System.Linq;

using System.Text.Json;

​

public class Program

{

    public static void Main()

    {

        using (var context = new BloggingContext())

        {

            context.Database.EnsureCreated();

            SeedArticles(context);

            SeedArticleEditions(context);

        }

        var topFiveArticles = new BloggingContext().Articles.Take(5).Select(x => x.Id).ToList();

        // causes System.InvalidOperationException: Nullable object must have a value.

        var (count, latestEditionsOfArticles) = GetLatestArticleEditionsWithCount(topFiveArticles, 3);

​

        // if the Count() evaluated after .ToList() - request is processed properly

        //var (count, latestEditionsOfArticles) = GetLatestArticleEditionsWithClientEvaluatedCount(topFiveArticles, 3);

​

        // should be 5, as we provide 5 ids of the articles

        Console.WriteLine("Found versions in total: " + count);

        // should list first 3 of found

        foreach(var articleEdition in latestEditionsOfArticles){

            Console.WriteLine(JsonSerializer.Serialize(articleEdition));

        }

    }

    private static (int, IEnumerable<ArticleEdition>) GetLatestArticleEditionsWithCount(IReadOnlyCollection<int> articleIds, int editionsToTake) {

        using (var context = new BloggingContext())

        {

            var query = context.ArticleEditions.Where(articleEdition => articleIds.Contains(articleEdition.ArticleId)); 

            //group by ArticleId and try to get only the latest edition of each article

            query = query

                .GroupBy(x => x.ArticleId)

                .Select(group => group.FirstOrDefault(inner => inner.EditionNumber == group.Max(outer => outer.EditionNumber)));

            return (query.Count(), query.Take(editionsToTake).ToList());

        }

    }

    private static (int, IEnumerable<ArticleEdition>) GetLatestArticleEditionsWithClientEvaluatedCount(IReadOnlyCollection<int> articleIds, int editionsToTake) {

        using (var context = new BloggingContext())

        {

            var query = context.ArticleEditions.Where(articleEdition => articleIds.Contains(articleEdition.ArticleId)); 

            //group by ArticleId and try to get only the latest edition of each article

            query = query

                .GroupBy(x => x.ArticleId)

                .Select(group => group.FirstOrDefault(inner => inner.EditionNumber == group.Max(outer => outer.EditionNumber)));

            return (query.ToList().Count(), query.Take(editionsToTake).ToList());

        }

    }

    private static void SeedArticles(BloggingContext context) {

        context.Articles.AddRange(Enumerable.Range(1, 10).Select(item => new Article {Title = "Article #" + item }));

        context.SaveChanges();

    }

    private static void SeedArticleEditions(BloggingContext context) {

        var articles = context.Articles.ToList();

        foreach(var article in articles){

            context.ArticleEditions.AddRange(Enumerable.Range(1, 5).Select(item => new ArticleEdition {

                ArticleId = article.Id,

                EditionNumber = item,

                ArticleContent = "Some very important stuff #" + item,

                ModificationDate = DateTime.Now

            }));

        }

        context.SaveChanges();

    }

}

​

​

  public class BloggingContext : DbContext

    {

        public DbSet<Article> Articles { get; set; }

        public DbSet<ArticleEdition> ArticleEditions { get; set; }

​

        protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)

        {

            optionsBuilder.UseSqlServer(new SqlConnection(FiddleHelper.GetConnectionStringSqlServer()));

​

            base.OnConfiguring(optionsBuilder);

        }

​

    }

​

    public class Article {

      public int Id {get; set;}

      public string Title {get;set;}

      public virtual ICollection<ArticleEdition> ArticleEditions {get;set;}

    }

    public class ArticleEdition {

      public int Id {get;set;}

      public int ArticleId {get;set;}

      public virtual Article Article {get;set;}

      public int EditionNumber {get;set;}

      public string ArticleContent {get;set;}

      public DateTime ModificationDate {get;set;}

    }

​

​

​

​