CODEWITHSUNDEEP
c#linq
Hisham Aburass
Hisham Aburass

Reputation: 625

using Any() with IQueryable List takes long time when executing?

I have the following code:

List<int> projectIds=GetProjectsIds();// size is about 100,000 int value.
List<int> userIds=GetUsersIds();// size is about 100,000 int value.
List<int> nextIds=GetNextIds();// size is about 100,000 int value.


var IQueryList= db.Users.Where(obj=> projectIds.any(x=>x==obj.ProjectID) 
                                    || userIds.any(x=>x==obj.UserId) 
                                    || nextIds.any(x=>x==obj.NextId) );

it takes really long time when converting the IQueryable to List.

var YearsGroup = IQueryList.GroupBy(x => x.CreatedOn.Year)
                           .Select(g => g.FirstOrDefault())
                           .ToList()
                           .OrderByDescending(x => x.CreatedOn.Year);
// it's taking about 3 seconds to get executed.

How can I solve this issues, is there any other ways that I can apply to my code?

Upvotes: 2

Views: 1812

Answers (1)

Barr J
Barr J

Reputation: 10927

It's called Abstraction leak and it is the result of switching between Linq-To-Entities (i.e. Entity Framework) into - Linq-To-Objects.

Basically what the law states is that developers of reliable software must learn the abstraction's underlying details anyway.

Meaning:

your abstraction exposes some of the implementation details and there is nothing you can do with it unfortunetly.

From wikipedia:

A leaky abstraction refers to any implemented abstraction, intended to reduce (or hide) complexity, where the underlying details are not completely hidden

One way to solve it is to create an object with good constraint and maintain linq queries with As less as conditions possible.

In your code you are using Where and GroupBy which might cause for the penalty in the abstraction leak:

List<int> projectIds=GetProjectsIds();// size is about 100,000 int value.
List<int> userIds=GetUsersIds();// size is about 100,000 int value.
List<int> nextIds=GetNextIds();// size is about 100,000 int value.

//Linq to sql statement
var IQueryList= db.Users.Where(obj=> projectIds.Any(x=>x==obj.ProjectID) 
                                    || userIds.Any(x=>x==obj.UserId) 
                                    || nextIds.Any(x=>x==obj.NextId) );

//Linq to object, possible abstraction leak.
var YearsGroup = IQueryList.GroupBy(x => x.CreatedOn.Year)
                           .Select(g => g.FirstOrDefault())
                           .ToList()
                           .OrderByDescending(x => x.CreatedOn.Year);

Upvotes: 3

Related Questions