The projection Entity framework 6 generates the SQL equivalent of "Select *" and does not create a WHERE clause

Question

I support an ASP.NET WebAPI2 application with Entity framework 6 and an MSSQL server database. IoC Container - Windsor Castle. I have a method in my repository that I use to get some information for the user from the database. Since I don't need every column, I thought I would use projection. The problem is that the generated SQL selects ALL the columns in my table. Here is the dbcontext

public partial class SecurityContext : DbContext { public SecurityContext() : base("name=SecurityContext") { } public virtual DbSet<User> secUsers { get; set; } } 

Here where the context is declared / initialized in the repository

 public class BaseRepository<T> : IRepository<T> where T : class { protected DbContext context; public BaseRepository() { context = new SecurityContext(); } public BaseRepository(DbContext context) { this.context = context; } //elided } 

and here is the method in the repository

 public User FindUserForLoginVerification(string name) { var loginInfo = context.Set<User>() .Where(c => c.LoginName == name) .Select(c => new { LoginName = c.LoginName, Password = c.HashedPassword, Salt = c.PasswordHashSalt }) .SingleOrDefault(); return new User() { LoginName = loginInfo.LoginName, HashedPassword = loginInfo.Password, PasswordHashSalt = loginInfo.Salt }; } 

SQL is output here.

 SELECT [Extent1].[UserId] AS [UserId], [Extent1].[CreatedByUserId] AS [CreatedByUserId], [Extent1].[Comment] AS [Comment], [Extent1].[CreatedDate] AS [CreatedDate], [Extent1].[DefaultCulture] AS [DefaultCulture], [Extent1].[EmailAddress] AS [EmailAddress], [Extent1].[FirstName] AS [FirstName], [Extent1].[IsDeleted] AS [IsDeleted], [Extent1].[IsExcludedFromPasswordPolicy] AS [IsExcludedFromPasswordPolicy], [Extent1].[IsChangePassword] AS [IsChangePassword], [Extent1].[IsLocked] AS [IsLocked], [Extent1].[LastName] AS [LastName], [Extent1].[LastPasswordChangeDate] AS [LastPasswordChangeDate], [Extent1].[LoginName] AS [LoginName], [Extent1].[NumberOfFailedLoginAttempts] AS [NumberOfFailedLoginAttempts], [Extent1].[PasswordHash] AS [PasswordHash], [Extent1].[PasswordHashSalt] AS [PasswordHashSalt] [Extent1].[UpdatedDate] AS [UpdatedDate] FROM [dbo].[User] AS [Extent1] 

I think that I am doing something wrong, but I cannot understand that. Any ideas would be appreciated.

EDIT: I just noticed something strange - there is no WHERE clause in the generated SQL, which means that all rows are fetched from the database, delivered to the client and filtered there. EDIT 2: The same SQL is created using the LINQ query syntax. EDIT 3: After writing a unit test, where I create an instance of the repository and services manually (instead of leaving it in CastleWindsor), the SQL created when the test was run has a WHERE clause.

Answer 1

If your context is what IEnumerable<T> (and not IQueryable<T> ) returns from the Set<T> method, then this is your problem, because the expression:

 context.Set<User> .Where(...) .Select(...) .SingleOrDefault() 

... will read the entire table in memory, and then apply the Where clause and projection ( Select ). So you expect the behavior of SELECT * FROM table .

In the implementation of the DbContext class, a DbSet<T> is IQueryable<T> , which implements IQueryable<T> , so it will be OK. But since it looks like you have a custom repository implementation, I am suspicious of what else might happen behind the scenes ...