C# Linq to SQL 多条件 where 子句

Linq to SQL multiple conditional where clauses

提问by StevieB

At the moment I am retrieving my results as follows :

目前我正在检索我的结果如下：

public List<claim> GetClaims()
{
    return _db.claims.OrderBy(cl => cl.claimId).ToList();
}

But now I am trying to add up to 8 conditional where clauses based on filters above my listview.

但是现在我试图根据列表视图上方的过滤器添加多达 8 个条件 where 子句。

So I turned into:

于是我变成了：

public List<claim> GetFilteredClaims(string submissionId, string claimId,
                                     string organization, string status,
                                     string filterFromDate, string filterToDate,
                                     string region, string approver)
{
    return _db.claims.Where(cl => cl.submissionId == 5).ToList();
}

How can I do a check for each filter to add a where clause only if they contain a value?

如何检查每个过滤器以仅在它们包含值时添加 where 子句？

采纳答案by kevev22

There is no reason why you can't just keep filtering the results by calling .Whereseveral times. Because of the deferred execution of LINQ to SQL it will all be executed in one SQL statement:

没有理由不能通过.Where多次调用来继续过滤结果。由于 LINQ to SQL 的延迟执行，它将全部在一条 SQL 语句中执行：

public List<claim> GetFilteredClaims(string submissionId, string claimId,
                                     string organization, string status,
                                     string filterFromDate, string filterToDate,
                                     string region, string approver)
{
    IQueryable<claim> filteredClaims = _db.claims;

    if (!string.IsNullOrWhiteSpace(submissionId))
    {
        filteredClaims = filteredClaims.Where(claim => claim.submissionId == submissionId);
    }

    if (!string.IsNullOrWhiteSpace(claimId))
    {
        filteredClaims = filteredClaims.Where(claim => claim.claimId == claimId);
    }

    ...

    return filteredClaims.ToList();
}

If you will ever need to add OR conditions, you could take a look at PredicateBuilder.

如果您需要添加 OR 条件，您可以查看PredicateBuilder。

回答by Jason

You could group each filter option with an OR null condition, and chain them all together with AND, like so:

您可以使用 OR 空条件对每个过滤器选项进行分组，并使用 AND 将它们全部链接在一起，如下所示：

public List<claim> GetFilteredClaims(string submissionId, string claimId, string organization, string status, string filterFromDate, string filterToDate, string region, string approver)
    {
        return _db.claims
            .Where(cl => (cl.submissionId == submissionId || submissionId == null)
            && (cl.claimId == claimId || claimId == null)
            && so on and so on... ).ToList();

    }

So if submissionId == null and claimId == "123", it would only filter the results on claimId. You could replace each nullwith an empty string or whatever your "no value" condition is.

因此，如果submissionId == null 和 claimId == "123"，它只会过滤 claimId 上的结果。您可以用null空字符串或任何“无值”条件替换每个。

回答by atbebtg

Take a look at LINQKIT http://www.albahari.com/nutshell/linqkit.aspxIt will allow you to build predicate

看看 LINQKIT http://www.albahari.com/nutshell/linqkit.aspx它将允许您构建谓词

Here is a code that I use but read the documentation above. The predicate will allow you to chain up a bunch of OR or AND or combination of it.

这是我使用的代码，但请阅读上面的文档。谓词将允许您链接一堆 OR 或 AND 或其组合。

private static IEnumerable<SurveyResult> filterData(string firstName, string lastName, List<SurveyResult> results)
{
    var predicate = PredicateBuilder.True<SurveyResult>();


    IEnumerable<SurveyResult> a;
    if (excludeBadData)

    if (firstName != string.Empty)
    {
        predicate = predicate.And(p => p.User.FirstName.ToLower().Contains(firstName.ToLower()));
    }

    if (lastName != string.Empty)
    {
        predicate = predicate.And(p => p.User.LastName.ToLower().Contains(lastName.ToLower()));
    }


    a = from r in results.AsQueryable().Where(predicate) select r;
    return a;
}