Entityframework count rows when using lazy loading-CodePudding

I am trying to count the amount of rows for a specific Account. I configure entityframework to use lazy loading.

I load the account like this:

 Account? account = FindSingleOrDefault(x => x.IdCompanyOrUser == id & x.AccountType == accountType);

and later on if necessary I try to get the count of the virtual property loginattempts. I do this by executing this:

account.LoginAttempts.Count();

I read everywhere that this should generate a select count(*) but instead my entityframework is generating the following query:

Executed DbCommand (4ms) [Parameters=[@__p_0='?' (DbType = Int64), @__p_1='?' (DbType = Int32)], CommandType='Text', CommandTimeout='30']
SELECT l."Id", l."AccountIdCompanyOrUser", l."AccountType", l."FailReason", l."Ip", l."Success", l."Time"
FROM "LoginAttempt" AS l
WHERE (l."AccountIdCompanyOrUser" = @__p_0) AND (l."AccountType" = @__p_1) <s:Microsoft.EntityFrameworkCore.Database.Command>

The count result is correct but with this query, it will be loading all the entities first. This could have a serious performance impact when my application is filled with millions of records. For this reason, I really need it to use the select count.

my configuration of entity framework is the following:

builder.Services.AddDbContext<AuthDbContext>(options =>
{
    options.UseLazyLoadingProxies();
});

modelBuilder.Entity<Account>()
    .HasKey(table => new
    {
        table.IdCompanyOrUser,
        table.AccountType
    });
modelBuilder.Entity<Account>()
    .Navigation(e => e.LoginAttempts);

the login attempt is a virtual list of the model LoginAttempt which also has its own table in the database.

I also tried this other options and it didn't work:

account.LoginAttempts.Count();
account.LoginAttempts.AsQueryable().Count();
account.LoginAttempts.AsQueryable().ToList().Count();

Additional information:

Version Microsoft.AspNetCore.Identity.EntityFrameworkCore 6.0.2

Version Microsoft.EntityFrameworkCore.Proxies 6.0.5

.Net 6

CodePudding user response：

It is expected behavior. When you access Lazy Loading navigation property - EF loads property, in your case whole collection. Then Count is performed on loaded collection.

To get just count of items you have to run query again:

var attemptsCount = _context.Accounts
   .Where(a => a.Id == account.Id)
   .Select(a => a.LoginAttempts.Count())
   .First();

Or faster variant

var attemptsCount = _context.LoginAttempts
   .Where(a => a.AccountId == account.Id)
   .Count();

CodePudding user response：

You can use 'where' and 'select' combined

db.Table
.Where(x => x.IdCompanyOrUser == id & x.AccountType == accountType)
.Select(x => x.LoginAttemps.Count()).FirstOrDefault()

Edit: As gert arnolt pointed out below, using Include is unnecessary so I removed it.

Include is needed ONLY for loading related entities. It has zero influence on filters. LINQ translator will still create SQL because it uses just metadata information - what is needed for filter