Entityframework count rows when using lazy loading

Question

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

Svyatoslav Danyliv · Accepted Answer · 2022-06-06 10:55:34Z

1

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();

answered Jun 6, 2022 at 10:55

Svyatoslav Danyliv

28.2k4 gold badges24 silver badges46 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Okan Karadag Over a year ago

you cannot write where conditional (as AccountType) for Account in option 2.

Svyatoslav Danyliv Over a year ago

@OkanKaradag, OP already loaded account, we can reuse PK in this case.

srzsanti Over a year ago

I have no access to the dbcontext where the count is being called. Also to avoid other programmers from skipping business login, loginattempts is not accesable as DBset but only as owned type of an account.

Svyatoslav Danyliv Over a year ago

So, then you have to improve your abstraction :)

srzsanti Over a year ago

@SvyatoslavDanyliv Thanks for your feedback. I will have it into consideration. However, I still find it interesting. The idea of lazy loading is to load the data only at the moment you need it. If the account has been already retrieved it would be ideal to be able to retrieve only the count of the login attempts without having to get the account again from the context. Maybe it is not possible. But I think it's something that would be quite useful.

|

Okan Karadag · Accepted Answer · 2022-06-06 11:15:03Z

1

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

edited Jun 6, 2022 at 11:15

answered Jun 6, 2022 at 10:46

Okan Karadag

3,1051 gold badge16 silver badges27 bronze badges

5 Comments

Gert Arnold Over a year ago

You have to remove the Include. It does nothing, so it's only distracting and even makes it look like all LoginAttemps are still loaded.

Okan Karadag Over a year ago

if i remove it, wouldn't x.LoginAttemps be null?

Svyatoslav Danyliv Over a year ago

@OkanKaradag, 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.

Okan Karadag Over a year ago

Ty. I understand. I'm updating my answer.

srzsanti Over a year ago

Thank you for your answer but in my architecture I have no access to the dbcontext where the loginattempts are being count. The reason for this is for other programmers not to skip business logic.

Collectives™ on Stack Overflow

Entityframework count rows when using lazy loading

2 Answers 2

6 Comments

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

6 Comments

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related