How to programmatically add JOINs to query from list using EF Core

Question

I am trying to query data from database using EF Core, but the scenery is a bit complicated for me. I will try to be clear and synthesize what I want to accomplish.

There are three tables involved:

Table WORK_TO_DO - Columns: ID, DESCRIPTION
Table PARAM_DEFINITIONS_FOR_WORK - Columns: ID, NAME
Table PARAM_VALUES_FOR_WORK - Columns: WORK_TO_DO_ID, PARAM_DEFINITION_ID, VALUE

Let's say these tables have their classes as below.

public class WorkToDo
{
    public int Id { get; set; }
    public string Description { get; set; }
}

public class ParamDefinition
{
    public int Id { get; set; }
    public string Name { get; set; }
}

public class ParamValue
{
    public int WorkToDoId { get; set; }
    public int ParamDefinitionId { get; set; }
    public string Value { get; set; }
}

I have a list of ParamValue items with ParamDefinitionId and Value populated, but without WorkToDoId.

I want to query all WorkToDo items that match the ParamValue items, considering all the ParamValue items and not just any of them.

Let me explain with example records on each table:

WORK_TO_DO

ID	DESCRIPTION
1	Work Example A
2	Work Example B

PARAM_DEFINITIONS_FOR_WORK

ID	NAME
101	Param Definition X
102	Param Definition Y
103	Param Definition W
104	Param Definition Z
105	Param Definition +

PARAM_VALUES_FOR_WORK

WORK_TO_DO_ID	PARAM_DEFINITION_ID	VALUE
1	101	Param Value J
1	102	Param Value K
2	103	Param Value L
2	104	Param Value M
2	105	Param Value N

So, let's say my list of ParamValues has two items: ParamDefinitionId = 101, Value = "Param Value J" and ParamDefinitionId = 102, Value = "Param Value K". I would like to retrieve the WorkToDo of Id = 1.

If my list of ParamValues had, instead, three items:

ParamDefinitionId = 103, Value = "Param Value L"
ParamDefinitionId = 104, Value = "Param Value M"
ParamDefinitionId = 105, Value = "Param Value N"

Then I would like my query to retrieve the WorkToDo of Id = 2.

Note that the size of ParamValues list is variable!

I'd like to say that I have tried a solution, but the truth is I don't even know how to begin. I've searched on the web but had no luck.

I only have an idea of how I would do this using SQL:

SELECT DISTINCT WORK_TO_DO.ID, WORK_TO_DO.DESCRIPTION
FROM WORK_TO_DO
INNER JOIN PARAM_VALUES_FOR_WORK PV1 ON PV1.WORK_TO_DO_ID = WORK_TO_DO.ID
INNER JOIN PARAM_VALUES_FOR_WORK PV2 ON PV2.WORK_TO_DO_ID = WORK_TO_DO.ID
(... Adding as many INNER JOINs as needed based on list of ParamValues)
INNER JOIN PARAM_VALUES_FOR_WORK PVX ON PVX.WORK_TO_DO_ID = WORK_TO_DO.ID
WHERE PV1.PARAM_DEFINITION_ID = :ParamValues[0].ParamDefinitionId
  AND PV1.VALUE = :ParamValues[0].Value
  AND PV2.PARAM_DEFINITION_ID = :ParamValues[1].ParamDefinitionId
  AND PV2.VALUE = :ParamValues[1].Value
  (... Adding as many conditions as needed based on list of ParamValues)
  AND PVX.PARAM_DEFINITION_ID = :ParamValues[X].ParamDefinitionId
  AND PVX.VALUE = :ParamValues[X].Value

Basically I want to add JOINs and filters to the query based on my list of ParamValues. How can I do this?

Why not just put navigation props on your entities and then query them starting from ParamValue — Caius Jard, Mar 30 '22 at 20:54

score 0 · Accepted Answer · answered Mar 30 '22 at 19:23

0

Use FilterByItems extension and you can generate desired query:

var requiredCount = ParamValues.Count();

var query = context.WorkToDo
    .Where(w => context.ParamValue
        .Where(pv = pv.WorkToDoId == w.Id)
        .FilterByItems(ParamValues, (pv, v) => pv.ParamDefinitionId == v.ParamDefinitionId && pv.Value == v.Name, true)
        .Count() >= requiredCount
    );

answered Mar 30 '22 at 19:23

Svyatoslav Danyliv

21,911
3
16
32

I accepted your answer as useful although I am not willing to use it. Looks like it will do the job - if I changed the last parameter of FilterByItems() to "false" - but seems too complicated, possibly making it hard to maintain. For now I got it to work using Raw SQL. Thank you!! – Marcelo Apr 05 '22 at 12:36
I do not understand phrase **hard to maintain**? Hard to maintain is the SQL. – Svyatoslav Danyliv Apr 05 '22 at 12:38

How to programmatically add JOINs to query from list using EF Core

1 Answers1