why does not work OrderBy method in EF core?

c# entity-framework-core

Question

I want to get sorted products(table products) by 'value'(table Product Parameters) according to "ParamertomId"(table Parameters) from my model. How to sort related data correctly?

These are my model classes:

public class Product 
{
    public int Id { get; set; }

    public string Name { get; set; }
    public string Number { get; set; }
    public double Amount { get; set; }
    public double PrimeCostEUR { get; set; }

    [ForeignKey("ProductTypeId")]
    public int  ProductTypeId {  get; set; }
    public virtual ProductType ProductType { get; set; }

    public ICollection<ProductParameter> ProductParameters { get; set; } = new List<ProductParameter>();
}
public class ProductType //: BaseObject
{   
    public int Id { get; set; }

    public string NameType { get; set; }

    public ICollection<Parameter> Parameters { get; set; } = new List<Parameter>();
    public ICollection<Product> Products { get; set; } = new List<Product>();
}
public class Parameter 
{
    public int Id { get; set; }
    public string Name { get; set; }

    [ForeignKey("ProductType")]
    public int ProductTypeId { get; set; }
    public  virtual ProductType ProductType { get; set; }

    public ICollection<ProductParameter> ProductParameters { get; set; } = new List<ProductParameter>();
}
 public class ProductParameter 
{
    public int Id { get; set; }

    public int ProductId { get; set; }
    public virtual Product Product { get; set; }

    public int ParameterId { get; set; }
    public virtual Parameter Parameter { get; set; }

    public string Value { get; set; }

}

These are my DTO classes needed to display the data I need.:

public class ProductDTO{   
   public int ProductId { get; set; }
   public string Number { get; set; }
   public double Amount { get; set; }
   public double PrimeCostEUR { get; set; }

   public int ProductTypeId { get; set; }
   public string NameType { get; set; }

   public ICollection<ParameterDTO> Parameters { get; set; } = new 
   List<ParameterDTO>();}

public class ParameterDTO {
   public int Id { get; set; }
   public string Name { get; set; }
   public string Value { get; set; }
}

I receive related data from three table. This my method GetSortedProducts:

public async Task<IEnumerable<ProductDTO>> GetProducts(int id)
{
    var typeParams = _context.Parameters
        .Where(t => t.ProductTypeId == id)
        .ToList();

    var products =  _context.Products
        .Include(t => t.ProductParameters)
        .Where(t => t.ProductTypeId == id)
        .ToList();

    var items = new List<ProductDTO>();

    foreach (var product in products)
    {
        var productDTO = new ProductDTO()
        {
            ProductId = product.Id,
            Number = product.Number,
            Amount = product.Amount,
            PrimeCostEUR = product.PrimeCostEUR,
            ProductTypeId = product.ProductTypeId
        };

        foreach (var typeParam in typeParams)
        {
            var paramDTO = new ParameterDTO();                        
            var value = product.ProductParameters.FirstOrDefault(t => t.ParameterId == typeParam.Id);
            if (value != null)
            {
                paramDTO.Id = value.Id;
                paramDTO.Value = value.Value;
            }
            paramDTO.ParameterId = typeParam.Id;
            paramDTO.Name = typeParam.Name;

            productDTO.Parameters.Add(paramDTO);
        }
          // sort products by value
         productDTO.Parameters.Where(y => y.ParameterId == 4)
         .OrderBy(t => t.Value).ToList();

         items.Add(productDTO);
    }        

    return items;           
}
1
-1
4/10/2019 10:45:59 PM

Accepted Answer

It doesn't work because LINQ never affects the original collection, so this doesn't do anything at all:

productDTO.Parameters
    .Where(y => y.ParameterId == 4)
    .OrderBy(t => t.Value)
    .ToList();

Overall, anyway, that code is not even close to good code. Don't create Product (and others) objects just to convert them to their DTO version. Use Entity Framework properly:

var products = await _context.Products
    .Where(prod => prod.ProductTypeId == id)
    .Select(prod => new ProductDTO
    {
        ProductId = prod.ProductId,
        // rest of properties...

        // now get the parameters
        Parameters = prod.Parameters
            // assuming you want to order by value, the question isn't very clear
            .OrderBy(par => par.Value)
            .Select(par => new ParameterDTO
            {
                Id = par.Id,
                // etc
            })
            .ToList()
    })
    .ToListAsync();

Compare that single database query to the multiple calls that the question's code is doing when the data is already in memory.

1
4/10/2019 10:56:08 PM


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow