EntityFramework Core Raw SQL

朱雀 2022-06-17 10:39 172阅读 0赞

## EntityFramework Core Raw SQL ##

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

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

![复制代码][copycode.gif]

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

![复制代码][copycode.gif]

public class BlogRepository : EntityBaseRepository<Blog>,
            IBlogRepository
        {
            private EFCoreContext _efCoreContext;
            public BlogRepository(EFCoreContext efCoreContext) : base(efCoreContext)
            {
                _efCoreContext = efCoreContext;
            }
    
            public IEnumerable<Blog> GetList()
            {
                var iQueryTable = _efCoreContext.Set<Blog>().
                    FromSql("select * from Blog");
                return iQueryTable.ToList();
            }
        }

![复制代码][copycode.gif]

![589642-20170105223225159-452576468.gif][]

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

![复制代码][copycode.gif]

CREATE PROCEDURE dbo.GetBlogList
    AS
    BEGIN
        SELECT * FROM dbo.Blog
    END
    GO

![复制代码][copycode.gif]

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

![复制代码][copycode.gif]

### 参数查询  ###

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

![复制代码][copycode.gif]

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

![复制代码][copycode.gif]

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

![复制代码][copycode.gif]

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

![复制代码][copycode.gif]

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

![复制代码][copycode.gif]

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

![复制代码][copycode.gif]

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

![589642-20170105232022925-1713017003.png][]

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

### linq查询 ###

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

![复制代码][copycode.gif]

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

![复制代码][copycode.gif]

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

![复制代码][copycode.gif]

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

![复制代码][copycode.gif]

![589642-20170105233528519-433924931.png][]

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

![复制代码][copycode.gif]

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

![复制代码][copycode.gif]

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

![复制代码][copycode.gif]

USE [EFCoreDb]
    GO
    
    IF OBJECT_ID('dbo.GetBlog') IS NOT NULL
        DROP FUNCTION dbo.GetBlog;
    GO
    
    CREATE FUNCTION dbo.GetBlog 
        (@Name VARCHAR(max)) RETURNS TABLE WITH SCHEMABINDING
    AS   
    RETURN
     SELECT Id, Name, Url FROM dbo.Blog WHERE Name = @Name
    GO

![复制代码][copycode.gif]

调用如下：

![复制代码][copycode.gif]

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

![复制代码][copycode.gif]

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

![589642-20170106000331175-2024095248.png][]

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

![589642-20170106000439737-2066767319.png][]

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

![589642-20170106000519300-2072569602.png][]

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

![复制代码][copycode.gif]

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

![复制代码][copycode.gif]

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

![589642-20170106000926941-357702723.png][]

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

### 底层ADO.NET查询 ###

![复制代码][copycode.gif]

public IEnumerable<Blog> GetList()
            {
                var list = new List<Blog>();
                using (var connection = _efCoreContext.Database.GetDbConnection())
                {
                    connection.Open();
    
                    using (var command = connection.CreateCommand())
                    {
                        command.CommandText = "SELECT * FROM dbo.Blog";
    
                        using (SqlDataReader reader = command.ExecuteReader() as SqlDataReader)
                        {
                            while (reader.Read())
                            {
                                var blog = new Blog();
                                blog.Id = Convert.ToInt32(reader["Id"]);
                                blog.Name = reader["Name"].ToString();
                                blog.Url = reader["Url"].ToString();
                                list.Add(blog);
                            }
                        }                      
                    }
                }
                return list;
            }

![复制代码][copycode.gif]

![589642-20170106003241644-696059426.gif][]

##     ##

[copycode.gif]: /images/20220617/df621935b7d94ae0b78f71c2dec05429.png
[589642-20170105223225159-452576468.gif]: /images/20220617/3a07a7cbdd06447789aaa73ea8e9c30e.png
[589642-20170105232022925-1713017003.png]: /images/20220617/66fc732edfce4b989c91c5349d58d38a.png
[589642-20170105233528519-433924931.png]: /images/20220617/c2e8967fadbc468cbfa75c6caf66dfd9.png
[589642-20170106000331175-2024095248.png]: /images/20220617/d62ae0249b764fbd8ee89304881a096d.png
[589642-20170106000439737-2066767319.png]: /images/20220617/510e2345007845e7bfe5c97b8dfabbfb.png
[589642-20170106000519300-2072569602.png]: /images/20220617/e50f1e68941240cb972cede33f084ea6.png
[589642-20170106000926941-357702723.png]: /images/20220617/8b2b747ac40a48c1bf89b2cba46e1ebd.png
[589642-20170106003241644-696059426.gif]: /images/20220617/72c15a9db7924b09a07875d7cee04473.png