SQL to LINQ Conversion Involving Max()

Question

I wanted to retrieve the top 5 merchants with the latest order and the grand total amount. However, I can't seem to get the Grand total amount. I think the issue lies within LastOrderGrandTotal= x.FirstOrDefault().GrandTotal. The code below syntax is in LINQ method syntax

var list = _ApplicationDbContext.OrderTransaction
          .Where(x => x.Status != 0)
          .GroupBy(x => x.MerchantUserId)
          .Select(x => new DashboardActiveMerchantStore { MerchantUserId = x.Key, CreatedDate = x.Max(c => c.CreatedDate), LastOrderGrandTotal= x.FirstOrDefault().GrandTotal })
          .OrderByDescending(x => x.CreatedDate)
          .Take(5)
          .ToList();

For further clarification the SQL syntax for the query above is

select TOP(5) MerchantUserId,MAX(CreatedDate),GrandTotal from OrderTransaction 
group by MerchantUserId
order by CreatedDate desc 

I have a class to store the data retrieve from the LINQ syntax

public class DashboardActiveMerchantStore
    {
        public string MerchantName { get; set; }
        public double LastOrderGrandTotal { get; set; }
        public Guid MerchantUserId { get; set; }
        public DateTime CreatedDate { get; set; }

    }

Answer 1

As you are using entity framework, a totally different, more natural approach is possible. You can use the virtual ICollection<...>.

I wanted to retrieve the top 5 merchants with the latest order and the grand total amount.

The classes in entity framework

It seems there is a one-to-many relation between Merchants and OrderTransactions: every Merchant has zero or more OrderTransactions, and every OrderTransaction is a transaction of exactly when Merchant, namely the Merchant that the foreign key MechantId refers to.

If you've followed the entity framework conventions, you will have classes similar to:

public class Merchant
{
    public Guid Id {get; set;}      // primary key
    public string Name {get; set;}
    ...

    // Every Merchant has zero or more TransactionOrders (one-to-many)
    public virtual ICollection<TransactionOrder> TransactionOrders {get; set;}
}

public class TransactionOrder
{
    public Guid Id {get; set;}      // primary key
    public DateTime CreatedDate {get; set;}
    public int Status {get; set;}
    public double GrandTotal {get; set;}
    ...

    // every TransactionOrder is an order of a Merchant, using foreign key
    public Guid MerchantId {get; set;}
    public virtual Merchant Merchant {get; set;}
}

And of course the DbContext:

public class OrderDbContext : DbContext
{
    public DbSet<Merchant> Merchants {get; set;}
    public DbSet<TransactionOrder> TransactionOrders {get; set;}
}

This is all that entity framework needs to detect the tables, the columns of the tables and the one-to-many relation between the table. Only if you deviate from the conventions, like you want to use different property names, or different table names, you need attributes or fluent API.

In entity framework the columns of a table are represented by non-virtual properties. The virtual properties represent the relations between the tables (one-to-many, many-to-many)

Get top 5 Merchants

If you want some information about "merchants that ... with some information about some of their OrderTransactions" you start at dbContext.Merchants;

If you want some information about "OrderTransactions that ..., each with some information about their Merchant", you start at dbContext.OrderTransactions.

I want to query the top 5 merchants with the latest order and the grand total amount

So you want Merchants. From these Merchants you want at least their Id, and their Name, and some information about their OrderTransactions.

Not all OrderTransactions, only information about their last OrderTransaction with a non-zero Status. From this last OrderTransaction you want the CreatedDate and the GrandTotal.

Now that you've got Merchants with their last non-zero Status Order, you don't want all these Merchants, you only want the five Merchants with the newest CreatedDate.

I hope the above is your requirement. It is not what your SQL said, but your SQL didn't fetch Merchants, it fetched groups of TransactionOrders.

var result = dbContext.Merchants
    .Select(merchant => new
    {
        Id = merchant.Id,
        Name = merchant.Name,

        // from Every Merchant, get the Date and Total of their last
        // non-zero status Order
        // or null if there is no such order at all
        LastOrder = merchant.OrderTransactions
            .Where(orderTransaction => orderTransaction.Status != 0)
            .OrderByDescending(orderTransaction => oderTransaction.CreatedDate)
            .Select(orderTransaction => new
            {
                CreatedDate = orderTransaction.CreatedDate,
                GrandTotal = orderTransaction.GrandTotal,
            })
            .FirstOrDefault(),
    })

Entity Framework knows your one-to-many relation. Because you use the virtual ICollection<...> it will automatically create the (Group-)Join for you.

Now you don't want all Merchants, you don't want Merchants without LastOrder. They didn't have any Order with a non-zero Status. From the remaining Merchants you only want the five Merchants with the newest LastOrder.CreatedDate.

So remove all Merchants that have no LastOrders, order by descending LastOrder.CreatedDate and take the top 5.

Continuing the LINQ:

.Where(merchant => merchant.LastOrder != null) // at leas one Order with non-zero Status
    .OrderbyDescending(merchant => merchant.LastOrder.CreatedDate)
    .Take(5).

You will have "Merchants (Id and Name) with their LastOrder (CreatedData and GrandTotal)", if you want, add an extra Select to convert this into five DashboardActiveMerchantStores

Answer 2

var lastFiveProducts = products.OrderByDescending(p => p.LastOrderGrandTotal).Take(5)

Answer 3

I would rewrite the query like this. You may not even need FirstOrDefault because with GroupBy I believe there is always at least a record.

var result = data
    .Where(x => x.Status != 0)
    .GroupBy(x => x.MerchantUserId)
    .Select(q => new
    {
        q.Key,
        Orders = q.OrderByDescending(q => q.CreatedDate),
    })
    .Select(x => new DashboardActiveMerchantStore { 
        MerchantUserId = x.Key, 
        CreatedDate = x.Orders.FirstOrDefault().CreatedDate,
        LastOrderGrandTotal = x.Orders.FirstOrDefault().GrandTotal,
    })
    .Take(5)
    .ToList();