Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Entity framework 6 projection generates SQL equivalent to "Select *" and does not produce WHERE clause

I'm maintaining an ASP.NET WebAPI2 application with Entity framework 6 and MSSQL server database. The IoC container is Castle Windsor. I have a method on my repository that I use to get some details for a user from DB. Since I don't need every column, I thought I'd use projection. The problem is that the generated SQL selects ALL the columns in my table. Here's the DbContext

public partial class SecurityContext : DbContext
{
    public SecurityContext()
        : base("name=SecurityContext")
    {
    }

    public virtual DbSet<User> secUsers { get; set; }
}

Here's 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's 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                
    };
}

Here's the output SQL.

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 guess I'm doing something wrong, but I can't figure out what. Any ideas will be appreciated.

EDIT: I just noticed something strange - in the generated SQL there's no WHERE clause, which means that all the rows are selected from the database, brought to the client, and filtered there. EDIT 2: the same SQL is produced by using the LINQ query syntax. EDIT 3: After writing a unit test where I instantiate the repository and service manually (instead of leaving it to CastleWindsor), the SQL produced when running the test has the WHERE clause.

like image 904
user2936023 Avatar asked Dec 07 '15 14:12

user2936023


1 Answers

If your context is something that returns an IEnumerable<T> (and not IQueryable<T>) from the Set<T> method, then that's your problem, because the expression:

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

...will read the whole table into memory, and then apply the Where clause and the projection (Select). So, you would expect SELECT * FROM table behaviour.

The DbContext class implementation of Set<T> returns a DbSet<T> which does implement IQueryable<T>, so that would be OK. But since it looks like you have a custom repository implementation, I'm suspicious about what else might be going on behind the scenes...

like image 50
Gary McGill Avatar answered Oct 31 '22 03:10

Gary McGill