Database optimization - Entity Framework Foreign Key attribute

Question

I have got a table containing several foreignkey attributes going to the Users table from the Identity Provider 2.0.

    [ForeignKey("IsApprovedBy")]
    public ApplicationUser IsApprovedByUser { get; set; }
    public string IsApprovedBy { get; set; }

This is not the issue on itself while I know that normalizing this should make it so that I do not nead this foreignkey.

The issue I do have is that when I get the data and return it to my frontend, Entity framework calls the database 3 times just to fill in these ApplicationUsers rather than doing it on my call.

Db.Contractors.Include(x => x.IsApprovedByUser)

Why does he call the database to get this specific data (all other data is already loaded) when returning my values and not while executing my query as such?

Thanks in advance for this.

Trace capture:

Answer 1

This is an issue with Lazy Loading coming form DbContext, not from Identity. By default Identity does not include child objects when you load ApplicationUser or ApplicationRole.

If you turn off lazy loading, issue with multiple requests to DB will go away, but you will have nulls for child objects of ApplicationUser. You are better off with lazy loading disabled if you care about number of DB requests and DB-performance. But you'll have to work around loading of child objects separately.