EntityFramework Core Raw SQL

JeffckyWang
2022-11-06
原文

前言

本节我们来讲讲EF Core中的原始查询，目前在项目中对于简单的查询直接通过EF就可以解决，但是涉及到多表查询时为了一步到位就采用了原始查询的方式进行。下面我们一起来看看。

EntityFramework Core Raw SQL

基础查询（执行SQL和存储过程）

啥也不说了，拿起键盘就是干，如下:

    public class HomeController : Controller<br/>
    {<br/>
        private IBlogRepository _blogRepository;<br/>
        public HomeController(IBlogRepository blogRepository)<br/>
        {<br/>
            _blogRepository = blogRepository;<br/>
        }<br/>
        public IActionResult Index()<br/>
        {<br/>
            var list = _blogRepository.GetList();<br/>
            return Ok();<br/>
        }<br/>
    }

    public class BlogRepository : EntityBaseRepository<Blog>,<br/>
        IBlogRepository<br/>
    {<br/>
        private EFCoreContext _efCoreContext;<br/>
        public BlogRepository(EFCoreContext efCoreContext) : base(efCoreContext)<br/>
        {<br/>
            _efCoreContext = efCoreContext;<br/>
        }

        public IEnumerable<Blog> GetList()<br/>
        {<br/>
            var iQueryTable = _efCoreContext.Set<Blog>().<br/>
                FromSql("select * from Blog");<br/>
            return iQueryTable.ToList();<br/>
        }<br/>
    }

下面我们来看看存储过程。

CREATE PROCEDURE dbo.GetBlogList<br/>
AS<br/>
BEGIN<br/>
    SELECT * FROM dbo.Blog<br/>
END<br/>
GO

        public IEnumerable<Blog> GetList()<br/>
        {<br/>
            var iQueryTable = _efCoreContext.Set<Blog>().<br/>
                FromSql("EXECUTE  dbo.GetBlogList");<br/>
            return iQueryTable.ToList();<br/>
        }

参数查询

利用参数化存储过程查询。

ALTER PROCEDURE [dbo].[GetBlogList]<br/>
@id INT<br/>AS<br/>
BEGIN<br/>
    SELECT * FROM dbo.Blog WHERE Id = @id<br/>
END

结果利用FromSql就变成了如下：

        public IEnumerable<Blog> GetList()<br/>
        {<br/>
            var Id = new SqlParameter("Id", "");<br/>
            var iQueryTable = _efCoreContext.Set<Blog>().<br/>
                FromSql("EXEC dbo.GetBlogList {0}", 1);<br/>
            return iQueryTable.ToList();<br/>
        }

上述是利用string.format的形式来传参，我们也可以利用SqlParameter来传参，如下：

        public IEnumerable<Blog> GetList()<br/>
        {<br/>
            var Id = new SqlParameter("Id", "");<br/>
            var iQueryTable = _efCoreContext.Set<Blog>().<br/>
                FromSql("EXEC dbo.GetBlogList @id", Id);<br/>
            return iQueryTable.ToList();<br/>
        }

我们通过开启调试，可以清晰看到执行的存储过程。

通过如上我们知道参数化查询有两种形式，下面我们再来看看linq查询。

linq查询

上述我们演示一直直接使用FromSql，其实在此之后我们可以继续通过linq来进行查询，如下：

        public IEnumerable<Blog> GetList()<br/>
        {<br/>
            var Id = new SqlParameter("Id", "");<br/>
            var iQueryTable = _efCoreContext.Set<Blog>().<br/>
                FromSql("EXEC dbo.GetBlogList @id", Id).Where(d => d.Name == "efcore2");<br/>
            return iQueryTable.ToList();<br/>
        }

之前我们映射了Blog和Post之间的关系，这里我们只能查询出Blog表的数据，通过对上述linq的讲解，我们完全可以通过inlcude来显式加载Post表数据，如下：

        public IEnumerable<Blog> GetList()<br/>
        {<br/>
            var Id = new SqlParameter("Id", "");<br/>
            var iQueryTable = _efCoreContext.Set<Blog>().<br/>
                FromSql("EXEC dbo.GetBlogList @id", Id).Include(d => d.Posts);<br/>
            return iQueryTable.ToList();<br/>
        }

好吧，明确告诉我们对于存储过程是不支持Inlude操作的，所以要想Include我们只能进行简单的查询，如下：

        public IEnumerable<Blog> GetList()<br/>
        {<br/>
            var iQueryTable = _efCoreContext.Set<Blog>().<br/>
                FromSql("select * from blog").Include(d => d.Posts);<br/>
            return iQueryTable.ToList();<br/>
        }

查找官网资料时发现居然对表值函数（TVF）是可以Include的，创建内嵌表值函数如下：

USE [EFCoreDb]<br/>
GO

IF OBJECT_ID('dbo.GetBlog') IS NOT NULL<br/>
    DROP FUNCTION dbo.GetBlog;<br/>
GO

CREATE FUNCTION dbo.GetBlog<br/>
    (@Name VARCHAR(max)) RETURNS TABLE WITH SCHEMABINDING<br/>
AS<br/>
RETURN<br/>
 SELECT Id, Name, Url FROM dbo.Blog WHERE Name = @Name<br/>
GO

调用如下：

        public IEnumerable<Blog> GetList()<br/>
        {<br/>
            var name = "efcore2";<br/>
            var iQueryTable = _efCoreContext.Set<Blog>().<br/>
                FromSql("select * from [dbo].[GetBlog] {0}", name).Include(d => d.Posts);<br/>
            return iQueryTable.ToList();<br/>
        }

结果出乎意料的出现语法错误：

通过SQL Server Profiler查看发送的SQL语句如下：

这能不错么，官网给的示例也是和上述一样，如下：

只是按照和他一样的搬过来了，未曾想太多，还是粗心大意了，想了好一会，按照我们正常调用表值函数即可，我们需要用括号括起来才行，如下：

        public IEnumerable<Blog> GetList()<br/>
        {<br/>
            var name = "efcore2";<br/>
            var iQueryTable = _efCoreContext.Set<Blog>().<br/>
                FromSql("select * from [dbo].[GetBlog] ({0})", name).Include(d => d.Posts);<br/>
            return iQueryTable.ToList();<br/>
        }

上述将[dbo.GetBlog]和（{0}）隔开和挨着都可以。这个时候才不会出现语法错误。执行的SQL如下才是正确的。

好了，到了这里关于EF Core中原始查询我们就告一段落了，其中还有一个知识点未谈及到，在EF Core我们可以直接通过底层的ADO.NET来进行查询，我们来看下：

底层ADO.NET查询

        public IEnumerable<Blog> GetList()<br/>
        {<br/>
            var list = new List<Blog>();<br/>
            using (var connection = _efCoreContext.Database.GetDbConnection())<br/>
            {<br/>
                connection.Open();

                using (var command = connection.CreateCommand())<br/>
                {<br/>
                    command.CommandText = "SELECT * FROM dbo.Blog";

                    using (SqlDataReader reader = command.ExecuteReader() as SqlDataReader)<br/>
                    {<br/>
                        while (reader.Read())<br/>
                        {<br/>
                            var blog = new Blog();<br/>
                            blog.Id = Convert.ToInt32(reader["Id"]);<br/>
                            blog.Name = reader["Name"].ToString();<br/>
                            blog.Url = reader["Url"].ToString();<br/>
                            list.Add(blog);<br/>
                        }<br/>
                    }<br/>
                }<br/>
            }<br/>
            return list;<br/>
        }