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
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...
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.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With