执行sql

基于原始SQL查询创建LINQ查询，代替旧版的FromSql、SqlQuery

基本原生 SQL 查询

可使用 FromSqlRaw 扩展方法基于原始 SQL 查询开始 LINQ 查询。 FromSqlRaw 只能在直接位于 DbSet<> 上的查询根上使用

//在 SQL 查询字符串中包含形参占位符并提供额外的实参，将单个形参传递到存储过程   Blogs是Context上下文中配置的实体类属性  
var user = "johndoe";
var blogs = context.Blogs.FromSqlRaw("EXECUTE dbo.GetMostPopularBlogsForUser {0}", user).ToList();

//使用字符串内插语法,该值会转换为 DbParameter，且不易受到 SQL 注入攻击
var user = "johndoe";
var blogs = context.Blogs.FromSqlInterpolated($"EXECUTE dbo.GetMostPopularBlogsForUser {user}").ToList();

//DbParameter 并将其作为参数值提供。 由于使用了常规 SQL 参数占位符而不是字符串占位符，因此可安全地使用 FromSqlRaw
var user = new SqlParameter("user", "johndoe");
var blogs = context.Blogs.FromSqlRaw("EXECUTE dbo.GetMostPopularBlogsForUser @user", user).ToList();

//借助 FromSqlRaw，可以在 SQL 查询字符串中使用已命名的参数，这在存储的流程具有可选参数时非常有用
var user = new SqlParameter("user", "johndoe");
var blogs = context.Blogs.FromSqlRaw("EXECUTE dbo.GetMostPopularBlogsForUser @filterByUser=@user", user).ToList();

Database.ExecuteSqlRaw

针对数据库执行给定的SQL，并返回受影响的行数，代替旧版的ExecuteSqlCommand

join

public IActionResult Index()
{
	// left join ：返回第一个表(左表)的全部记录，第二个表（右表）没有匹配的数据也会显示
	// inner join ：返回两个表交集的记录，要两个表同时有数据匹配才会显示
	// 总结：因为innert join比left join性能高，如果业务允许尽量推荐使用inner join，但是有不少业务是必须使用left join

	//Linq方式(推荐方式)
	var result = (from e in _context.Zlemployee
				  join d in _context.Zldept on e.ZleptCode equals d.Code
				  join z in _context.E_zhiwu on e.E_zhiwuID equals z.ID
				  select new ZlemployeeView { ID = e.ID, Code = e.Code, Name = e.Name, ZldeptName = d.Name, E_zhiwuName = z.Name }
				  ).ToList();

	//Lambda方式
	var result = (from e in _context.Set<Zlemployee>()
				  from d in _context.Set<Zldept>().Where(d => d.Code == e.ZleptCode)
				  from z in _context.Set<E_zhiwu>().Where(z => z.ID == e.E_zhiwuID)
				  select new ZlemployeeView { ID = e.ID, Code = e.Code, Name = e.Name, ZldeptName = d.Name, E_zhiwuName = z.Name }
				  ).ToList();

	//Left Join:需要into、DefaultIfEmpty()
	var result = (from e in _context.Zlemployee
				 join d in _context.Zldept on e.ZleptCode equals d.Code into zlemptgrouping
				 from d in zlemptgrouping.DefaultIfEmpty()
				 join z in _context.E_zhiwu on e.E_zhiwuID equals z.ID into E_zhiwugrouping
				 from z in E_zhiwugrouping.DefaultIfEmpty()
				 select new ZlemployeeView { Code = e.Code, Name = e.Name, ZldeptName = d.Name, E_zhiwuName = z.Name }
				 ).ToList();
	return View(result);
}

分页

Skip().Take()，EFCore3.1分页是sql2012新增的Offset Fetch分页

如果要兼容sql2008的Row_Number分页要用EFCore2.1的UseRowNumberForPaging()

查看EF生成的SQL语句

EF Core 日志跟踪sql语句

efcore 如何查看生成的sql 百度经验

两种查看EFCore生成Sql语句的方法

.net core 利用日志查看ef生成的SQL语句

连接池：DbContextPool

官方说明：DbContextPool 连接池 、 AddDbContextPool API说明 、SQL Server 连接池 (ADO.NET)

EF Core 小坑：DbContextPool 会引起数据库连接池连接耗尽

博客园升级到.net core3.0翻车之：峰回路转：去掉 DbContextPool 后 Windows 上的 .NET Core 版博客表现出色

详解数据库连接池概念、原理、运行机制等

数据库 -- 由数据库连接池引出的三种设计模式

数据库连接池到底应该设多大？这篇文章可能会颠覆你的认知

概念

在版本.net core 2.0 中，我们引入了一种在依赖关系注入中注册自定义 DbContext 类型的新方法，即以透明形式引入可重用 DbContext 实例的池。 要使用 DbContext 池，请在服务注册期间使用 AddDbContextPool 而不是 AddDbContext：services.AddDbContextPool​( options => options.UseSqlServer(connectionString));

如果使用此方法，那么在控制器请求 DbContext 实例时，我们会首先检查池中有无可用的实例。 请求处理完成后，实例的任何状态都将被重置，并且实例本身会返回池中。

从概念上讲，此方法类似于连接池在 ADO.NET 提供程序中的运行原理，并具有节约 DbContext 实例初始化成本的优势。

源码阅读

DbContextPool继承IDbContextPool接口

DbContextPool构造函数判断是都第一次使用，如果是就通过激活器CreateActivator来创建

// Copyright (c) .NET Foundation. All rights reserved.
// Licensed under the Apache License, Version 2.0. See License.txt in the project root for license information.

using System;
using System.Collections.Concurrent;
using System.Linq;
using System.Linq.Expressions;
using System.Reflection;
using System.Threading;
using System.Threading.Tasks;
using JetBrains.Annotations;
using Microsoft.EntityFrameworkCore.Diagnostics;
using Microsoft.EntityFrameworkCore.Infrastructure;
using Microsoft.EntityFrameworkCore.Utilities;

namespace Microsoft.EntityFrameworkCore.Internal
{
    /// <summary>
    ///     This is an internal API that supports the Entity Framework Core infrastructure and not subject to
    ///     the same compatibility standards as public APIs. It may be changed or removed without notice in
    ///     any release. You should only use it directly in your code with extreme caution and knowing that
    ///     doing so can result in application failures when updating to a new Entity Framework Core release.
    ///翻译：
    ///    这是一个内部API，支持Entity Framework Core基础结构，并且不受与公共API相同的兼容性标准的约束。 在任何版本中，它都可能更改或删除，恕不另行通知。
    ///    您仅应非常谨慎地在代码中直接使用它，并且知道这样做会导致在更新到新的Entity Framework Core版本时导致应用程序失败。
    /// 
    ///DbContextPool类源码阅读步骤：
    ///     1 查看构造函数
    ///     2 DbContextPool类被EntityFrameworkServiceCollectionExtensions类的两个AddDbContextPool方法分别调用了，相当于调用2次
    ///
    ///疑问：ef默认的AddDbContext有没有使用连接池的？难道一定要一定要AddDbContextPool才会使用连接池 
    /// </summary>
    public class DbContextPool<TContext> : IDbContextPool<TContext>, IDisposable, IAsyncDisposable
        where TContext : DbContext
    {
        private const int DefaultPoolSize = 32;

        //ConcurrentQueue 并发队列：表示线程安全的先进先出（FIFO）集合。
        private readonly ConcurrentQueue<IDbContextPoolable> _pool = new ConcurrentQueue<IDbContextPoolable>();

        private readonly Func<DbContext> _activator;

        private int _maxSize;
        private int _count;

        /// <summary>
        /// 构造函数
        /// </summary>
        /* 使用方式：services.AddDbContextPool<BloggingContext>(options => options.UseSqlServer(connectionString))
         * DbContextOptions：上下文选项类
         * FindExtension：获取指定类型的扩展名。 如果未配置指定类型的扩展名，则返回null
         */
        public DbContextPool([NotNull] DbContextOptions<TContext> options)
        {
            //最大值
            _maxSize = options.FindExtension<CoreOptionsExtension>()?.MaxPoolSize ?? DefaultPoolSize;
            //冻结：指定不应再配置此选项对象
            options.Freeze();
            //创建激活器：参数是上下文配置类 ------重点 
            _activator = CreateActivator(options);

            if (_activator == null)
            {
                //InvalidOperationException：初始化的新实例System.InvalidOperationException使用指定的错误消息初始化。
                throw new InvalidOperationException(
                    CoreStrings.PoolingContextCtorError(typeof(TContext).ShortDisplayName()));
            }
        }
        /// <summary>
        /// 创建激活器：参数是上下文配置类
        /// </summary>
        private static Func<DbContext> CreateActivator(DbContextOptions<TContext> options)
        {
            //DeclaredConstructors：获取当前类型声明的构造函数的集合
            var constructors = typeof(TContext).GetTypeInfo().DeclaredConstructors
                                    .Where(c => !c.IsStatic && c.IsPublic)
                                    .ToArray();

            if (constructors.Length == 1)
            {
                var parameters = constructors[0].GetParameters();

                if (parameters.Length == 1 && (parameters[0].ParameterType == typeof(DbContextOptions)
                    || parameters[0].ParameterType == typeof(DbContextOptions<TContext>)))
                {
                    //Expression：构造一个新的System.Linq.Expressions.Expression实例
                    //Lambda：创建一个System.Linq.Expressions.Expression`1，其中在编译时知道委托类型，并带有参数表达式数组。
                    //Compile：将表达式树描述的lambda表达式编译为可执行代码，并生成代表lambda表达式的委托。
                    return Expression.Lambda<Func<TContext>>(Expression.New(constructors[0], Expression.Constant(options))).Compile();
                }
            }

            return null;
        }

        /// <summary>
        ///     This is an internal API that supports the Entity Framework Core infrastructure and not subject to
        ///     the same compatibility standards as public APIs. It may be changed or removed without notice in
        ///     any release. You should only use it directly in your code with extreme caution and knowing that
        ///     doing so can result in application failures when updating to a new Entity Framework Core release.
        /// </summary>
        public virtual IDbContextPoolable Rent()
        {
            if (_pool.TryDequeue(out var context))
            {
                Interlocked.Decrement(ref _count);

                Check.DebugAssert(_count >= 0, $"_count is {_count}");

                return context;
            }

            context = _activator();

            context.SnapshotConfiguration();

            return context;
        }

        /// <summary>
        ///     This is an internal API that supports the Entity Framework Core infrastructure and not subject to
        ///     the same compatibility standards as public APIs. It may be changed or removed without notice in
        ///     any release. You should only use it directly in your code with extreme caution and knowing that
        ///     doing so can result in application failures when updating to a new Entity Framework Core release.
        /// </summary>
        public virtual void Return(IDbContextPoolable context)
        {
            if (Interlocked.Increment(ref _count) <= _maxSize)
            {
                context.ResetState();

                _pool.Enqueue(context);
            }
            else
            {
                PooledReturn(context);
            }
        }

        /// <summary>
        ///     This is an internal API that supports the Entity Framework Core infrastructure and not subject to
        ///     the same compatibility standards as public APIs. It may be changed or removed without notice in
        ///     any release. You should only use it directly in your code with extreme caution and knowing that
        ///     doing so can result in application failures when updating to a new Entity Framework Core release.
        /// </summary>
        public virtual async ValueTask ReturnAsync(IDbContextPoolable context, CancellationToken cancellationToken = default)
        {
            if (Interlocked.Increment(ref _count) <= _maxSize)
            {
                await context.ResetStateAsync(cancellationToken).ConfigureAwait(false);

                _pool.Enqueue(context);
            }
            else
            {
                PooledReturn(context);
            }
        }

        private void PooledReturn(IDbContextPoolable context)
        {
            Interlocked.Decrement(ref _count);

            Check.DebugAssert(_maxSize == 0 || _pool.Count <= _maxSize, $"_maxSize is {_maxSize}");

            context.ClearLease();
            context.Dispose();
        }

        /// <summary>
        ///     This is an internal API that supports the Entity Framework Core infrastructure and not subject to
        ///     the same compatibility standards as public APIs. It may be changed or removed without notice in
        ///     any release. You should only use it directly in your code with extreme caution and knowing that
        ///     doing so can result in application failures when updating to a new Entity Framework Core release.
        /// </summary>
        public virtual void Dispose()
        {
            _maxSize = 0;

            while (_pool.TryDequeue(out var context))
            {
                context.ClearLease();
                context.Dispose();
            }
        }

        /// <summary>
        ///     This is an internal API that supports the Entity Framework Core infrastructure and not subject to
        ///     the same compatibility standards as public APIs. It may be changed or removed without notice in
        ///     any release. You should only use it directly in your code with extreme caution and knowing that
        ///     doing so can result in application failures when updating to a new Entity Framework Core release.
        /// </summary>
        public virtual async ValueTask DisposeAsync()
        {
            _maxSize = 0;

            while (_pool.TryDequeue(out var context))
            {
                context.ClearLease();
                await context.DisposeAsync().ConfigureAwait(false);
            }
        }
    }
}

DbContextPool类被EntityFrameworkServiceCollectionExtensions类的AddDbContextPool方法用到

        public static IServiceCollection AddDbContextPool<TContextService, TContextImplementation>(
            [NotNull] this IServiceCollection serviceCollection,
            [NotNull] Action<IServiceProvider, DbContextOptionsBuilder> optionsAction,
            int poolSize = 128)
            where TContextImplementation : DbContext, TContextService
            where TContextService : class
        {
            Check.NotNull(serviceCollection, nameof(serviceCollection));
            Check.NotNull(optionsAction, nameof(optionsAction));

            AddPoolingOptions<TContextImplementation>(serviceCollection, optionsAction, poolSize);

            serviceCollection.TryAddSingleton<IDbContextPool<TContextImplementation>, DbContextPool<TContextImplementation>>();
            serviceCollection.AddScoped<IScopedDbContextLease<TContextImplementation>, ScopedDbContextLease<TContextImplementation>>();

            serviceCollection.AddScoped<TContextService>(
                sp => sp.GetRequiredService<IScopedDbContextLease<TContextImplementation>>().Context);

            return serviceCollection;
        }

        public static IServiceCollection AddPooledDbContextFactory<TContext>(
            [NotNull] this IServiceCollection serviceCollection,
            [NotNull] Action<IServiceProvider, DbContextOptionsBuilder> optionsAction,
            int poolSize = 128)
            where TContext : DbContext
        {
            Check.NotNull(serviceCollection, nameof(serviceCollection));
            Check.NotNull(optionsAction, nameof(optionsAction));

            AddPoolingOptions<TContext>(serviceCollection, optionsAction, poolSize);
            /* 如果尚未注册，则将TImplementation中指定的指定TService作为Microsoft.Extensions.DependencyInjection.ServiceLifetime.Singleton
             * 服务实现类型添加到集合中。
             */
            serviceCollection.TryAddSingleton<IDbContextPool<TContext>, DbContextPool<TContext>>();
            serviceCollection.TryAddSingleton<IDbContextFactory<TContext>, PooledDbContextFactory<TContext>>();

            return serviceCollection;
        }

DbContextPool类的方法被DbContextLease结构用到

        public DbContextLease([NotNull] IDbContextPool contextPool, bool standalone)
        {
            _contextPool = contextPool;
            _standalone = standalone;

            var context = _contextPool.Rent();
            Context = context;

            context.SetLease(this);
        }

        public void Release()
        {
            if (Release(out var pool, out var context))
            {
                pool.Return(context);
            }
        }

        public ValueTask ReleaseAsync() => Release(out var pool, out var context) ? pool.ReturnAsync(context) : new ValueTask();

        private bool Release(out IDbContextPool pool, out IDbContextPoolable context)
        {
            pool = _contextPool;
            context = Context;
            _contextPool = null;
            Context = null;

            return pool != null;
        }