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.
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...
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With