我正在嘗試使用 EF Core 從資料庫中查詢資料，但情況對我來說有點復雜。我會盡量清楚并綜合我想要完成的事情。
涉及三個表：
- 表 WORK_TO_DO - 列：ID、DESCRIPTION
- 表 PARAM_DEFINITIONS_FOR_WORK - 列：ID、NAME
- 表 PARAM_VALUES_FOR_WORK - 列：WORK_TO_DO_ID、PARAM_DEFINITION_ID、VALUE
假設這些表的類如下。
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; }
}
我有一個 ParamValue 專案串列，其中填充了 ParamDefinitionId 和 Value，但沒有 WorkToDoId。
我想查詢與 ParamValue 項匹配的所有 WorkToDo 項，考慮所有ParamValue 項，而不僅僅是其中的任何一項。
讓我用每個表上的示例記錄來解釋：
要做的作業
| ID | 描述 |
|---|---|
| 1 | 作業示例 A |
| 2 | 作業示例 B |
PARAM_DEFINITIONS_FOR_WORK
| ID | 姓名 |
|---|---|
| 101 | 引數定義 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?
uj5u.com熱心網友回復：
使用FilterByItems擴展，您可以生成所需的查詢：
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
);
轉載請註明出處，本文鏈接：https://www.uj5u.com/qianduan/456800.html
標籤：c# entity-framework linq 实体框架核心
上一篇：物體框架-檢查導航屬性是否存在