{"action":"created","issue":{"url":"https://api.github.com/repos/aspnet/EntityFramework/issues/6448","repository_url":"https://api.github.com/repos/aspnet/EntityFramework","labels_url":"https://api.github.com/repos/aspnet/EntityFramework/issues/6448/labels{/name}","comments_url":"https://api.github.com/repos/aspnet/EntityFramework/issues/6448/comments","events_url":"https://api.github.com/repos/aspnet/EntityFramework/issues/6448/events","html_url":"https://github.com/aspnet/EntityFramework/issues/6448","id":174445056,"number":6448,"title":"Chaining where clauses creates 1000ths of queries","user":{"login":"nfdevil","id":7434048,"avatar_url":"https://avatars.githubusercontent.com/u/7434048?v=3","gravatar_id":"","url":"https://api.github.com/users/nfdevil","html_url":"https://github.com/nfdevil","followers_url":"https://api.github.com/users/nfdevil/followers","following_url":"https://api.github.com/users/nfdevil/following{/other_user}","gists_url":"https://api.github.com/users/nfdevil/gists{/gist_id}","starred_url":"https://api.github.com/users/nfdevil/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/nfdevil/subscriptions","organizations_url":"https://api.github.com/users/nfdevil/orgs","repos_url":"https://api.github.com/users/nfdevil/repos","events_url":"https://api.github.com/users/nfdevil/events{/privacy}","received_events_url":"https://api.github.com/users/nfdevil/received_events","type":"User","site_admin":false},"labels":[{"url":"https://api.github.com/repos/aspnet/EntityFramework/labels/type-investigation","name":"type-investigation","color":"0052cc"}],"state":"open","locked":false,"assignee":{"login":"maumar","id":1591930,"avatar_url":"https://avatars.githubusercontent.com/u/1591930?v=3","gravatar_id":"","url":"https://api.github.com/users/maumar","html_url":"https://github.com/maumar","followers_url":"https://api.github.com/users/maumar/followers","following_url":"https://api.github.com/users/maumar/following{/other_user}","gists_url":"https://api.github.com/users/maumar/gists{/gist_id}","starred_url":"https://api.github.com/users/maumar/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/maumar/subscriptions","organizations_url":"https://api.github.com/users/maumar/orgs","repos_url":"https://api.github.com/users/maumar/repos","events_url":"https://api.github.com/users/maumar/events{/privacy}","received_events_url":"https://api.github.com/users/maumar/received_events","type":"User","site_admin":false},"assignees":[{"login":"maumar","id":1591930,"avatar_url":"https://avatars.githubusercontent.com/u/1591930?v=3","gravatar_id":"","url":"https://api.github.com/users/maumar","html_url":"https://github.com/maumar","followers_url":"https://api.github.com/users/maumar/followers","following_url":"https://api.github.com/users/maumar/following{/other_user}","gists_url":"https://api.github.com/users/maumar/gists{/gist_id}","starred_url":"https://api.github.com/users/maumar/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/maumar/subscriptions","organizations_url":"https://api.github.com/users/maumar/orgs","repos_url":"https://api.github.com/users/maumar/repos","events_url":"https://api.github.com/users/maumar/events{/privacy}","received_events_url":"https://api.github.com/users/maumar/received_events","type":"User","site_admin":false}],"milestone":{"url":"https://api.github.com/repos/aspnet/EntityFramework/milestones/19","html_url":"https://github.com/aspnet/EntityFramework/milestones/1.1.0-preview1","labels_url":"https://api.github.com/repos/aspnet/EntityFramework/milestones/19/labels","id":1748298,"number":19,"title":"1.1.0-preview1","description":"","creator":{"login":"rowanmiller","id":4741851,"avatar_url":"https://avatars.githubusercontent.com/u/4741851?v=3","gravatar_id":"","url":"https://api.github.com/users/rowanmiller","html_url":"https://github.com/rowanmiller","followers_url":"https://api.github.com/users/rowanmiller/followers","following_url":"https://api.github.com/users/rowanmiller/following{/other_user}","gists_url":"https://api.github.com/users/rowanmiller/gists{/gist_id}","starred_url":"https://api.github.com/users/rowanmiller/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/rowanmiller/subscriptions","organizations_url":"https://api.github.com/users/rowanmiller/orgs","repos_url":"https://api.github.com/users/rowanmiller/repos","events_url":"https://api.github.com/users/rowanmiller/events{/privacy}","received_events_url":"https://api.github.com/users/rowanmiller/received_events","type":"User","site_admin":false},"open_issues":117,"closed_issues":202,"state":"open","created_at":"2016-05-04T18:23:47Z","updated_at":"2016-09-29T22:58:27Z","due_on":null,"closed_at":null},"comments":6,"created_at":"2016-09-01T06:18:56Z","updated_at":"2016-09-29T23:30:32Z","closed_at":null,"body":"### Steps to reproduce\r\nThe LINQ-to-SQL code:\r\n``` C#\r\n_context.Patienten.Include(p => p.Dossiers).ThenInclude(d => d.DossierContact).ThenInclude(dc => dc.Verpleegperiodes)\r\n                .Where(p =>\r\n                    p.Dossiers.Any(d =>\r\n                            d.DossierContact.Verpleegperiodes.Any(date =>\r\n                                (!date.BeginDatumUur.HasValue || date.BeginDatumUur.Value <= datetime) &&\r\n                                (!date.BeginDatumUur.HasValue || date.EindDatumUur.Value >= datetime))\r\n                    )).Where(p =>\r\n                    p.Dossiers.Any(d => d.DossierContact == null ||\r\n                        d.DossierContact.Verpleegperiodes.Any(vp =>\r\n                            !vp.BeginDatumUur.HasValue || vp.BeginDatumUur.Value.Year.ToString() == \"2015\"))).Count()\r\n```\r\n\r\n[The files on a public Gist](https://gist.github.com/nfdevil/9a43ccd0ceb5d2ffd387b1c82e5a560d)\r\n\r\nRelationships defined in the DbEntityConfiguration's: Patient 1-to-many Dossiers, Dossier one-to-one DossierContact, DossierContact 1-to-many Verpleegperiodes.\r\n\r\n\r\n### The issue\r\nI 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).\r\n\r\nThis 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 ...)\r\n\r\nIn EF6, this generated a query with both Where clauses in 1 and using joins and subqueries automatically created by EF6 and worked.\r\n\r\nI am not sure that this is something that isn't finished in EF core, or that this is a fault in my code.\r\n[I've asked this on StackOverflow yesterday](http://stackoverflow.com/questions/39248809/ef-core-1-0-0-chaining-where-clauses).\r\n\r\n~~If you are seeing an exception, include the full exceptions details (message and stack trace).~~\r\n\r\n~~Exception message:~~\r\n~~Stack trace:~~\r\n\r\n### Further technical details\r\n\r\nEF Core version: (found in project.json or packages.config)   \r\n`\"Microsoft.EntityFrameworkCore\": \"1.0.0\"`\r\n`\"Microsoft.EntityFrameworkCore.SqlServer\": \"1.0.0\"`\r\n\r\nOperating system: Windows 8.1/Windows Server 2012 (tested on both my pc and our server)\r\nVisual Studio version: (e.g. VS 2013 or n/a) VS 2015 Professional Update 3\r\n\r\nThe logs of the query:\r\n![Screen1](https://cloud.githubusercontent.com/assets/7434048/18157338/0d27009c-701d-11e6-9b2a-56a42944f212.PNG)\r\n![Screen2](https://cloud.githubusercontent.com/assets/7434048/18157340/0e89346e-701d-11e6-8f75-e9048fcc02a3.PNG) these small queries keep on being created 1000ths of times."},"comment":{"url":"https://api.github.com/repos/aspnet/EntityFramework/issues/comments/250621100","html_url":"https://github.com/aspnet/EntityFramework/issues/6448#issuecomment-250621100","issue_url":"https://api.github.com/repos/aspnet/EntityFramework/issues/6448","id":250621100,"user":{"login":"maumar","id":1591930,"avatar_url":"https://avatars.githubusercontent.com/u/1591930?v=3","gravatar_id":"","url":"https://api.github.com/users/maumar","html_url":"https://github.com/maumar","followers_url":"https://api.github.com/users/maumar/followers","following_url":"https://api.github.com/users/maumar/following{/other_user}","gists_url":"https://api.github.com/users/maumar/gists{/gist_id}","starred_url":"https://api.github.com/users/maumar/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/maumar/subscriptions","organizations_url":"https://api.github.com/users/maumar/orgs","repos_url":"https://api.github.com/users/maumar/repos","events_url":"https://api.github.com/users/maumar/events{/privacy}","received_events_url":"https://api.github.com/users/maumar/received_events","type":"User","site_admin":false},"created_at":"2016-09-29T23:30:32Z","updated_at":"2016-09-29T23:30:32Z","body":"This works now, most likely fixed by 757e6d4b1bcdad9e4831321edb9ecf42f4007fbd\r\n\r\nCurrently the query that is produced is as follows:\r\n\r\n```sql\r\nSELECT COUNT(*)\r\nFROM [Patient] AS [p]\r\nWHERE EXISTS (\r\n    SELECT 1\r\n    FROM [Dossier] AS [d]\r\n    LEFT JOIN [DossierContact] AS [d.DossierContact] ON [d].[DossierId] = [d.DossierContact].[DossierId]\r\n    WHERE EXISTS (\r\n        SELECT 1\r\n        FROM [Verpleegperiodes] AS [date]\r\n        WHERE (([date].[BeginDatumUur] IS NULL OR ([date].[BeginDatumUur] <= @__datetime_0)) AND ([date].[BeginDatumUur] IS NULL OR ([date].[EindDatumUur] >= @__datetime_1))) AND ([d.DossierContact].[DossierContactId] = [date].[DossierContactId])) AND ([p].[PatientId] = [d].[PatientId])) AND EXISTS (\r\n    SELECT 1\r\n    FROM [Dossier] AS [d0]\r\n    LEFT JOIN [DossierContact] AS [d.DossierContact1] ON [d0].[DossierId] = [d.DossierContact1].[DossierId]\r\n    WHERE ([d.DossierContact1].[DossierContactId] IS NULL OR EXISTS (\r\n        SELECT 1\r\n        FROM [Verpleegperiodes] AS [vp]\r\n        WHERE ([vp].[BeginDatumUur] IS NULL OR (DATEPART(year, [vp].[BeginDatumUur]) = 2015)) AND ([d.DossierContact1].[DossierContactId] = [vp].[DossierContactId]))) AND ([p].[PatientId] = [d0].[PatientId]))\r\n```"}}
{"actor":{"display_login":"maumar"}}