private static void WhereLikeInternal <T>(IQuery query, Expression <Func <T, object> > propertySelector, string likeValue, Operator @operator, Like like, Case @case)
{
var column = propertySelector.Body.GetMemberInfo();
var predicate = $"{@case.GetSql()} {String.Format(like.GetSql(), likeValue)}";
query.AddWhere(column, typeof(T), predicate, @operator);
}
private static void WhereComparedInternal <T>(IQuery query, Expression <Func <T, object> > propertySelector, string value,
Operator @operator, Comparison comparison)
{
var column = propertySelector.Body.GetMemberInfo();
query.AddWhere(column, typeof(T), $"{comparison.GetSql()} {value}", @operator);
}
public static void user()
{
IQuery <User> q = context.Query <User>().AddWhere(" Name>'1'");
q = q.AddWhere("ID=1");
q = q.Where(a => a.Id == 1);
q.ToList();
}
public static IQuery WhereAnyWith <T>(this IQuery query, Expression <Func <T, object> > propertySelector, Expression <Func <object> > parameterSelector, Operator @operator = default)
{
var dapperParameter = parameterSelector.Body.GetDapperParameter();
var column = propertySelector.Body.GetMemberInfo();
var predicate = $"= {ANY}( {dapperParameter} )";
query.AddWhere(column, typeof(T), predicate, @operator);
return(query);
}
public static IQuery WhereInSubQuery <T>(this IQuery query, Expression <Func <T, object> > propertySelector, IQuery subQuery, Operator @operator = default)
{
query.AddWhere(propertySelector.GetMemberInfo(), typeof(T), $"IN ({subQuery.ToStatement()})", @operator);
return(query);
}
public static void BasicQuery()
{
IQuery <User> q = context.Query <User>();
q = q.AddWhere("ID=1");
q = q.Where(a => a.Id == 1);
var ss = q.ToList();
q.FirstOrDefault();
return;
/*
* SELECT TOP (1) [Users].[Id] AS [Id],[Users].[Name] AS [Name],[Users].[Gender] AS [Gender],[Users].[Age] AS [Age],[Users].[CityId] AS [CityId],[Users].[OpTime] AS [OpTime] FROM [Users] AS [Users] WHERE [Users].[Id] = 1
*/
//可以选取指定的字段
q.Where(a => a.Id == 1).Select(a => new { a.Id, a.Name }).FirstOrDefault();
/*
* SELECT TOP (1) [Users].[Id] AS [Id],[Users].[Name] AS [Name] FROM [Users] AS [Users] WHERE [Users].[Id] = 1
*/
//分页
q.Where(a => a.Id > 0).OrderBy(a => a.Age).Skip(20).Take(10).ToList();
/*
* SELECT TOP (10) [T].[Id] AS [Id],[T].[Name] AS [Name],[T].[Gender] AS [Gender],[T].[Age] AS [Age],[T].[CityId] AS [CityId],[T].[OpTime] AS [OpTime] FROM (SELECT [Users].[Id] AS [Id],[Users].[Name] AS [Name],[Users].[Gender] AS [Gender],[Users].[Age] AS [Age],[Users].[CityId] AS [CityId],[Users].[OpTime] AS [OpTime],ROW_NUMBER() OVER(ORDER BY [Users].[Age] ASC) AS [ROW_NUMBER_0] FROM [Users] AS [Users] WHERE [Users].[Id] > 0) AS [T] WHERE [T].[ROW_NUMBER_0] > 20
*/
/* like 查询 */
q.Where(a => a.Name.Contains("so") || a.Name.StartsWith("s") || a.Name.EndsWith("o")).ToList();
/*
* SELECT
* [Users].[Gender] AS [Gender],[Users].[Age] AS [Age],[Users].[CityId] AS [CityId],[Users].[OpTime] AS [OpTime],[Users].[Id] AS [Id],[Users].[Name] AS [Name]
* FROM [Users] AS [Users]
* WHERE ([Users].[Name] LIKE '%' + N'so' + '%' OR [Users].[Name] LIKE N's' + '%' OR [Users].[Name] LIKE '%' + N'o')
*/
/* in 一个数组 */
List <User> users = null;
List <int> userIds = new List <int>()
{
1, 2, 3
};
users = q.Where(a => userIds.Contains(a.Id)).ToList(); /* list.Contains() 方法组合就会生成 in一个数组 sql 语句 */
/*
* SELECT
* [Users].[Gender] AS [Gender],[Users].[Age] AS [Age],[Users].[CityId] AS [CityId],[Users].[OpTime] AS [OpTime],[Users].[Id] AS [Id],[Users].[Name] AS [Name]
* FROM [Users] AS [Users]
* WHERE [Users].[Id] IN (1,2,3)
*/
/* in 子查询 */
users = q.Where(a => context.Query <City>().Select(c => c.Id).ToList().Contains((int)a.CityId)).ToList(); /* IQuery<T>.ToList().Contains() 方法组合就会生成 in 子查询 sql 语句 */
/*
* SELECT
* [Users].[Gender] AS [Gender],[Users].[Age] AS [Age],[Users].[CityId] AS [CityId],[Users].[OpTime] AS [OpTime],[Users].[Id] AS [Id],[Users].[Name] AS [Name]
* FROM [Users] AS [Users]
* WHERE [Users].[CityId] IN (SELECT [City].[Id] AS [C] FROM [City] AS [City])
*/
/* distinct 查询 */
q.Select(a => new { a.Name }).Distinct().ToList();
/*
* SELECT DISTINCT [Users].[Name] AS [Name] FROM [Users] AS [Users]
*/
ConsoleHelper.WriteLineAndReadKey();
}