EF Core 1.0.0 chaining Where clauses

asp.net-core c# entity-framework entity-framework-core sql-server

Question

I have a piece of code that I'm trying to port from EF6 to EF core (in an ASP.NET core 1.0 RTM project). It filters the patients table for all patients with a datetime between the "Verpleegperiode" startdate and enddate and for example with a "Verpleegperiode" in the year 2015. (I used this in my EF6 project to create dynamic filters by adding Where clauses to the IQueryable object, to filter on name, birthdate, and these kind of filters)

The code:

var datetime = DateTime.Parse("5/7/2015");
var patients = _context.Patienten.Include(p => p.Dossiers).ThenInclude(d => d.DossierContact).ThenInclude(dc => dc.Verpleegperiodes)
  .Where(p =>
    p.Dossiers.Any(d =>
      d.DossierContact.Verpleegperiodes.Any(vp =>
         (!vp.BeginDatumUur.HasValue || vp.BeginDatumUur.Value <= datetime) &&
         (!vp.BeginDatumUur.HasValue || vp.EindDatumUur >= datetime))
  ))
  .Where(p =>
    p.Dossiers.Any(d => d.DossierContact == null ||
      d.DossierContact.Verpleegperiodes.Any(vp =>
        !vp.BeginDatumUur.HasValue || vp.BeginDatumUur.Value.Year.ToString() == "2015")
)).ToList();

This results in a query being called for each "Verpleegperiode" in each "Dossier's" "DossierContact". Since there are over 50000 records in each table, this keeps on going and never finishes the query (probably takes hours ...)

In EF6, this generated a query with both Where clauses in 1 and using joins and subqueries automatically created by EF6 and worked.

I am not sure that this is something that isn't finished in EF core, or that this is a fault in my code, which is why I ask this on SO before filing a bug on the EF core Github project.

Relationships defined in EF: Patient 1-to-many Dossiers, Dossier one-to-one DossierContact, DossierContact 1-to-many Verpleegperiodes.

1
2
8/31/2016 11:47:30 AM

Popular Answer

Entity Framework Core working a little bite different then the old versions. In the past your query will be translated in one single big query but with Core the query can be divided into subqueries which is not a bad idea (big query with a lot of joins is very expensive on SQL Server).

There are some similar bugs in github to your bug but you have got it in more complicated form. In your query EF Core going crazy and joins totally in an inefficient way. The below given query is called XXXXXX times, you can check it out just use SQL Profiler or EF Profiler!

SELECT [vp3].[DateTime], [vp3].[PapierId]
FROM [SubPapiers] AS [vp3]

I hope that like those issues will be soon solved or you get a support from EF team just post all classes/entities in case.

1
1/16/2017 10:34:12 PM


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow