FreeSql 導入數據的各種場景總結 [C#.NET ORM]

• 2022 年 9 月 7 日
• 筆記
• freesql, orm, 導數據, 批量導入

💻 前言

導入數據這種臟活、累活，相信大家多多少少都有經歷，常見的場景有：

• 同伺服器從A表導數據到B表
• 批量導入新數據
• 批量新增或更新數據
• 跨伺服器從A表導數據到B表

每種場景有自己的特點，我們一般會根據特點訂製做導入數據優化，減少總體導入的耗時，或者避免資料庫IO/CPU佔用過高，而影響到其他正常業務。

FreeSql 有好幾個實用功能，流式讀取數據、查詢並插入、批量對比更新、插入或修改（支援實體類或字典），用好這些功能可以很方便的實現各種導入數據場景。其實 FreeSql 對應的文檔一直都有，只是內容介紹比較零散，這篇文章將針對數據導入詳細介紹它們的使用方法，既然 FreeSql bug 少那就多優化一下文檔吧！

本文講解以上四種常見的數據導入實現，讓使用者高效解決工作問題。如果你在使用其他更好的導入方案，歡迎加入討論！

🌳 C#.NET ORM概念

.NET ORM Object Relational Mapping 是一種為了解決面向對象與關係資料庫存在的互不匹配的現象的技術。FreeSql .NET ORM 支援 .NetFramework4.0+、.NetCore、Xamarin、MAUI、Blazor、以及還有說不出來的運行平台，因為程式碼綠色無依賴，支援新平台非常簡單。目前單元測試數量：8500+，Nuget下載數量：1M+。使用最寬鬆的開源協議 MIT //github.com/dotnetcore/FreeSql ，可以商用，文檔齊全，甚至拿去賣錢也可以。

FreeSql 主要優勢在於易用性上，基本是開箱即用，在不同資料庫之間切換兼容性比較好，整體的功能特性如下：

• 支援 CodeFirst 對比結構變化遷移、DbFirst 從資料庫生成實體類；
• 支援 豐富的表達式函數，獨特的自定義解析；
• 支援 批量添加、批量更新、BulkCopy、導航屬性，貪婪載入、延時載入、級聯保存、級聯刪除；
• 支援 讀寫分離、分表分庫，租戶設計，分散式事務；
• 支援 MySql/SqlServer/PostgreSQL/Oracle/Sqlite/Firebird/達夢/神通/人大金倉/翰高/Clickhouse/MsAccess Ado.net 實現包，以及 Odbc 的專門實現包；

8000+個單元測試作為基調，支援10多數資料庫，我們提供了通用Odbc理論上支援所有資料庫，目前已知有群友使用 FreeSql 操作華為高斯、mycat、tidb 等資料庫。安裝時只需要選擇對應的資料庫實現包：

dotnet add packages FreeSql.Provider.Sqlite

static IFreeSql fsql = new FreeSql.FreeSqlBuilder()
    .UseConnectionString(FreeSql.DataType.Sqlite, @"Data Source=db1.db")
    .UseAutoSyncStructure(true) //自動同步實體結構到資料庫
    .UseNoneCommandParameter(true) //SQL不使用參數化，以便調試
    .UseMonitorCommand(cmd => Console.WriteLine(cmd.CommandText)) //列印 SQL
    .Build();

FreeSql 提供多種 CRUD 使用習慣，請根據實際情況選擇團隊合適的一種：

• 要麼 FreeSql，原始用法；
• 要麼 FreeSql.Repository，倉儲+工作單元習慣；
• 要麼 FreeSql.DbContext，很像 EFCore 的使用習慣；
• 要麼 FreeSql.BaseEntity，充血模式；
• 要麼 直接像 dapper 那樣使用 DbConnection 擴展方法；

⚡ 場景一：同伺服器從A表導數據到B表

導入數據，正常需要根據源數據量的大小來評估，評估過程需要在實踐中慢慢積累，以便選擇對應的導入方案。同伺服器導入數據的方案有：

1、insert into A(field1, field2) select name, code from B where …

fsql.Select<B>()
    .Where(b => b.Time > DateTime.Parse("2022-08-01"))
    .InsertInto("A", b => new { b.Name, b.Code });

如果數據源是多個表組成，也可以：

fsql.Select<B, C>()
    .InnerJoin((b, c) => b.Id == c.Id)
    .Where((b, c) => b.Time > DateTime.Parse("2022-08-01"))
    .InsertInto("A", (b, c) => new { b.Name, c.Code });

2、分段插入

FreeSql 提供流式分段返回數據，防止讀取的數據源量過多時佔用記憶體，假設數據表有100W萬記錄，我們可以設置每次只返回 1000 條。提示：ToChunk 只執行了一次 SQL 查詢。

fsql.Select<B>()
    .Where(b => b.Time > DateTime.Parse("2022-08-01"))
    .ToChunk(b => new A { field1 = b.Name, field2 = b.Code }, 1000, cb => {
        fsql.Insert(cb.Object).NoneParameter().ExecuteAffrows();
        //如果資料庫支援 BulkCopy 可以調用對應的 API 方法，如 SqlServer：
        //fsql.Insert(cb.Object).ExecuteSqlBulkCopy();
    });

3、分頁插入

利用分頁多次讀取，分頁可能造成新舊數據問題，盡量設置好分頁排序並記錄好偏移量，確保重複問題。（不推薦）

var pageNumber = 1;
while (true)
{
    var list = fsql.Select<B>()
        .Where(b => b.Time > DateTime.Parse("2022-08-01"))
        .Page(pageNumber++, 1000)
        .OrderBy(b => b.Time)
        .ToList(b => new A { field1 = b.Name, field2 = b.Code }, 1000);
    if (list.Count == 0) break;

    fsql.Insert(cb.Object).NoneParameter().ExecuteAffrows();
    //如果資料庫支援 BulkCopy 可以調用對應的 API 方法，如 SqlServer：
    //fsql.Insert(cb.Object).ExecuteSqlBulkCopy();

    //停頓5秒後再插入，這個值可以根據需要自己調
    Thread.CurrentThread.Join(TimeSpan.FromSeconds(5));
}

📯 場景二：批量導入新數據

從 Excel/CVS 文件批量導入新數據，第一步需要將文件內容轉換為 DataTable/List<T> c# 對象，這一步網上有很多工具類，在此就不講解了。

此場景適合導入的數據是全新的、不存在於目標資料庫表，假設我們都已經將 Excel/CVS 內容轉換成為了 List<T>

1、利用無參數化插入

批量插入利用無參數化，會比參數化效率更高，注意參數化與SQL注入沒有必然聯繫。

fsql.Insert(list).NoneParameter().ExecuteAffrows();

2、利用 BulkCopy 插入

BulkCopy 是大批量數據插入的最優方案，只可惜不是每種資料庫都支援，FreeSql 支援了 SqlServer/Oracle/MySql/PostgreSQL/達夢 等資料庫的 BulkCopy API，如果是其他資料庫建議使用無參數化插入。

fsql.Insert(list).ExecuteSqlBulkCopy(); //SqlServer
fsql.Insert(list).ExecuteOracleBulkCopy(); //Oracle
fsql.Insert(list).ExecuteMySqlBulkCopy(); //MySql
fsql.Insert(list).ExecutePgCopy(); //PostgreSQL
fsql.Insert(list).ExecuteDmBulkCopy(); //達夢

為什麼不統一 API？

目前每種資料庫驅動的 BulkCopy API 參數不一致，這些參數可以針對性的進行調優。

🚀 場景三：批量新增或更新數據

相比場景二，場景三會更麻煩，因為不是簡單的追加數據，還要處理歷史數據的更新，甚至對歷史數據存在則忽略。正因為複雜才衍生出了更多的方案，每種方案都有優缺點，需要使用者根據自身實際情況選擇最適合的一種方法。

同上，我們假設已經將 Excel/CVS 內容轉換成為了 List<T>

1、記憶體循環 list 查詢判斷（不推薦）

foreach (var item in list)
{
    if (fsql.Select<T>(item).Any() == false)
        fsql.Insert(item).ExecuteAffrows();
    else
        fsql.Update<T>().SetSource(item).ExecuteAffrows();
}

這種方式實在不推薦作為批量操作，性能非常低。其實 FreeSql.Repository 提供了上述的封裝：

var repo = fsql.GetRepository<T>();
foreach (var item in list)
    repo.InsertOrUpdate(item);

2、利用資料庫 MERGE INTO 特性

IFreeSql 定義了 InsertOrUpdate 方法實現添加或修改的功能，利用資料庫特性：

fsql.InsertOrUpdate<T>()
  .SetSource(list) //需要操作的數據
  //.IfExistsDoNothing() //如果數據存在，啥事也不幹（相當於只有不存在數據時才插入）
  .ExecuteAffrows();

//或者..
var sql = fsql.Select<T2, T3>()
  .ToSql((a, b) => new
  {
    id = a.id + 1,
    name = "xxx"
  }, FieldAliasOptions.AsProperty);

fsql.InsertOrUpdate<T>()
  .SetSource(sql)
  .ExecuteAffrows();

fsql.InsertOrUpdateDict 方法可針對非實體類操作（字典類型）

題外話，是否見過這種 SQL：

insert into T
select name, code
from dual
where not exists(
   select 1 from T where code = dual.code
)

3、利用 BeginEdit 批量編輯

MERGE INTO 資料庫特性，其實性能是很低的。800萬行記錄導入7000行大約7秒，各資料庫性能差不多。

BeginEdit 是 FreeSql 特色功能之一，非常實用，可它卻是少有被發掘到的功能。創意源自於醫療軟體，比如操作員編輯清單，會新增，會刪除，會修改，等操作完後再點保存。

其實我過往開發的項目也有過類似需求，每步操作都進行資料庫保存，沒什麼問題吧？讓我們看下最後統一保存應該怎麼做。

//將 list 返回給 UI 端渲染
var list = fsql.Select<T>().Where(a => a.OrderId == 100).ToList();


//統一保存

//舊數據可通過查詢，或者由 UI 端提供
List<T> oldlist = fsql.Select<T>().Where(a => a.OrderId == 100).ToList();
//新數據由 UI 端提供
List<T> newlist = ...;

var repo = fsql.GetRepository<T>();
repo.BeginEdit(oldlist); //開始進行編輯
repo.EndEdit(newlist); //對比新舊List

BeginEdit/EndEdit 只針對 oldlist 對比，而不是針對全表。在記憶體中對比計算 Insert/Update/Delete 比 MERGE INTO 性能快得多，利用該功能可以很方便的實現批量導入或更新，例如重複導入一天的數據。

應當注意當導入的數據量過大時，應該分批進行操作，而不是一次性對比全部，假設我們每批執行 1000條：

//查詢舊數據
var oldlist = fsql.Select<T>().WhereDynamic(list1000).ToList();
//使用 IN 查詢性能可能較慢，可以按時間範圍查詢，如下：
//var minTime = list1000.Select(a => a.Time).Min();
//var maxTime = list1000.Select(a => a.Time).Max();
//var oldlist = fsql.Select<T>().Where(a=> a.Time.Between(minTime, maxTime)).ToList();
//在記憶體二次過濾，還可以進一步優化將 list1000.Any 改成字典
//oldlist = oldlist.Where(a=> !list1000.Any(b => b.Id == a.Id)).ToList();

var repo = fsql.GetRepository<T>();
//被編輯的數據
repo.BeginEdit(oldlist);
//將 list1000 與 oldlist 進行對比，計算出 delete/insert/update 語句執行
repo.EndEdit(list1000);

EndEdit 最多執行 3條 SQL，從而大大提升了命令往返時間消耗。特別適合導入大批量數據，且大部分數據已經存在，或者數據未發生變更的場景。

4、MySql insert ignore into

如果只插入不存的的數據，並且使用 MySql 資料庫，可以使用以下方式：

fsql.Insert<T>().MySqlIgnoreInto().AppendData(list).NoneParameter().ExecuteAffrows();
///INSERT IGNORE INTO `T`(...)
//VALUES(...),(...),(...)

🌌 場景四：跨伺服器從A表導數據到B表

與場景一類似，跨伺服器需要定義多個 IFreeSql 對象，假設 A表所在伺服器訪問對象是 fsql1，B表使用 fsql2

1、分段插入

fsql2.Select<B>()
    .Where(b => b.Time > DateTime.Parse("2022-08-01"))
    .ToChunk(b => new A { field1 = b.Name, field2 = b.Code }, 1000, cb => {
        fsql1.Insert(cb.Object).NoneParameter().ExecuteAffrows();
        //如果資料庫支援 BulkCopy 可以調用對應的 API 方法，如 SqlServer：
        //fsql1.Insert(cb.Object).ExecuteSqlBulkCopy();

        //這裡也可以使用 BeginEdit 進行批量編輯功能，解決數據重複問題
    });

2、分頁插入

利用分頁多次讀取，分頁可能造成新舊數據問題，盡量設置好分頁排序並記錄好偏移量，確保重複問題。（不推薦）

var pageNumber = 1;
while (true)
{
    var list = fsql2.Select<B>()
        .Where(b => b.Time > DateTime.Parse("2022-08-01"))
        .Page(pageNumber++, 1000)
        .OrderBy(b => b.Time)
        .ToList(b => new A { field1 = b.Name, field2 = b.Code }, 1000);
    if (list.Count == 0) break;

    fsql1.Insert(cb.Object).NoneParameter().ExecuteAffrows();
    //如果資料庫支援 BulkCopy 可以調用對應的 API 方法，如 SqlServer：
    //fsql1.Insert(cb.Object).ExecuteSqlBulkCopy();

    //這裡也可以使用 BeginEdit 進行批量編輯功能，解決數據重複問題

    //停頓5秒後再插入，這個值可以根據需要自己調
    Thread.CurrentThread.Join(TimeSpan.FromSeconds(5));
}

⛳ 結束語

FreeSql 的功能強大，以及穩定性，我不想吹牛，也不喜歡吹牛，如果大家有什麼好的想法可以一起討論，畢竟我們只能遇到有限的場景，還有很多我不知道的場景需求不是嗎？

希望這篇文章能幫助大家輕鬆理解並熟練掌握它，快速解決工作中遇到的數據導入問題，為企業的項目研發貢獻力量。

開源地址：//github.com/dotnetcore/FreeSql

作者是什麼人？

作者是一個入行 18年的老批，他目前寫的.net 開源項目有：

需要的請拿走，這些都是最近幾年的開源作品，以前更早寫的就不發了。