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