SelectMany进行过多查询 [英] SelectMany makes too many queries

问题描述

我在用户组中有动态字段，我想根据用户是哪个用户组来选择它们.

I have dynamic fields inside usergroups and I want to select them based on what usergroups user is.

基本上，我想模拟像.Where(x => x.UserGroupId == x || ...这样的查询，因为否则它会进行约20个查询以获取动态字段.

Basically I want to simulate query like .Where(x => x.UserGroupId == x || ... because otherwise it makes about 20 queries just to get dynamicfields.

也许我可以以某种方式将整数数组传递为UserGroupId，它将使用||模拟查询.

Maybe I can somehow pass array of integers as UserGroupId and it will simulate the query with ||.

这里是我的示例，两个结果输出相同，唯一的区别是第一个对数据库有20个查询，第二个只有1个查询.

Here is my example, both results output is same, only difference is that first one has 20 queries to database and second has only 1.

public IEnumerable<UserGroup> UserGroups
{
    get
    {
        var db = new MainDataContext();
        return db.UserGroupUsers.Where(x => x.UserId == this.Id).Select(x => x.UserGroup);
    }
}
public IEnumerable<UserDynamicField> DynamicFields
{
    get
    {
        var db = new MainDataContext();

        var fields = this.UserGroups.SelectMany(x => x.UserGroupDynamicFields); // 20+ queries

        var fields2 = db.UserGroupDynamicFields.Where(x =>
            x.UserGroupId == 1 ||
            x.UserGroupId == 2 ||
            x.UserGroupId == 3 ||
            x.UserGroupId == 4 ||
            x.UserGroupId == 5 ||
            x.UserGroupId == 6 ||
            x.UserGroupId == 7 ||
            x.UserGroupId == 8 ||
            x.UserGroupId == 9 ||
            x.UserGroupId == 10); // 1 query, maybe I can somehow pass array of Id's here?
    }
}

推荐答案

尝试将其转换为IQueryable<T>而不是IEnumerable<T>:

Try converting it to an IQueryable<T> instead of an IEnumerable<T>:

public IQueryable<UserGroup> UserGroups
{
    get
    {
        var db = new MainDataContext();
        return db.UserGroupUsers.Where(x => x.UserId == this.Id)
                                .Select(x => x.UserGroup);
    }
}
public IQueryable<UserDynamicField> DynamicFields
{
    get
    {
        // 1 query
        return this.UserGroups.SelectMany(x => x.UserGroupDynamicFields); 
    }
}

这将使Linq利用以下事实:在迭代之前不必将结果集拉入内存，因此将其转换为常规的SQL连接.

This will allow Linq to take advantage of the fact that it doesn't have to pull the result set in memory until it's iterated, so this will be translated to a conventional SQL join.

这篇关于SelectMany进行过多查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！