CODEWITHSUNDEEP
c#entity-frameworklinq
Stas Iliukovich
Stas Iliukovich

Reputation: 69

Why does my query fail to load related data in EF?

I’m beginner to EF and to linq. My models consists of tables Products/ Product Types/Parameters/Product Parameters. I'm having some difficulties with loading the data using eager loading via the Include() method. I need get relateddata from 4 tables: fieldes Products -> fielde ProductType Name -> fielde Parameter Name -> fielde ProductParameter Value

Model and DTO:

 public class Product 
{
    public long ProductId { get; set; }

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

    public long ProductTypeId {  get; set; }
    public ProductType ProductType { get; set; }

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

public class ProductType 
{
    public long ProductTypeId { get; set; }
    public string NameType { get; set; }

    public ICollection<Product> Products { get; set; }
    public ICollection<Parameter> Parameters { get; set; }

}
public class Parameter 
{
    public long ParameterId { get; set; }
    public string Name { get; set; }

    public long ProductTypeId { get; set; }
    public ProductType ProductType { get; set; }

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

 public class ProductParameter 
{
    public long ProductParameterId { get; set; }

    public long ProductId { get; set; }
    public long ParameterId { get; set; }

    public Parameter Parameter { get; set; }
    public Product Product { get; set; }

    public string Value { get; set; }
}

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

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

    public long ParameterId { get; set; }
    public string Name { get; set; }

    public long ProductParameterId { get; set; }
    public string Value { get; set; }

}

This my method:

public async Task<IEnumerable<ProductDTO>> GetProducts()
    {
       var items = await _context.Products
           .Include(t => t.ProductType)
                   .ThenInclude(p => p.Parameters)
                   .ThenInclude(pp => pp.ProductParameters)
            .Include(t => t.ProductParameters)
            .Select(p => new ProductDTO
             {
               ProductId = p.ProductId,
               Number = p.Number,
               Amount = p.Amount,
               PrimeCostEUR = p.PrimeCostEUR,
               NameType = p.ProductType.NameType,
               ProductTypeId = p.ProductTypeId,
               Name = p.Parameter.Name,
               Value = p.ProductParameter.Value
             })
              .Where(p => p.ProductTypeId == 1)
               .ToListAsync();
        return items;
 }

This is the error i have encountered:

Error CS1061 'Product' does not contain a definition for 'Parameter'/'ProductParameter' and no accessible extension method 'Parameter' accepting a first argument of type 'Product' could be found (are you missing a using directive or an assembly reference?

Upvotes: 0

Views: 51

Answers (1)

Steve Py
Steve Py

Reputation: 34653

Include will only perform an eager load, it is not neccessary when performing a Select operation. From what I can see the code you supplied should not even compile given you are trying to load a Parameter and ProductParameter from a Product entity, and this entity does not have either of these references.

The first thing to look at is your entity relationships. Looking at your object model it looks like you have a many to 1 between Product and ProductType, and then a Many to Many between Product and Parameter facilitated by the ProductParameter table. There is also a collection of Parameters on a ProductType which I would be fairly suspicious of. When dealing with relationships I recommend using single-direction references by default, and only enabling a bi-directional reference if it is needed. For example, a Product needs a reference to a product type, however, does product type really need references to all products of that type? (we can still get to that if we need it by querying from Product) Fewer bi-directional references are easier to work with.

To get your product DTO you're going to need to address whether the DTO represents a product (with it's parameters) or a combination of a single product and parameter. One product has several parameters, so if the DTO represents a single product, that DTO will need to handle a collection of parameters. If the DTO represents a combination of a product and parameter, then a product with 3 associated parameters would result in 3x DTOs being created.

For that second case, given the DTO structure you currently have, you'd be looking at a Linq query more like:

   var items = await _context.Products
        .SelectMany(p => p.ProductParameters)
        .Select(pp => new ProductDTO
         {
           ProductId = pp.ProductId,
           Number = pp.Product.Number,
           Amount = pp.Product.Amount,
           PrimeCostEUR = pp.Product.PrimeCostEUR,
           NameType = pp.Product.ProductType.NameType,
           ProductTypeId = pp.Product.ProductTypeId,
           Name = pp.Parameter.Name,
           Value = pp.Value
         })
          .Where(pp => pp.Product.ProductTypeId == 1)
          .ToListAsync();
    return items;

The query would run against ProductParameters to get access to the product and each of the parameters, as well as the product parameter value.

Upvotes: 1

Related Questions