[創意料理] 單純對資料表簡單的 CRUD 讓 Chef.DbAccess.SqlServer 來協助我們

2020-01-20 置頂文章

2216
0
Chef.DbAccess
2024-06-01

Chef.DbAccess.SqlServer 這個套件是小弟本人開發的，在 ORM 工具盛行的現在，後端工程師能有一套像是 Entity Framework 的資料庫存取框架來協助開發工作，是件很幸福的事情，但是無奈因為環境的關係、政策的關係、~~仇恨的關係~~、...blah blah 的，無法盡如人意，在這種情況之下只好自立自強，於是這個套件就誕生了。

Chef.DbAccess.SqlServer 只有簡單陽春的 CRUD，使用上也有一些限制，是無法跟 Entity Framework 相提並論的，等級差太多，如果能使用 Entity Framework 的朋友就繼續使用 Entity Framework 就好了，文章看到這邊可以左轉出去了，但是如果還是跟我一樣需要操著 SQL 跟資料庫奮戰的朋友，說不定這個套件會對各位有點兒幫助。

適合的使用情境是這樣的，很多時候我們需要開發一些對單一資料表做簡單的 CRUD 存取，在分層的機制下，會多出不少簡短又相似的程式碼，我們會想說能不能將這些程式碼進一步做抽象化，讓它們更彈性一點，而這個套件的開發初衷就是希望能解決這方面的問題。

先說明一下我這次使用的範例，我有兩個資料表叫 tbl_member、tbl_department，其結構如下圖：

與之對應的類別如下：

[Table("tbl_member")]
public class Member
{
    [Column("No")]
    [Required]
    public int Id { get; set; }

    [StringLength(50)]
    [Required]
    public string Name { get; set; }

    [Column(TypeName = "varchar")]
    [StringLength(20)]
    public string Phone { get; set; }

    public int Age { get; set; }
    
    [Required]
    public int DepartmentId { get; set; }

    public Department Department { get; set; }

    [Required]
    public int ManagerId { get; set; }

    public Member Manager { get; set; }

    public List<Department> ManagedDepartments { get; set; }
    
    [NotMapped]
    public string Secret { get; set; }
}

[Table("tbl_member")]
public class MemberStatistics
{
    [Column("No")]
    public int Id { get; set; }

    public string Name { get; set; }

    public int Age { get; set; }

    public decimal AverageAge { get; set; }
}

[Table("tbl_department")]
public class Department
{
    [Required]
    public int Id { get; set; }

    [Required]
    public string Name { get; set; }

    public int ManagerId { get; set; }
}

如果資料表名稱跟類別名稱不一樣，可以用 TableAttribute 來標記，而欄位名稱跟屬性名稱不一樣，則是用 ColumnAttribute 來標記，另外，這個套件相依於 Dapper，所以有針對參數型別的語法做調整，如果欄位的資料型態是 varchar 的話，建議特別標記 Column 的 TypeName，而 StringLengthAttribute 則是選擇性的標記。

適用版本

Chef.DbAccess.SqlServer v3.0.3+

起手式

首先，類別建好後要為它指定連線字串，標記 [ConnectionString] 告訴套件說資料表是在哪一個資料庫內，這樣基本上就可以開始下 Query 了，既然是自己寫的，當然要弄得越方便越好。

[ConnectionString("nameOrConnectionString")]
[Table("tbl_member")]
public class Member
{
    ...
}

[ConnectionString("nameOrConnectionString")]
[Table("tbl_member")]
public class MemberStatistics
{
    ...
}

[ConnectionString("nameOrConnectionString")]
[Table("tbl_department")]
public class Department
{
    ...
}

連線字串可以直接是 SQL Server 的連線字串，也可以是連線字串的名稱，而透過連線字串名稱取得連線字串的方式，需要先操作 SqlServerDataAccessFactory.Instance.AddConnectionString() 方法，從設定檔中將連線字串加進來，這樣才會對應得到。

internal class Program
{
    private static void Main(string[] args)
    {
        var configurationRoot = new ConfigurationBuilder().SetBasePath(Path.GetDirectoryName(Assembly.GetExecutingAssembly().Location))
            .AddJsonFile("appsettings.json")
            .AddJsonFile("appsettings.Debug.json", true)
            .AddJsonFile("appsettings.Release.json", true)
            .Build();

        // 將設定檔中的連線字串加到 SqlServerDataAccessFactory
        foreach (var configurationSection in configurationRoot.GetSection("ConnectionStrings").GetChildren())
        {
            SqlServerDataAccessFactory.Instance.AddConnectionString(configurationSection.Key, configurationSection.Value);
        }
    }
}

弄好之後，直接呼叫 IDataAccessFactory.Create<T>() 就可以建立 Data Access Object。

internal class Program
{
    private static void Main(string[] args)
    {
        ...

        IDataAccessFactory dataAccessFactory = SqlServerDataAccessFactory.Instance;

        var memberDataAccess = dataAccessFactory.Create<Member>();
    }
}

[ConnectionString] 可以標記多個，也就是說一個類別可以對應不同資料庫相同結構的資料表，只是在建立 Data Access Object 時需要指定連線字串名稱。

另外，一個類別不只能對應一個資料表，如果資料庫中有多個資料表有相同的欄位，類別能夠共用，只要在 Create 的時候指定資料表名稱即可。

[ConnectionString("MemberDB")]
[ConnectionString("AnotherMemberDB")]
[Table("tbl_member")]
public class Member
{
    ...
}

internal class Program
{
    private static void Main(string[] args)
    {
        ...

        IDataAccessFactory dataAccessFactory = SqlServerDataAccessFactory.Instance;

        // 對應不同資料庫相同結構的資料表
        var memberDataAccessOnAnotherMemberDB = dataAccessFactory.Create<Member>(null, "AnotherMemberDB");

        // 對應相同結構的不同資料表
        var userDataAccessOnAnotherMemberDB = dataAccessFactory.Create<Member>("User", "AnotherMemberDB");
    }
}

建好 Data Access Object 之後就可以開始 Query 了，Query 的方法會在下面介紹，而套件中只有非同步方法，不支援同步方法，範例程式也都是使用非同步方法。

C - Create

關於新增資料的方法有 8 個如下：

public interface IDataAccess<T>
{
    ...
    Task<int> InsertAsync(T value, Expression<Func<T, bool>> nonexistence = null);
    Task<T> InsertAsync(T value, Expression<Func<T, object>> output, Expression<Func<T, bool>> nonexistence = null);
    Task<int> InsertAsync(Expression<Func<T>> setter, Expression<Func<T, bool>> nonexistence = null);
    Task<T> InsertAsync(Expression<Func<T>> setter, Expression<Func<T, object>> output, Expression<Func<T, bool>> nonexistence = null);
    Task<int> InsertAsync(IEnumerable<T> values, Expression<Func<T, bool>> nonexistence = null);
    Task<List<T>> InsertAsync(IEnumerable<T> values, Expression<Func<T, object>> output, Expression<Func<T, bool>> nonexistence = null);
    Task<int> InsertAsync(Expression<Func<T>> setterTemplate, IEnumerable<T> values, Expression<Func<T, bool>> nonexistence = null);
    Task<List<T>> InsertAsync(Expression<Func<T>> setterTemplate, IEnumerable<T> values, Expression<Func<T, object>> output, Expression<Func<T, bool>> nonexistence = null);
    ...
}

其中如果要使用 InsertAsync(T value) 及 InsertAsync(IEnumerable<T> values) 這兩個方法的話，需要到對應的類別裡面，為指定的必填欄位標上 [Required]。

有 output 參數的 InsertAsync() 方法，可以讓我們回傳指定的新增欄位，這個用在 IDENTITY 識別屬性的欄位特別好用。

使用上建議優先選擇 Chef.DbAccess.Fluent.Extention 提供的擴充方法，範例程式碼如下，說明寫在註解中。

private static async Task DemoInsert()
{
    var member = new Member { Id = 99, Name = "Kevin", Phone = "0000-000000", Age = 88 };

    var members = new List<Member>
                  {
                      new Member { Id = 98, Name = "Bob", Phone = "0000-000001", Age = 38 },
                      new Member { Id = 97, Name = "Tom", Phone = "0000-000002", Age = 85 }
                  };

    IDataAccessFactory dataAccessFactory = SqlServerDataAccessFactory.Instance;

    var memberDataAccess = dataAccessFactory.Create<Member>();

    // INSERT 一筆 Member，僅預設的必填欄位。
    await memberDataAccess.InsertAsync(member);

    // INSERT 多筆 Member，僅預設的必填欄位。
    await memberDataAccess.InsertAsync(members);

    // INSERT 一筆 Member，使用 Fluent 語法，僅 Id, Name, Phone 欄位。
    await memberDataAccess
        .Set(() => new Member { Id = 100, Name = "Pass", Phone = "0978-878787" })
        .InsertAsync();

    // INSERT 一筆 Member，使用 Fluent 語法，僅 Id, Name, Phone 欄位，回傳 Id 欄位。
    var outputMember = await memberDataAccess
                           .Set(() => new Member { Id = 100, Name = "Pass", Phone = "0978-878787" })
                           .InsertAsync(x => new { x.Id });

    // 上述語法也可以寫成下面這樣
    await memberDataAccess
        .Set(m => m.Id, 100)
        .Set(m => m.Name, "Pass")
        .Set(m => m.Phone, "0978-878787")
        .InsertAsync();

    // INSERT 多筆 Member，使用 Fluent 語法，僅 Id, Name, Age 欄位。
    await memberDataAccess.Set(() => new Member { Id = default(int), Name = default(string), Age = default(int) })
        .InsertAsync(
            new List<Member>
            {
                new Member { Id = 11, Name = "Joe", Phone = "0000-000003", Age = 23 },
                new Member { Id = 22, Name = "Steve", Phone = "0000-000004", Age = 45 }
            });

    // 上述語法也可以寫成下面這樣
    await memberDataAccess.Set(m => m.Id, default(int))
        .Set(m => m.Name, default(string))
        .Set(m => m.Age, default(int))
        .InsertAsync(
            new List<Member>
            {
                new Member { Id = 11, Name = "Joe", Phone = "0000-000003", Age = 23 },
                new Member { Id = 22, Name = "Steve", Phone = "0000-000004", Age = 45 }
            });
            
    // 當 nonexistence 參數的條件不存在時，才 INSERT 資料。
    var result = await memberDataAccess
        .Set(() => new Member { Id = 11, Name = "Joe", Phone = "0000-000003", Age = 23 })
        .InsertAsync(x => x.Id == 11);
}

R - Retrieve

關於查詢資料的方法有 30 個如下：

public interface IDataAccess<T>
{
    ..
    Task<bool> ExistsAsync(Expression<Func<T, bool>> predicate);
    Task<bool> ExistsAsync<TSecond>((Expression<Func<T, TSecond>>, Expression<Func<T, List<TSecond>>>, Expression<Func<T, TSecond, bool>>, JoinType) secondJoin, Expression<Func<T, TSecond, bool>> predicate);
    Task<bool> ExistsAsync<TSecond, TThird>((Expression<Func<T, TSecond>>, Expression<Func<T, List<TSecond>>>, Expression<Func<T, TSecond, bool>>, JoinType) secondJoin, (Expression<Func<T, TSecond, TThird>>, Expression<Func<T, TSecond, List<TThird>>>, Expression<Func<T, TSecond, TThird, bool>>, JoinType) thirdJoin, Expression<Func<T, TSecond, TThird, bool>> predicate);
    Task<bool> ExistsAsync<TSecond, TThird, TFourth>((Expression<Func<T, TSecond>>, Expression<Func<T, List<TSecond>>>, Expression<Func<T, TSecond, bool>>, JoinType) secondJoin, (Expression<Func<T, TSecond, TThird>>, Expression<Func<T, TSecond, List<TThird>>>, Expression<Func<T, TSecond, TThird, bool>>, JoinType) thirdJoin, (Expression<Func<T, TSecond, TThird, TFourth>>, Expression<Func<T, TSecond, TThird, List<TFourth>>>, Expression<Func<T, TSecond, TThird, TFourth, bool>>, JoinType) fourthJoin, Expression<Func<T, TSecond, TThird, TFourth, bool>> predicate);
    Task<bool> ExistsAsync<TSecond, TThird, TFourth, TFifth>((Expression<Func<T, TSecond>>, Expression<Func<T, List<TSecond>>>, Expression<Func<T, TSecond, bool>>, JoinType) secondJoin, (Expression<Func<T, TSecond, TThird>>, Expression<Func<T, TSecond, List<TThird>>>, Expression<Func<T, TSecond, TThird, bool>>, JoinType) thirdJoin, (Expression<Func<T, TSecond, TThird, TFourth>>, Expression<Func<T, TSecond, TThird, List<TFourth>>>, Expression<Func<T, TSecond, TThird, TFourth, bool>>, JoinType) fourthJoin, (Expression<Func<T, TSecond, TThird, TFourth, TFifth>>, Expression<Func<T, TSecond, TThird, TFourth, List<TFifth>>>, Expression<Func<T, TSecond, TThird, TFourth, TFifth, bool>>, JoinType) fifthJoin, Expression<Func<T, TSecond, TThird, TFourth, TFifth, bool>> predicate);
    Task<bool> ExistsAsync<TSecond, TThird, TFourth, TFifth, TSixth>((Expression<Func<T, TSecond>>, Expression<Func<T, List<TSecond>>>, Expression<Func<T, TSecond, bool>>, JoinType) secondJoin, (Expression<Func<T, TSecond, TThird>>, Expression<Func<T, TSecond, List<TThird>>>, Expression<Func<T, TSecond, TThird, bool>>, JoinType) thirdJoin, (Expression<Func<T, TSecond, TThird, TFourth>>, Expression<Func<T, TSecond, TThird, List<TFourth>>>, Expression<Func<T, TSecond, TThird, TFourth, bool>>, JoinType) fourthJoin, (Expression<Func<T, TSecond, TThird, TFourth, TFifth>>, Expression<Func<T, TSecond, TThird, TFourth, List<TFifth>>>, Expression<Func<T, TSecond, TThird, TFourth, TFifth, bool>>, JoinType) fifthJoin, (Expression<Func<T, TSecond, TThird, TFourth, TFifth, TSixth>>, Expression<Func<T, TSecond, TThird, TFourth, TFifth, List<TSixth>>>, Expression<Func<T, TSecond, TThird, TFourth, TFifth, TSixth, bool>>, JoinType) sixthJoin, Expression<Func<T, TSecond, TThird, TFourth, TFifth, TSixth, bool>> predicate);
    Task<bool> ExistsAsync<TSecond, TThird, TFourth, TFifth, TSixth, TSeventh>((Expression<Func<T, TSecond>>, Expression<Func<T, List<TSecond>>>, Expression<Func<T, TSecond, bool>>, JoinType) secondJoin, (Expression<Func<T, TSecond, TThird>>, Expression<Func<T, TSecond, List<TThird>>>, Expression<Func<T, TSecond, TThird, bool>>, JoinType) thirdJoin, (Expression<Func<T, TSecond, TThird, TFourth>>, Expression<Func<T, TSecond, TThird, List<TFourth>>>, Expression<Func<T, TSecond, TThird, TFourth, bool>>, JoinType) fourthJoin, (Expression<Func<T, TSecond, TThird, TFourth, TFifth>>, Expression<Func<T, TSecond, TThird, TFourth, List<TFifth>>>, Expression<Func<T, TSecond, TThird, TFourth, TFifth, bool>>, JoinType) fifthJoin, (Expression<Func<T, TSecond, TThird, TFourth, TFifth, TSixth>>, Expression<Func<T, TSecond, TThird, TFourth, TFifth, List<TSixth>>>, Expression<Func<T, TSecond, TThird, TFourth, TFifth, TSixth, bool>>, JoinType) sixthJoin, (Expression<Func<T, TSecond, TThird, TFourth, TFifth, TSixth, TSeventh>>, Expression<Func<T, TSecond, TThird, TFourth, TFifth, TSixth, List<TSeventh>>>, Expression<Func<T, TSecond, TThird, TFourth, TFifth, TSixth, TSeventh, bool>>, JoinType) seventhJoin, Expression<Func<T, TSecond, TThird, TFourth, TFifth, TSixth, TSeventh, bool>> predicate);
    Task<int> CountAsync<TSecond>((Expression<Func<T, TSecond>>, Expression<Func<T, List<TSecond>>>, Expression<Func<T, TSecond, bool>>, JoinType) secondJoin, Expression<Func<T, TSecond, bool>> predicate);
    Task<int> CountAsync<TSecond, TThird>((Expression<Func<T, TSecond>>, Expression<Func<T, List<TSecond>>>, Expression<Func<T, TSecond, bool>>, JoinType) secondJoin, (Expression<Func<T, TSecond, TThird>>, Expression<Func<T, TSecond, List<TThird>>>, Expression<Func<T, TSecond, TThird, bool>>, JoinType) thirdJoin, Expression<Func<T, TSecond, TThird, bool>> predicate);
    Task<int> CountAsync<TSecond, TThird, TFourth>((Expression<Func<T, TSecond>>, Expression<Func<T, List<TSecond>>>, Expression<Func<T, TSecond, bool>>, JoinType) secondJoin, (Expression<Func<T, TSecond, TThird>>, Expression<Func<T, TSecond, List<TThird>>>, Expression<Func<T, TSecond, TThird, bool>>, JoinType) thirdJoin, (Expression<Func<T, TSecond, TThird, TFourth>>, Expression<Func<T, TSecond, TThird, List<TFourth>>>, Expression<Func<T, TSecond, TThird, TFourth, bool>>, JoinType) fourthJoin, Expression<Func<T, TSecond, TThird, TFourth, bool>> predicate);
    Task<int> CountAsync<TSecond, TThird, TFourth, TFifth>((Expression<Func<T, TSecond>>, Expression<Func<T, List<TSecond>>>, Expression<Func<T, TSecond, bool>>, JoinType) secondJoin, (Expression<Func<T, TSecond, TThird>>, Expression<Func<T, TSecond, List<TThird>>>, Expression<Func<T, TSecond, TThird, bool>>, JoinType) thirdJoin, (Expression<Func<T, TSecond, TThird, TFourth>>, Expression<Func<T, TSecond, TThird, List<TFourth>>>, Expression<Func<T, TSecond, TThird, TFourth, bool>>, JoinType) fourthJoin, (Expression<Func<T, TSecond, TThird, TFourth, TFifth>>, Expression<Func<T, TSecond, TThird, TFourth, List<TFifth>>>, Expression<Func<T, TSecond, TThird, TFourth, TFifth, bool>>, JoinType) fifthJoin, Expression<Func<T, TSecond, TThird, TFourth, TFifth, bool>> predicate);
    Task<int> CountAsync<TSecond, TThird, TFourth, TFifth, TSixth>((Expression<Func<T, TSecond>>, Expression<Func<T, List<TSecond>>>, Expression<Func<T, TSecond, bool>>, JoinType) secondJoin, (Expression<Func<T, TSecond, TThird>>, Expression<Func<T, TSecond, List<TThird>>>, Expression<Func<T, TSecond, TThird, bool>>, JoinType) thirdJoin, (Expression<Func<T, TSecond, TThird, TFourth>>, Expression<Func<T, TSecond, TThird, List<TFourth>>>, Expression<Func<T, TSecond, TThird, TFourth, bool>>, JoinType) fourthJoin, (Expression<Func<T, TSecond, TThird, TFourth, TFifth>>, Expression<Func<T, TSecond, TThird, TFourth, List<TFifth>>>, Expression<Func<T, TSecond, TThird, TFourth, TFifth, bool>>, JoinType) fifthJoin, (Expression<Func<T, TSecond, TThird, TFourth, TFifth, TSixth>>, Expression<Func<T, TSecond, TThird, TFourth, TFifth, List<TSixth>>>, Expression<Func<T, TSecond, TThird, TFourth, TFifth, TSixth, bool>>, JoinType) sixthJoin, Expression<Func<T, TSecond, TThird, TFourth, TFifth, TSixth, bool>> predicate);
    Task<int> CountAsync<TSecond, TThird, TFourth, TFifth, TSixth, TSeventh>((Expression<Func<T, TSecond>>, Expression<Func<T, List<TSecond>>>, Expression<Func<T, TSecond, bool>>, JoinType) secondJoin, (Expression<Func<T, TSecond, TThird>>, Expression<Func<T, TSecond, List<TThird>>>, Expression<Func<T, TSecond, TThird, bool>>, JoinType) thirdJoin, (Expression<Func<T, TSecond, TThird, TFourth>>, Expression<Func<T, TSecond, TThird, List<TFourth>>>, Expression<Func<T, TSecond, TThird, TFourth, bool>>, JoinType) fourthJoin, (Expression<Func<T, TSecond, TThird, TFourth, TFifth>>, Expression<Func<T, TSecond, TThird, TFourth, List<TFifth>>>, Expression<Func<T, TSecond, TThird, TFourth, TFifth, bool>>, JoinType) fifthJoin, (Expression<Func<T, TSecond, TThird, TFourth, TFifth, TSixth>>, Expression<Func<T, TSecond, TThird, TFourth, TFifth, List<TSixth>>>, Expression<Func<T, TSecond, TThird, TFourth, TFifth, TSixth, bool>>, JoinType) sixthJoin, (Expression<Func<T, TSecond, TThird, TFourth, TFifth, TSixth, TSeventh>>, Expression<Func<T, TSecond, TThird, TFourth, TFifth, TSixth, List<TSeventh>>>, Expression<Func<T, TSecond, TThird, TFourth, TFifth, TSixth, TSeventh, bool>>, JoinType) seventhJoin, Expression<Func<T, TSecond, TThird, TFourth, TFifth, TSixth, TSeventh, bool>> predicate);
    Task<int> CountAsync(Expression<Func<T, bool>> predicate);
    Task<T> QueryOneAsync(Expression<Func<T, bool>> predicate, IEnumerable<(Expression<Func<T, object>>, Sortord)> orderings = null, Expression<Func<T, object>> selector = null, Expression<Func<T, object>> groupingColumns = null, Expression<Func<Grouping<T>, T>> groupingSelector = null, bool distinct = false, int? skipped = null, int? taken = null);
    Task<T> QueryOneAsync<TSecond>((Expression<Func<T, TSecond>>, Expression<Func<T, List<TSecond>>>, Expression<Func<T, TSecond, bool>>, JoinType) secondJoin, Expression<Func<T, TSecond, bool>> predicate, IEnumerable<(Expression<Func<T, TSecond, object>>, Sortord)> orderings = null, Expression<Func<T, TSecond, object>> selector = null, Expression<Func<T, TSecond, object>> groupingColumns = null, Expression<Func<Grouping<T, TSecond>, T>> groupingSelector = null, bool distinct = false, int? skipped = null, int? taken = null);
    Task<T> QueryOneAsync<TSecond, TThird>((Expression<Func<T, TSecond>>, Expression<Func<T, List<TSecond>>>, Expression<Func<T, TSecond, bool>>, JoinType) secondJoin, (Expression<Func<T, TSecond, TThird>>, Expression<Func<T, TSecond, List<TThird>>>, Expression<Func<T, TSecond, TThird, bool>>, JoinType) thirdJoin, Expression<Func<T, TSecond, TThird, bool>> predicate, IEnumerable<(Expression<Func<T, TSecond, TThird, object>>, Sortord)> orderings = null, Expression<Func<T, TSecond, TThird, object>> selector = null, Expression<Func<T, TSecond, TThird, object>> groupingColumns = null, Expression<Func<Grouping<T, TSecond, TThird>, T>> groupingSelector = null, bool distinct = false, int? skipped = null, int? taken = null);
    Task<T> QueryOneAsync<TSecond, TThird, TFourth>((Expression<Func<T, TSecond>>, Expression<Func<T, List<TSecond>>>, Expression<Func<T, TSecond, bool>>, JoinType) secondJoin, (Expression<Func<T, TSecond, TThird>>, Expression<Func<T, TSecond, List<TThird>>>, Expression<Func<T, TSecond, TThird, bool>>, JoinType) thirdJoin, (Expression<Func<T, TSecond, TThird, TFourth>>, Expression<Func<T, TSecond, TThird, List<TFourth>>>, Expression<Func<T, TSecond, TThird, TFourth, bool>>, JoinType) fourthJoin, Expression<Func<T, TSecond, TThird, TFourth, bool>> predicate, IEnumerable<(Expression<Func<T, TSecond, TThird, TFourth, object>>, Sortord)> orderings = null, Expression<Func<T, TSecond, TThird, TFourth, object>> selector = null, Expression<Func<T, TSecond, TThird, TFourth, object>> groupingColumns = null, Expression<Func<Grouping<T, TSecond, TThird, TFourth>, T>> groupingSelector = null, bool distinct = false, int? skipped = null, int? taken = null);
    Task<T> QueryOneAsync<TSecond, TThird, TFourth, TFifth>((Expression<Func<T, TSecond>>, Expression<Func<T, List<TSecond>>>, Expression<Func<T, TSecond, bool>>, JoinType) secondJoin, (Expression<Func<T, TSecond, TThird>>, Expression<Func<T, TSecond, List<TThird>>>, Expression<Func<T, TSecond, TThird, bool>>, JoinType) thirdJoin, (Expression<Func<T, TSecond, TThird, TFourth>>, Expression<Func<T, TSecond, TThird, List<TFourth>>>, Expression<Func<T, TSecond, TThird, TFourth, bool>>, JoinType) fourthJoin, (Expression<Func<T, TSecond, TThird, TFourth, TFifth>>, Expression<Func<T, TSecond, TThird, TFourth, List<TFifth>>>, Expression<Func<T, TSecond, TThird, TFourth, TFifth, bool>>, JoinType) fifthJoin, Expression<Func<T, TSecond, TThird, TFourth, TFifth, bool>> predicate, IEnumerable<(Expression<Func<T, TSecond, TThird, TFourth, TFifth, object>>, Sortord)> orderings = null, Expression<Func<T, TSecond, TThird, TFourth, TFifth, object>> selector = null, Expression<Func<T, TSecond, TThird, TFourth, TFifth, object>> groupingColumns = null, Expression<Func<Grouping<T, TSecond, TThird, TFourth, TFifth>, T>> groupingSelector = null, bool distinct = false, int? skipped = null, int? taken = null);
    Task<T> QueryOneAsync<TSecond, TThird, TFourth, TFifth, TSixth>((Expression<Func<T, TSecond>>, Expression<Func<T, List<TSecond>>>, Expression<Func<T, TSecond, bool>>, JoinType) secondJoin, (Expression<Func<T, TSecond, TThird>>, Expression<Func<T, TSecond, List<TThird>>>, Expression<Func<T, TSecond, TThird, bool>>, JoinType) thirdJoin, (Expression<Func<T, TSecond, TThird, TFourth>>, Expression<Func<T, TSecond, TThird, List<TFourth>>>, Expression<Func<T, TSecond, TThird, TFourth, bool>>, JoinType) fourthJoin, (Expression<Func<T, TSecond, TThird, TFourth, TFifth>>, Expression<Func<T, TSecond, TThird, TFourth, List<TFifth>>>, Expression<Func<T, TSecond, TThird, TFourth, TFifth, bool>>, JoinType) fifthJoin, (Expression<Func<T, TSecond, TThird, TFourth, TFifth, TSixth>>, Expression<Func<T, TSecond, TThird, TFourth, TFifth, List<TSixth>>>, Expression<Func<T, TSecond, TThird, TFourth, TFifth, TSixth, bool>>, JoinType) sixthJoin, Expression<Func<T, TSecond, TThird, TFourth, TFifth, TSixth, bool>> predicate, IEnumerable<(Expression<Func<T, TSecond, TThird, TFourth, TFifth, TSixth, object>>, Sortord)> orderings = null, Expression<Func<T, TSecond, TThird, TFourth, TFifth, TSixth, object>> selector = null, Expression<Func<T, TSecond, TThird, TFourth, TFifth, TSixth, object>> groupingColumns = null, Expression<Func<Grouping<T, TSecond, TThird, TFourth, TFifth, TSixth>, T>> groupingSelector = null, bool distinct = false, int? skipped = null, int? taken = null);
    Task<T> QueryOneAsync<TSecond, TThird, TFourth, TFifth, TSixth, TSeventh>((Expression<Func<T, TSecond>>, Expression<Func<T, List<TSecond>>>, Expression<Func<T, TSecond, bool>>, JoinType) secondJoin, (Expression<Func<T, TSecond, TThird>>, Expression<Func<T, TSecond, List<TThird>>>, Expression<Func<T, TSecond, TThird, bool>>, JoinType) thirdJoin, (Expression<Func<T, TSecond, TThird, TFourth>>, Expression<Func<T, TSecond, TThird, List<TFourth>>>, Expression<Func<T, TSecond, TThird, TFourth, bool>>, JoinType) fourthJoin, (Expression<Func<T, TSecond, TThird, TFourth, TFifth>>, Expression<Func<T, TSecond, TThird, TFourth, List<TFifth>>>, Expression<Func<T, TSecond, TThird, TFourth, TFifth, bool>>, JoinType) fifthJoin, (Expression<Func<T, TSecond, TThird, TFourth, TFifth, TSixth>>, Expression<Func<T, TSecond, TThird, TFourth, TFifth, List<TSixth>>>, Expression<Func<T, TSecond, TThird, TFourth, TFifth, TSixth, bool>>, JoinType) sixthJoin, (Expression<Func<T, TSecond, TThird, TFourth, TFifth, TSixth, TSeventh>>, Expression<Func<T, TSecond, TThird, TFourth, TFifth, TSixth, List<TSeventh>>>, Expression<Func<T, TSecond, TThird, TFourth, TFifth, TSixth, TSeventh, bool>>, JoinType) seventhJoin, Expression<Func<T, TSecond, TThird, TFourth, TFifth, TSixth, TSeventh, bool>> predicate, IEnumerable<(Expression<Func<T, TSecond, TThird, TFourth, TFifth, TSixth, TSeventh, object>>, Sortord)> orderings = null, Expression<Func<T, TSecond, TThird, TFourth, TFifth, TSixth, TSeventh, object>> selector = null, Expression<Func<T, TSecond, TThird, TFourth, TFifth, TSixth, TSeventh, object>> groupingColumns = null, Expression<Func<Grouping<T, TSecond, TThird, TFourth, TFifth, TSixth, TSeventh>, T>> groupingSelector = null, bool distinct = false, int? skipped = null, int? taken = null);
    Task<T> QueryOneAsync(string sql, object param);
    Task<List<T>> QueryAsync(Expression<Func<T, bool>> predicate, IEnumerable<(Expression<Func<T, object>>, Sortord)> orderings = null, Expression<Func<T, object>> selector = null, Expression<Func<T, object>> groupingColumns = null, Expression<Func<Grouping<T>, T>> groupingSelector = null, bool distinct = false, int? skipped = null, int? taken = null);
    Task<List<T>> QueryAsync<TSecond>((Expression<Func<T, TSecond>>, Expression<Func<T, List<TSecond>>>, Expression<Func<T, TSecond, bool>>, JoinType) secondJoin, Expression<Func<T, TSecond, bool>> predicate, IEnumerable<(Expression<Func<T, TSecond, object>>, Sortord)> orderings = null, Expression<Func<T, TSecond, object>> selector = null, Expression<Func<T, TSecond, object>> groupingColumns = null, Expression<Func<Grouping<T, TSecond>, T>> groupingSelector = null, bool distinct = false, int? skipped = null, int? taken = null);
    Task<List<T>> QueryAsync<TSecond, TThird>((Expression<Func<T, TSecond>>, Expression<Func<T, List<TSecond>>>, Expression<Func<T, TSecond, bool>>, JoinType) secondJoin, (Expression<Func<T, TSecond, TThird>>, Expression<Func<T, TSecond, List<TThird>>>, Expression<Func<T, TSecond, TThird, bool>>, JoinType) thirdJoin, Expression<Func<T, TSecond, TThird, bool>> predicate, IEnumerable<(Expression<Func<T, TSecond, TThird, object>>, Sortord)> orderings = null, Expression<Func<T, TSecond, TThird, object>> selector = null, Expression<Func<T, TSecond, TThird, object>> groupingColumns = null, Expression<Func<Grouping<T, TSecond, TThird>, T>> groupingSelector = null, bool distinct = false, int? skipped = null, int? taken = null);
    Task<List<T>> QueryAsync<TSecond, TThird, TFourth>((Expression<Func<T, TSecond>>, Expression<Func<T, List<TSecond>>>, Expression<Func<T, TSecond, bool>>, JoinType) secondJoin, (Expression<Func<T, TSecond, TThird>>, Expression<Func<T, TSecond, List<TThird>>>, Expression<Func<T, TSecond, TThird, bool>>, JoinType) thirdJoin, (Expression<Func<T, TSecond, TThird, TFourth>>, Expression<Func<T, TSecond, TThird, List<TFourth>>>, Expression<Func<T, TSecond, TThird, TFourth, bool>>, JoinType) fourthJoin, Expression<Func<T, TSecond, TThird, TFourth, bool>> predicate, IEnumerable<(Expression<Func<T, TSecond, TThird, TFourth, object>>, Sortord)> orderings = null, Expression<Func<T, TSecond, TThird, TFourth, object>> selector = null, Expression<Func<T, TSecond, TThird, TFourth, object>> groupingColumns = null, Expression<Func<Grouping<T, TSecond, TThird, TFourth>, T>> groupingSelector = null, bool distinct = false, int? skipped = null, int? taken = null);
    Task<List<T>> QueryAsync<TSecond, TThird, TFourth, TFifth>((Expression<Func<T, TSecond>>, Expression<Func<T, List<TSecond>>>, Expression<Func<T, TSecond, bool>>, JoinType) secondJoin, (Expression<Func<T, TSecond, TThird>>, Expression<Func<T, TSecond, List<TThird>>>, Expression<Func<T, TSecond, TThird, bool>>, JoinType) thirdJoin, (Expression<Func<T, TSecond, TThird, TFourth>>, Expression<Func<T, TSecond, TThird, List<TFourth>>>, Expression<Func<T, TSecond, TThird, TFourth, bool>>, JoinType) fourthJoin, (Expression<Func<T, TSecond, TThird, TFourth, TFifth>>, Expression<Func<T, TSecond, TThird, TFourth, List<TFifth>>>, Expression<Func<T, TSecond, TThird, TFourth, TFifth, bool>>, JoinType) fifthJoin, Expression<Func<T, TSecond, TThird, TFourth, TFifth, bool>> predicate, IEnumerable<(Expression<Func<T, TSecond, TThird, TFourth, TFifth, object>>, Sortord)> orderings = null, Expression<Func<T, TSecond, TThird, TFourth, TFifth, object>> selector = null, Expression<Func<T, TSecond, TThird, TFourth, TFifth, object>> groupingColumns = null, Expression<Func<Grouping<T, TSecond, TThird, TFourth, TFifth>, T>> groupingSelector = null, bool distinct = false, int? skipped = null, int? taken = null);
    Task<List<T>> QueryAsync<TSecond, TThird, TFourth, TFifth, TSixth>((Expression<Func<T, TSecond>>, Expression<Func<T, List<TSecond>>>, Expression<Func<T, TSecond, bool>>, JoinType) secondJoin, (Expression<Func<T, TSecond, TThird>>, Expression<Func<T, TSecond, List<TThird>>>, Expression<Func<T, TSecond, TThird, bool>>, JoinType) thirdJoin, (Expression<Func<T, TSecond, TThird, TFourth>>, Expression<Func<T, TSecond, TThird, List<TFourth>>>, Expression<Func<T, TSecond, TThird, TFourth, bool>>, JoinType) fourthJoin, (Expression<Func<T, TSecond, TThird, TFourth, TFifth>>, Expression<Func<T, TSecond, TThird, TFourth, List<TFifth>>>, Expression<Func<T, TSecond, TThird, TFourth, TFifth, bool>>, JoinType) fifthJoin, (Expression<Func<T, TSecond, TThird, TFourth, TFifth, TSixth>>, Expression<Func<T, TSecond, TThird, TFourth, TFifth, List<TSixth>>>, Expression<Func<T, TSecond, TThird, TFourth, TFifth, TSixth, bool>>, JoinType) sixthJoin, Expression<Func<T, TSecond, TThird, TFourth, TFifth, TSixth, bool>> predicate, IEnumerable<(Expression<Func<T, TSecond, TThird, TFourth, TFifth, TSixth, object>>, Sortord)> orderings = null, Expression<Func<T, TSecond, TThird, TFourth, TFifth, TSixth, object>> selector = null, Expression<Func<T, TSecond, TThird, TFourth, TFifth, TSixth, object>> groupingColumns = null, Expression<Func<Grouping<T, TSecond, TThird, TFourth, TFifth, TSixth>, T>> groupingSelector = null, bool distinct = false, int? skipped = null, int? taken = null);
    Task<List<T>> QueryAsync<TSecond, TThird, TFourth, TFifth, TSixth, TSeventh>((Expression<Func<T, TSecond>>, Expression<Func<T, List<TSecond>>>, Expression<Func<T, TSecond, bool>>, JoinType) secondJoin, (Expression<Func<T, TSecond, TThird>>, Expression<Func<T, TSecond, List<TThird>>>, Expression<Func<T, TSecond, TThird, bool>>, JoinType) thirdJoin, (Expression<Func<T, TSecond, TThird, TFourth>>, Expression<Func<T, TSecond, TThird, List<TFourth>>>, Expression<Func<T, TSecond, TThird, TFourth, bool>>, JoinType) fourthJoin, (Expression<Func<T, TSecond, TThird, TFourth, TFifth>>, Expression<Func<T, TSecond, TThird, TFourth, List<TFifth>>>, Expression<Func<T, TSecond, TThird, TFourth, TFifth, bool>>, JoinType) fifthJoin, (Expression<Func<T, TSecond, TThird, TFourth, TFifth, TSixth>>, Expression<Func<T, TSecond, TThird, TFourth, TFifth, List<TSixth>>>, Expression<Func<T, TSecond, TThird, TFourth, TFifth, TSixth, bool>>, JoinType) sixthJoin, (Expression<Func<T, TSecond, TThird, TFourth, TFifth, TSixth, TSeventh>>, Expression<Func<T, TSecond, TThird, TFourth, TFifth, TSixth, List<TSeventh>>>, Expression<Func<T, TSecond, TThird, TFourth, TFifth, TSixth, TSeventh, bool>>, JoinType) seventhJoin, Expression<Func<T, TSecond, TThird, TFourth, TFifth, TSixth, TSeventh, bool>> predicate, IEnumerable<(Expression<Func<T, TSecond, TThird, TFourth, TFifth, TSixth, TSeventh, object>>, Sortord)> orderings = null, Expression<Func<T, TSecond, TThird, TFourth, TFifth, TSixth, TSeventh, object>> selector = null, Expression<Func<T, TSecond, TThird, TFourth, TFifth, TSixth, TSeventh, object>> groupingColumns = null, Expression<Func<Grouping<T, TSecond, TThird, TFourth, TFifth, TSixth, TSeventh>, T>> groupingSelector = null, bool distinct = false, int? skipped = null, int? taken = null);
    Task<List<T>> QueryAsync(string sql, object param);
    ...
}

查詢語法目前除了必要的 SELECT ... FROM ... WHERE 之外，還支援底下幾個語法：

比較運算子：=、<>、>、<、>=、<=、LIKE、NOT LIKE、IN、NOT IN、IS NULL、IS NOT NULL
邏輯運算子：&、|
字串欄位比大小：x.Name.CompareTo("strB") [>, >=, <, <=] 0。
DISTINCT
TOP
ORDER BY
GROUP BY：GroupBy 有支援的函式為 MAX、MIN、SUM、AVG
OFFSET | FETCH
INNER JOIN、LEFT JOIN：Join 最多可以到 7 個資料表

另外這邊不鼓勵 SELECT * 語法，所以一定要 SELECT 欄位，如果要 SELECT 所有欄位可以使用 x => new { x }，還有任何加入 SQL 運算的屬性都不可以被標記 [NotMapped]，範例程式碼如下，說明寫在註解中。

private static async Task DemoQuery()
{
    IDataAccessFactory dataAccessFactory = SqlServerDataAccessFactory.Instance;

    var memberDataAccess = dataAccessFactory.Create<Member>();

    // SELECT 一筆 Id = 1 的 Member
    var member = await memberDataAccess
                     .Where(x => x.Id == 1)
                     .Select(x => new { x.Name, x.Age })
                     .QueryOneAsync();

    // SELECT 多筆 Id IN (1, 2, 3) 的 Member
    var members = await memberDataAccess
                      .Where(x => new[] { 1, 2, 3 }.Contains(x.Id))
                      .Select(x => new { x.Name, x.Age })
                      .QueryAsync();

    // SELECT 出來的結果再轉成任意類別
    var partialMembers = await memberDataAccess
                             .Where(x => new[] { 1, 2, 3 }.Contains(x.Id))
                             .Select(x => new { x.Id, x.Name, x.Age })
                             .QueryAsync(x => new { x.Id, x.Name });

    // SELECT 出來的結果再傳入 Aggregate 函式
    var totalAge = await memberDataAccess
                       .Where(x => new[] { 1, 2, 3 }.Contains(x.Id))
                       .Select(x => new { x.Id, x.Name, x.Age })
                       .QueryAsync(0, (accuAge, m) => accuAge + m.Age);

    // SELECT 多筆 Id NOT IN (1, 2, 3) 的 Member
    members = await memberDataAccess
                  .Where(x => !new[] { 1, 2, 3 }.Contains(x.Id))
                  .Select(x => new { x.Name, x.Age })
                  .QueryAsync();

    // SELECT TOP 10 筆 Age >= 30 AND Name LIKE 'J%' ORDER BY Age 的 Member
    members = await memberDataAccess
                  .Where(x => x.Age >= 30 && x.Name.StartsWith("J"))
                  .OrderBy(x => x.Age)
                  .Take(10)
                  .Select(x => new { x.Name, x.Age })
                  .QueryAsync();

    // SELECT (Id & 1) > 0 的 Member
    members = await memberDataAccess
                  .Where(x => (x.Id & 1) > 0)
                  .Select(x => new { x.Name, x.Age })
                  .QueryAsync();

    // SELECT 多筆 Age = 18 AND Name LIKE '%y' 的 Member，並傳回 Name, Age 欄位。
    members = await memberDataAccess
                  .Where(x => x.Age == 18 && x.Name.EndsWith("y"))
                  .Select(x => new { x.Name, x.Age })
                  .QueryAsync();

    // SELECT 多筆 Name LIKE '%e%' ORDER BY Id, Name DESC 的 Member
    members = await memberDataAccess
                  .Where(x => x.Name.Contains("e"))
                  .OrderBy(x => x.Id)
                  .ThenByDescending(x => x.Name)
                  .Select(x => new { x.Name, x.Age })
                  .QueryAsync();

    // 從全文檢索（Full-Text Search）SELECT 多筆 Name 含有 'Johnny' 的 Member
    members = await memberDataAccess
                  .Where(x => x.Name.Includes("Johnny"))
                  .Select(x => new { x.Name, x.Age })
                  .QueryAsync();

    // SELECT 多筆 Name NOT LIKE '%e%' ORDER BY Id, Name DESC 的 Member
    members = await memberDataAccess
                  .Where(x => !x.Name.Contains("e"))
                  .OrderBy(x => x.Id)
                  .ThenByDescending(x => x.Name)
                  .Select(x => new { x.Name, x.Age })
                  .QueryAsync();

    // 計算 Age = 20 的 Member 數量
    var memberCount = await memberDataAccess
                          .Where(x => x.Age == 20)
                          .CountAsync();

    // 計算 Age = 20 的 Member 數量（使用 Equals()）
    memberCount = await memberDataAccess
                      .Where(x => x.Age.Equals(20))
                      .CountAsync();

    // 計算 Age <> 20 的 Member 數量（使用 Equals()）
    memberCount = await memberDataAccess
                      .Where(x => !x.Age.Equals(20))
                      .CountAsync();

    // 回傳 Id = 1 的 Member 是否存在？
    var memberExists = await memberDataAccess
                           .Where(x => x.Id == 1)
                           .ExistsAsync();

    // 回傳 Name 有 "業務" 字樣的 Department 是否有 Member 存在？
    memberExists = await memberDataAccess
                       .InnerJoin(x => x.Department, (x, y) => x.DepartmentId == y.Id)
                       .Where((x, y) => y.Name.Contains("業務"))
                       .ExistsAsync();

    // 使用 And() 方法 SELECT 多筆 Age = 18 AND Name LIKE '%y' 的 Member，並傳回 Name, Age 欄位。
    members = await memberDataAccess
                  .Where(x => x.Age == 18)
                  .And(x => x.Name.EndsWith("y"))
                  .Select(x => new { x.Name, x.Age })
                  .QueryAsync();

    // 使用 Or() 方法 SELECT 多筆 Id IN (1, 2, 3) 的 Member
    members = await memberDataAccess
                  .Where(x => x.Id == 1)
                  .Or(x => x.Id == 2)
                  .Or(x => x.Id == 3)
                  .Select(x => new { x.Name, x.Age })
                  .QueryAsync();

    // 使用 Distinct() 方法 SELECT 不重複的 Member 名字
    members = await memberDataAccess
                  .Where(x => x.Id > 0)
                  .Distinct(x => new { x.Name })
                  .QueryAsync();

    // 使用 Skip() + Take() 方法跳過 20 筆取 10 筆資料，可以用做分頁查詢。
    members = await memberDataAccess
                  .OrderBy(x => x.Id)
                  .Select(x => new { x.Name, x.Age })
                  .Skip(20)
                  .Take(10)
                  .QueryAsync();

    // 使用 InnerJoin() 找出年紀大於 20 歲的 Member，以及 Member 的 Department 和 Manager。
    members = await memberDataAccess
                  .InnerJoin(x => x.Department, (x, y) => x.DepartmentId == y.Id)
                  .InnerJoin((x, y) => x.Manager, (x, y, z) => x.ManagerId == z.Id)
                  .Where((x, y, z) => x.Age > 20)
                  .Select(
                      (x, y, z) => new
                                   {
                                       x.Id,
                                       x.Name,
                                       DepartmentId = y.Id,
                                       DepartmentName = y.Name,
                                       ManagerId = z.Id,
                                       ManagerName = z.Name
                                   })
                  .QueryAsync();

    var memberStatisticsDataAccess = dataAccessFactory.Create<MemberStatistics>();

    // 使用 GroupBy 找出 Member 名字叫 Johnny 的平均年齡
    var statistics = await memberStatisticsDataAccess
                         .Where(x => x.Name == "Johnny")
                         .GroupBy(
                             x => new { x.Name },
                             g => new MemberStatistics
                                  {
                                      Name = g.Select(x => x.Name),
                                      AverageAge = g.Avg(x => x.Age)
                                  })
                         .QueryOneAsync();
}

一對多關係（One-To-Many）

如果要撈出一對多關係（One-To-Many）的結果，需要在資料類別以主索引鍵為參數，覆寫兩個方法：Equals(object obj) 及 GetHashCode()，主要是因為從資料庫回傳的原始結果是一個純二維的資料，在做關係對應的時候需要刪除重複資料（Deduplication），而且多做一件事情，就是當沒有主索引鍵的時候，必須呼叫各自的基礎方法。

假設 Member 及 Department 為一對多關係，Member 的主索引鍵為 Id 跟 Name，Department 的主索引鍵為 Id，調整後程式碼如下：

[Table("tbl_member")]
public class Member
{
    [Column("No")]
    [Required]
    public int Id { get; set; }

    [StringLength(50)]
    [Required]
    public string Name { get; set; }

    // ...
    
    public override bool Equals(object obj)
    {
        if (ReferenceEquals(null, obj)) return false;
        if (ReferenceEquals(this, obj)) return true;
        if (obj.GetType() != this.GetType()) return false;

        if (this.Id == default) return base.Equals(obj);
        if (this.Name == default) return base.Equals(obj);

        return this.Equals((MemberForBenchmark)obj);
    }

    public override int GetHashCode()
    {
        if (this.Id == default) return base.GetHashCode();
        if (this.Name == default) return base.GetHashCode();

        return HashCode.Combine(this.Id, this.Name);
    }

    protected bool Equals(MemberForBenchmark other)
    {
        return this.Id == other.Id && this.Name == other.Name;
    }
}

[Table("tbl_department")]
public class Department
{
    [Required]
    public int Id { get; set; }

    public override bool Equals(object obj)
    {
        if (ReferenceEquals(null, obj)) return false;
        if (ReferenceEquals(this, obj)) return true;
        if (obj.GetType() != this.GetType()) return false;

        if (this.Id == default) return base.Equals(obj);

        return this.Equals((DepartmentForBenchmark)obj);
    }

    public override int GetHashCode()
    {
        if (this.Id == default) return base.GetHashCode();

        return this.Id;
    }

    protected bool Equals(DepartmentForBenchmark other)
    {
        return this.Id == other.Id;
    }
}

而 Join 的語法就跟前面一樣，只是因為是一對多的關係，所以關聯的屬性改指定 List<T> 型別的集合。

private static async Task DemoOneToManyQuery()
{
    IDataAccessFactory dataAccessFactory = SqlServerDataAccessFactory.Instance;

    var memberDataAccess = dataAccessFactory.Create<Member>();

    // 使用 InnerJoin() 找出年紀大於 20 歲的 Member，以及 Member 的 Manager 和目前所掌管的所有 ManagedDepartments。
    var members = await memberDataAccess.InnerJoin(x => x.ManagedDepartments, (x, y) => x.Id == y.ManagerId)
                      .InnerJoin((x, y) => x.Manager, (x, y, z) => x.ManagerId == z.Id)
                      .Where((x, y, z) => x.Age > 20)
                      .Select(
                          (x, y, z) => new
                                       {
                                           x.Id,
                                           x.Name,
                                           DepartmentId = y.Id,
                                           DepartmentName = y.Name,
                                           ManagerId = z.Id,
                                           ManagerName = z.Name
                                       })
                      .QueryAsync();
}

運算子右邊的運算式僅支援`常數`、`變數`、`屬性`，除此之外的運算式是不支援的，這點需要特別注意。

U - Update

關於更新資料的方法有 8 個如下：

public interface IDataAccess<T>
{
    ...
    Task<int> UpdateAsync(Expression<Func<T, bool>> predicate, Expression<Func<T>> setter);
    Task<int> UpdateAsync<TSecond>((Expression<Func<T, TSecond>>, Expression<Func<T, List<TSecond>>>, Expression<Func<T, TSecond, bool>>, JoinType) secondJoin, Expression<Func<T, TSecond, bool>> predicate, Expression<Func<T>> setter);
    Task<int> UpdateAsync<TSecond, TThird>((Expression<Func<T, TSecond>>, Expression<Func<T, List<TSecond>>>, Expression<Func<T, TSecond, bool>>, JoinType) secondJoin, (Expression<Func<T, TSecond, TThird>>, Expression<Func<T, TSecond, List<TThird>>>, Expression<Func<T, TSecond, TThird, bool>>, JoinType) thirdJoin, Expression<Func<T, TSecond, TThird, bool>> predicate, Expression<Func<T>> setter);
    Task<int> UpdateAsync<TSecond, TThird, TFourth>((Expression<Func<T, TSecond>>, Expression<Func<T, List<TSecond>>>, Expression<Func<T, TSecond, bool>>, JoinType) secondJoin, (Expression<Func<T, TSecond, TThird>>, Expression<Func<T, TSecond, List<TThird>>>, Expression<Func<T, TSecond, TThird, bool>>, JoinType) thirdJoin, (Expression<Func<T, TSecond, TThird, TFourth>>, Expression<Func<T, TSecond, TThird, List<TFourth>>>, Expression<Func<T, TSecond, TThird, TFourth, bool>>, JoinType) fourthJoin, Expression<Func<T, TSecond, TThird, TFourth, bool>> predicate, Expression<Func<T>> setter);
    Task<int> UpdateAsync<TSecond, TThird, TFourth, TFifth>((Expression<Func<T, TSecond>>, Expression<Func<T, List<TSecond>>>, Expression<Func<T, TSecond, bool>>, JoinType) secondJoin, (Expression<Func<T, TSecond, TThird>>, Expression<Func<T, TSecond, List<TThird>>>, Expression<Func<T, TSecond, TThird, bool>>, JoinType) thirdJoin, (Expression<Func<T, TSecond, TThird, TFourth>>, Expression<Func<T, TSecond, TThird, List<TFourth>>>, Expression<Func<T, TSecond, TThird, TFourth, bool>>, JoinType) fourthJoin, (Expression<Func<T, TSecond, TThird, TFourth, TFifth>>, Expression<Func<T, TSecond, TThird, TFourth, List<TFifth>>>, Expression<Func<T, TSecond, TThird, TFourth, TFifth, bool>>, JoinType) fifthJoin, Expression<Func<T, TSecond, TThird, TFourth, TFifth, bool>> predicate, Expression<Func<T>> setter);
    Task<int> UpdateAsync<TSecond, TThird, TFourth, TFifth, TSixth>((Expression<Func<T, TSecond>>, Expression<Func<T, List<TSecond>>>, Expression<Func<T, TSecond, bool>>, JoinType) secondJoin, (Expression<Func<T, TSecond, TThird>>, Expression<Func<T, TSecond, List<TThird>>>, Expression<Func<T, TSecond, TThird, bool>>, JoinType) thirdJoin, (Expression<Func<T, TSecond, TThird, TFourth>>, Expression<Func<T, TSecond, TThird, List<TFourth>>>, Expression<Func<T, TSecond, TThird, TFourth, bool>>, JoinType) fourthJoin, (Expression<Func<T, TSecond, TThird, TFourth, TFifth>>, Expression<Func<T, TSecond, TThird, TFourth, List<TFifth>>>, Expression<Func<T, TSecond, TThird, TFourth, TFifth, bool>>, JoinType) fifthJoin, (Expression<Func<T, TSecond, TThird, TFourth, TFifth, TSixth>>, Expression<Func<T, TSecond, TThird, TFourth, TFifth, List<TSixth>>>, Expression<Func<T, TSecond, TThird, TFourth, TFifth, TSixth, bool>>, JoinType) sixthJoin, Expression<Func<T, TSecond, TThird, TFourth, TFifth, TSixth, bool>> predicate, Expression<Func<T>> setter);
    Task<int> UpdateAsync<TSecond, TThird, TFourth, TFifth, TSixth, TSeventh>((Expression<Func<T, TSecond>>, Expression<Func<T, List<TSecond>>>, Expression<Func<T, TSecond, bool>>, JoinType) secondJoin, (Expression<Func<T, TSecond, TThird>>, Expression<Func<T, TSecond, List<TThird>>>, Expression<Func<T, TSecond, TThird, bool>>, JoinType) thirdJoin, (Expression<Func<T, TSecond, TThird, TFourth>>, Expression<Func<T, TSecond, TThird, List<TFourth>>>, Expression<Func<T, TSecond, TThird, TFourth, bool>>, JoinType) fourthJoin, (Expression<Func<T, TSecond, TThird, TFourth, TFifth>>, Expression<Func<T, TSecond, TThird, TFourth, List<TFifth>>>, Expression<Func<T, TSecond, TThird, TFourth, TFifth, bool>>, JoinType) fifthJoin, (Expression<Func<T, TSecond, TThird, TFourth, TFifth, TSixth>>, Expression<Func<T, TSecond, TThird, TFourth, TFifth, List<TSixth>>>, Expression<Func<T, TSecond, TThird, TFourth, TFifth, TSixth, bool>>, JoinType) sixthJoin, (Expression<Func<T, TSecond, TThird, TFourth, TFifth, TSixth, TSeventh>>, Expression<Func<T, TSecond, TThird, TFourth, TFifth, TSixth, List<TSeventh>>>, Expression<Func<T, TSecond, TThird, TFourth, TFifth, TSixth, TSeventh, bool>>, JoinType) seventhJoin, Expression<Func<T, TSecond, TThird, TFourth, TFifth, TSixth, TSeventh, bool>> predicate, Expression<Func<T>> setter);
    Task<int> UpdateAsync(Expression<Func<T, bool>> predicateTemplate, Expression<Func<T>> setterTemplate, IEnumerable<T> values);
    ...
}

範例程式碼如下，說明寫在註解中。

private static async Task DemoUpdate()
{
    IDataAccessFactory dataAccessFactory = SqlServerDataAccessFactory.Instance;

    var memberDataAccess = dataAccessFactory.Create<Member>();

    // UPDATE 一筆 Id = 1 的 Member Phone 欄位
    await memberDataAccess
        .Set(() => new Member { Phone = "02-87871234" })
        .Where(x => x.Id == 1)
        .UpdateAsync();

    // 動態 UPDATE 一筆 Id = 1 的 Member Age, Phone 欄位
    await memberDataAccess
        .Set(x => x.Age, 20)
        .Set(x => x.Phone, "02-87871234")
        .Where(x => x.Id == 1)
        .UpdateAsync();

    // 動態 UPDATE Department Name 有 "業務" 字樣的 Member Age, Phone 欄位
    await memberDataAccess
        .InnerJoin(x => x.Department, (x, y) => x.DepartmentId == y.Id)
        .Set(x => x.Age, 20)
        .Set(x => x.Phone, "02-87871234")
        .Where((x, y) => y.Name.Contains("業務"))
        .UpdateAsync();

    // UPDATE 多筆 Member 的 Age, Phone 欄位
    await memberDataAccess
        .Set(() => new Member { Age = default(int), Phone = default(string) })
        .Where(x => x.Id == default(int))
        .UpdateAsync(new List<Member>
                     {
                         new Member { Id = 44, Phone = "0000-000006", Age = 63 },
                         new Member { Id = 55, Phone = "0000-000007", Age = 75 }
                     });

    // 上述語法也可以寫成下面這樣
    await memberDataAccess
        .Set(m => m.Age, default(int))
        .Set(m => m.Phone, default(string))
        .Where(x => x.Id == default(int))
        .UpdateAsync(new List<Member>
                     {
                         new Member { Id = 44, Phone = "0000-000006", Age = 63 },
                         new Member { Id = 55, Phone = "0000-000007", Age = 75 }
                     });
}

U - Upsert

Upsert 是 UpdateOrInsert 的縮寫，顧名思義，它們作用就如同它的名稱，方法有 4 個如下：

public interface IDataAccess<T>
{
    ...
    Task<int> UpsertAsync(Expression<Func<T, bool>> predicate, Expression<Func<T>> setter);
    Task<T> UpsertAsync(Expression<Func<T, bool>> predicate, Expression<Func<T>> setter, Expression<Func<T, object>> output);
    Task<int> UpsertAsync(Expression<Func<T, bool>> predicateTemplate, Expression<Func<T>> setterTemplate, IEnumerable<T> values);
    Task<List<T>> UpsertAsync(Expression<Func<T, bool>> predicateTemplate, Expression<Func<T>> setterTemplate, IEnumerable<T> values, Expression<Func<T, object>> output);
    ...
}

它也有類似於 InsertAsync() 的 output 參數，一樣會回傳指定的資料欄位，範例程式碼如下，說明寫在註解中。

private static async Task DemoUpsert()
{
    IDataAccessFactory dataAccessFactory = SqlServerDataAccessFactory.Instance;

    var memberDataAccess = dataAccessFactory.Create<Member>();

    // UPDATE or INSERT 一筆 Id = 1 的 Member，包含 Name, Phone 欄位
    await memberDataAccess
        .Set(() => new Member { Name = "Black", Phone = "02-56567788" })
        .Where(x => x.Id == 1).UpsertAsync();

    // UPDATE or INSERT 一筆 Id = 1 的 Member，包含 Name, Phone 欄位，回傳 Id 欄位。
    var outputMember = await memberDataAccess
                           .Set(() => new Member { Name = "Black", Phone = "02-56567788" })
                           .Where(x => x.Id == 1)
                           .UpsertAsync(x => new { x.Id });

    // 上述語法也可以寫成下面這樣
    await memberDataAccess
        .Set(m => m.Name, "Black")
        .Set(m => m.Phone, "02-56567788")
        .Where(x => x.Id == 1).UpsertAsync();

    // UPDATE or INSERT 多筆 Member，包含 Age, Phone 欄位
    await memberDataAccess.Set(() => new Member { Name = default(string), Age = default(int), Phone = default(string) })
        .Where(x => x.Id == default(int))
        .UpdateAsync(
            new List<Member>
            {
                new Member { Id = 77, Phone = "0000-000066", Age = 36 },
                new Member { Id = 88, Phone = "0000-000077", Age = 57 }
            });

    // 上述語法也可以寫成下面這樣
    await memberDataAccess.Set(m => m.Name, default(string))
        .Set(m => m.Age, default(int))
        .Set(m => m.Phone, default(string))
        .Where(x => x.Id == default(int))
        .UpdateAsync(
            new List<Member>
            {
                new Member { Id = 77, Phone = "0000-000066", Age = 36 },
                new Member { Id = 88, Phone = "0000-000077", Age = 57 }
            });
}

D - Delete

關於刪除資料的方法目前只有 7 個如下：

public interface IDataAccess<T>
{
    ...
    Task<int> DeleteAsync(Expression<Func<T, bool>> predicate);
    Task<int> DeleteAsync<TSecond>((Expression<Func<T, TSecond>>, Expression<Func<T, List<TSecond>>>, Expression<Func<T, TSecond, bool>>, JoinType) secondJoin, Expression<Func<T, TSecond, bool>> predicate);
    Task<int> DeleteAsync<TSecond, TThird>((Expression<Func<T, TSecond>>, Expression<Func<T, List<TSecond>>>, Expression<Func<T, TSecond, bool>>, JoinType) secondJoin, (Expression<Func<T, TSecond, TThird>>, Expression<Func<T, TSecond, List<TThird>>>, Expression<Func<T, TSecond, TThird, bool>>, JoinType) thirdJoin, Expression<Func<T, TSecond, TThird, bool>> predicate);
    Task<int> DeleteAsync<TSecond, TThird, TFourth>((Expression<Func<T, TSecond>>, Expression<Func<T, List<TSecond>>>, Expression<Func<T, TSecond, bool>>, JoinType) secondJoin, (Expression<Func<T, TSecond, TThird>>, Expression<Func<T, TSecond, List<TThird>>>, Expression<Func<T, TSecond, TThird, bool>>, JoinType) thirdJoin, (Expression<Func<T, TSecond, TThird, TFourth>>, Expression<Func<T, TSecond, TThird, List<TFourth>>>, Expression<Func<T, TSecond, TThird, TFourth, bool>>, JoinType) fourthJoin, Expression<Func<T, TSecond, TThird, TFourth, bool>> predicate);
    Task<int> DeleteAsync<TSecond, TThird, TFourth, TFifth>((Expression<Func<T, TSecond>>, Expression<Func<T, List<TSecond>>>, Expression<Func<T, TSecond, bool>>, JoinType) secondJoin, (Expression<Func<T, TSecond, TThird>>, Expression<Func<T, TSecond, List<TThird>>>, Expression<Func<T, TSecond, TThird, bool>>, JoinType) thirdJoin, (Expression<Func<T, TSecond, TThird, TFourth>>, Expression<Func<T, TSecond, TThird, List<TFourth>>>, Expression<Func<T, TSecond, TThird, TFourth, bool>>, JoinType) fourthJoin, (Expression<Func<T, TSecond, TThird, TFourth, TFifth>>, Expression<Func<T, TSecond, TThird, TFourth, List<TFifth>>>, Expression<Func<T, TSecond, TThird, TFourth, TFifth, bool>>, JoinType) fifthJoin, Expression<Func<T, TSecond, TThird, TFourth, TFifth, bool>> predicate);
    Task<int> DeleteAsync<TSecond, TThird, TFourth, TFifth, TSixth>((Expression<Func<T, TSecond>>, Expression<Func<T, List<TSecond>>>, Expression<Func<T, TSecond, bool>>, JoinType) secondJoin, (Expression<Func<T, TSecond, TThird>>, Expression<Func<T, TSecond, List<TThird>>>, Expression<Func<T, TSecond, TThird, bool>>, JoinType) thirdJoin, (Expression<Func<T, TSecond, TThird, TFourth>>, Expression<Func<T, TSecond, TThird, List<TFourth>>>, Expression<Func<T, TSecond, TThird, TFourth, bool>>, JoinType) fourthJoin, (Expression<Func<T, TSecond, TThird, TFourth, TFifth>>, Expression<Func<T, TSecond, TThird, TFourth, List<TFifth>>>, Expression<Func<T, TSecond, TThird, TFourth, TFifth, bool>>, JoinType) fifthJoin, (Expression<Func<T, TSecond, TThird, TFourth, TFifth, TSixth>>, Expression<Func<T, TSecond, TThird, TFourth, TFifth, List<TSixth>>>, Expression<Func<T, TSecond, TThird, TFourth, TFifth, TSixth, bool>>, JoinType) sixthJoin, Expression<Func<T, TSecond, TThird, TFourth, TFifth, TSixth, bool>> predicate);
    Task<int> DeleteAsync<TSecond, TThird, TFourth, TFifth, TSixth, TSeventh>((Expression<Func<T, TSecond>>, Expression<Func<T, List<TSecond>>>, Expression<Func<T, TSecond, bool>>, JoinType) secondJoin, (Expression<Func<T, TSecond, TThird>>, Expression<Func<T, TSecond, List<TThird>>>, Expression<Func<T, TSecond, TThird, bool>>, JoinType) thirdJoin, (Expression<Func<T, TSecond, TThird, TFourth>>, Expression<Func<T, TSecond, TThird, List<TFourth>>>, Expression<Func<T, TSecond, TThird, TFourth, bool>>, JoinType) fourthJoin, (Expression<Func<T, TSecond, TThird, TFourth, TFifth>>, Expression<Func<T, TSecond, TThird, TFourth, List<TFifth>>>, Expression<Func<T, TSecond, TThird, TFourth, TFifth, bool>>, JoinType) fifthJoin, (Expression<Func<T, TSecond, TThird, TFourth, TFifth, TSixth>>, Expression<Func<T, TSecond, TThird, TFourth, TFifth, List<TSixth>>>, Expression<Func<T, TSecond, TThird, TFourth, TFifth, TSixth, bool>>, JoinType) sixthJoin, (Expression<Func<T, TSecond, TThird, TFourth, TFifth, TSixth, TSeventh>>, Expression<Func<T, TSecond, TThird, TFourth, TFifth, TSixth, List<TSeventh>>>, Expression<Func<T, TSecond, TThird, TFourth, TFifth, TSixth, TSeventh, bool>>, JoinType) seventhJoin, Expression<Func<T, TSecond, TThird, TFourth, TFifth, TSixth, TSeventh, bool>> predicate);
    ...
}

範例程式碼如下，說明寫在註解中。

private static async Task DemoDelete()
{
    IDataAccessFactory dataAccessFactory = SqlServerDataAccessFactory.Instance;

    var memberDataAccess = dataAccessFactory.Create<Member>();

    // DELETE Name LIKE '%A%' 的 Member
    await memberDataAccess
        .Where(x => x.Name.Contains("A"))
        .DeleteAsync();

    // 刪除 Name 有 'A' 且 Department Name 有 '業務' 字樣的 Member
    await memberDataAccess
        .InnerJoin(x => x.Department, (x, y) => x.DepartmentId == y.Id)
        .Where((x, y) => x.Name.Contains("A") && y.Name.Contains("業務"))
        .DeleteAsync();

    // DELETE Name LIKE '%A%' 的 Member，並且回傳已刪除的 Member 資料。
    var deletedMembers = await memberDataAccess
                             .Where(x => x.Name.Contains("A"))
                             .DeleteAsync(m => new { m.Id, m.Name });
}

Bulk 系列

Bulk 系列是使用 Table-Valued Parameter 來向資料庫傳遞參數，套件會自動在 SQL Server 建立暫時性的 User Defined Table Type，此系列操作資料的方法有 7 個如下：

public interface IDataAccess<T>
{
    ...
    Task<int> BulkInsertAsync(IEnumerable<T> values, Expression<Func<T, bool>> nonexistence = null);
    Task<List<T>> BulkInsertAsync(IEnumerable<T> values, Expression<Func<T, object>> output, Expression<Func<T, bool>> nonexistence = null);
    Task<int> BulkInsertAsync(Expression<Func<T>> setterTemplate, IEnumerable<T> values, Expression<Func<T, bool>> nonexistence = null);
    Task<List<T>> BulkInsertAsync(Expression<Func<T>> setterTemplate, IEnumerable<T> values, Expression<Func<T, object>> output, Expression<Func<T, bool>> nonexistence = null);
    Task<int> BulkUpdateAsync(Expression<Func<T, bool>> predicateTemplate, Expression<Func<T>> setterTemplate, IEnumerable<T> values);
    Task<int> BulkUpsertAsync(Expression<Func<T, bool>> predicateTemplate, Expression<Func<T>> setterTemplate, IEnumerable<T> values);
    Task<List<T>> BulkUpsertAsync(Expression<Func<T, bool>> predicateTemplate, Expression<Func<T>> setterTemplate, IEnumerable<T> values, Expression<Func<T, object>> output);
    ...
}

BulkInsertAsync() 及 BulkUpsertAsync() 也一樣 output 參數可以回傳指定的資料欄位，範例程式碼如下，說明寫在註解中。

private static async Task DemoBulk()
{
    IDataAccessFactory dataAccessFactory = SqlServerDataAccessFactory.Instance;

    var memberDataAccess = dataAccessFactory.Create<Member>();

    // 批次 INSERT，只有預設的必填欄位。
    await memberDataAccess.BulkInsertAsync(
        new List<Member>
        {
            new Member { Id = 345, Name = "Logi" },
            new Member { Id = 678, Name = "Alucky" }
        });

    // 批次 INSERT，新增 Id, Name, Phone, Age 欄位。
    await memberDataAccess
        .Set(() => new Member { Id = default(int), Name = default(string), Phone = default(string), Age = default(int) })
        .BulkInsertAsync(
            new List<Member>
            {
                new Member { Id = 324, Name = "Cruzer", Phone = "0000-000123", Age = 12 },
                new Member { Id = 537, Name = "Slice", Phone = "0000-000456", Age = 21 }
            });

    // 批次 UPDATE Name, Phone 欄位
    await memberDataAccess
        .Where(x => x.Id == default(int))
        .Set(() => new Member { Name = default(string), Phone = default(string), })
        .BulkUpdateAsync(
            new List<Member>
            {
                new Member { Id = 324, Name = "Lawson", Phone = "0000-000523" },
                new Member { Id = 537, Name = "Marlon", Phone = "0000-000756" }
            });

    // 批次 UPDATE or INSERT Name, Phone 欄位
    await memberDataAccess
        .Where(x => x.Id == default(int))
        .Set(() => new Member { Name = default(string), Phone = default(string), })
        .BulkUpsertAsync(
            new List<Member>
            {
                new Member { Id = 324, Name = "Ramone", Phone = "0000-000623" },
                new Member { Id = 657, Name = "Chris", Phone = "0000-000835" }
            });

    // 批次 UPDATE or INSERT Name, Phone 欄位，回傳 Id 欄位。
    await memberDataAccess
        .Where(x => x.Id == default(int))
        .Set(() => new Member { Name = default(string), Phone = default(string), })
        .BulkUpsertAsync(
            new List<Member>
            {
                new Member { Id = 324, Name = "Ramone", Phone = "0000-000623" },
                new Member { Id = 657, Name = "Chris", Phone = "0000-000835" }
            },
            x => new { x.Id });
}

輸出產生的 SQL 語法

IDataAccess 有一個型別為 Action<string, IDictionary<string, object>> 的屬性 OutputSql，可以在執行 SQL 語法的同時將 SQL 語法輸出到指定的方法之中。

IDataAccessFactory dataAccessFactory = SqlServerDataAccessFactory.Instance;

var memberDataAccess = dataAccessFactory.Create<Member>();

memberDataAccess.OutputSql = (sql, parameters) => Console.WriteLine(sql);

預設為 Dirty Read

SqlServerDataAccess 預設是 Dirty Read，我們可以將 IsDirtyRead 屬性設為 false 來改變這個行為。

IDataAccessFactory dataAccessFactory = SqlServerDataAccessFactory.Instance;

var memberDataAccess = dataAccessFactory.Create<Member>();

memberDataAccess.IsDirtyRead = false;

以上，針對 Chef.DbAccess.SqlServer 這個套件的使用方式做基本的介紹，分享給大家，希望能有一丁點兒的幫助。

軟體主廚的程式料理廚房

軟體跟料理一樣，都變成了每人每天的生活必需品。