Introduction

Entity Framework Core allows you to use the navigation properties in your model to load related entities. There are three common O/RM patterns used to load related data.

Eager loading means that the related data is loaded from the database as part of the initial query.
Explicit loading means that the related data is explicitly loaded from the database at a later time.
Lazy loading means that the related data is transparently loaded from the database when the navigation property is accessed.

Eager loading is the default loading of EF Core.

Understanding the query of Eager Loading

var matters = ctx.Matters.Where(x => x.MatterMatter == projectId)

                .Include(x => x.MatterDates)

                .Include(x => x.PropertyTitles)

                .Include(x => x.MatterContacts)

                .Include(x => x.MatterClients);

What’s the query for the above code? Use Sql Profiler can get the real query executed on the database. How to use Sql Profiler is a big topic, we don’t discuss here.

The corresponding SQL query is,

SELECT [m].[Id], [m].[BalanceDeposit], [m].[BalanceDepositDatePaid], [m].[BalanceDue], [m].[CaveatOnTitle], [m].[Created], [m].[CreatedBy], [m].[DeactivateCriticalDatesTasks], [m].[DepositOutstanding], [m].[FirstHomeOwner], [m].[ForeignPurchaser], [m].[HiddenLastModified], [m].[IncludeGST], [m].[InitialDeposit], [m].[InitialDepositDatePaid], [m].[InterestOnDeposit], [m].[Investment], [m].[isCGTCleared], [m].[isCombinedSalutationUpdated], [m].[IsCopy], [m].[IsDepositAmountRequired], [m].[IsMasterMatter], [m].[IsPexa], [m].[isReferenceNoEditable], [m].[IsSetteled], [m].[IsShared], [m].[LoanProceeds], [m].[LockReferrer], [m].[LotLiabilityTotal], [m].[LotLiabilityUnit], [m].[Matter_AcquisitionOverlay], [m].[MatterDescription], [m].[Matter_Employee], [m].[Matter_Firm], [m].[Matter_Matter], [m].[Matter_MatterReferrer], [m].[Matter_State], [m].[Matter_StateAcquisitionCode], [m].[Matter_Suburb], [m].[MatterType], [m].[Matter_Workflow], [m].[Matter_Zone1], [m].[Modified], [m].[ModifiedBy], [m].[MortgageOnTitle], [m].[MortgagePayoutAmount], [m].[NameForMail], [m].[Notes], [m].[OffPlanDutyConcession], [m].[Other], [m].[PensionerExemption], [m].[PexaworkspaceID], [m].[Price], [m].[PrincipalPlaceOfResidence], [m].[Project], [m].[PropertyPostCode], [m].[PropertyState], [m].[PropertyStreetName], [m].[PropertyStreetNo], [m].[PropertyStreetType], [m].[PropertySubdivisionVersion], [m].[PropertyUnitNo], [m].[PropertyUnitType], [m].[ReferenceNo], [m].[Referrer], [m].[RegistrationFees], [m].[RowVersion], [m].[Salutation], [m].[SettlementDate], [m].[SettlementLocation_Matter], [m].[SettlementTime], [m].[StampDuty], [m].[Status], [m].[TotalCashDeposit], [m].[TotalDeposit], [m].[TotalNonCashDeposit], [m].[TotalOptionalExtras]

      FROM [Matters] AS [m]

      WHERE [m].[Matter_Matter] = @__masterMatterId_0

      ORDER BY [m].[Id]

Executed DbCommand (18ms) [Parameters=[], CommandType='Text', CommandTimeout='30']

      SELECT [x.MatterDates].[Id], [x.MatterDates].[Created], [x.MatterDates].[CreatedBy], [x.MatterDates].[DateName], [x.MatterDates].[EventDate], [x.MatterDates].[MatterDate_MasterDate], [x.MatterDates].[MatterDate_Matter], [x.MatterDates].[Modified], [x.MatterDates].[ModifiedBy], [x.MatterDates].[RowVersion]

      FROM [MatterDates] AS [x.MatterDates]


SELECT [m1].[Id], [m1].[BalanceDeposit], [m1].[BalanceDepositDatePaid], [m1].[BalanceDue], [m1].[CaveatOnTitle], [m1].[Created], [m1].[CreatedBy], [m1].[DeactivateCriticalDatesTasks], [m1].[DepositOutstanding], [m1].[FirstHomeOwner], [m1].[ForeignPurchaser], [m1].[HiddenLastModified], [m1].[IncludeGST], [m1].[InitialDeposit], [m1].[InitialDepositDatePaid], [m1].[InterestOnDeposit], [m1].[Investment], [m1].[isCGTCleared], [m1].[isCombinedSalutationUpdated], [m1].[IsCopy], [m1].[IsDepositAmountRequired], [m1].[IsMasterMatter], [m1].[IsPexa], [m1].[isReferenceNoEditable], [m1].[IsSetteled], [m1].[IsShared], [m1].[LoanProceeds], [m1].[LockReferrer], [m1].[LotLiabilityTotal], [m1].[LotLiabilityUnit], [m1].[Matter_AcquisitionOverlay], [m1].[MatterDescription], [m1].[Matter_Employee], [m1].[Matter_Firm], [m1].[Matter_Matter], [m1].[Matter_MatterReferrer], [m1].[Matter_State], [m1].[Matter_StateAcquisitionCode], [m1].[Matter_Suburb], [m1].[MatterType], [m1].[Matter_Workflow], [m1].[Matter_Zone1], [m1].[Modified], [m1].[ModifiedBy], [m1].[MortgageOnTitle], [m1].[MortgagePayoutAmount], [m1].[NameForMail], [m1].[Notes], [m1].[OffPlanDutyConcession], [m1].[Other], [m1].[PensionerExemption], [m1].[PexaworkspaceID], [m1].[Price], [m1].[PrincipalPlaceOfResidence], [m1].[Project], [m1].[PropertyPostCode], [m1].[PropertyState], [m1].[PropertyStreetName], [m1].[PropertyStreetNo], [m1].[PropertyStreetType], [m1].[PropertySubdivisionVersion], [m1].[PropertyUnitNo], [m1].[PropertyUnitType], [m1].[ReferenceNo], [m1].[Referrer], [m1].[RegistrationFees], [m1].[RowVersion], [m1].[Salutation], [m1].[SettlementDate], [m1].[SettlementLocation_Matter], [m1].[SettlementTime], [m1].[StampDuty], [m1].[Status], [m1].[TotalCashDeposit], [m1].[TotalDeposit], [m1].[TotalNonCashDeposit], [m1].[TotalOptionalExtras]

      FROM [Matters] AS [m1]

      WHERE [m1].[Matter_Matter] = @__masterMatterId_0


SELECT [x.PropertyTitles].[Id], [x.PropertyTitles].[Created], [x.PropertyTitles].[CreatedBy], [x.PropertyTitles].[Folio], [x.PropertyTitles].[LastVerified], [x.PropertyTitles].[Lot], [x.PropertyTitles].[LotType], [x.PropertyTitles].[Modified], [x.PropertyTitles].[ModifiedBy], [x.PropertyTitles].[PlanNo], [x.PropertyTitles].[PropertyTitle_Matter], [x.PropertyTitles].[PropertyTitle_PlanType], [x.PropertyTitles].[RowVersion], [x.PropertyTitles].[TitleReference], [x.PropertyTitles].[Valid], [x.PropertyTitles].[Volume]

      FROM [PropertyTitles] AS [x.PropertyTitles]


SELECT [m3].[Id], [m3].[BalanceDeposit], [m3].[BalanceDepositDatePaid], [m3].[BalanceDue], [m3].[CaveatOnTitle], [m3].[Created], [m3].[CreatedBy], [m3].[DeactivateCriticalDatesTasks], [m3].[DepositOutstanding], [m3].[FirstHomeOwner], [m3].[ForeignPurchaser], [m3].[HiddenLastModified], [m3].[IncludeGST], [m3].[InitialDeposit], [m3].[InitialDepositDatePaid], [m3].[InterestOnDeposit], [m3].[Investment], [m3].[isCGTCleared], [m3].[isCombinedSalutationUpdated], [m3].[IsCopy], [m3].[IsDepositAmountRequired], [m3].[IsMasterMatter], [m3].[IsPexa], [m3].[isReferenceNoEditable], [m3].[IsSetteled], [m3].[IsShared], [m3].[LoanProceeds], [m3].[LockReferrer], [m3].[LotLiabilityTotal], [m3].[LotLiabilityUnit], [m3].[Matter_AcquisitionOverlay], [m3].[MatterDescription], [m3].[Matter_Employee], [m3].[Matter_Firm], [m3].[Matter_Matter], [m3].[Matter_MatterReferrer], [m3].[Matter_State], [m3].[Matter_StateAcquisitionCode], [m3].[Matter_Suburb], [m3].[MatterType], [m3].[Matter_Workflow], [m3].[Matter_Zone1], [m3].[Modified], [m3].[ModifiedBy], [m3].[MortgageOnTitle], [m3].[MortgagePayoutAmount], [m3].[NameForMail], [m3].[Notes], [m3].[OffPlanDutyConcession], [m3].[Other], [m3].[PensionerExemption], [m3].[PexaworkspaceID], [m3].[Price], [m3].[PrincipalPlaceOfResidence], [m3].[Project], [m3].[PropertyPostCode], [m3].[PropertyState], [m3].[PropertyStreetName], [m3].[PropertyStreetNo], [m3].[PropertyStreetType], [m3].[PropertySubdivisionVersion], [m3].[PropertyUnitNo], [m3].[PropertyUnitType], [m3].[ReferenceNo], [m3].[Referrer], [m3].[RegistrationFees], [m3].[RowVersion], [m3].[Salutation], [m3].[SettlementDate], [m3].[SettlementLocation_Matter], [m3].[SettlementTime], [m3].[StampDuty], [m3].[Status], [m3].[TotalCashDeposit], [m3].[TotalDeposit], [m3].[TotalNonCashDeposit], [m3].[TotalOptionalExtras]

      FROM [Matters] AS [m3]

      WHERE [m3].[Matter_Matter] = @__masterMatterId_0


SELECT [x.MatterContacts].[Id], [x.MatterContacts].[Created], [x.MatterContacts].[CreatedBy], [x.MatterContacts].[IsGuaranteeAndIndemnitySigned], [x.MatterContacts].[IsReferrer], [x.MatterContacts].[LoanNumber], [x.MatterContacts].[MatterCategory], [x.MatterContacts].[MatterContact_BranchContact], [x.MatterContacts].[MatterContact_Matter], [x.MatterContacts].[MatterContact_OtherContact], [x.MatterContacts].[MatterContact_OtherContactBranch], [x.MatterContacts].[Modified], [x.MatterContacts].[ModifiedBy], [x.MatterContacts].[Reference], [x.MatterContacts].[RowVersion], [x.MatterContacts].[UpdateIndicatorDate]

      FROM [MatterContacts] AS [x.MatterContacts]

SELECT [m5].[Id], [m5].[BalanceDeposit], [m5].[BalanceDepositDatePaid], [m5].[BalanceDue], [m5].[CaveatOnTitle], [m5].[Created], [m5].[CreatedBy], [m5].[DeactivateCriticalDatesTasks], [m5].[DepositOutstanding], [m5].[FirstHomeOwner], [m5].[ForeignPurchaser], [m5].[HiddenLastModified], [m5].[IncludeGST], [m5].[InitialDeposit], [m5].[InitialDepositDatePaid], [m5].[InterestOnDeposit], [m5].[Investment], [m5].[isCGTCleared], [m5].[isCombinedSalutationUpdated], [m5].[IsCopy], [m5].[IsDepositAmountRequired], [m5].[IsMasterMatter], [m5].[IsPexa], [m5].[isReferenceNoEditable], [m5].[IsSetteled], [m5].[IsShared], [m5].[LoanProceeds], [m5].[LockReferrer], [m5].[LotLiabilityTotal], [m5].[LotLiabilityUnit], [m5].[Matter_AcquisitionOverlay], [m5].[MatterDescription], [m5].[Matter_Employee], [m5].[Matter_Firm], [m5].[Matter_Matter], [m5].[Matter_MatterReferrer], [m5].[Matter_State], [m5].[Matter_StateAcquisitionCode], [m5].[Matter_Suburb], [m5].[MatterType], [m5].[Matter_Workflow], [m5].[Matter_Zone1], [m5].[Modified], [m5].[ModifiedBy], [m5].[MortgageOnTitle], [m5].[MortgagePayoutAmount], [m5].[NameForMail], [m5].[Notes], [m5].[OffPlanDutyConcession], [m5].[Other], [m5].[PensionerExemption], [m5].[PexaworkspaceID], [m5].[Price], [m5].[PrincipalPlaceOfResidence], [m5].[Project], [m5].[PropertyPostCode], [m5].[PropertyState], [m5].[PropertyStreetName], [m5].[PropertyStreetNo], [m5].[PropertyStreetType], [m5].[PropertySubdivisionVersion], [m5].[PropertyUnitNo], [m5].[PropertyUnitType], [m5].[ReferenceNo], [m5].[Referrer], [m5].[RegistrationFees], [m5].[RowVersion], [m5].[Salutation], [m5].[SettlementDate], [m5].[SettlementLocation_Matter], [m5].[SettlementTime], [m5].[StampDuty], [m5].[Status], [m5].[TotalCashDeposit], [m5].[TotalDeposit], [m5].[TotalNonCashDeposit], [m5].[TotalOptionalExtras]

      FROM [Matters] AS [m5]

      WHERE [m5].[Matter_Matter] = @__masterMatterId_0

That’s query becomes horrible when matters table have thousands of record. In fact it took 2 minutes to return the result.

Optimize Query

Create the index properly

Create non-cluster index for [Matters].[Matter_Matter]

After create the index, the query can finish in 30 -45 seconds.

Optimize the code

var matterIds = ctx.Matters.Where(x => x.MatterMatter == masterMatterId)

        .Select(x => x.Id).ToList();

      var matters = ctx.Matters

        .Where(x => matterIds.Contains(x.Id))

        .Include(x => x.MatterDates)

        .Include(x => x.PropertyTitles)

        .Include(x => x.MatterContacts)

        .Include(x => x.MatterClients);

The above code will be translated to the below queries.

SELECT [m].[Id], [m].[BalanceDeposit], [m].[BalanceDepositDatePaid], [m].[BalanceDue], [m].[CaveatOnTitle], [m].[Created], [m].[CreatedBy], [m].[DeactivateCriticalDatesTasks], [m].[DepositOutstanding], [m].[FirstHomeOwner], [m].[ForeignPurchaser], [m].[HiddenLastModified], [m].[IncludeGST], [m].[InitialDeposit], [m].[InitialDepositDatePaid], [m].[InterestOnDeposit], [m].[Investment], [m].[isCGTCleared], [m].[isCombinedSalutationUpdated], [m].[IsCopy], [m].[IsDepositAmountRequired], [m].[IsMasterMatter], [m].[IsPexa], [m].[isReferenceNoEditable], [m].[IsSetteled], [m].[IsShared], [m].[LoanProceeds], [m].[LockReferrer], [m].[LotLiabilityTotal], [m].[LotLiabilityUnit], [m].[Matter_AcquisitionOverlay], [m].[MatterDescription], [m].[Matter_Employee], [m].[Matter_Firm], [m].[Matter_Matter], [m].[Matter_MatterReferrer], [m].[Matter_State], [m].[Matter_StateAcquisitionCode], [m].[Matter_Suburb], [m].[MatterType], [m].[Matter_Workflow], [m].[Matter_Zone1], [m].[Modified], [m].[ModifiedBy], [m].[MortgageOnTitle], [m].[MortgagePayoutAmount], [m].[NameForMail], [m].[Notes], [m].[OffPlanDutyConcession], [m].[Other], [m].[PensionerExemption], [m].[PexaworkspaceID], [m].[Price], [m].[PrincipalPlaceOfResidence], [m].[Project], [m].[PropertyPostCode], [m].[PropertyState], [m].[PropertyStreetName], [m].[PropertyStreetNo], [m].[PropertyStreetType], [m].[PropertySubdivisionVersion], [m].[PropertyUnitNo], [m].[PropertyUnitType], [m].[ReferenceNo], [m].[Referrer], [m].[RegistrationFees], [m].[RowVersion], [m].[Salutation], [m].[SettlementDate], [m].[SettlementLocation_Matter], [m].[SettlementTime], [m].[StampDuty], [m].[Status], [m].[TotalCashDeposit], [m].[TotalDeposit], [m].[TotalNonCashDeposit], [m].[TotalOptionalExtras]

      FROM [Matters] AS [m]

      WHERE [m].[Matter_Matter] = @__masterMatterId_0


SELECT [x].[Id], [x].[BalanceDeposit], [x].[BalanceDepositDatePaid], [x].[BalanceDue], [x].[CaveatOnTitle], [x].[Created], [x].[CreatedBy], [x].[DeactivateCriticalDatesTasks], [x].[DepositOutstanding], [x].[FirstHomeOwner], [x].[ForeignPurchaser], [x].[HiddenLastModified], [x].[IncludeGST], [x].[InitialDeposit], [x].[InitialDepositDatePaid], [x].[InterestOnDeposit], [x].[Investment], [x].[isCGTCleared], [x].[isCombinedSalutationUpdated], [x].[IsCopy], [x].[IsDepositAmountRequired], [x].[IsMasterMatter], [x].[IsPexa], [x].[isReferenceNoEditable], [x].[IsSetteled], [x].[IsShared], [x].[LoanProceeds], [x].[LockReferrer], [x].[LotLiabilityTotal], [x].[LotLiabilityUnit], [x].[Matter_AcquisitionOverlay], [x].[MatterDescription], [x].[Matter_Employee], [x].[Matter_Firm], [x].[Matter_Matter], [x].[Matter_MatterReferrer], [x].[Matter_State], [x].[Matter_StateAcquisitionCode], [x].[Matter_Suburb], [x].[MatterType], [x].[Matter_Workflow], [x].[Matter_Zone1], [x].[Modified], [x].[ModifiedBy], [x].[MortgageOnTitle], [x].[MortgagePayoutAmount], [x].[NameForMail], [x].[Notes], [x].[OffPlanDutyConcession], [x].[Other], [x].[PensionerExemption], [x].[PexaworkspaceID], [x].[Price], [x].[PrincipalPlaceOfResidence], [x].[Project], [x].[PropertyPostCode], [x].[PropertyState], [x].[PropertyStreetName], [x].[PropertyStreetNo], [x].[PropertyStreetType], [x].[PropertySubdivisionVersion], [x].[PropertyUnitNo], [x].[PropertyUnitType], [x].[ReferenceNo], [x].[Referrer], [x].[RegistrationFees], [x].[RowVersion], [x].[Salutation], [x].[SettlementDate], [x].[SettlementLocation_Matter], [x].[SettlementTime], [x].[StampDuty], [x].[Status], [x].[TotalCashDeposit], [x].[TotalDeposit], [x].[TotalNonCashDeposit], [x].[TotalOptionalExtras]

      FROM [Matters] AS [x]

      WHERE [x].[Id] IN (3263, 3283, 3320, 3325, 3384, 3386, 3438)

      ORDER BY [x].[Id]


SELECT [x.MatterDates].[Id], [x.MatterDates].[Created], [x.MatterDates].[CreatedBy], [x.MatterDates].[DateName], [x.MatterDates].[EventDate], [x.MatterDates].[MatterDate_MasterDate], [x.MatterDates].[MatterDate_Matter], [x.MatterDates].[Modified], [x.MatterDates].[ModifiedBy], [x.MatterDates].[RowVersion]

      FROM [MatterDates] AS [x.MatterDates]

      INNER JOIN (

          SELECT [x0].[Id]

          FROM [Matters] AS [x0]

          WHERE [x0].[Id] IN (3263, 3283, 3320, 3325, 3384, 3386, 3438)

      ) AS [t] ON [x.MatterDates].[MatterDate_Matter] = [t].[Id]

      ORDER BY [t].[Id]


SELECT [x.PropertyTitles].[Id], [x.PropertyTitles].[Created], [x.PropertyTitles].[CreatedBy], [x.PropertyTitles].[Folio], [x.PropertyTitles].[LastVerified], [x.PropertyTitles].[Lot], [x.PropertyTitles].[LotType], [x.PropertyTitles].[Modified], [x.PropertyTitles].[ModifiedBy], [x.PropertyTitles].[PlanNo], [x.PropertyTitles].[PropertyTitle_Matter], [x.PropertyTitles].[PropertyTitle_PlanType], [x.PropertyTitles].[RowVersion], [x.PropertyTitles].[TitleReference], [x.PropertyTitles].[Valid], [x.PropertyTitles].[Volume]

      FROM [PropertyTitles] AS [x.PropertyTitles]

      INNER JOIN (

          SELECT [x1].[Id]

          FROM [Matters] AS [x1]

          WHERE [x1].[Id] IN (3263, 3283, 3320, 3325, 3384, 3386, 3438)

      ) AS [t0] ON [x.PropertyTitles].[PropertyTitle_Matter] = [t0].[Id]

      ORDER BY [t0].[Id]


SELECT [x.MatterContacts].[Id], [x.MatterContacts].[Created], [x.MatterContacts].[CreatedBy], [x.MatterContacts].[IsGuaranteeAndIndemnitySigned], [x.MatterContacts].[IsReferrer], [x.MatterContacts].[LoanNumber], [x.MatterContacts].[MatterCategory], [x.MatterContacts].[MatterContact_BranchContact], [x.MatterContacts].[MatterContact_Matter], [x.MatterContacts].[MatterContact_OtherContact], [x.MatterContacts].[MatterContact_OtherContactBranch], [x.MatterContacts].[Modified], [x.MatterContacts].[ModifiedBy], [x.MatterContacts].[Reference], [x.MatterContacts].[RowVersion], [x.MatterContacts].[UpdateIndicatorDate]

      FROM [MatterContacts] AS [x.MatterContacts]

      INNER JOIN (

          SELECT [x2].[Id]

          FROM [Matters] AS [x2]

          WHERE [x2].[Id] IN (3263, 3283, 3320, 3325, 3384, 3386, 3438)

      ) AS [t1] ON [x.MatterContacts].[MatterContact_Matter] = [t1].[Id]

      ORDER BY [t1].[Id]

Now the query can complete in 1 second.

Query Profiling on Azure SQL

Azure SQL is one of the most valuable Platform as a Service (PaaS) offerings on Azure, second only to Web Apps. It's incredibly cost effective and fully featured. It supports a number of database versions and can be deployed in seconds. But one of pain is the SQL Profiler is not available for Azure SQL. especially when you need to identify what queries you're running and how long they take to execute. This makes our EF Core tuning tougher. Fortunately we have two way to address this limitation.

Using SQL AZURE DMVS to profile queries

The query against the DMVs that identifies the queries that were run, grouped together and time averaged is attached below:

SELECT query_stats.query_hash AS "Query Hash",

    SUM(query_stats.total_worker_time) / SUM(query_stats.execution_count) AS "Avg CPU Time",

    MIN(query_stats.statement_text) AS "Statement Text"

FROM

    (SELECT QS.*,

    SUBSTRING(ST.text, (QS.statement_start_offset/2) + 1,

    ((CASE statement_end_offset

        WHEN -1 THEN DATALENGTH(ST.text)

        ELSE QS.statement_end_offset END

            - QS.statement_start_offset)/2) + 1) AS statement_text

     FROM sys.dm_exec_query_stats AS QS

     CROSS APPLY sys.dm_exec_sql_text(QS.sql_handle) as ST) as query_stats

GROUP BY query_stats.query_hash

ORDER BY 2 DESC;

You can run the query in SQL Server Management Studio (SSMS), by connecting to your SQL Azure database instance and selecting the database you want to profile.

Enable Entity Framework Core logging

You can enable Entity Framework or Entity Framework Core logging to log the actual query generated by EF.

In EF Core, you need to create a ILoggerProvider and inject it to the EF Context.

public void ConfigureServices(IServiceCollection services)

 {

     services.AddLogging(loggingBuilder =>

     loggingBuilder.AddSerilog(dispose: true));


 }

Inject ILoggerFactory to DbContextService

public DbContextService(IOptions<AdminServiceSettings> settings, IUserProfileService userProfileService,

      ILogger logger, ILoggerFactory loggerFactory)

    {

      _settings = settings.Value;

      _logger = logger;

      _userProfileService = userProfileService;

      _loggerFactory = loggerFactory;

      _adminContextOptions =

        new DbContextOptionsBuilder<AdminContext>().UseSqlServer(_settings.AdminConnectionString).Options;

}

Then enable logging in DBContext,

DbContextOptions<MatterContext> contextOptions =

          new DbContextOptionsBuilder<MatterContext>()

            .UseLoggerFactory(_loggerFactory)

            .UseSqlServer(connectionString).Options;

Using Cache

Install EFSecondLevelCache.Core with NuGet.

Second level caching is a query cache. The results of EF commands will be stored in the cache, so that the same EF commands will retrieve their data from the cache rather than executing them against the database again.

Register the required services

public void ConfigureServices(IServiceCollection services)
{

    services.AddEFSecondLevelCache();


    // Add an in-memory cache service provider

    services.AddSingleton(typeof(ICacheManager<>), typeof(BaseCacheManager<>));

    services.AddSingleton(typeof(ICacheManagerConfiguration),

    new CacheManager.Core.ConfigurationBuilder()

           .WithJsonSerializer()

           .WithMicrosoftMemoryCacheHandle()

           .WithExpiration(ExpirationMode.Absolute, TimeSpan.FromMinutes(10))

           .Build());

}

public void Configure(IApplicationBuilder app)
{

     app.UseEFSecondLevelCache();

}

Setting up the cache invalidation by overriding the SaveChanges method to prevent stale reads

public override int SaveChanges()

{

      this.ChangeTracker.DetectChanges();

      var changedEntityNames = this.GetChangedEntityNames();


      var result = base.SaveChanges();

      this.GetService<IEFCacheServiceProvider>().InvalidateCacheDependencies(changedEntityNames);


      return result;
}

Then to cache the results of the normal queries

var products = context.Products.Include(x => x.Tags).Cacheable().FirstOrDefault();

Using Dapper to replace EF .Core

Those who do not know the Dapper, I recommend having a look. It is a little ORM, It does not do all that the Entity Framework and NHibernate do, but its query is very fast. In the last years is common to see a project with an ORM and the Dapper to optimize the query.

Performance Tuning of Entity Framework Core with Azure SQL Server