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

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

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

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

先說明一下我這次使用的範例,我有兩個資料表叫 tbl_membertbl_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 v2.6.0+

起手式

首先,類別建好後要為它指定連線字串,標記 [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 時需要指定連線字串名稱。

[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>("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 之外,還支援底下幾個語法:

  • 比較運算子:=<>><>=<=LIKENOT LIKEINNOT INIS NULLIS NOT NULL
  • 字串欄位比大小:x.Name.CompareTo("strB") [>, >=, <, <=] 0
  • DISTINCT
  • TOP
  • ORDER BY
  • GROUP BY:GroupBy 有支援的函式為 MAXMINSUMAVG
  • OFFSET | FETCH
  • INNER JOINLEFT 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 多筆 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 多筆 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();

    // 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();
}

Bulk 系列

Bulk 系列是使用 Table-Valued Parameter 來向資料庫傳遞參數的,所以需要先在 SQL Server 建立 User Defined Table Type,應用程式這邊需要透過 SqlServerDataAccessFactory.Instance.AddUserDefinedTable() 方法預先做設定,然後在有需要使用 User Defined Table Type 的資料類別上標記 UserDefinedAttribute

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

        SqlServerDataAccessFactory.Instance.AddUserDefinedTable(
            "MemberType",
            new Dictionary<string, Type>
            {
                ["Id"] = typeof(int),
                ["Name"] = typeof(string),
                ["Phone"] = typeof(string),
                ["Age"] = typeof(int)
            });

        ...
    }
}

[UserDefined(TableType = "MemberType")]
[Table("tbl_member")]
public class Member
{
    ...
}

Bulk 系列操作資料的方法有 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 這個套件的使用方式做基本的介紹,分享給大家,希望能有一丁點兒的幫助。

相關資源

C# 指南
ASP.NET 教學
ASP.NET MVC 指引
Azure SQL Database 教學
SQL Server 教學
Xamarin.Forms 教學