EFCore 复杂SQL查询踩坑记录

复杂查询

在EFCore 中的查询一般通过DbContext.Set().Where()方法来查询，但是这样进行的是单表查询，如果我们需要进行多表关联查询，这种方法就显得非常无力。
以下为本次记录用到的实体类

    // 博客实体
    public class Blog
    {
        public Blog()
        {
            Posts = new HashSet<Post>();
        }

        public long BlogId { get; set; }
        public string Name{ get; set; }
        public string Url { get; set; }
        public long Rating { get; set; }

        public ICollection<Post> Posts { get; set; }
    }

	// 文章实体
    public class Post
    {
        public long PostId { get; set; }
        public string Title { get; set; }
        public string Content { get; set; }
        public long BlogId { get; set; }
        public DateTime PostDate { get; set; }

        public Blog Blog { get; set; }
    }

在实际的业务场景中是经常需要返回多表关联的数据实体用于前端展示，如果你的EF实体构建得比较完善(外键什么的都在Context的OnModelCreating方法中定义好了)，是可以使用Include方法来进行关联查询，将关联的实体也一并查询出来。
假如前端展示需要这样的数据

    public class PostDto
    {
        public string Title { get; set; }
        public string Content { get; set; }
        public DateTime PostDate { get; set; }
        public string BlogUrl { get; set; }
        public string BlogName { get; set; }
    }

既包含文章表的数据，又包含博客表的数据。如果用include的方法是可以把数据都拉取到，但是…这样定义实体真的太累人了，既要定义数据库里有的字段，又要定义外键字段。

var test = await ctx.Set<Post>().Include(s => s.Blog)
    .Select<Post, PostDto>(s => new PostDto
    {
        PostDate = s.PostDate,
        BlogUrl = s.Blog.Url,
        BlogName = s.Blog.Name,
        Content = s.Content,
        Title = s.Title
    }).ToListAsync();

如果我们的实体类中没有定义外键类属性，这种方式就完全不可用了。以下是两种可行的解决方法。

Linq

总所周知，EFCore是对IQueryable进行编译，生成SQL语句执行，简单的讲就是根据实体之间的关系翻译IQueryable成为SQL语句。那么就意味着我们可以通过编写Linq的方式进行复杂查询。
以下是使用Linq查询PostDto的示例。

 var query = from post in ctx.Set<Post>()
             join blog in ctx.Set<Blog>()
                 on post.BlogId equals blog.BlogId
             where post.BlogId == 1
             select new PostDto
             {
                 PostDate = post.PostDate,
                 BlogName = blog.Name,
                 BlogUrl = blog.Url,
                 Content = post.Content,
                 Title = post.Title
             };
 // 执行查询
 var lst = await query.ToListAsync();

以上示例与使用Include方法所生产的SQL语句是完全一样的。而且不需要为各个实体编写外键属性。

使用Dapper执行SQL语句

细心的伙伴可能已经发现了，这样写Linq和直接写SQL好像没什么区别，而且Linq还要多耗费一部分性能转换成SQL语句，这就有点脱裤子放屁的意思了。
你要说像上文那个样子写Linq跟写SQL没区别，其实也没错，但是考虑到数据库发生变更的情况就有区别了。因为Linq到实际SQL语句之间还有一层翻译，正是这层翻译使得数据库与应用程序隔离开来，即使数据库由SQL Server转为MySQL或者SQLite什么的，Linq代码依然不需要做任何修改，而如果直接写SQL语句则可能需要按数据库的类型做出必要的修改。
如果在一些性能要求极其苛刻，或者需要用到数据库专有语法的时候，我们只能用SQL语句的形式进行数据库查询。
总所周知，Dapper是一个高性能的数据库工具类库，用Dapper可能非常方便快捷地执行SQL语句。
在EFCore中可以使用以下的方法获取到对应的DbConnection

DbConnection conn = context.Database.GetDbConnection();

从而对DbConnection使用Dapper的扩展方法进行查询。

using Microsoft.EntityFrameworkCore;
using Dapper;
using System.Data.Common;
...
string sql = "select p.PostDate,p.Content,p.Title,b.Url as BlogUrl b.Name as BlogName " +
            "from Post p inner join Blog b on p.BlogId=b.BlogId " +
            "where p.BlogId=1";
DbConnection conn = context.Database.GetDbConnection();
var lst = await conn.QueryAsync<PostDto>(sql);

值得注意的是，如果EFCore中配置了自动事务，或者EFCore中开启了事务，那么使用Dapper执行SQL语句之前要把事务拿到并传给Dapper。

using Microsoft.EntityFrameworkCore;
using Microsoft.EntityFrameworkCore.Storage;
using Dapper;
using System.Data.Common;
...
string sql = "select p.PostDate,p.Content,p.Title,b.Url as BlogUrl b.Name as BlogName " +
             "from Post p inner join Blog b on p.BlogId=b.BlogId " +
             "where p.BlogId=1";
DbConnection conn = context.Database.GetDbConnection();
DbTransaction tran = default;
// 拿到当前事务并转成DbTransaction
var efTran = context.Database.CurrentTransaction;
if (efTran != null)
{
    tran = efTran.GetDbTransaction();
}

var lst = await conn.QueryAsync<PostDto>(sql, null, tran);

参考资料

EFCore 复杂查询

EFCore 使用SQL语句查询