0%

[DotnetCore]ORM系列-Chloe:Select篇

前提情要

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

內容

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

基礎查詢

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

1
2
3
4
5
6
7
8
9
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如下

PrimaryKey過濾

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
// 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));
}

Like查詢

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
// 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));
}

In查詢

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
// 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));
}

排序分頁查詢

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
// 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));
}

Distinct查詢

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
// 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());
}

Exists查詢

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
// 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));
}

結論

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