如何在EF中获取与组合（键/值）列表匹配的记录？ [英] How to get records in EF that match a list of combinations (key/values)?

问题描述

我有一个数据库表，其中包含每个用户/年组合的记录。

I have a database table with records for each user/year combination.

如何使用EF和用户ID /年组合列表从数据库中获取数据？
样本组合：

How can I get data from the database using EF and a list of userId/year combinations? Sample combinations:

UserId      Year
1           2015
1           2016 
1           2018
12          2016
12          2019
3           2015
91          1999

我只需要上述组合中定义的记录。

I only need the records defined in above combinations. Can't wrap my head around how to write this using EF/Linq?

 List<UserYearCombination> userYears =  GetApprovedYears();

 var records = dbcontext.YearResults.Where(?????);

类

public class YearResult
{
    public int UserId;
    public int Year;
    public DateTime CreatedOn;
    public int StatusId;
    public double Production;
    public double Area;
    public double Fte;
    public double Revenue;
    public double Diesel;
    public double EmissionsCo2;
    public double EmissionInTonsN;
    public double EmissionInTonsP;
    public double EmissionInTonsA;
        ....
}

public class UserYearCombination
{
    public int UserId;
    public int Year;
}

推荐答案

这是一个臭名昭著的问题，我之前在这里进行了讨论。克里希纳·穆帕拉（Krishna Muppalla）的解决方案是我在那里提出的解决方案之一。它的缺点是它不可靠，即无法从涉及的数据库字段上的任何索引中受益。

This is a notorious problem that I discussed before here. Krishna Muppalla's solution is among the solutions I came up with there. Its disadvantage is that it's not sargable, i.e. it can't benefit from any indexes on the involved database fields.

同时，我提出了另一种可能对解决问题有帮助的解决方案。在某些情况下。基本上，它按字段之一对输入数据进行分组，然后按分组键和包含组元素的包含查询查找和合并数据库数据：

In the meantime I coined another solution that may be helpful in some circumstances. Basically it groups the input data by one of the fields and then finds and unions database data by grouping key and a Contains query of group elements:

IQueryable<YearResult> items = null;

foreach (var yearUserIds in userYears.GroupBy(t => t.Year, t => t.UserId))
{
    var userIds = yearUserIds.ToList();
    var grp = dbcontext.YearResults
        .Where(x => x.Year == yearUserIds.Key 
                 && userIds.Contains(x.UserId));
    items = items == null ? grp : items.Concat(grp);
}

我使用 Concat 这是因为 Union 会浪费时间使结果变得明显，并且在EF6中， Concat 会生成带有链接的的SQL UNION 语句，而 Union 会生成嵌套的 UNION 语句，并且可能会达到最大嵌套级别。

I use Concat here because Union will waste time making results distinct and in EF6 Concat will generate SQL with chained UNION statements while Union generates nested UNION statements and the maximum nesting level may be hit.

当索引到位时，此查询可能会表现良好。从理论上讲，SQL语句中 UNION 的最大数量是无限的，但是 IN 中的项目数量是无限的子句（包含 的子句）不应超过几千。这意味着
的数据内容将确定哪个分组字段的效果更好，年份或 UserId 。面临的挑战是使 UNION 的数量最小化，同时使所有 IN 子句中的项目数保持低于大约。 5000。

This query may perform well enough when indexes are in place. In theory, the maximum number of UNIONs in a SQL statement is unlimited, but the number of items in an IN clause (that Contains translates to) should not exceed a couple of thousands. That means that the content of your data will determine which grouping field performs better, Year or UserId. The challenge is to minimize the number of UNIONs while keeping the number of items in all IN clauses below approx. 5000.

这篇关于如何在EF中获取与组合（键/值）列表匹配的记录？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！