These are my models:

Account

public enum AccountType { Company, User }

public class Account
{
    [Key, Column(Order=0)] //Composite key with AccountType
    public long IdCompanyOrUser { get; set; }

    [Key, Column(Order = 1)] //Composite key with IdCompanyOrUser
    public AccountType AccountType { get; set; }

    [JsonIgnore]
    public string? Hash { get; set; }

    [JsonIgnore]
    public List<RefreshToken>? RefreshTokens { get; set; } 

    [JsonIgnore]
    public List<LoginAttempt>? LoginAttempts { get; set; }
}

Refreshtoken

[Owned]
public class RefreshToken
{
    [Key]
    [JsonIgnore]
    public long Id { get; set; }
    public string Token { get; set; }
    public DateTime Expires { get; set; }
    public DateTime Created { get; set; }
    public DateTime? Revoked { get; set; }
    public string? RevokedBy { get; set; }
    public string? ReasonRevoked { get; set; }
    public bool IsExpired => DateTime.UtcNow >= Expires;
    public bool IsRevoked => Revoked != null;
    public bool IsActive => !IsRevoked && !IsExpired;
}

LoginAttempt

public enum FailReason { WrongUserOrPassword, UserInactive, CompanyExpired, CountryRestriction, NoRoleAssigned }
    [Owned]
    public class LoginAttempt
    {
        [Key]
        [JsonIgnore]
        public long Id { get; set; }
        public DateTime Time { get; set; }
        public bool Success { get; set; }
        public string Ip { get; set; }
        public FailReason? FailReason { get; set; }
    }
}

And this is my DB context

public class AuthDbContext : DbContext
{
    public AuthDbContext(DbContextOptions<AuthDbContext> options) : base(options) { }

    public DbSet<Account> Account { get; set; }
    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<Account>().HasKey(table => new {
            table.IdCompanyOrUser,
            table.AccountType
        });
    }
}

When I run this query

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

I am using the design pattern repository so this makes a call to

protected readonly DbContext Context;
private DbSet<TEntity> _entities;
public Repository(DbContext context)
{
    Context = context;
    _entities = context.Set<TEntity>();
}

 public TEntity FindSingleOrDefault(Expression<Func<TEntity, bool>> predicate) => _entities.SingleOrDefault(predicate);

The request takes up to 3 seconds to retrieve the account and around 3000 refresh tokens and 200 login attempts. Which I find too long for such a small amount of records.

I was checking my logs and this is the query that EF core translates from my expression. (the 93 and the 0 are filled by me. These are the parameters). I tested it directly in pgadmin and it takes around the same.

SELECT t."IdCompanyOrUser", t."AccountType", t."Hash", l."Id", l."AccountIdCompanyOrUser", l."AccountType", l."FailReason", l."Ip", l."Success", l."Time", r."Id", r."AccountIdCompanyOrUser", r."AccountType", r."Created", r."Expires", r."ReasonRevoked", r."Revoked", r."RevokedBy", r."Token"
FROM (
    SELECT a."IdCompanyOrUser", a."AccountType", a."Hash"
    FROM "Account" AS a
    WHERE (a."IdCompanyOrUser" = 93) AND (a."AccountType" = 0)
    LIMIT 2
) AS t
LEFT JOIN "LoginAttempt" AS l ON (t."IdCompanyOrUser" = l."AccountIdCompanyOrUser") AND (t."AccountType" = l."AccountType")
LEFT JOIN "RefreshToken" AS r ON (t."IdCompanyOrUser" = r."AccountIdCompanyOrUser") AND (t."AccountType" = r."AccountType") 
ORDER BY t."IdCompanyOrUser", t."AccountType", l."Id"

I tested two separate queries on my database for getting the login attempts and another one for the refresh tokens and they are pretty quick (60 ms). Does someone know why it is so slow? Is there a way to optimize it?

I would like to keep the current implementation where refresh tokens and login attempts are [owned] by an account. It is my first time using owned entity types, so I am not sure if I am implementing it correctly and if I understand how they are meant to be used. I would appreciate some feedback and help if possible :)

PS: I am using ET core 6.

CodePudding user response：

Usually Eager Loading introduces Cartesian Explosion of master-detail records. EF Core loads everything into memory and then provides records deduplication. If count of the records is huge it may cause dramatical performance degradation.

Starting from EF Core 5 there is operator AsSplitQuery() which changes this behavior and loads master-details records by separate queries.

For further reading: Split queries