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