EFCore之SQL擴充套件元件BeetleX.EFCore.Extension

​        EFCore是.NETCore團隊開發的一個ORM元件，但這個元件在執行傳統SQL的時候並不方便，因此BeetleX.EFCore.Extension的設計目的是讓EFCore執行傳統SQL更簡單方便。

引用

在使用元件之前需要引用它，可以通過以下地址獲取最新版本:https://www.nuget.org/packages/BeetleX.EFCore.Extension/

使用

引用元件後就可以使用，元件的操作操作都是針對EFCore的DBContext物件來進行資料庫操作。

SQL sql = "select * from employees";
var employees = sql.List<Employee, NorthwindContext>();

元件提供SQL物件用來操作傳統的SQL語句，以上程式碼是在NorthwindContext資料庫上執行查詢語句並返回相關資訊列表。

sql = "select * from customers where country=@country";
sql += ("@country", "UK");
var customers = sql.List<Customer, NorthwindContext>();

以上是針對引數化的SQL處理，在操作上DBContext可以通過泛參傳入或通過例項以變數的引數傳入,通過泛參傳入的好處是不用例項化DBContext。

批量更新

在EFCore更新某個條件的欄位資訊操作起來比較麻煩，所以元件也擴充套件出相關方法來解決。

    using (NorthwindContext db = new NorthwindContext())
    {
        db.Customers.Where(c => c.CustomerID == "ken")
            .Update(c => new Customer { City="GZ" });
    }

以上操作是把國家是UK的所有記錄Region改成uk

批量刪除

同樣EFCore在批條件刪除上也不怎麼方便，元件同樣也在DBSet的基礎上擴充套件了Delete批刪除方法.

        using (NorthwindContext db = new NorthwindContext())
        {
            db.Customers.Where(c => c.CustomerID == "ken")
                .Delete();
        }

以上是刪除國家是UK的所有記錄.

Select物件

 Select物件是針對單個表的個性查詢需求制定的，它可以定義查詢不同的定段和條件來返回到指定的物件中。

class CustomerName
{
    public string CustomerID { get; set; }
    public string CompanyName { get; set; }
}
[Fact]
​
Select<Customer> select = new Select<Customer>("CustomerID", "CompanyName");
select &= c => c.Country == "UK";
var items = select.List<CustomerName, NorthwindContext>();

以上是針對客戶資訊查詢一些欄位並返回到其他結構的物件列表中。

SQL物件

為了讓字元的Sql語句處理更方便，所以組針對Sql語句封了一個物件。該物件主要用於執行Sql語句並返回結果，它的主要作用是簡單化DbCommand的處理。在這基礎上提供引數化處理，查詢支援返回固定型別和動態型別，支援同步和非同步操作等。

定義物件

該物件支援直接從String轉義,所以在定義的時候可以直接指向一個Sql的字串。

SQL sql = "select * from customers";

以上是定義一個客戶查詢的SQL物件，定義完成後就可以進行相關查詢操作，它提供了Execute,ExecuteScalar,ListFirst和List方法，分別是返回符合查詢的第一個物件和返回符查詢條件某個區中的物件;以上兩個方法都支援非同步ExecuteAsync,ExecuteScalarAsync,ListFirstAsync和ListAsync,並支援多個過載版本可操作。

ListFirst

public T ListFirst<T, DB>() where DB : DbContext, new()
public T ListFirst<T>(DbContext db) where T : new()
public Task<T> ListFirstAsync<T, DB>() where DB : DbContext, new()
    where T : new()
public async Task<T> ListFirstAsync<T>(DbContext db) where T : new()    

T可以是任意物件型別，元件會自動做欄位和屬性匹配；DbContext支援兩種方式提供，一種基於泛參在這情況下對應DbContext必須提供預設構建函式；別一種則直接通引數傳入對應的例項物件。

List

public IList<T> List<T, DB>(Region region = null) where T : new()
        where DB : DbContext, new()
public IList<T> List<T>(DbContext db, Region region = null) 
        where T : new()
public void List(DbContext db, Region region, Action<IDataReader> handler)            
public async Task<IList<T>> ListAsync<T, DB>(Region region = null) 
        where T : new()
        where DB : DbContext, new()
public async Task<IList<T>> ListAsync<T>(DbContext db, Region region = null) 
        where T : new()
public async Task ListAsync(DbContext db, Region region, Action<IDataReader> handler)

T可以是任意物件型別，元件會自動做欄位和屬性匹配；DbContext支援兩種方式提供，一種基於泛參在這情況下對應DbContext必須提供預設構建函式；別一種則直接通引數傳入對應的例項物件。region是預設引數用於指定載入那個區間的資料，預設不指定的情況下元件只載入100條資料。

SQL sql = "select * from customers";
var customers = sql.List<Customer, NorthwindContext>();
customers = await sql.ListAsync<Customer, NorthwindContext>();

動態型別

有很多時候只查詢個別欄位，但又不可以定義相關結構物件的情況下可以使用動態型別傳入.

SQL sql = "select CustomerID,CompanyName from customers";
var items=sql.List<ExpandoObject, NorthwindContext>();
foreach(dynamic item in items)
{
    Console.WriteLine(item.CustomerID);
}

可以通過ExpandoObject作為動態型別物件傳入，後繼通過dynamic定義操作即可.

分頁獲取

雖然在查詢過程中提供了Region引數用於獲取某個區間的資料，但在分頁應用中還是需要統計總數和頁數的處理，為了簡化這個操作元件封了DBRegionData<T>物件來簡化這操作.

SQL sql = "select * from customers";
DBRegionData<Customer> region = new DBRegionData<Customer>(0, 20);
await region.ExecuteAsync<NorthwindContext>(sql);
foreach (var item in region.Items)
{
    Console.WriteLine(item.CompanyName);
}

以上示例是按20條來分頁，獲取第一頁的資料。DBRegionData<T>提供了Count,Pages,Index和Items等屬性可提供訪問。

拼裝和引數化

在編寫Sql語句的時候往往都不能一條寫完，很多時候針對不同的條件來組裝語句；SQL物件重寫了運算子直接支援和String相加的處理。

SQL sql = "select * from customers where 1=1";
sql += " and country=@country";
sql += ("@country", "UK");
Console.WriteLine(sql.Count<NorthwindContext>());

 SQL支援和String直接相加，引數變數也是通過加的方式來新增，只是對應引數必須以ValueTask<string,object>的方式編寫。

表示式條件

對於直接用String來拼接Sql語句容易出錯，那可以採用物件提供的表示式來進行條件增加處理；這種處理方式的好處是不容易寫錯欄位名稱和引數化的處理。

SQL sql = "select * from orders where 1=1";
sql.And().Where<Order>((o) => o.EmployeeID == 3 && o.CustomerID == "henry");

物件提供了Where方法來編寫條件表示式，該表示式支援多型別傳入處理。

SQL sql = "select * from orders where 1=1";
sql.And().Where<Order>((o) => o.EmployeeID == 3 && o.CustomerID == "henry");
sql.OrderBy<Order>(o => o.OrderDate.ASC());

同樣排序方式也可以通過表示式的方式來新增.

轉義物件

DBObjectList<T>,DBValueList<T>DBExecute<T>和DBExecute都支援轉義操作，對應的結構是ValueTuple<DbContext, SQL>.在轉義過程中元件可支援執行處理。

using (NorthwindContext db = new NorthwindContext())
{
    SQL sql = "select * from customers";
    DBObjectList<Customer> customers = (db, sql);
​
    sql = "select CustomerID,CompanyName from customers";
    DBObjectList<ExpandoObject> names = (db, sql);
​
    sql = "select count(*) from customers";
    DBValueList<long> count = (db, sql);
}

事務

SQL物件無須針對事務做任何設定，它的事務開啟主要是由對應的DbContext物件所決定的。

執行鏈跟蹤

在新版中新增了執行跟蹤鏈，可以進一步檢視元件執行SQL的情況。 

using (CodeTrackFactory.TrackReport("AutoExecute", CodeTrackLevel.Bussiness, null, "EFCore", "BeetleX"))
{
    using (NorthwindContext db = new NorthwindContext())
    {
        DBValueList<string> values = (db, "select customerid from customers");
        DBObjectList<CustomerName> items = (db, "select CustomerID,CompanyName from customers");
        DBExecute<string> id = (db, "select CompanyName from customers where CustomerID='ALFKI'");
        var item = db.Customers.Where(c => c.CustomerID == "AROUT").FirstOrDefault();
        item.Region = "GuangZhou";
        db.SaveChanges();
    }
}
Console.WriteLine(CodeTrackFactory.Activity?.GetReport());