I have below customer and customer message model. I want to get top n customers with latest messages and have only the latest message per customer included.

I am using EfCore 7 on Postgres and my database has around a million customers and each customer might have a around 10 to 100 messages.

public class Customer 
{
    public long Id{ get; set;}
    public string LastName { get; set; }
    public string FirstName { get; set; }
    public ICollection<CustomerMessage>? CustomerMessages { get; set; }
}

public class CustomerMessage
{
    public long Id{ get; set;}
    public string Message { get; set; }
    public long CustomerId { get; set; }
    public Customer Customer { get; set; }
    public DateTime CreatedOnUtc{ get; set;}
}

Customer Message Table:

Customer Table:

Result with take=2, skip=0. result is based on latest messages received:

`[ { "Id":2, "Name":"Joe", "CustomerMessages":[{ "Message":"Test5"}]},

{ "Id:3, "Name":"Jim", "CustomerMessages":[{ "Message":"Test7"}]}]`

Please suggest thanks.

CodePudding user response：

Try the following query:

var n = 10;
var query = context.Customers
    .Include(c => c.CustomerMessages.OrderByDescending(m => m.CreatedOnUtc).Take(1))
    .Take(n)
    .ToList();

Or via custom projection, which is closer to your JSON

var n = 10;
var query = context.Customers
    .Take(n)
    .Select(c => new 
    {
        c.Id,
        Name = c.FirstName,
        CustomerMessages = c.CustomerMessages
            .OrderByDescending(m => m.CreatedOnUtc)
            .Take(1)
            .Select(m => new { m.Message })
            .ToList(),
    })
    .ToList();

UPDATE, based on updated requirements

var n = 10;

var joinQuery = 
    from c in context.Customers
    from m in c.CustomerMessages
        .OrderByDescending(m => m.CreatedOnUtc)
        .Take(1)
    orderby m.CreatedOnUtc descending
    select new 
    {
        Customer = c, 
        Message = new { m.Message }
    };

var query = joinQuery
    .Take(n)
    .Select(x => new 
    {
        x.Customer.Id,
        Name = x.Customer.FirstName,
        CustomerMessage = new { m.Message.Message },
    });