CODEWITHSUNDEEP
c#entity-frameworklinq
Mehran
Mehran

Reputation: 47

How to order items based on a column in child table in EF

I have two tables (FactorItems and Products), my Factoritems table contains columns Id, ProductId, Qty, IsBuy and Products table contains columns Id, Name, .... .

I want to use code to select my product where my Qty column has a value (for show popular Products in index), and I have a foreign key from ProductId column in FactorItems table to Id column in the Product table.

And I use a virtual view model in ASP.NET MVC:

model.BestSellersProducts = blProduct.Select().OrderByDescending(p => p.FactorItems.Select(c => c.Qty)).take(3);

I get this error from my index in foreach:

An exception of type 'System.ArgumentException' occurred in EntityFramework.SqlServer.dll but was not handled in user code

Additional information: DbSortClause expressions must have a type that is order comparable.

Upvotes: 2

Views: 420

Answers (1)

TanvirArjel
TanvirArjel

Reputation: 32069

Considering your Product and FactorItem have One-to-Zero-or-One relationship as follows:

public class Product
{
    public int Id { get; set; }
    public string Name { get; set; }
    public FactorItem FactorItem { get; set; }
}

public class FactorItem
{
    [Key, ForeignKey("Product")]
    public int ProductId { get; set; }
    public int Qty { get; set; }
    public bool IsBuy { get; set; }
    public Product Product { get; set; }
}

Then your query to get top 3 sold products should be as be follows:

List<Product> products = dbContext.Products.OrderByDescending(p => p.FactorItem.Qty).Take(3).ToList();

Considering your Product and FactorItem have One-to-Many relationship as follows:

public class Product
{
    public int Id { get; set; }
    public string Name { get; set; }
    public ICollection<FactorItem> FactorItems { get; set; }
}

public class FactorItem
{
    public int Id { get; set; }
    [ForeignKey("Product")]
    public int ProductId { get; set; }
    public int Qty { get; set; }
    public bool IsBuy { get; set; }
    public Product Product { get; set; }
}

Then your query to get top 3 sold products should be as be follows:

var productList = dbContext.FactorItems.GroupBy(fi => new { fi.ProductId, fi.Product.Name }).Select(g => new
        {
            ProductId = g.Key.ProductId,
            ProductName = g.Key.Name,
            TotalSoldQty = g.Sum(x => x.Qty)
        }).OrderByDescending(x => x.TotalSoldQty).ToList();

Upvotes: 1

Related Questions