How to join a table to itself in EF core?

Question

I have a table (“ProductParameter”) that links two tables together (“Product” and “Parameter”) it has the fields Id, ProductId, ParameterId, Value, each ParameterId has a set [Value] I need to filter the collection first by two ParameterId (where pp.ParameterId = 4 or pp1.ParameterId = 2 and pp1.Value = 'Standard') after filtering one by one ParameterId (where pp.ParameterId = 4).

Example:

+---------+-------------+------------+---------+
|     ID  |  ProductId  |ParameterId | Value   | 
+---------+-------------+------------+---------+
|     25  |    14       |    2       | Standard|
|     26  |    14       |    3       |  CK45   |
|     27  |    14       |    4       |   63    |
|     28  |    15       |    2       |   XXX   |
|     29  |    15       |    3       |  CK45   |
|     30  |    15       |    4       |   70    |
|     34  |    17       |    2       | Standard|
|     35  |    17       |    3       |  CK45   |
|     36  |    17       |    4       |   40    |
|     37  |    25       |    2       | Standard|
|     38  |    25       |    3       |  CK45   |
|     39  |    25       |    4       |   20    |     
+---------+-------------+------------+---------+-

should return:

+-------+
| Value |  
+-------+
|   63  |   
|   40  | 
|   20  | 
+-------+

The logic of the query is as follows:

 select distinct pp.Value from ProductParameters pp
 join ProductParameters pp1 on pp1.ProductId = pp.ProductId and 
 pp1.ParameterId = 2 and pp1.Value = 'Standard'
 where pp.ParameterId = 4

Here is my query in the EF core:

public async Task<IEnumerable<ProductParameter>> GetProductDiameters(long id, string param = "Standard")
{
    var value = await _context.ProductParameters
        .Include(p => p.Product)
        .ThenInclude(p => p.ProductParameters.Where(i => i.Value == param))
        .Where(p => p.ParameterId == id)
        .Distinct()
        .ToListAsync();

    return value;
}

Throws exception:

ArgumentException: The ThenInclude property lambda expression 'p => {from ProductParameter i in p.ProductParameters where ([i].Value == __param_0) select [i]}' is invalid. The expression should represent a property access: 't => t.MyProperty'. To target navigations declared on derived types, specify an explicitly typed lambda parameter of the target type, E.g. '(Derived d) => d.MyProperty'.

Where is the mistake?

.ThenInclude(p => p.ProductParameters.Where(i => i.Value == param)) should be .ThenInclude(p=>p.ProductParameters).Where(i=>i.Product.ProductParameters.Value == param) — DevilSuichiro
than Where(i=>i.Product.ProductParameters does not contain Value — Stas Iliukovich
right, it is a collection. Then the correct syntax (instead of .Where(..)) would be .ForEach(i=>i.Product.ProductParameters=i.Product.ProductParameters.Where(p=>p.Value==param)) — DevilSuichiro

Jens Kloster Jens Kloster · Accepted Answer · 2019-04-26T21:22:49

EF complains because you are trying to filter inside the .ThenInclude(..) statement.

var value = await _context.ProductParameters
    .Include(p => p.Product)
    .ThenInclude(p => p.ProductParameters.Where(i => i.Value == param)) //<-- here
    .Where(p => p.ParameterId == id)
    .Distinct()
    .ToListAsync();

This is a problem, because EF will use the function inside the to determine what tables that should be eager loaded. These functions inside Include(..) and ThenInclude(..) should always point to a related table, and nothing else :)

The correct way of doing your query is like this:

var value = await _context.ProductParameters
    .Include(p => p.Product)
    .ThenInclude(p => p.ProductParameters) // <- removed the Where clause from here
    .Where(p => p.ParameterId == id)
    .Where(p => p.ProductParameters.Value == param)) //<- and added it here
    .Distinct()
    .ToListAsync();

How to join a table to itself in EF core?

1 Answers