通过 Azure SQL Server 优化 Entity Framework Core 性能

Fred Song (墨尔本)

5.00/5 (5投票s)

2018年11月22日

CPOL

3分钟阅读

9392

引言

Entity Framework Core 允许您使用模型中的导航属性来加载相关的实体。有三种常见的 O/RM 模式用于加载相关数据。

急切加载 意味着相关数据作为初始查询的一部分从数据库中加载。
显式加载 意味着相关数据稍后从数据库中显式加载。
延迟加载 意味着当访问导航属性时，相关数据会从数据库中透明地加载。

急切加载是 EF Core 的默认加载方式。

理解急切加载的查询

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

上述代码的查询是什么？使用 Sql Profiler 可以获取在数据库上执行的实际查询。如何使用 Sql Profiler 是一个很大的话题，我们这里不讨论。

对应的 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

当 matters 表有数千条记录时，该查询变得非常糟糕。事实上，返回结果需要 2 分钟。

优化查询

正确创建索引

为 [Matters].[Matter_Matter] 创建非聚集索引

创建索引后，查询可以在 30-45 秒内完成。

优化代码

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

上述代码将被翻译成以下查询。

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]

现在查询可以在 1 秒内完成。

在 Azure SQL 上进行查询分析

Azure SQL 是 Azure 上最有价值的平台即服务 (PaaS) 产品之一，仅次于 Web Apps。它具有令人难以置信的成本效益和完整的功能。它支持许多数据库版本，并且可以在几秒钟内部署。但其中一个痛点是 SQL Profiler 无法用于 Azure SQL。尤其是在您需要识别正在运行的查询以及它们需要多长时间才能执行时。这使得我们的 EF Core 调优更加困难。幸运的是，我们有两种方法可以解决此限制。

使用 SQL AZURE DMVS 分析查询

针对 DMVs 的查询，该查询标识了已运行的查询，将它们分组在一起并进行时间平均，附在下面

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;

您可以通过连接到您的 SQL Azure 数据库实例并选择要分析的数据库，在 SQL Server Management Studio (SSMS) 中运行该查询。

启用 Entity Framework Core 记录

您可以启用 Entity Framework 或 Entity Framework Core 记录，以记录 EF 生成的实际查询。

在 EF Core 中，您需要创建一个 ILoggerProvider 并将其注入到 EF Context 中。

public void ConfigureServices(IServiceCollection services)

 {

     services.AddLogging(loggingBuilder =>

     loggingBuilder.AddSerilog(dispose: true));


 }

将 ILoggerFactory 注入到 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;

}

然后启用 DBContext 中的日志记录，

DbContextOptions<MatterContext> contextOptions =

          new DbContextOptionsBuilder<MatterContext>()

            .UseLoggerFactory(_loggerFactory)

            .UseSqlServer(connectionString).Options;

使用缓存

使用 NuGet 安装 EFSecondLevelCache.Core。

二级缓存是一个查询缓存。EF 命令的结果将存储在缓存中，因此相同的 EF 命令将从缓存中检索其数据，而不是再次对数据库执行它们。

注册所需的服务

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

}

通过覆盖 SaveChanges 方法来设置缓存失效，以防止读取陈旧数据

public override int SaveChanges()

{

      this.ChangeTracker.DetectChanges();

      var changedEntityNames = this.GetChangedEntityNames();


      var result = base.SaveChanges();

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


      return result;
}

然后缓存普通查询的结果

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

使用 Dapper 替换 EF .Core

那些不了解 Dapper 的人，我建议去看一下。它是一个小型的 ORM，它不会做 Entity Framework 和 NHibernate 所做的一切，但它的查询速度非常快。近年来，经常可以看到一个项目同时使用一个 ORM 和 Dapper 来优化查询。