EFCore執行Sql語句的方法：FromSql與ExecuteSqlCommand

来源:http://www.cnblogs.com/skig/archive/2016/12/19/EFCore-SqlQuery.html
-Advertisement-
Play Games

前言 在EFCore中執行Sql語句的方法為：FromSql與ExecuteSqlCommand；在EF6中的為SqlQuery與ExecuteSqlCommand，而FromSql和SqlQuery有很大區別，FromSql返回值為IQueryable，因此為延遲載入的，可以與Linq擴展方法配合 ...


前言

在EFCore中執行Sql語句的方法為：FromSql與ExecuteSqlCommand；在EF6中的為SqlQuery與ExecuteSqlCommand，而FromSql和SqlQuery有很大區別，FromSql返回值為IQueryable，因此為延遲載入的，可以與Linq擴展方法配合使用，但是有不少的坑(EFCore版本為1.1.0)，直接執行Sql語句的建議不要使用FromSql，但是EFCore中並沒有提供SqlQuery方法，因此下麵會貼出SqlQuery的實現代碼供大家參考，以便在EFCore中能使用。

 

FromSql和ExecuteSqlCommand的使用

測試時使用了SqlServer2008和SqlServer Profiler進行Sql語句捕捉，EFCore的版本為1.1.0。

測試的Entity Model與DbContext

 1     public class MSSqlDBContext : DbContext
 2     {
 3         protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
 4         {
 5             optionsBuilder.UseSqlServer(@"data source=localhost;initial catalog=TestDB;Integrated Security=True;");
 6         }
 7         public DbSet<Person> Person { get; set; }
 8         public DbSet<Address> Address { get; set; }
 9 }
10 
11     [Table(nameof(Person))]
12     public class Person
13     {
14         public int id { get; set; }
15         public string name { get; set; }
16         [Column(TypeName = "datetime")]
17         public DateTime? birthday { get; set; }
18         public int? addrid { get; set; }
19 }
20 
21     [Table(nameof(Address))]
22     public class Address
23     {
24         public int id { get; set; }
25         public string fullAddress { get; set; }
26         public double? lat { get; set; }
27         public double? lon { get; set; }
28     }
View Code

 

ExecuteSqlCommand

EFCore的ExecuteSqlCommand和EF6的一樣，執行非查詢的Sql語句：

1             var db = new MSSqlDBContext();
2             db.Database.ExecuteSqlCommand($"update {nameof(Person)} set name=@name where id=@id", new[] 
3             {
4                 new SqlParameter("name", "tom1"),
5                 new SqlParameter("id", 1),
6             });

 

FromSql

官方參考文檔：https://docs.microsoft.com/en-us/ef/core/querying/raw-sql

簡單使用

1             var db = new MSSqlDBContext();
2             var name = "tom";
3             var list = db.Set<Person>().FromSql($"select * from {nameof(Person)} where {nameof(name)}=@{nameof(name)} ", 
4                 new SqlParameter(nameof(name), name)).ToList();

生成的Sql：

exec sp_executesql N'select * from Person where name=@name 
',N'@name nvarchar(3)',@name=N'tom'

註意：

預設生成的為Person的Model，如果Select獲取的欄位中不包含Person中的某欄位就會拋異常了，例如：下麵的語句只獲取name欄位，並沒有包含Person的其他欄位，那麼拋異常：The required column 'id' was not present in the results of a 'FromSql' operation.

db.Set<Person>().FromSql($"select name from {nameof(Person)} ").ToList();

那麼改為：

db.Set<Person>().Select(l => l.name).FromSql($"select name from {nameof(Person)} ").ToList();

 

執行存儲過程

1             var db = new MSSqlDBContext();
2             db.Set<Person>().FromSql("exec testproc @id", new SqlParameter("id", 1)).ToList();

生成的Sql：

exec sp_executesql N'exec testproc @id
',N'@id int',@id=1

 

與Linq擴展方法配合使用

1             var db = new MSSqlDBContext();
2             db.Set<Person>().FromSql($"select * from {nameof(Person)} where name=@name ", new SqlParameter("@name", "tom"))
3                 .Select(l => new { l.name, l.birthday }).ToList();

生成的Sql：

exec sp_executesql N'SELECT [l].[name], [l].[birthday]
FROM (
    select * from Person where name=@name 
) AS [l]',N'@name nvarchar(3)',@name=N'tom'
View Code

inner join + order by

1             var db = new MSSqlDBContext();
2              (from p in db.Set<Person>().FromSql($"select * from {nameof(Person)} ")
3              join a in db.Set<Address>().Where(l => true)
4              on p.addrid equals a.id
5              select new { p.id, p.name, a.fullAddress }).OrderBy(l => l.id).ToList();

生成的Sql：

SELECT [p].[id], [p].[name], [t].[fullAddress]
FROM (
    select * from Person 
) AS [p]
INNER JOIN (
    SELECT [l0].*
    FROM [Address] AS [l0]
) AS [t] ON [p].[addrid] = [t].[id]
ORDER BY [p].[id]
View Code

left join + order by

1             var db = new MSSqlDBContext();
2              (from p in db.Set<Person>().FromSql($"select * from {nameof(Person)} ")
3              join a in db.Set<Address>().Where(l => true)
4              on p.addrid equals a.id into alist
5              from a in alist.DefaultIfEmpty()
6              select new { p.id, p.name, fullAddress = a == null ? null : a.fullAddress }).OrderBy(l => l.id).ToList();

生成的Sql：(生成的Sql很有問題，order by後面多了[p].[addrid]，而且生成的select的欄位也是多了)

SELECT [p].[id], [p].[addrid], [p].[birthday], [p].[name], [t].[id], [t].[fullAddress], [t].[lat], [t].[lon]
FROM (
    select * from Person 
) AS [p]
LEFT JOIN (
    SELECT [l0].[id], [l0].[fullAddress], [l0].[lat], [l0].[lon]
    FROM [Address] AS [l0]
) AS [t] ON [p].[addrid] = [t].[id]
ORDER BY [p].[id], [p].[addrid]

將FromSql換成Where擴展方法試試：

1             (from p in db.Set<Person>().Where(l => true)
2              join a in db.Set<Address>().Where(l => true)
3              on p.addrid equals a.id into alist
4              from a in alist.DefaultIfEmpty()
5              select new { p.id, p.name, fullAddress = a == null ? null : a.fullAddress }).OrderBy(l => l.id).ToList();

EFCore生成的Sql（order by後面還是多了[addrid]，select的欄位也是多了）：

SELECT [l].[id], [l].[addrid], [l].[birthday], [l].[name], [t].[id], [t].[fullAddress], [t].[lat], [t].[lon]
FROM [Person] AS [l]
LEFT JOIN (
    SELECT [l1].[id], [l1].[fullAddress], [l1].[lat], [l1].[lon]
    FROM [Address] AS [l1]
) AS [t] ON [l].[addrid] = [t].[id]
ORDER BY [l].[id], [l].[addrid]

而在EF6中生成的Sql，比EFCore的生成好多了：

SELECT 
    [Project1].[id] AS [id], 
    [Project1].[name] AS [name], 
    [Project1].[C1] AS [C1]
    FROM ( SELECT 
        [Extent1].[id] AS [id], 
        [Extent1].[name] AS [name], 
        CASE WHEN ([Extent2].[id] IS NULL) THEN CAST(NULL AS varchar(1)) ELSE [Extent2].[fullAddress] END AS [C1]
        FROM  [dbo].[Person] AS [Extent1]
        LEFT OUTER JOIN [dbo].[Address] AS [Extent2] ON [Extent1].[addrid] = [Extent2].[id]
    )  AS [Project1]
    ORDER BY [Project1].[id] ASC

結果說明

FromSql不能代替原來EF6的SqlQuery使用，而且結合Linq擴展方法使用的時候生成的Sql會存在一些問題(EFCore版本為：1.1.0)，那麼為了能在EFCore中執行Sql查詢語句，下麵提供對SqlQuery方法的實現。

SqlQuery的實現

 1 public static IList<T> SqlQuery<T>(DbContext db, string sql, params object[] parameters)
 2             where T : new()
 3         {
 4             //註意：不要對GetDbConnection獲取到的conn進行using或者調用Dispose，否則DbContext後續不能再進行使用了，會拋異常
 5             var conn = db.Database.GetDbConnection();
 6             try
 7             {
 8                 conn.Open();
 9                 using (var command = conn.CreateCommand())
10                 {
11                     command.CommandText = sql;
12                     command.Parameters.AddRange(parameters);
13                     var propts = typeof(T).GetProperties();
14                     var rtnList = new List<T>();
15                     T model;
16                     object val;
17                     using (var reader = command.ExecuteReader())
18                     {
19                         while (reader.Read())
20                         {
21                             model = new T();
22                             foreach (var l in propts)
23                             {
24                                 val = reader[l.Name];
25                                 if (val == DBNull.Value)
26                                 {
27                                     l.SetValue(model, null);
28                                 }
29                                 else
30                                 {
31                                     l.SetValue(model, val);
32                                 }
33                             }
34                             rtnList.Add(model);
35                         }
36                     }
37                     return rtnList;
38                 }
39             }
40             finally
41             {
42                 conn.Close();
43             }
44         }
View Code

使用：

1             var db = new MSSqlDBContext();
2             string name = "tom";
3             var list = SqlQuery<PAModel>(db,
4                 $" select p.id, p.name, a.fullAddress, a.lat, a.lon " +
5                 $" from ( select * from {nameof(Person)} where {nameof(name)}=@{nameof(name)} ) as p " +
6                 $" left join {nameof(Address)} as a on p.addrid = a.id ",
7                 new[] { new SqlParameter(nameof(name), name) });

生成的Sql：

exec sp_executesql N' select p.id, p.name, a.fullAddress, a.lat, a.lon  from ( select * from Person where name=@name ) as p  left join Address as a on p.addrid = a.id ',N'@name nvarchar(3)',@name=N'tom'

 


您的分享是我們最大的動力!

-Advertisement-
Play Games
更多相關文章
  • 複製（克隆）控制項屬性（使用反射）
    /// /// Clones the control. /// /// The source element. /// The dest element. public void CloneControl(UIElement sourceElement, UIElement destElement.... ...
  • 關於log4net日誌的配置流程
    最近又重新整理一下log4net日誌的配置,現在記錄一下流程和一些遇到的問題,以備後續使用,具體的配置參數等信息.此文無,見諒! 1. 下載log4net.dll文件(網上很多,隨便找一個!) 2. 項目中引用此dll 3. appconfig中配置log4net(具體配置方案有很多例子.) 4.  ...
  • .Net語言 APP開發平臺——Smobiler學習日誌：如何快速實現地圖定位時的地點微調功能
    Smobiler是一個在VS環境中使用.Net語言來開發APP的開發平臺，也許比Xamarin更方便 ...
  • Solr 排除查詢
    前言 solr排除查詢也就是我們在資料庫和程式中經常處理的不等於，solr的語法是在定語前加【-】。、 從Solr控制台 從solr控制台看下排除查詢的url， 也可以在構建url查詢時自己添加篩選條件 ...
  • [C#] C# 知識回顧 - 學會使用異常
    學會使用異常 在 C# 中，程式中在運行時出現的錯誤，會不斷在程式中進行傳播，這種機制稱為“異常”。 異常通常由錯誤的代碼引發，並由能夠更正錯誤的代碼進行 catch。 異常可由 .NET 的 CLR 或由程式中的代碼引發。 一旦引發了異常，這個異常將會在調用堆棧中一直向上進行傳播，直到尋找到跟它匹 ...
  • MVC學習筆記1
    1. Action 如果返回的是自定義的引用類型，則預設返回的是類名，其實就是調用了類的tostring方法。 2. @Html.Partial用於將分部視圖渲染為字元串 @{Html.RenderPartial}將分佈視圖直接寫入響應輸出流，所以只能直接放在代碼塊中，不能放在表達式中(返回值是vo ...
  • WebApi - 路由
    這段時間的博客打算和大家一起分享下webapi的使用和心得，主要原因是群裡面有朋友說希望能有這方面的文章分享，隨便自己也再回顧下；後面將會和大家分不同篇章來分享交流心得，希望各位多多掃碼支持和點贊，謝謝，希望大家喜歡： . 使用vs創建webapi項目及項目結構介紹 . 預設路由模板介紹 . 屬性路 ...
  • C#學習記錄
    轉眼幾個月沒更博了，把幾個月前學C#的筆記發一下，就記錄了點教重要的點子而已 1.列印 Console.WriteLine(); 列印 Console.ReadKey(); 按一個按鍵繼續執行 Console.ReadLine(); //用戶輸入文字的時候程式是暫停的，用戶輸入完畢點回車，把用戶輸入 ...
一周排行
    -Advertisement-
    Play Games
  • 移動開發（一）：使用.NET MAUI開發第一個安卓APP
    移動開發（一）：使用.NET MAUI開發第一個安卓APP 對於工作多年的C#程式員來說，近來想嘗試開發一款安卓APP，考慮了很久最終選擇使用.NET MAUI這個微軟官方的框架來嘗試體驗開發安卓APP，畢竟是使用Visual Studio開發工具，使用起來也比較的順手，結合微軟官方的教程進行了安卓 ...
  • wpf ToggleButton選中效果和一個登錄界面
    前言 QuestPDF 是一個開源 .NET 庫，用於生成 PDF 文檔。使用了C# Fluent API方式可簡化開發、減少錯誤並提高工作效率。利用它可以輕鬆生成 PDF 報告、發票、導出文件等。 項目介紹 QuestPDF 是一個革命性的開源 .NET 庫，它徹底改變了我們生成 PDF 文檔的方 ...
  • 彈幕樹洞項目功能新增篇
    項目地址 項目後端地址： https://github.com/ZyPLJ/ZYTteeHole 項目前端頁面地址： ZyPLJ/TreeHoleVue (github.com) https://github.com/ZyPLJ/TreeHoleVue 目前項目測試訪問地址： http://tree ...
  • 第27篇 sqlserver2022詳細安裝步驟
    話不多說，直接開乾 一.下載 1.官方鏈接下載： https://www.microsoft.com/zh-cn/sql-server/sql-server-downloads 2.在下載目錄中找到下麵這個小的安裝包 SQL2022-SSEI-Dev.exe，運行開始下載SQL server； 二. ...
  • .NET 開源高性能 MQTT 類庫
    前言 隨著物聯網（IoT）技術的迅猛發展，MQTT（消息隊列遙測傳輸）協議憑藉其輕量級和高效性，已成為眾多物聯網應用的首選通信標準。 MQTTnet 作為一個高性能的 .NET 開源庫，為 .NET 平臺上的 MQTT 客戶端與伺服器開發提供了強大的支持。 本文將全面介紹 MQTTnet 的核心功能 ...
  • Serilog文檔翻譯系列（六） - 可用的接收器、增強器、格式化輸出
    Serilog支持多種接收器用於日誌存儲，增強器用於添加屬性，LogContext管理動態屬性，支持多種輸出格式包括純文本、JSON及ExpressionTemplate。還提供了自定義格式化選項，適用於不同需求。 ...
  • 警惕 Visual Studio 屬性求值副作用導致邏輯不符合預期
    目錄簡介獲取 HTML 文檔解析 HTML 文檔測試參考文章 簡介 動態內容網站使用 JavaScript 腳本動態檢索和渲染數據，爬取信息時需要模擬瀏覽器行為，否則獲取到的源碼基本是空的。 本文使用的爬取步驟如下： 使用 Selenium 獲取渲染後的 HTML 文檔 使用 HtmlAgility ...
  • [使用目前最新版]HybridCLR6.9.0+YooAsset2.2.4實現純C# Unity熱更新方案 (一)
    1.前言 什麼是熱更新 游戲或者軟體更新時，無需重新下載客戶端進行安裝，而是在應用程式啟動的情況下，在內部進行資源或者代碼更新 Unity目前常用熱更新解決方案 HybridCLR，Xlua，ILRuntime等 Unity目前常用資源管理解決方案 AssetBundles，Addressable， ...
  • 在 ASP.NET Core Web API 中使用操作篩選器統一處理通用操作
    本文章主要是在C# ASP.NET Core Web API框架實現向手機發送驗證碼簡訊功能。這裡我選擇是一個互億無線簡訊驗證碼平臺，其實像阿裡雲，騰訊雲上面也可以。 首先我們先去 互億無線 https://www.ihuyi.com/api/sms.html 去註冊一個賬號 註冊完成賬號後，它會送 ...
  • 第28篇 如何.net中實現高效可靠數據同步api
    通過以下方式可以高效，並保證數據同步的可靠性 1.API設計 使用RESTful設計，確保API端點明確，並使用適當的HTTP方法（如POST用於創建，PUT用於更新）。 設計清晰的請求和響應模型，以確保客戶端能夠理解預期格式。 2.數據驗證 在伺服器端進行嚴格的數據驗證，確保接收到的數據符合預期格 ...
所有分類