Home > Net >  How to programmatically add JOINs to query from list using EF Core
How to programmatically add JOINs to query from list using EF Core

Time:04-05

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?

CodePudding user response:

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
    );
  • Related