如何使用表达式构建动态查询(IN子句)

本文关键字：IN 子句 查询 动态 何使用 表达式 构建 | 更新日期: 2023-09-27 18:16:06

我有

<!-- language: c# -->
   Expression<Func<TEntity, bool>>
由属性 组成的

  • StartDate可以datetime
  • EndDate datetime
  • ShowArticles bool
  • ShowMaterials bool
  • ShowProducts bool

我想建立一个动态查询，它看起来像:

SELECT * FROM Docs
WHERE StartDate >= @StartDAte 
AND EndDate <= @EndDAte 
AND ArticleInternalType IN (1,2,3)

所有bool类型都限定为复选框(Checked)。如果用户取消选中复选框，查询也应更改。例如，如果用户未选中ShowMaterials复选框:

SELECT * FROM Docs
WHERE StartDate >= @StartDate 
AND EndDate <= @EndDAte 
AND ArticleInternalType IN (1,3)

这个表达式给出了尴尬的结果

FilterExpression =
f => f.DocumentDate >= StartDate.Date && f.DocumentDate <= EndDate.Date
&& (
showArticles == true ? f.ArticleInternalType == 1 : f.ArticleInternalType == -1 ||
showMaterials == true ? f.ArticleInternalType == 0 : f.ArticleInternalType == -1 ||
showProducts == true ? f.ArticleInternalType == 2 : f.ArticleInternalType == -1
);

下面是SQLProfiler

的结果
WHERE 
([Extent1].[DocumentDate] >= @p__linq__0) AND ([Extent1].[DocumentDate] <= @p__linq__1) 
AND 
((CASE WHEN (1 = @p__linq__2) THEN 
        CASE WHEN (1 = [Extent1].[ArticleInternalType]) 
                THEN cast(1 as bit) 
             WHEN (1 <> [Extent1].[ArticleInternalType]) 
                THEN cast(0 as bit) 
        END 
        WHEN (1 = @p__linq__3) THEN 
        CASE WHEN (2 = [Extent1].[ArticleInternalType]) 
                THEN cast(1 as bit) 
            WHEN (2 <> [Extent1].[ArticleInternalType]) 
                THEN cast(0 as bit) 
        END 
        WHEN (1 = @p__linq__4) THEN 
        CASE WHEN (3 = [Extent1].[ArticleInternalType]) 
                THEN cast(1 as bit) 
            WHEN (3 <> [Extent1].[ArticleInternalType]) 
                THEN cast(0 as bit) 
        END 
    ELSE cast(0 as bit) 
END) = 1)
谁能给我点建议，怎么做?

如何使用表达式构建动态查询(IN子句)

您应该能够使用int类型列表:

var types = new List<int>();
if (showArticles) types.Add(1);
if (showMaterials) types.Add(0);
if (showProducts) types.Add(2);
FilterExpression = f => f.DocumentDate >= StartDate.Date
    && f.DocumentDate <= EndDate.Date
    && types.Contains(f. ArticleInternalType);

你也可以简化你写的表达式:

FilterExpression =
f => f.DocumentDate >= StartDate.Date && f.DocumentDate <= EndDate.Date
&& (
    showArticles && f.ArticleInternalType == 1
||  showMaterials && f.ArticleInternalType == 0 
||  showProducts && f.ArticleInternalType == 2
);
相关文章：