如果筛选器在 EF6 中为空，如何删除 where 条件

Question

我的查询如下。如果id = 0，我也不希望在我的where子句中使用它，如果标题为空，那么我需要从where子句中删除它。有什么办法可以做到这一点。我是EF的新手，我尝试了很多，但仍然没有运气。

public dynamic GetData(int id,string title){
    var entryPoint = (from ep in dbContext.tbl_EntryPoint
                     join e in dbContext.tbl_Entry on ep.EID equals e.EID
                     join t in dbContext.tbl_Title on e.TID equals t.TID
                     where e.OwnerID == id || t.title==title
                     select new {
                         UID = e.OwnerID,
                         TID = e.TID,
                         Title = t.Title,
                         EID = e.EID
                     }).Take(10);
}

Answer 1

有几个级别可以做到这一点。您可以使用 where 子句（如 where ((e.OwnerID == id) || (id == 0))）将其嵌入到生成的 SQL 中，也可以完全采用另一种方式，为不同的 where 子句变体提供整个 LINQ 表达式的四个单独副本。我个人建议采用中间方法：使用单独的代码分支根据过滤器构建不同的IQueryable值，而无需重复常见部分：

public dynamic GetData(int id, string title)
{
    var baseQuery =
        from ep in dbContext.tbl_EntryPoint
            join e in dbContext.tbl_Entry on ep.EID equals e.EID
            join t in dbContext.tbl_Title on e.TID equals t.TID
        select new { e, t };
    var filtered = baseQuery; // Implicitly type filtered to match the anonymous type in baseQuery
    if (id > 0)
    {
        if (!string.IsNullOrWhiteSpace(title))
            filtered = baseQuery.Where(ep => (ep.e.OwnerID == id) || (ep.t.title == title));
        else
            filtered = baseQuery.Where(ep => ep.e.OwnerID == id);
    }
    else
    {
        if (!string.IsNullOrWhiteSpace(title))
            filtered = baseQuery.Where(ep => ep.t.title == title);
        else
            filtered = baseQuery;
    }
    var entryPoint = filtered.Select(ep =>
        new
        {
            UID = ep.e.OwnerID,
            TID = ep.e.TID,
            Title = ep.t.Title,
            EID = e.EID
        }).Take(10);
    ...
}

实体框架足够聪明，知道在 baseQuery 中构造的匿名类型中，ep.e引用tbl_Entry联接表，ep.t指联tbl_Title表。下面是从上述代码中生成的 SQL 的示例：

SELECT 
    [Limit1].[EID] AS [EID], 
    [Limit1].[OwnerID] AS [OwnerID], 
    [Limit1].[TID] AS [TID], 
    [Limit1].[Title] AS [Title], 
    [Limit1].[EID1] AS [EID1]
    FROM ( SELECT TOP (10) 
        [Extent1].[EID] AS [EID], 
        [Extent2].[EID] AS [EID1], 
        [Extent2].[OwnerID] AS [OwnerID], 
        [Extent2].[TID] AS [TID], 
        [Extent3].[Title] AS [Title]
        FROM   [dbo].[tbl_EntryPoint] AS [Extent1]
        INNER JOIN [dbo].[tbl_Entry] AS [Extent2] ON [Extent1].[EID] = [Extent2].[EID]
        INNER JOIN [dbo].[tbl_Title] AS [Extent3] ON [Extent2].[TID] = [Extent3].[TID]
        WHERE [Extent2].[OwnerID] = @p__linq__0 OR [Extent3].[Title] = @p__linq__1
    )  AS [Limit1]

（这是用非零id和非空title生成的，因此在第一个if情况下，使用测试id和title的表达式调用.Where。