Dapper 一直以來都是我使用 ORM 的選擇之一,現在又多了一個選項那就是 'linq2db',他比 Dapper 多了更多的 CUD 的強型別操作,R 查詢用的是 LINQ,但又沒有 EF 那麼完整,比如追蹤的功能、產生DB,所以有人說他介於 Dapper 和 EF 之間。
linq2db 除了可以支援 LINQ,也可以像 Dapper 一樣用 Queery 、Execute,令我驚訝的是他的部分更新比 EF 更優雅,還支持 LINQPad,這不推真的不行啊
開發環境
- VS 2017 15.9.5
- .NET Framework 4.7.2
- linq2db 2.6.4
實作步驟
產生 POCO Class
- Install-Package linq2db.SqlServer
- Install-Package linq2db
安裝後會有 LinqToDB.Templates 資料夾,這是用來產生 linq2db 的 POCO,目前 linq2db 只能透過 T4 來產生 POCO。
複製 CopyMe.SqlServer.tt.txt 到 EntityModel 資料夾,修正檔名為 CopyMe.SqlServer.tt,修改 Namespace,連線字串給他 Server 的位置、DB的位置
T4 並沒有去讀 app.config / web.config
CopyMe.SqlServer.tt.txt 的步驟寫的很清楚,照做就可以
其他DB,請參考 https://github.com/linq2db/examples
存檔,按下OK
順利的話就會長出東西
在 App.Config / Web.Config 加上連線字串,LabEmployee2DB 會用的到
<connectionStrings>
<add name="LabDbContext"
connectionString="data source=(localdb)\mssqllocaldb;initial catalog=LabEmployee2;integrated security=True;MultipleActiveResultSets=True;App=EntityFramework"
providerName="System.Data.SqlClient" />
</connectionStrings>
開始寫扣
連線字串加解密
Linq2db 的連線字串是透過,以下方法
DataConnection.GetConnectionString:由組態設定取得連線字串
DataConnection.SetConnectionString:設定連線字串
以上兩個方法都依賴 connection string name
代碼
var dbConnectString = DataConnection.GetConnectionString(ConnectionName);
//解密
dbConnectString = this.Validator.Decrypt(dbConnectString);
DataConnection.SetConnectionString(ConnectionName, dbConnectString);
組態設定
<connectionStrings>
<add name="LabDbContext"
connectionString="yn20dc9lt/9AP5PV56SGd44jLYvT+FmY7nMr5/PNiUTvYgm9n10uJqdwwdokSWHP/Hk9B3wvIILvTFTPMbE8eXaBHlnXM9/hlN8ziMLUMOehh0oi/BqcCYuR48D+D6NVP2ulZlfhrOq04vmXdLgr7lmU+8ihXiWFWzOj8xWVjZgEKd0vdsyaUkPr9dY/G9rJ"
providerName="System.Data.SqlClient" />
</connectionStrings>
更新一筆
跟 EF 的語法一樣優雅
[TestMethod]
public void 更新一筆()
{
var toDb = Insert();
using (var db = new LabEmployee2DB(ConnectionName))
{
var updateDb = new Employee
{
Id = toDb.Id,
Name = "小章",
Age = 19
};
var count = db.Update(updateDb);
Assert.IsTrue(count == 1);
}
}
渲染出來的 SQL
exec sp_executesql N'UPDATE
[t1]
SET
[t1].[Name] = @Name,
[t1].[Age] = @Age,
[t1].[Remark] = @Remark
FROM
[dbo].[Employee] [t1]
WHERE
[t1].[Id] = @Id
',N'@Name nvarchar(4000),@Age int,@Remark nvarchar(4000),@Id uniqueidentifier',@Name=N'小章',@Age=19,@Remark=NULL,@Id='C3A706D6-D567-4A28-B2ED-0C3F986AF189'
部分更新
不得不說,這比 EF 更容易閱讀、簡潔
[TestMethod]
public void 部分更新()
{
var toDb = Insert();
using (var db = new LabEmployee2DB(ConnectionName))
{
var selector = db.Employees
.Where(p => p.Id == toDb.Id)
;
var updateable = selector.Set(p => p.Name, "yao");
var count = updateable.Update();
Assert.IsTrue(count == 1);
}
}
渲染出來的 SQL
exec sp_executesql N'UPDATE
[p]
SET
[p].[Name] = N''yao''
FROM
[dbo].[Employee] [p]
WHERE
[p].[Id] = @Id
',N'@Id uniqueidentifier',@Id='D6C374E4-1778-4ED3-B7E9-B3D5A2F4D627'
新增一筆
[TestMethod]
public void 新增一筆()
{
using (var db = new LabEmployee2DB(ConnectionName))
{
var count = db.Insert(new Employee {Id = Guid.NewGuid(), Name = "小章", Age = 18});
Assert.IsTrue(count == 1);
}
}
渲染出來的 SQL
exec sp_executesql N'INSERT INTO [dbo].[Employee]
(
[Id],
[Name],
[Age],
[Remark]
)
VALUES
(
@Id,
@Name,
@Age,
@Remark
)
',N'@Id uniqueidentifier,@Name nvarchar(4000),@Age int,@Remark nvarchar(4000)',@Id='E6FEF120-3EA0-4CCB-BB8B-7B70D51B1383',@Name=N'小章',@Age=18,@Remark=NULL
新增大量資料
內建這個功能,這實在是令人太感動了
[TestMethod]
public void 新增大量資料()
{
List<Employee> employees = new List<Employee>();
for (int i = 0; i < 10000; i++)
{
employees.Add(new Employee
{
Id = Guid.NewGuid(),
Name = Name.FullName(),
Age = RandomNumber.Next(1, 120)
});
}
using (var db = new LabEmployee2DB(ConnectionName))
{
db.BulkCopy(employees);
}
}
渲染出來的 SQL
insert bulk [dbo].[Employee] ([Id] UniqueIdentifier, [Name] NVarChar(50) COLLATE SQL_Latin1_General_CP1_CI_AS, [Age] Int, [Remark] NVarChar(50) COLLATE SQL_Latin1_General_CP1_CI_AS)
查詢
在 Select 區段瀏覽 Employee,會根據 FK 定義,自動渲染出 InnerJoin
Employee 是 Master,Identity 是 Detail,兩者關係是一對一
[TestMethod]
public void InnerJoin查詢()
{
using (var db = new LabEmployee2DB(ConnectionName))
{
var selector = db.Identities
.Where(p => p.SequenceId > 0)
.Select(p => new EmployeeViewModel
{
Id = p.Employee.Id,
Name = p.Employee.Name,
Age = p.Employee.Age,
SequenceId = p.Employee.SequenceId,
Account = p.Account,
Password = p.Password
});
var count = selector.Count();
var result = selector.OrderBy(p => p.SequenceId).ToList();
Assert.IsTrue(result.Count > 1);
}
}
這有兩段 SQL
SELECT
Count(*)
FROM
[dbo].[Identity] [p]
WHERE
[p].[SequenceId] > 0
SELECT
[a_Employee].[Id],
[a_Employee].[Name],
[a_Employee].[Age],
[a_Employee].[SequenceId],
[p].[Account],
[p].[Password]
FROM
[dbo].[Identity] [p]
INNER JOIN [dbo].[Employee] [a_Employee] ON [p].[Employee_Id] = [a_Employee].[Id]
WHERE
[p].[SequenceId] > 0
ORDER BY
[a_Employee].[SequenceId]
預存
多參數對應
[TestMethod]
public void 呼叫預存()
{
using (var db = new LabEmployee2DB(ConnectionName))
{
var count = db.InsertOrUpdateEmployee(Guid.NewGuid(), "yao", 19, "Remark");
Assert.IsTrue(count == 1);
}
}
SP 長這樣
CREATE PROCEDURE dbo.InsertOrUpdateEmployee
@Id uniqueidentifier,
@Name nvarchar(50),
@Age int,
@Remark nvarchar(50) AS
BEGIN
IF EXISTS
(SELECT * FROM dbo.Employee WHERE Id = @Id)
UPDATE dbo.Employee SET
Name = @Name,
Age = @Age,
Remark = @Remark
WHERE Id = @Id
ELSE
INSERT INTO dbo.Employee (
Id,
Name,
Age,
Remark)
VALUES(
@Id,
@Name,
@Age,
@Remark)
END
Table Type 參數對應,這裡他把 Table Type 對應成 DataTable
[TestMethod]
public void 呼叫預存2()
{
using (var db = new LabEmployee2DB(ConnectionName))
{
var toDb = new DataTable();
toDb.Columns.Add("Id", typeof(Guid));
toDb.Columns.Add("Name", typeof(string));
toDb.Columns.Add("Age", typeof(int));
toDb.Columns.Add("Remark", typeof(string));
var row = toDb.NewRow();
row["Id"] = Guid.NewGuid();
row["Name"] = "yao";
row["Age"] = 19;
row["Remark"] = "Remark";
toDb.Rows.Add(row);
var count = db.InsertOrUpdateEmployee2(toDb);
Assert.IsTrue(count == 1);
}
SP 長這樣
CREATE PROCEDURE dbo.InsertOrUpdateEmployee2
@EmployeeType InsertOrUpdateEmployeeType READONLY
AS
BEGIN
INSERT INTO employee
SELECT * FROM @EmployeeType
END
Table Type
CREATE TYPE [dbo].[InsertOrUpdateEmployeeType] AS TABLE (
[Id] UNIQUEIDENTIFIER NOT NULL,
[Name] NVARCHAR (50) NULL,
[Age] INT NULL,
--[SequenceId] BIGINT IDENTITY (1, 1) NOT NULL,
[Remark] NVARCHAR (50) NULL
);
GO
整合 LINQPad
連我常用的工具都整合了,按照連結上的說明照做就可以了
https://github.com/linq2db/linq2db.LINQPad
完成結果如下
參考
官網
https://github.com/linq2db/linq2db
更多的 join
https://linq2db.github.io/articles/sql/Join-Operators.html
CRUD
http://blog.linq2db.com/2015/05/linq-crud-operations.html?view=sidebar
專案位置
https://github.com/yaochangyu/sample.dotblog/tree/master/ORM/Linq2Db/Lab.Linq2Db
裡面有一個資料庫專案,你可以發行它,試著體驗 linq2db
若有謬誤,煩請告知,新手發帖請多包涵
Microsoft MVP Award 2010~2017 C# 第四季
Microsoft MVP Award 2018~2022 .NET