如何用LINQ按SchemeId+InvestorId+Date组合过滤数据库数据？

定义的过滤器类 #

public class SchemeInvestorDataDateFilter
{
    public int SchemeId { get; set; }
    public int InvestorId { get; set; }
    public DateTime Date { get; set; }
}

当前不符合需求的LINQ查询 #

我用LINQ传入过滤器对象列表查询数据库，但返回结果不符合预期：

var schemeIds = filters.Select(x => x.SchemeId);
var investorIds = filters.Select(x => x.InvestorId);
var dates = filters.Select(x => x.Date);

var query = Context.SchemeInvestorDataDates
        .Where(x =>
        schemeIds.Contains(x.SchemeInvestor.SchemeId) &&
        investorIds.Contains(x.SchemeInvestor.InvestorId) &&
        dates.Contains(x.Date) &&
        !x.IsDeleted
        );

这段代码会返回所有传入SchemeId、InvestorId、Date的排列组合结果，包含多余数据。我需要的是只返回与过滤器列表中某一组SchemeId+InvestorId+Date完全匹配的数据库行。

已实现的Dapper方案 #

我已经能用Dapper实现该需求，代码如下：

StringBuilder query = new StringBuilder("select * from schemeinvestors si join (");

for (int i = 0; i < schemeInvestorIds.Count(); i++)
{
    var data = schemeInvestorIds.ElementAt(i);
    query.Append($"select {data.investorId} as InvestorId, {data.schemeId} as SchemeId");
    query.Append(schemeInvestorIds.Count() - 1 == i ? "" : " union ");
}

query.Append(") x on si.InvestorId=x.InvestorId and si.SchemeId=x.SchemeId");

using (var connection = BuildConnection())
{
    connection.Open();

    return await connection.QueryAsync<SchemeInvestor>(query.ToString());
}

LINQ实现方案 #

可以通过以下两种方式用LINQ实现组合匹配过滤：

方法1：用Any匹配完整组合 #

直接在Where中检查当前行的三个字段组合是否存在于过滤器列表：

var query = Context.SchemeInvestorDataDates
    .Where(x => !x.IsDeleted && 
                filters.Any(f => 
                    f.SchemeId == x.SchemeInvestor.SchemeId && 
                    f.InvestorId == x.SchemeInvestor.InvestorId && 
                    f.Date == x.Date));

这种方式会生成类似EXISTS子查询的SQL，对应Dapper方案中的UNION逻辑，确保只有完全匹配的组合才会被返回。

方法2：投影组合后关联查询 #

先将过滤器列表投影成匿名类型的组合，再与数据库表进行Join操作：

var filterCombinations = filters.Select(f => new 
{ 
    f.SchemeId, 
    f.InvestorId, 
    f.Date 
});

var query = from data in Context.SchemeInvestorDataDates
            join filter in filterCombinations 
                on new 
                { 
                    SchemeId = data.SchemeInvestor.SchemeId, 
                    InvestorId = data.SchemeInvestor.InvestorId, 
                    data.Date 
                } equals filter
            where !data.IsDeleted
            select data;

这种方式会生成类似Dapper方案中的JOIN查询逻辑，性能与方法1相近，具体取决于数据库的查询优化。

额外优化建议 #

• 如果过滤器列表数据量大，建议先对组合去重：filters.DistinctBy(f => new { f.SchemeId, f.InvestorId, f.Date })（需.NET 6及以上版本支持）。
• 给数据库中SchemeId、InvestorId、Date的组合字段添加索引，提升查询效率。

内容的提问来源于stack exchange，提问作者Pawan Nogariya