[DotnetCore]ORM系列-Chloe:Interceptor篇

前情提要

筆者前篇介紹Select相關的method[DotnetCore]ORM系列-Chloe:Select篇，這次要來寫interceptor，觀察看看那些select method對應的sql語法，才會知道Chloe都幫我們轉成什麼語法，才不會誤用造成錯誤，筆者覺得是值得投資的。

內容

Interceptor介紹

筆者這邊稍微解釋一下Interceptor，它要實作對應的六個Method，主要分法是照著DBCommand下的方法

• ExecuteReader
• ExecuteNonQuery
• ExecuteScalar

針對這三個DbCommand下的這三個Method，Chloe規劃有執行前跟執行後的事件對應的Method，因此有六個Method可以實作。筆者寫的上篇都是Query，屬於ExecuteReader部份，只要撰寫

• ReaderExecuting
• ReaderExecuted

這兩個Method，其他的就先維持預設的「throw new NotImplementedException();」，另外wiki中也示範另一個重點是「DataBag」的概念，他是用key、value的方式設定，可以於不同事件method中交換資料，感覺很像mvc網站中的ViewBag、ViewData。範例中是示範ReaderExecuting中設定好startTime參數、ReaderExecuted中讀出startTime參數，與現在時間做比較，算出sql執行時間並印出來。

實作Interceptor

public class DbCommandInterceptor : IDbCommandInterceptor
{
    public void NonQueryExecuted(IDbCommand command, DbCommandInterceptionContext<int> interceptionContext)
    {
        throw new NotImplementedException();
    }

    public void NonQueryExecuting(IDbCommand command, DbCommandInterceptionContext<int> interceptionContext)
    {
        throw new NotImplementedException();
    }

    /* 执行 DbCommand.ExecuteReader() 时调用 */
    public void ReaderExecuting(IDbCommand command, DbCommandInterceptionContext<IDataReader> interceptionContext)
    {
        interceptionContext.DataBag.Add("startTime", DateTime.Now);
        Console.WriteLine(command.CommandText);
    }
    /* 执行 DbCommand.ExecuteReader() 后调用 */
    public void ReaderExecuted(IDbCommand command, DbCommandInterceptionContext<IDataReader> interceptionContext)
    {
        DateTime startTime = (DateTime)(interceptionContext.DataBag["startTime"]);
        Console.WriteLine(DateTime.Now.Subtract(startTime).TotalMilliseconds);
        if (interceptionContext.Exception == null)
            Console.WriteLine(interceptionContext.Result.FieldCount);
    }

    public void ScalarExecuted(IDbCommand command, DbCommandInterceptionContext<object> interceptionContext)
    {
        throw new NotImplementedException();
    }

    public void ScalarExecuting(IDbCommand command, DbCommandInterceptionContext<object> interceptionContext)
    {
        throw new NotImplementedException();
    }
}

註冊Interceptor

要調整一下之前註冊IDbContext的地方

services.AddScoped<ChloeORM.IDbContext>((serviceProvider) =>
{
    var dbContext = new ChloeORM.MySql.MySqlContext(
        new MySqlConnectionFactory(Configuration.GetConnectionString("ClassicModels")));
        dbContext.Session.AddInterceptor(new DbCommandInterceptor());
    return dbContext;
});

調用結果

筆者以上篇為例，貼出CustomerService，以註解的方式填寫對應的sql指令

public class CustomerService : ICustomerService
{
    private readonly IDbContext _db;

    public CustomerService(IDbContext db)
    {
        _db = db;
    }
    
    public Customers GetCustomerById(int customerNumber)
    {
        // LIMIT部份不同資料庫會有不同的宣告
        /*
        SELECT
            `Customers`.`CustomerNumber` AS `CustomerNumber`,
            `Customers`.`CustomerName` AS `CustomerName`,
            `Customers`.`ContactLastName` AS `ContactLastName`,
            `Customers`.`ContactFirstName` AS `ContactFirstName`,
            `Customers`.`Phone` AS `Phone`,
            `Customers`.`AddressLine1` AS `AddressLine1`,
            `Customers`.`AddressLine2` AS `AddressLine2`,
            `Customers`.`City` AS `City`,
            `Customers`.`State` AS `State`,
            `Customers`.`PostalCode` AS `PostalCode`,
            `Customers`.`Country` AS `Country`,
            `Customers`.`SalesRepEmployeeNumber` AS `SalesRepEmployeeNumber`,
            `Customers`.`CreditLimit` AS `CreditLimit`
        FROM
            `Customers` AS `Customers`
        WHERE
            `Customers`.`CustomerNumber` = ?P_0
        LIMIT
            0, 1
        */
        return _db.Query<Customers>()
        .FirstOrDefault(x => x.CustomerNumber == customerNumber);
    }

    public IEnumerable<Customers> GetCustomerListByLikeConditions(CustomerLikeConditions conditions)
    {
        /*
        SELECT
            `Customers`.`CustomerNumber` AS `CustomerNumber`,
            `Customers`.`CustomerName` AS `CustomerName`,
            `Customers`.`ContactLastName` AS `ContactLastName`,
            `Customers`.`ContactFirstName` AS `ContactFirstName`,
            `Customers`.`Phone` AS `Phone`,
            `Customers`.`AddressLine1` AS `AddressLine1`,
            `Customers`.`AddressLine2` AS `AddressLine2`,
            `Customers`.`City` AS `City`,
            `Customers`.`State` AS `State`,
            `Customers`.`PostalCode` AS `PostalCode`,
            `Customers`.`Country` AS `Country`,
            `Customers`.`SalesRepEmployeeNumber` AS `SalesRepEmployeeNumber`,
            `Customers`.`CreditLimit` AS `CreditLimit`
        FROM
            `Customers` AS `Customers`
        WHERE
            `Customers`.`CustomerName` LIKE CONCAT(?P_0, '%')
        */
        return _db.Query<Customers>()
        .Where(x => x.CustomerName.StartsWith(conditions.CustomerName))
        .ToList();
    }

    public IEnumerable<Customers> GetCustomersListByInConditions(CustomerInConditions conditions)
    {
        /*
        SELECT
            `Customers`.`CustomerNumber` AS `CustomerNumber`,
            `Customers`.`CustomerName` AS `CustomerName`,
            `Customers`.`ContactLastName` AS `ContactLastName`,
            `Customers`.`ContactFirstName` AS `ContactFirstName`,
            `Customers`.`Phone` AS `Phone`,
            `Customers`.`AddressLine1` AS `AddressLine1`,
            `Customers`.`AddressLine2` AS `AddressLine2`,
            `Customers`.`City` AS `City`,
            `Customers`.`State` AS `State`,
            `Customers`.`PostalCode` AS `PostalCode`,
            `Customers`.`Country` AS `Country`,
            `Customers`.`SalesRepEmployeeNumber` AS `SalesRepEmployeeNumber`,
            `Customers`.`CreditLimit` AS `CreditLimit`
        FROM
            `Customers` AS `Customers`
        WHERE
            `Customers`.`CustomerName` IN (?P_0, ?P_1, ?P_2)
        */
        return _db.Query<Customers>()
        .Where(x => conditions.CustomerNameList.Contains(x.CustomerName))
        .ToList();
    }

    public IEnumerable<Customers> GetCustomersListByPaginationConditions(CustomerPageConditions conditions)
    {
        /*
        SELECT
            `Customers`.`CustomerNumber` AS `CustomerNumber`,
            `Customers`.`CustomerName` AS `CustomerName`,
            `Customers`.`ContactLastName` AS `ContactLastName`,
            `Customers`.`ContactFirstName` AS `ContactFirstName`,
            `Customers`.`Phone` AS `Phone`,
            `Customers`.`AddressLine1` AS `AddressLine1`,
            `Customers`.`AddressLine2` AS `AddressLine2`,
            `Customers`.`City` AS `City`,
            `Customers`.`State` AS `State`,
            `Customers`.`PostalCode` AS `PostalCode`,
            `Customers`.`Country` AS `Country`,
            `Customers`.`SalesRepEmployeeNumber` AS `SalesRepEmployeeNumber`,
            `Customers`.`CreditLimit` AS `CreditLimit`
        FROM
            `Customers` AS `Customers`
        ORDER BY
            `Customers`.`CustomerName` ASC,
            `Customers`.`CustomerNumber` ASC
        LIMIT
            0, 10
        */
        return _db.Query<Customers>()
        .OrderBy(x => x.CustomerName)
        .ThenBy(x => x.CustomerNumber)
        .TakePage(conditions.PageNumber, conditions.PageSize)
        .ToList();
    }

    public List<string> GetCustomersCountryListByDistinct()
    {
        // SELECT DISTINCT `Customers`.`Country` AS `C` FROM `Customers` AS `Customers`
        return _db.Query<Customers>()
        .Select(x => x.Country)
        .Distinct()
        .ToList();
    }

    public IEnumerable<Customers> GetCustomersListByExists(CustomerExistConditions conditions)
    {
        /*
        SELECT
            `Customers`.`CustomerNumber` AS `CustomerNumber`,
            `Customers`.`CustomerName` AS `CustomerName`,
            `Customers`.`ContactLastName` AS `ContactLastName`,
            `Customers`.`ContactFirstName` AS `ContactFirstName`,
            `Customers`.`Phone` AS `Phone`,
            `Customers`.`AddressLine1` AS `AddressLine1`,
            `Customers`.`AddressLine2` AS `AddressLine2`,
            `Customers`.`City` AS `City`,
            `Customers`.`State` AS `State`,
            `Customers`.`PostalCode` AS `PostalCode`,
            `Customers`.`Country` AS `Country`,
            `Customers`.`SalesRepEmployeeNumber` AS `SalesRepEmployeeNumber`,
            `Customers`.`CreditLimit` AS `CreditLimit`
        FROM
            `Customers` AS `Customers`
        WHERE
            Exists (
                SELECT
                    ?P_0 AS `C`
                FROM
                    `Customers` AS `Customers0`
                WHERE
                    (
                        `Customers0`.`Country` = ?P_1
                        AND `Customers0`.`CustomerNumber` = `Customers`.`CustomerNumber`
                    )
            )
        */
        return _db.Query<Customers>()
        .Where(x => _db.Query<Customers>()
                    .Where(c => c.Country == conditions.CountryName
                    && c.CustomerNumber == x.CustomerNumber)
                    .Any())
        .ToList();
    }
}

以上，筆者需要解釋的就是?P_0、?P_1等，應該就是會使用sql parameter對應的參數，其他的應該都看得懂，相較於EFcore產出的sql，Chloe產出的還算看得懂。

結論

畢竟Chloe套件本身也只是利用Extension及Expression解析，包裝成方便使用的linq形式存取對應的資料表，就像使用EFCore時因應效能上的考量，需要知道EFCore幫我們做了哪些事一樣，使用類似的ORM套件時，一開始使用，需要了解它底層幫我們做了什麼事是可以避免誤用的，筆者覺得是值得把時間投資在上面的。

---

Buy me a coffee