Click here to Skip to main content
15,035,283 members
Articles / Hosted Services / Azure
Article
Posted 22 Nov 2018

Stats

6K views
3 bookmarked

Performance Tuning of Entity Framework Core with Azure SQL Server

Rate me:
Please Sign up or sign in to vote.
5.00/5 (5 votes)
22 Nov 2018CPOL3 min read

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

C#
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,

SQL
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

C#
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.
SQL
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:

SQL
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.  

C#
public void ConfigureServices(IServiceCollection services)

 {

     services.AddLogging(loggingBuilder =>

     loggingBuilder.AddSerilog(dispose: true));


 }

 

Inject ILoggerFactory to DbContextService

C#
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,

C#
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

C#
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

C#
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

C#
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.

 

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

Fred Song (Melbourne)
Software Developer (Senior)
Australia Australia
Fred is a senior software developer who lives in Melbourne, Australia. In 1993, he started Programming using Visual C++, Visual Basic, Java, and Oracle Developer Tools. From 2003, He started with .Net using C#, and then expertise .Net development.

Fred is often working with software projects in different business domains based on different Microsoft Technologies like SQL-Server, C#, VC++, ASP.NET, ASP.Net MVC, WCF,WPF, Silverlight, .Net Core and Angular, although he also did some development works on IBM AS400.

Comments and Discussions

 
PraiseThanks for sharing the code Bro Pin
Member 1406424822-Nov-18 17:55
MemberMember 1406424822-Nov-18 17:55 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.