Linq to SQL多条件where子句

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.

但是现在我想在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子句？

3 个解决方案

#1

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

你没有理由不能通过多次调用来过滤结果。由于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。

#2

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

您可以将每个过滤器选项分组为OR null条件，并将它们与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 null with an empty string or whatever your "no value" condition is.

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

#3

Take a look at LINQKIT http://www.albahari.com/nutshell/linqkit.aspx It 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;
}

#1