4

---------------- describing why this is not a duplicate -------------

EDIT: This is different than the question marked as a duplicate because I am not trying to re-query the list that is returned (and further refine it). E.G. If I change the ClientProviderAccountRep.GetByProviderID method from a .Where to a .First(), I would still expect the following to be optimized with an EXISTS:

If I change the GetByProviderID to this:

public ClientProviderAccount GetByProviderID(Guid providerID)
{
    return Db.ClientProviderAccounts.First(cpa => cpa.IsActive && cpa.ProviderID == providerID);
}

Then use that method here:

var clientProviderAccount = ClientProviderAccountRep.GetByProviderID(providerId);
var schedulesExist = clientProvider.Schedules.Any();

Our application uses entity framework 6.1. The entity can have a large collection of children (schedules). We have a method that is transforming the EF entity into a viewmodel entity. For the purpose of the ViewModel, it does not need all the schedules. It just needs to know if one exists. I expected using .Any() would not get the query and create the whole list of schedules.

var lst = ClientProviderAccountRep.GetByProviderID(providerID).Select(x => new ClientProviderAccountVM
            {
                ClientProviderAccountID = x.ClientProviderAccountID,
                ClientID = x.ClientID,
                ClientName = x.Client.Name,
                ProviderID = x.ProviderID,
                ProviderName = x.Provider.Name,
                Name = x.Name,
                LoginID = x.LoginID,
                Password = x.Password,
                StartRecordNumber = x.StartRecordNumber,
                IsActive = x.IsActive,
                SchedulesExist = x.Schedules.Any(s => s.IsActive == true)
            })

The problem line is:

SchedulesExist = x.Schedules.Any(s => s.IsActive == true)

I would have expected a really quick EXISTS call, but it is getting all the schedule objects first.

Things I have tried:

  1. foreach instead of the .GetByProviderID(providerID).Select(x. then creating ViewModel with the object initializer syntax.
  2. in a loop, creating a new ViewModel and setting each property explicitly.

I am pretty sure I tried other permutations, but have not been successful.

here is the generated DbContext:

public partial class AppLogDb : DbContext
{
    public AppLogDb()
        : base("name=AppLogDb")
    {
        this.Configuration.LazyLoadingEnabled = false;
    }

    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
        throw new UnintentionalCodeFirstException();
    }

    public virtual DbSet<ApplicationLog> ApplicationLogs { get; set; }
    public virtual DbSet<AuditLog> AuditLogs { get; set; }
    public virtual DbSet<LogDetail> LogDetails { get; set; }
    public virtual DbSet<LoginLog> LoginLogs { get; set; }
}

Here is the GetByProviderID method. The Db is the database context. I know there is a too list, but I don' think that is it because it is still lazy loading the schedules after returning from this method. The ToLis(), just realizes the top parent object (I think).

public IEnumerable<ClientProviderAccount> GetByProviderID(Guid providerID)
{
    var clientProviderAccount = Db.ClientProviderAccounts
        .Where(cpa => cpa.IsActive && cpa.ProviderID == providerID)
        .ToList();

    return clientProviderAccount.OrderBy(cpa => cpa.Name);
DanCaveman
  • 676
  • 1
  • 6
  • 22
  • 2
    What does your `GetByProviderID` method do. I suspect the database query is being executed within that method, so you already have all the child records by the time you reach your `Select` statement. On its own, I would have expected `SchedulesExist = x.Schedules.Any(s => s.IsActive == true)` to use `EXISTS` once executed. – Tone Sep 14 '17 at 00:04
  • @Tone - I have updated the question. As I mentioned in the edit, the ClientProviderAccount.Schedules is successfully lazy loading after returning from the GetByProviderId. I will try getting rid of the ToList() to see if it makes a difference, but I am not confident it will work. – DanCaveman Sep 14 '17 at 02:22
  • You really need to build your projection (`Select`) on real EF `IQueryable`. By real I mean returned by EF query, not `AsQueryable()` on in memory collection. – Ivan Stoev Sep 14 '17 at 02:30
  • @IvanStoev - that may be a better implementation (I am relatively new to EF), but IQueryable or not, shouldn't the ClientProviderAccount object know how to build the optimized query? What if I was just returning a single object (even with IQueryable), but just want to transform that single object, I wouldn't call it on the IQueryable, but the single object itself. Am I missing something? I will try this as well and report back if it works. – DanCaveman Sep 14 '17 at 03:03
  • @IvanStoev please look at my edit and let me know if I am missing something. I don't think this is a duplicate. – DanCaveman Sep 14 '17 at 03:54
  • 2
    @DanKaufman Try changing `public IQueryable GetByProviderID(Guid providerID) { return Db.ClientProviderAccounts.Where(cpa => cpa.IsActive && cpa.ProviderID == providerID).OrderBy(cpa => cpa.Name); }` and run the code in question. Do you see the difference? – Ivan Stoev Sep 14 '17 at 06:18
  • 2
    @DanKaufman Re your addition, although `GetByProviderID` might just retrieve the parents, it will be using relationship fixup on these proxies to retrieve the child records when you realize the select query. Your cleanest options are return `IQueryable` from `GetByProviderID` (no ToList) or do all in a single query. Other options will add complication to your code (e.g. manually handling child records). More information on proxies, fixup at [Entity Framework Relationships and Navigation Properties - MSDN](https://msdn.microsoft.com/en-us/library/jj713564(v=vs.113).aspx) – Tone Sep 14 '17 at 06:27
  • @IvanStoev - I tested IQueryable and it worked as expected - thanks. If you put it as an answer, I will mark it as the solution. One question: If I wanted a single object (like by key) like my repo change in the edit above (returning `.First()` with no `.ToList()`, is the proper way to return IQueryable from the DbContext, then do the `.First()` so it is efficiently creates the "EXISTS"? I assumed that if I expected a single object, I could return first, then access the child collection at a later time and still have optimized queries. – DanCaveman Sep 14 '17 at 16:37
  • 2
    @DanKaufman I can't answer the question marked as duplicate :) But glad it helped you. Regarding the last question, yes, keep `IQueryable` result as long as possible. This way you could apply additional filtering, ordering, grouping, projection etc. All they will build a single db query with only necessary data which will be executed when you apply some final materialization operator like `ToList`, `First(OrDefault)`, `Count` etc. Once you get an object, every further query starts working in memory (and lazy loading when enabled) on fully loaded objects. – Ivan Stoev Sep 14 '17 at 16:46

0 Answers0