{"action":"closed","issue":{"url":"https://api.github.com/repos/aspnet/EntityFramework/issues/3807","repository_url":"https://api.github.com/repos/aspnet/EntityFramework","labels_url":"https://api.github.com/repos/aspnet/EntityFramework/issues/3807/labels{/name}","comments_url":"https://api.github.com/repos/aspnet/EntityFramework/issues/3807/comments","events_url":"https://api.github.com/repos/aspnet/EntityFramework/issues/3807/events","html_url":"https://github.com/aspnet/EntityFramework/issues/3807","id":117919501,"number":3807,"title":"EF 7 SQL statements with no filter are generated for LINQ query using FirstOrDefault()","user":{"login":"jemiller0","id":15931877,"avatar_url":"https://avatars.githubusercontent.com/u/15931877?v=3","gravatar_id":"","url":"https://api.github.com/users/jemiller0","html_url":"https://github.com/jemiller0","followers_url":"https://api.github.com/users/jemiller0/followers","following_url":"https://api.github.com/users/jemiller0/following{/other_user}","gists_url":"https://api.github.com/users/jemiller0/gists{/gist_id}","starred_url":"https://api.github.com/users/jemiller0/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/jemiller0/subscriptions","organizations_url":"https://api.github.com/users/jemiller0/orgs","repos_url":"https://api.github.com/users/jemiller0/repos","events_url":"https://api.github.com/users/jemiller0/events{/privacy}","received_events_url":"https://api.github.com/users/jemiller0/received_events","type":"User","site_admin":false},"labels":[{"url":"https://api.github.com/repos/aspnet/EntityFramework/labels/bug","name":"bug","color":"f7c6c7"},{"url":"https://api.github.com/repos/aspnet/EntityFramework/labels/pri0","name":"pri0","color":"e11d21"}],"state":"closed","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},"milestone":{"url":"https://api.github.com/repos/aspnet/EntityFramework/milestones/7","html_url":"https://github.com/aspnet/EntityFramework/milestones/1.0.0","labels_url":"https://api.github.com/repos/aspnet/EntityFramework/milestones/7/labels","id":666128,"number":7,"title":"1.0.0","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":13,"closed_issues":274,"state":"open","created_at":"2014-05-21T19:25:32Z","updated_at":"2016-06-01T20:13:39Z","due_on":null,"closed_at":null},"comments":10,"created_at":"2015-11-19T22:09:26Z","updated_at":"2016-06-01T20:13:40Z","closed_at":"2016-06-01T20:13:39Z","body":"The following code queries for blogs and returns the first post for the blog. I checked the SQL that is executed using SQL Server Profiler. First a query for the blogs table is executed. That's fine. Then, it appears to execute a separate query for each blog against the posts table. It appears that this is wrong though. The posts queries don't have a where clause. It should be limiting those to just the blog at hand. The end result comes out correct. I'm assuming filtering is happening in memory. However, if the posts table contains lots of rows, it would be unacceptably slow. If it's doing N+1 queries, it should including a WHERE clause and filtering by id in the main table.\r\n\r\n```c#\r\nusing Microsoft.Data.Entity;\r\nusing System.Collections.Generic;\r\nusing System.Linq;\r\n\r\nnamespace ConsoleApplication1\r\n{\r\n    class Program\r\n    {\r\n        static void Main(string[] args)\r\n        {\r\n            using (var bc = new BlogContext())\r\n            {\r\n                bc.Database.EnsureCreated();\r\n                for (int i = 0; i < 10; i++)\r\n                {\r\n                    var b = new Blog { };\r\n                    bc.Blogs.Add(b);\r\n                    var p = new Post { Blog = b };\r\n                    bc.Posts.Add(p);\r\n                }\r\n                bc.SaveChanges();\r\n                bc.Blogs\r\n                    //.Take(100)\r\n                    //.Select(b2 => b2.Posts.Any())\r\n                    .Select(b2 => b2.Posts.FirstOrDefault().Content)\r\n                    .ToArray();\r\n            }\r\n        }\r\n    }\r\n\r\n    public class BlogContext : DbContext\r\n    {\r\n        public DbSet<Blog> Blogs { get; set; }\r\n        public DbSet<Post> Posts { get; set; }\r\n        //protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder) => optionsBuilder.UseSqlServer(@\"Server=(localdb)\\mssqllocaldb;Database=Blogs;Trusted_Connection=True;\");\r\n        protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder) => optionsBuilder.UseSqlServer(@\"Server=localhost;Database=Blogs;Trusted_Connection=True;\");\r\n    }\r\n\r\n    public class Blog\r\n    {\r\n        public int? Id { get; set; }\r\n        public ICollection<Post> Posts { get; set; }\r\n    }\r\n\r\n    public class Post\r\n    {\r\n        public int? Id { get; set; }\r\n        public string Content { get; set; }\r\n        public int? BlogId { get; set; }\r\n        public Blog Blog { get; set; }\r\n    }\r\n}\r\n```\r\n```SQL\r\nSELECT [b2].[Id]\r\nFROM [Blog] AS [b2]\r\ngo\r\nSELECT [p].[BlogId], [p].[Content]\r\nFROM [Post] AS [p]\r\ngo\r\nSELECT [p].[BlogId], [p].[Content]\r\nFROM [Post] AS [p]\r\ngo\r\nSELECT [p].[BlogId], [p].[Content]\r\nFROM [Post] AS [p]\r\ngo\r\nSELECT [p].[BlogId], [p].[Content]\r\nFROM [Post] AS [p]\r\ngo\r\nSELECT [p].[BlogId], [p].[Content]\r\nFROM [Post] AS [p]\r\ngo\r\nSELECT [p].[BlogId], [p].[Content]\r\nFROM [Post] AS [p]\r\ngo\r\nSELECT [p].[BlogId], [p].[Content]\r\nFROM [Post] AS [p]\r\ngo\r\nSELECT [p].[BlogId], [p].[Content]\r\nFROM [Post] AS [p]\r\ngo\r\nSELECT [p].[BlogId], [p].[Content]\r\nFROM [Post] AS [p]\r\ngo\r\nSELECT [p].[BlogId], [p].[Content]\r\nFROM [Post] AS [p]\r\ngo\r\n```"}}
{"actor":{"display_login":"maumar"}}