Clash Royale CLAN TAG#URR8PPP

How to get max value of related entity in Entity Framework most effectively

I would like to get list of categories with most recent product in category.

I am using Entity Framework Core 2.1.

I have tried like this, but it is very slow

var categories = context.Categories.Select(x => new
x.Id,
Product = x.Products.OrderByDescending(y => y.Date).Select(y => y.Name).FirstOrDefault()



y.Name

.FirstOrDefault().Name

Product

(CategoryId, Date)

Products

1 Answer
1

That LINQ query should generate a single SQL Query, which should be fast.

Something like:

SELECT [t].[CategoryId], (
SELECT TOP(1) [r].[Name]
FROM [Products] AS [r]
WHERE [t].[CategoryId] = [r].[CategoryId]
ORDER BY [r].[Date] DESC
) AS [FirstName]
FROM [Categorys] AS [t]


Here's a sample with 100 categories, each with 1000 products to test. Code-first will create an index on Product.CategoryId, but even without that, this isn't an expensive query.

using Microsoft.EntityFrameworkCore;
using System;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;
using System.Data;
using System.Data.SqlClient;
using System.Linq;

namespace EfCoreTest


public class Category

public int CategoryId get; set;
public virtual ICollection<Product> Readings get; = new HashSet<Product>();

public class Product

public int ProductId get; set;
public string Name get; set;
public DateTime Date get; set;
public Category Category get; set;




public class Db : DbContext

public DbSet<Category> Categorys get; set;
public DbSet<Product> Products get; set;

protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)

optionsBuilder.UseSqlServer("server=.;database=EfCoreTest;Integrated Security=true");
base.OnConfiguring(optionsBuilder);


protected override void OnModelCreating(ModelBuilder modelBuilder)

base.OnModelCreating(modelBuilder);



class Program


static void Main(string args)


using (var db = new Db())


db.Database.EnsureDeleted();
db.Database.EnsureCreated();

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

var t = new Category();

for (int j = 0; j < 1000; j++)

var product = new Product()

Category = t,
Date = DateTime.Now,
Name = $"Category ji"

;
db.Add(product);

db.Add(t);



db.SaveChanges();




using (var db = new Db())

var q = db.Categorys.Select(t => new

t.CategoryId,
FirstName = t.Readings.OrderByDescending(r => r.Date).Select(r => r.Name).FirstOrDefault()
);

var l = q.ToList();

Console.WriteLine("Hit any key to exit.");
Console.ReadKey();








By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.