Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Group join causes IQueryable to turn into IEnumerable, why?

I am accessing a remote data source and found that a group join causes an IQueryable query to be transformed into an IEnumerable,

question: will this affect performance? I want to offload as much of the query to the database as possible, and not execute anything in memory...

var allusers = repo.All<User>().Where(x => x.IsActive);
var _eprofile = repo.All<Profile>()
    .Where(x => x.IsProfileActive)
    .Join(allusers, x => x.UserID, y => y.UserID, (x, y) => new
    {
        eProfile = x,
        profile = y
    })
    .GroupJoin(_abilities, x => x.eProfile.ID, y => y.ID, (x, y) => new QuoteDTO
    {
        UserID = x.profile.Login,
        Email = x.profile.Email,
        Tel = x.profile.Tel,
        Mobile = x.eProfile.Mobile,
        CompanyID = x.profile.CompanyID,
        Ability = y.Select(c => new AbilityDTO
    {
        Name= c.Name
    })

    });

The line: .GroupJoin(_abilities, x => x.eProfile.ID, y => y.ID, (x, y) => new QuoteDTO

  1. _abilities is an IQueryable, I get the ability of a user, a collection of objects
  2. the second part (x,y) - here y gets transformed into an IEnumerable...

var cLists = List<int>(); //this collection is populated from the client code, lets say it //contains 1,3,55 for arguments sake...

 var _abilities= repo.All<UserAbility>()
      .Where(x => x.Status == Status.Active.ToString())
      .Where(x => cLists.Contains(x.CompetencyID));

NOTE: the reason I use var is so that I can transform into an object of my liking, I was using a lot of anonymous types before the DTO objects were inserted...

like image 370
Haroon Avatar asked Oct 20 '11 15:10

Haroon


1 Answers

The definition of IQueryable:

public interface IQueryable<T> : IEnumerable<T>, IQueryable, IEnumerable

...and ObjectQuery:

ObjectQuery<T> : ObjectQuery, IOrderedQueryable<T>, IQueryable<T>, IEnumerable<T>, IOrderedQueryable, IQueryable, IEnumerable, IListSource

Most (all?) LINQ expressions return an object cast as IEnumerable. But the type of the object remains the type it started as.

No, the database is not hit when a LINQ-to-Entities query is cast as IEnumerable, so performance is not compromised. You can, if you'd like, upcast to either IQueryable or ObjectQuery.

Edit: For clarification, calling ToArray() on an instance of ObjectQuery, IQueryable, or IEnumerable does hit the database and retrieves a result set.

Edit: Creating a new object within a query (i.e. new QuoteDTO) will hit the database, because there's no way to store the new object in a SQL query. Sorry, I missed that bit before.

like image 117
ken Avatar answered Nov 09 '22 02:11

ken