[DotnetCore]ORM系列-Chloe:Select篇

前情提要

上篇[DotnetCore]ORM系列-Chloe:入門篇介紹Chloe的使用方式，這篇主要繼續探討，Chloe提供哪些Select相關的Method，跟著筆者一起看下去。跟著Chloe github的wiki針對筆者之前建立的範例資料庫做一個應用吧。

內容

筆者這邊就照著github wiki上的教學，針對筆者自己建的範例資料庫，做一個實際應用的Demo，每一段都會有他自己的Conditions Model，對應的Service及Controller的宣告。

基礎查詢

這次所有範例的Service相關的Interface宣告如下

public interface ICustomerService
{
    Customers GetCustomerById(int customerNumber);
    IEnumerable<Customers> GetCustomerListByLikeConditions(CustomerLikeConditions conditions);
    IEnumerable<Customers> GetCustomersListByInConditions(CustomerInConditions conditions);
    IEnumerable<Customers> GetCustomersListByPaginationConditions(CustomerPageConditions conditions);
    List<string> GetCustomersCountryListByDistinct();
    IEnumerable<Customers> GetCustomersListByExists(CustomerExistConditions conditions);
}

對應的API Url如下

[CustomerAPI-Swagger]

PrimaryKey過濾

// Service
public Customers GetCustomerById(int customerNumber)
{
    return _db.Query<Customers>()
    .FirstOrDefault(x => x.CustomerNumber == customerNumber);
}

// Controller
/// <summary>
/// 依照客戶編號取得客戶
/// </summary>
/// <param name="customerNumber"></param>
/// <returns></returns>
[ProducesResponseType(typeof(Customers), StatusCodes.Status200OK)]
[HttpPost("{customerNumber}")]
public IActionResult GetCustomerById(int customerNumber)
{
    return Ok(_customerService.GetCustomerById(customerNumber));
}

[CustomerService-GetCustomerById]

Like查詢

// ConditionsModel
public class CustomerLikeConditions
{
    public string CustomerName { get; set; }
}

// Service(筆者這邊示範使用某個字母開頭的過濾)
public IEnumerable<Customers> GetCustomerListByLikeConditions(CustomerLikeConditions conditions)
{
    return _db.Query<Customers>()
    .Where(x => x.CustomerName.StartsWith(conditions.CustomerName))
    .ToList();
}

// Controller
/// <summary>
/// 依照客戶名稱做開頭過濾並取得清單
/// </summary>
/// <param name="conditions"></param>
/// <returns></returns>
[ProducesResponseType(typeof(IEnumerable<Customers>), StatusCodes.Status200OK)]
[HttpPost("like")]
public IActionResult GetCustomerListByLikeConditions(CustomerLikeConditions conditions)
{
    return Ok(_customerService.GetCustomerListByLikeConditions(conditions));
}

[CustomerService-GetCustomerListByLike]

In查詢

// ConditionsModel
public class CustomerInConditions
{
    public List<string> CustomerNameList { get; set; }
}
// Service
public IEnumerable<Customers> GetCustomersListByInConditions(CustomerInConditions conditions)
{
    return _db.Query<Customers>()
    .Where(x => conditions.CustomerNameList.Contains(x.CustomerName))
    .ToList();
}
// Controller
/// <summary>
/// 依照客戶名稱清單取得客戶清單
/// </summary>
/// <param name="conditions"></param>
/// <returns></returns>
[ProducesResponseType(typeof(IEnumerable<Customers>), StatusCodes.Status200OK)]
[HttpPost("in")]
public IActionResult GetCustomerListByInConditions(CustomerInConditions conditions)
{
    // var inConditionsNameList = new List<string>()
    // { "Atelier graphique"
    // , "Signal Gift Stores"
    // , "Australian Collectors, Co."};
    return Ok(_customerService.GetCustomersListByInConditions(conditions));
}

[CustomerService-GetCustomerListByIn]

排序分頁查詢

// ConditionsModel
public class BasePaginationParameters
{
    public virtual int PageNumber { get; set; } = 1;
    public virtual int PageSize { get; set; } = 10;
}
public class CustomerPageConditions : BasePaginationParameters
{

}
// Service
public IEnumerable<Customers> GetCustomersListByPaginationConditions(CustomerPageConditions conditions)
{
    return _db.Query<Customers>()
    .OrderBy(x => x.CustomerName)
    .ThenBy(x => x.CustomerNumber)
    .TakePage(conditions.PageNumber, conditions.PageSize)
    .ToList();
}
// Controller
/// <summary>
/// 依照分頁資訊取得客戶清單
/// </summary>
/// <param name="conditions"></param>
/// <returns></returns>
[ProducesResponseType(typeof(IEnumerable<Customers>), StatusCodes.Status200OK)]
[HttpPost("pagination")]
public IActionResult GetCustomerListByPagination(CustomerPageConditions conditions)
{
    return Ok(_customerService.GetCustomersListByPaginationConditions(conditions));
}

[CustomerService-GetCustomerListByPagSize:10]

[CustomerService-GetCustomerListByPageSize:20]

Distinct查詢

// Service
public List<string> GetCustomersCountryListByDistinct()
{
    return _db.Query<Customers>()
    .Select(x => x.Country)
    .Distinct()
    .ToList();
}

// Controller
/// <summary>
/// 取得客戶國家清單(distinct)
/// </summary>
/// <returns></returns>
[ProducesResponseType(typeof(List<string>), StatusCodes.Status200OK)]
[HttpPost("distinct")]
public IActionResult GetCustomerCountryDistinctList()
{
    return Ok(_customerService.GetCustomersCountryListByDistinct());
}

[CustomerService-GetCustomerListByDistinct]

Exists查詢

// Conditions Model
public class CustomerExistConditions
{
    public string CountryName { get; set; }
}
// Service
public IEnumerable<Customers> GetCustomersListByExists(CustomerExistConditions conditions)
{
    return _db.Query<Customers>()
    .Where(x => _db.Query<Customers>()
                .Where(c => c.Country == conditions.CountryName
                && c.CustomerNumber == x.CustomerNumber)
                .Any())
    .ToList();
}
// Controller
/// <summary>
/// 依照條件取得客戶清單(國家exists)
/// </summary>
/// <param name="conditions"></param>
/// <returns></returns>
[ProducesResponseType(typeof(IEnumerable<Customers>), StatusCodes.Status200OK)]
[HttpPost("exists")]
public IActionResult GetCustomerListByExists(CustomerExistConditions conditions)
{
    return Ok(_customerService.GetCustomersListByExists(conditions));
}

[CustomerService-GetCustomerListByExists]

結論

經由這篇介紹基本查詢部份，筆者覺得還滿方便使用的，可以應付日常使用，使用linq形式來過濾資料就是舒服，強型別Intellisense才是王道啊，下一篇撰寫Chloe提供的Interceptor，來探究一下，經由這樣的宣告，Chloe幫我們產生了什麼對應的sql語法，經由這樣驗證，才會更了解應用，就下篇見了。

---

Buy me a coffee