SelectMany makes too many queries

Question

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

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

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

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?
    }
}

p.s.w.g · Accepted Answer · 2013-07-17T21:52:10.527

2

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); 
    }
}

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.

edited Jul 17 '13 at 21:52

answered Jul 17 '13 at 21:47

p.s.w.g

146,324
30
291
331

Damn! That was easy, I thought it would be much harder. Thanks :) – Stan Jul 17 '13 at 21:49
1

@Steve Glad to help. In general if you leave the collection as an `IQueryable` any Linq methods that execute on it are run on the database, as opposed to `IEnumerble` which are run locally. Both of them take advantage of lazy execution, however, so it won't run at all until you actually try to get the results. – p.s.w.g Jul 17 '13 at 21:56
Yea but `DynamicFields` should probably stay `IEnumerable` because it is using `yield return` and I cannot use `IQueriable` with that. – Stan Jul 17 '13 at 22:00
Correct, `yield return` / `yield break` only work when the return type is `IEnumerable`, but yeah, I think you get the gist of it. Happy coding. – p.s.w.g Jul 17 '13 at 22:06
Hey, but what if I have not made all the helper tables and let EF connect them? Can I have an option here: http://stackoverflow.com/questions/17858004/how-to-avoid-n1-in-this-query ? – Stan Jul 25 '13 at 13:20

SelectMany makes too many queries

1 Answers1

Linked