public async Task capture_events_to_a_new_stream_and_fetch_the_events_back_async_with_linq(DocumentTracking sessionType)
{
var store = InitStore();
using (var session = store.OpenSession(sessionType))
{
// SAMPLE: start-stream-with-aggregate-type
var joined = new MembersJoined {
Members = new[] { "Rand", "Matt", "Perrin", "Thom" }
};
var departed = new MembersDeparted {
Members = new[] { "Thom" }
};
var id = session.Events.StartStream(joined, departed).Id;
await session.SaveChangesAsync();
// ENDSAMPLE
var streamEvents = await Queryable.Where <IEvent>(session.Events.QueryAllRawEvents(), x => x.StreamId == id).OrderBy(x => x.Version).ToListAsync();
streamEvents.Count().ShouldBe(2);
streamEvents.ElementAt(0).Data.ShouldBeOfType <MembersJoined>();
streamEvents.ElementAt(0).Version.ShouldBe(1);
streamEvents.ElementAt(1).Data.ShouldBeOfType <MembersDeparted>();
streamEvents.ElementAt(1).Version.ShouldBe(2);
streamEvents.Each(e => e.Timestamp.ShouldNotBe(default(DateTimeOffset)));
}
}
public void Constructor_WithOneParameter_IfTheProviderParameterIsNotNull_ShouldSetTheExpression()
{
var queryable = new Queryable<string>(Mock.Of<IQueryProvider>());
Assert.AreEqual(queryable, ((ConstantExpression) queryable.Expression).Value);
Assert.IsTrue(typeof(IQueryable<string>).IsAssignableFrom(queryable.Expression.Type));
}
private static object GenerateQueryable(Type queryableType, int size, Dictionary <Type, object> createdObjectReferences)
{
bool isGeneric = queryableType.IsGenericType;
object list;
if (isGeneric)
{
Type listType = typeof(List <>).MakeGenericType(queryableType.GetGenericArguments());
list = GenerateCollection(listType, size, createdObjectReferences);
}
else
{
list = GenerateArray(typeof(object[]), size, createdObjectReferences);
}
if (list == null)
{
return(null);
}
if (isGeneric)
{
Type argumentType = typeof(IEnumerable <>).MakeGenericType(queryableType.GetGenericArguments());
MethodInfo asQueryableMethod = typeof(Queryable).GetMethod("AsQueryable", new[] { argumentType });
return(asQueryableMethod.Invoke(null, new[] { list }));
}
return(Queryable.AsQueryable((IEnumerable)list));
}
//Func<Type, IEnumerable<object>> fnGetObjects;
#region CreateQuery
/// <summary>
/// called during deserialization.
/// </summary>
/// <param name="elementType"></param>
/// <returns></returns>
public dynamic CreateQuery(Type elementType)
{
dynamic ienumerable = this.fnGetObjects(elementType);
Type enumerableType = ienumerable.GetType();
if (!typeof(IEnumerable <>).MakeGenericType(elementType).IsAssignableFrom(enumerableType))
{
ienumerable = Enumerable.ToArray(LinqHelper.CastToGenericEnumerable(ienumerable, elementType));
//throw new InvalidOperationException(string.Format("Return value Type is {1}. Expected: {0}", typeof(IEnumerable<>).MakeGenericType(elementType), ienumerable.GetType()));
}
IQueryable queryable = Queryable.AsQueryable(ienumerable);
IQueryProvider provider = (IQueryProvider)queryable.Provider;
Type queryType = typeof(Query <>).MakeGenericType(elementType);
ConstructorInfo ctor = queryType.GetConstructors()[2];//Query(IQueryProvider provider, Expression expression)
ParameterExpression[] parameters = new ParameterExpression[]
{
Expression.Parameter(typeof(IQueryProvider)),
Expression.Parameter(typeof(Expression))
};
NewExpression newexpr = Expression.New(ctor, parameters);
LambdaExpression lambda = Expression.Lambda(newexpr, parameters);
var newFn = lambda.Compile();
dynamic query = newFn.DynamicInvoke(new object[] { provider, Expression.Constant(queryable) });
return(query);
}
public Expression GetGroupJoin(GroupJoinContext context)
{
// Convert outer condition.
//
var outerParam = Expression.Parameter(context._outerKeyLambda.Body.Type, "o");
var outerKey = context._outerKeyLambda.GetBody(context.Lambda.Parameters[0]);
outerKey = context.Builder.BuildExpression(context, outerKey, false);
// Convert inner condition.
//
var parameters = context.Builder.CurrentSqlParameters
.Select((p,i) => new { p, i })
.ToDictionary(_ => _.p.Expression, _ => _.i);
var paramArray = Expression.Parameter(typeof(object[]), "ps");
var innerKey = context._innerKeyLambda.Body.Transform(e =>
{
int idx;
if (parameters.TryGetValue(e, out idx))
{
return Expression.Convert(
Expression.ArrayIndex(paramArray, Expression.Constant(idx)),
e.Type);
}
return e;
});
// Item reader.
//
var expr = Expression.Call(
null,
MemberHelper.MethodOf(() => Queryable.Where(null, (Expression<Func<TElement,bool>>)null)),
context._innerExpression,
Expression.Lambda<Func<TElement,bool>>(
ExpressionBuilder.Equal(context.Builder.MappingSchema, innerKey, outerParam),
new[] { context._innerKeyLambda.Parameters[0] }));
var lambda = Expression.Lambda<Func<IDataContext,TKey,object[],IQueryable<TElement>>>(
Expression.Convert(expr, typeof(IQueryable<TElement>)),
Expression.Parameter(typeof(IDataContext), "ctx"),
outerParam,
paramArray);
var itemReader = CompiledQuery.Compile(lambda);
return Expression.Call(
null,
MemberHelper.MethodOf(() => GetGrouping(null, null, default(TKey), null)),
new[]
{
ExpressionBuilder.QueryRunnerParam,
Expression.Constant(context.Builder.CurrentSqlParameters),
outerKey,
Expression.Constant(itemReader),
});
}
public virtual async Task <List <OpenIddictScope> > ListAsync(int?count, int?offset, CancellationToken cancellationToken = default)
{
return(await Queryable.OrderBy((await GetMongoQueryableAsync(GetCancellationToken(cancellationToken))), x => x.Id)
.SkipIf <OpenIddictScope, IQueryable <OpenIddictScope> >(offset.HasValue, offset)
.TakeIf <OpenIddictScope, IQueryable <OpenIddictScope> >(count.HasValue, count)
.As <IMongoQueryable <OpenIddictScope> >()
.ToListAsync(GetCancellationToken(cancellationToken)));
}
public static void GetAverage(int[] numbers)
{
// Write a function that takes an integer array and prints the AVERAGE of the values in the array.
// For example, with an array [2, 10, 3], your program should write 5 to the console.
double avg = Queryable.Average(numbers.AsQueryable());
Console.WriteLine(avg);
}
public List<INSTITUTION> GetAllActiveDeleted(bool sign)
{
IQueryable<INSTITUTION> queryable = Queryable.Where<INSTITUTION>((IQueryable<INSTITUTION>) new DataClasses1DataContext().INSTITUTIONs, (Expression<Func<INSTITUTION, bool>>) (p => p.Active == sign));
List<INSTITUTION> list = new List<INSTITUTION>();
foreach (INSTITUTION institution in (IEnumerable<INSTITUTION>) queryable)
list.Add(institution);
return list;
}
/// <summary>
/// Applies the LambdaExpression as an Expression<Func<TSource, bool>> predicate expression on source.Where()
/// </summary>
/// <param name="source"></param>
/// <param name="predicate"></param>
/// <typeparam name="TSource"></typeparam>
/// <returns></returns>
public static IEnumerable <TSource> Where <TSource>(this IEnumerable <TSource> source, EntityQueryType <TSource> filter)
{
if (filter.HasValue)
{
return(Queryable.Where(source.AsQueryable(), filter.Query));
}
return(source);
}
public Task <List <string> > GetContributorsForEntity(long entityId)
{
return(Queryable.Include(edit => edit.User)
.Where(edit => edit.EntityId == entityId && edit.ApprovedByUserId.HasValue)
.Select(edit => edit.User.UserName)
.Distinct()
.ToListAsync());
}
public Task <List <SuggestedEdit> > GetEditsForCharacterAndUser(long characterId, long userId)
{
return(Queryable
.Include(edit => edit.User)
.Where(edit => edit.EntityId == characterId && edit.ApprovedByUserId == null &&
edit.UserId == userId)
.ToListAsync());
}
/// <summary>
/// 根据字段名排序
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="queryable"></param>
/// <param name="propertyName"></param>
/// <param name="desc"></param>
/// <returns></returns>
public static IQueryable <T> OrderBy <T>(this IQueryable <T> queryable, string propertyName, bool desc)
{
var param = System.Linq.Expressions.Expression.Parameter(typeof(T));
var body = System.Linq.Expressions.Expression.Property(param, propertyName);
dynamic keySelector = System.Linq.Expressions.Expression.Lambda(body, param);
return(desc ? Queryable.OrderByDescending(queryable, keySelector) : Queryable.OrderBy(queryable, keySelector));
}
public void Constructor_WithOneParameter_IfTheProviderParameterIsNotNull_ShouldSetTheProvider()
{
var provider = Mock.Of<IQueryProvider>();
var queryable = new Queryable<object>(provider);
Assert.AreEqual(provider, queryable.Provider);
}
/// <summary>
/// 再排序
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="queryable"></param>
/// <param name="propertyName"></param>
/// <param name="desc"></param>
/// <returns></returns>
public static IOrderedQueryable <T> ThenBy <T>(this IOrderedQueryable <T> queryable, string propertyName, bool desc = false)
{
var param = Expression.Parameter(typeof(T));
var body = Expression.Property(param, propertyName);
dynamic keySelector = Expression.Lambda(body, param);
return(desc ? Queryable.ThenByDescending(queryable, keySelector) : Queryable.ThenBy(queryable, keySelector));
}
public override void SetUp()
{
base.SetUp();
_source2 = Expression.Constant(new[] { "test1", "test2" });
_node = new UnionExpressionNode(
CreateParseInfo(SourceNode, "u", ReflectionUtility.GetMethod(() => Queryable.Union <int> (null, null))),
_source2);
}
public static TQueryable PageBy <T, TQueryable>(
this TQueryable query,
int skipCount,
int maxResultCount)
where TQueryable : IQueryable <T>
{
return((TQueryable)Queryable.Take <T>(Queryable.Skip <T>(query, skipCount), maxResultCount));
}
public static object Find(string typeString, long id, out ISave repo)
{
var type = GetModelType(typeString);
var set = BaseCruds.GetDbSet(type, out repo);
var q = Queryable.Where <IInt64Key>((IQueryable <IInt64Key>)set, b => b.Id == id);
return(q.FirstOrDefault());
}
internal static IOrderedQueryable <T> ThenBy(IOrderedQueryable <T> source, string propertyName, ListSortDirection sortDirection)
{
dynamic keySelector = GetLambdaExpression(propertyName);
return(sortDirection == ListSortDirection.Ascending
? Queryable.ThenBy(source, keySelector)
: Queryable.ThenByDescending(source, keySelector));
}
// ---------------------
// Query-builder Methods
// ---------------------
/// <summary>
/// This query-builder method creates a new query whose results are the
/// unique results of this query.
/// </summary>
/// <returns> a new ObjectQuery instance. </returns>
public ObjectQuery <T> Distinct()
{
if (IsLinqQuery(this))
{
return((ObjectQuery <T>)Queryable.Distinct(this));
}
return(new ObjectQuery <T>(EntitySqlQueryBuilder.Distinct(QueryState)));
}
/// <summary>
/// 按指定的属性名称对<see cref="IQueryable{T}"/>序列进行排序
/// </summary>
/// <param name="source">IQueryable{T}序列</param>
/// <param name="propertyName">属性名称</param>
/// <param name="sortDirection">排序方向</param>
/// <returns></returns>
public static IOrderedQueryable <T> OrderBy(IQueryable <T> source, string propertyName, ListSortDirection sortDirection)
{
dynamic keySelector = GetKeySelector(propertyName);
return(sortDirection == ListSortDirection.Ascending
? Queryable.OrderBy(source, keySelector)
: Queryable.OrderByDescending(source, keySelector));
}
public static IOrderedQueryable <TSource> ThenBy <TSource, TKey>(this IOrderedQueryable <TSource> source, Expression <Func <TSource, TKey> > keySelector, WebSortOrder order)
{
if (order == WebSortOrder.Asc)
{
return(Queryable.ThenBy(source, keySelector));
}
return(Queryable.ThenByDescending(source, keySelector));
}
public virtual IQueryable Distinct(IQueryable query, string elementSelector)
{
if (!string.IsNullOrWhiteSpace(elementSelector))
{
query = Select(query, elementSelector);
}
return(Queryable.Distinct((dynamic)query));
}
/// <summary>
/// Apply the Where condition when applyPredicate is true
/// </summary>
/// <param name="source"></param>
/// <param name="wherePredicate"></param>
/// <param name="applyPredicate"></param>
/// <typeparam name="TSource"></typeparam>
/// <returns></returns>
public static IQueryable <TSource> WhereWhen <TSource>(this IEnumerable <TSource> source, Expression <Func <TSource, bool> > wherePredicate, bool applyPredicate)
{
if (applyPredicate)
{
return(Queryable.Where(source.AsQueryable(), wherePredicate));
}
return(source.AsQueryable());
}
public IMobileServiceTableQuery <T> ThenByDescending <TKey>(Expression <Func <T, TKey> > keySelector)
{
if (keySelector == null)
{
throw new ArgumentNullException("keySelector");
}
return(this.QueryProvider.Create(this.Table, Queryable.ThenByDescending((IOrderedQueryable <T>) this.Query, keySelector), this.Parameters, this.RequestTotalCount));
}
public IMobileServiceTableQuery <T> Where(Expression <Func <T, bool> > predicate)
{
if (predicate == null)
{
throw new ArgumentNullException("predicate");
}
return(this.QueryProvider.Create <T>(this.Table, Queryable.Where(this.Query, predicate), this.Parameters, this.RequestTotalCount));
}
public void Test_WithMethodInfoAndClosedGenericMethod_NotAllowed()
{
var closedGenericMethod = ReflectionUtility.GetMethod(() => Queryable.Select(null, (Expression <Func <int, int> >)null));
Assert.That(
() => _registry.Register(new[] { closedGenericMethod }, typeof(SelectExpressionNode)),
Throws.InvalidOperationException.With.Message.EqualTo(
"Cannot register closed generic method 'Select', try to register its generic method definition instead."));
}
/// <summary>
/// 按指定的属性名称对<see cref="IOrderedQueryable{T}"/>序列进行排序
/// </summary>
/// <param name="source">IOrderedQueryable{T}序列</param>
/// <param name="propertyName">属性名称</param>
/// <param name="sortDirection">排序方向</param>
/// <returns></returns>
public static IOrderedQueryable <T> ThenBy(IOrderedQueryable <T> source, string propertyName, ListSortDirection sortDirection)
{
propertyName.CheckNotNullOrEmpty("propertyName");
dynamic keySelector = GetKeySelector(propertyName);
return(sortDirection == ListSortDirection.Ascending
? Queryable.ThenBy(source, keySelector)
: Queryable.ThenByDescending(source, keySelector));
}
public EasyTableQuery <T> OrderByDescending <TKey>(Expression <Func <T, TKey> > keySelector)
{
if (keySelector == null)
{
throw new ArgumentNullException("keySelector");
}
return(this.QueryProvider.Create(Queryable.OrderByDescending(this.Query, keySelector), this.Parameters, this.RequestTotalCount));
}
public virtual object Sum(IQueryable query, string elementSelector)
{
if (!string.IsNullOrWhiteSpace(elementSelector))
{
query = query.Select(elementSelector);
}
return(Queryable.Sum((dynamic)query));
}
public List <Widget> GetWidgetsFor(int planId = 0)
{
if (planId > 0)
{
return(Queryable.Where(_context.Widget, u => Enumerable.Any <WidgetPlan>(u.WidgetPlans, i => i.PlanId == planId))
.ToList());
}
return(GetWidgets());
}
public virtual object LastOrDefault(IQueryable query, string predicate)
{
if (!string.IsNullOrWhiteSpace(predicate))
{
query = Where(query, predicate);
}
return(Queryable.LastOrDefault((dynamic)query));
}
public async Task <List <Widget> > GetWidgetsForAsync(int planId = 0)
{
if (planId > 0)
{
return(await Queryable.Where(_context.Widget, u => Enumerable.Any <WidgetPlan>(u.WidgetPlans, i => i.PlanId == planId))
.ToListAsync());
}
return(await GetWidgetsAsync());
}
public void WhenEntitiesAreQueried_ResultsArePutInScopedCache()
{
// Arrange
var permission = new Lazy<Permission, PermissionMetadata>(() => new ViewPermission(), new PermissionMetadata(new Dictionary<string, object>()));
var userGroup = CoreCmsData.RequiredCoreUserGroups(Enumerable.Repeat(permission, 1)).FirstOrDefault(x => x.Name == "Administrator");
Assert.NotNull(userGroup);
using (var uow = GroupUnitFactory.Create())
{
uow.Repositories.AddOrUpdate(new SystemRoot());
uow.Repositories.AddOrUpdate(Framework.Security.Model.FixedEntities.UserGroupVirtualRoot);
uow.Repositories.AddOrUpdate(userGroup);
uow.Complete();
}
// Assert - check single result
using (var uow = GroupUnitFactory.Create())
{
// Cause the task to be fired
Expression<Func<UserGroup, bool>> expression = x => x.Name == "Administrator";
var getAdminByName = uow.Repositories.Query<UserGroup>().FirstOrDefault(expression);
Assert.NotNull(getAdminByName);
// Generate what should be an exact-same QueryDescription for the above query, to check the cache
var executor = new Executor(uow.Repositories.QueryableDataSource, Queryable<UserGroup>.GetBinderFromAssembly());
var queryable = new Queryable<UserGroup>(executor);
queryable.FirstOrDefault(expression);
var description = executor.LastGeneratedDescription;
// Assert the task has been fired
Assert.That(uow.UnitScopedCache.GetOrCreate(new QueryDescriptionCacheKey(description), () => null), Is.Not.Null);
}
// Assert - check many results
using (var uow = GroupUnitFactory.Create())
{
// Cause the task to be fired
Expression<Func<UserGroup, bool>> expression = x => x.Name == "Administrator";
var getAdminByName = uow.Repositories.Query<UserGroup>().Where(expression).ToList();
Assert.NotNull(getAdminByName.FirstOrDefault());
// Generate what should be an exact-same QueryDescription for the above query, to check the cache
var executor = new Executor(uow.Repositories.QueryableDataSource, Queryable<UserGroup>.GetBinderFromAssembly());
var queryable = new Queryable<UserGroup>(executor);
queryable.Where(expression).ToList();
var description = executor.LastGeneratedDescription;
// Assert the task has been fired
Assert.That(uow.UnitScopedCache.GetOrCreate(new QueryDescriptionCacheKey(description), () => null), Is.Not.Null);
}
}
/// <summary>
/// 拉姆达表达示
/// </summary>
private void QueryableDemo()
{
using (var db = SugarDao.GetInstance())
{
db.IsNoLock = true;
//查询所有
var student = db.Queryable<Student>().ToList();
var studentDynamic = db.Queryable<Student>().ToDynamic();
var studentJson = db.Queryable<Student>().ToJson();
//查询单条
var single = db.Queryable<Student>().Single(c => c.id == 1);
//查询单条根据主键
var singleByPk = db.Queryable<Student>().InSingle(1);
//查询单条没有记录返回空对象
var singleOrDefault = db.Queryable<Student>().SingleOrDefault(c => c.id == 11111111);
//查询单条没有记录返回空对象
var single2 = db.Queryable<Student>().Where(c => c.id == 1).SingleOrDefault();
//查询所有的Id
var singleFieldList = db.Queryable<Student>().Select<int>(it=>it.id).ToList();
//查询第一条
var first = db.Queryable<Student>().Where(c => c.id == 1).First();
var first2 = db.Queryable<Student>().Where(c => c.id == 1).FirstOrDefault();
//取11-20条
var page1 = db.Queryable<Student>().Where(c => c.id > 10).OrderBy(it => it.id).Skip(10).Take(10).ToList();
//取11-20条 等于 Skip(pageIndex-1)*pageSize).Take(pageSize) 等于 between (pageIndex-1)*pageSize and pageIndex*pageSize
var page2 = db.Queryable<Student>().Where(c => c.id > 10).OrderBy(it => it.id).ToPageList(2, 10);
//取11-20条并且返回PageCount
int pageCount = 0;
var page3 = db.Queryable<Student>().Where(c => c.id > 10).OrderBy(it => it.id).ToPageList(2, 10, ref pageCount);
//查询条数
var count = db.Queryable<Student>().Where(c => c.id > 10).Count();
//从第2条开始以后取所有
var skip = db.Queryable<Student>().Where(c => c.id > 10).OrderBy(it => it.id).Skip(2).ToList();
//取前2条
var take = db.Queryable<Student>().Where(c => c.id > 10).OrderBy(it => it.id).Take(2).ToList();
//Not like
string conval = "a";
var notLike = db.Queryable<Student>().Where(c => !c.name.Contains(conval.ToString())).ToList();
//Like
conval = "三";
var like = db.Queryable<Student>().Where(c => c.name.Contains(conval)).ToList();
//支持字符串Where 让你解决，更复杂的查询
var student12 = db.Queryable<Student>().Where(c => "a" == "a").Where("id>@id", new { id = 1 }).ToList();
var student13 = db.Queryable<Student>().Where(c => "a" == "a").Where("id>100 and id in( select 1)").ToList();
//存在记录反回true，则否返回false
bool isAny100 = db.Queryable<Student>().Any(c => c.id == 100);
bool isAny1 = db.Queryable<Student>().Any(c => c.id == 1);
//获取最大Id
object maxId = db.Queryable<Student>().Max(it => it.id);
int maxId1 = db.Queryable<Student>().Max(it => it.id).ObjToInt();//拉姆达
int maxId2 = db.Queryable<Student>().Max<int>("id"); //字符串写法
//获取最小
int minId1 = db.Queryable<Student>().Where(c => c.id > 0).Min(it => it.id).ObjToInt();//拉姆达
int minId2 = db.Queryable<Student>().Where(c => c.id > 0).Min<int>("id");//字符串写法
//order By
var orderList = db.Queryable<Student>().OrderBy("id desc,name asc").ToList();//字符串支持多个排序
//可以多个order by表达示
var order2List = db.Queryable<Student>().OrderBy(it => it.name).OrderBy(it => it.id, OrderByType.Desc).ToList(); // order by name as ,order by id desc
//In
var intArray = new[] { "5", "2", "3" };
var intList = intArray.ToList();
var listnew = db.Queryable<Student>().Where(it => intArray.Contains(it.name)).ToList();
var list0 = db.Queryable<Student>().In(it => it.id, 1, 2, 3).ToList();
var list1 = db.Queryable<Student>().In(it => it.id, intArray).ToList();
var list2 = db.Queryable<Student>().In("id", intArray).ToList();
var list3 = db.Queryable<Student>().In(it => it.id, intList).ToList();
var list4 = db.Queryable<Student>().In("id", intList).ToList();
var list6 = db.Queryable<Student>().In(intList).ToList();//不设置字段默认主键
//分组查询
var list7 = db.Queryable<Student>().Where(c => c.id < 20).GroupBy(it => it.sex).Select("sex,count(*) Count").ToDynamic();
var list8 = db.Queryable<Student>().Where(c => c.id < 20).GroupBy(it => it.sex).GroupBy(it => it.id).Select("id,sex,count(*) Count").ToDynamic();
List<StudentGroup> list9 = db.Queryable<Student>().Where(c => c.id < 20).GroupBy(it => it.sex).Select<StudentGroup>("Sex,count(*) Count").ToList();
List<StudentGroup> list10 = db.Queryable<Student>().Where(c => c.id < 20).GroupBy("sex").Select<StudentGroup>("Sex,count(*) Count").ToList();
//SELECT Sex,Count=count(*) FROM Student WHERE 1=1 AND (id < 20) GROUP BY Sex --生成结果
//2表关联查询
var jList = db.Queryable<Student>()
.JoinTable<School>((s1, s2) => s1.sch_id == s2.id) //默认left join
.Where<School>((s1, s2) => s1.id == 1)
.Select("s1.*,s2.name as schName")
.ToDynamic();
/*等于同于
SELECT s1.*,s2.name as schName
FROM [Student] s1
LEFT JOIN [School] s2 ON s1.sch_id = s2.id
WHERE s1.id = 1 */
//2表关联查询并分页
var jList2 = db.Queryable<Student>()
.JoinTable<School>((s1, s2) => s1.sch_id == s2.id) //默认left join
//如果要用inner join这么写
//.JoinTable<School>((s1, s2) => s1.sch_id == s2.id ,JoinType.INNER)
.Where<School>((s1, s2) => s1.id > 1)
.OrderBy(s1 => s1.name)
.Skip(10)
.Take(20)
.Select("s1.*,s2.name as schName")
.ToDynamic();
//3表查询并分页
var jList3 = db.Queryable<Student>()
.JoinTable<School>((s1, s2) => s1.sch_id == s2.id) // left join School s2 on s1.id=s2.id
.JoinTable<School>((s1, s3) => s1.sch_id == s3.id) // left join School s3 on s1.id=s3.id
.Where<School>((s1, s2) => s1.id > 1) // where s1.id>1
.Where(s1 => s1.id > 0)
.OrderBy<School>((s1, s2) => s1.id) //order by s1.id 多个order可以 .oderBy().orderby 叠加
.Skip(10)
.Take(20)
.Select("s1.*,s2.name as schName,s3.name as schName2")//select目前只支持这种写法
.ToDynamic();
//上面的方式都是与第一张表join，第三张表想与第二张表join写法如下
List<V_Student> jList4 =
db.Queryable<Student>()
.JoinTable<School>((s1, s2) => s1.sch_id == s2.id) // left join School s2 on s1.id=s2.id
.JoinTable<School, Area>((s1, s2, a1) => a1.id == s2.AreaId)// left join Area a1 on a1.id=s2.AreaId 第三张表与第二张表关联
.JoinTable<Area, School>((s1, a1, s3) => a1.id == s3.AreaId)// left join School s3 on a1.id=s3.AreaId 第四第表第三张表关联
.JoinTable<School>((s1, s4) => s1.sch_id == s4.id) // left join School s2 on s1.id=s4.id
.Select<School, Area, V_Student>((s1, s2, a1) => new V_Student { id = s1.id, name = s1.name, SchoolName = s2.name, AreaName = a1.name }).ToList();
//等同于
//SELECT id = s1.id, name = s1.name, SchoolName = s2.name, AreaName = a1.name
//FROM [Student] s1
//LEFT JOIN School s2 ON ( s1.sch_id = s2.id )
//LEFT JOIN Area a1 ON ( a1.id = s2.AreaId ) //第三张表与第二张表关联
//LEFT JOIN School s3 ON ( a1.id = s3.AreaId ) //第四张表与第三张表关联
//LEFT JOIN School s4 ON ( s1.sch_id = s4.id )
//WHERE 1=1
//Join子查询语句加分页的写法
var childQuery = db.Queryable<Area>().Where("id=@id").Select(it => new { id = it.id,name=it.name }).ToSql();//创建子查询SQL
string childTableName =SqlSugarTool.PackagingSQL(childQuery.Key);//将SQL语句用()包成表
var queryable = db.Queryable<Student>()
.JoinTable<School>((s1, s2) => s1.sch_id == s2.id) //LEFT JOIN School s2 ON ( s1.sch_id = s2.id )
.JoinTable(childTableName, "a1", "a1.id=s2.areaid", new { id = 1 }, JoinType.Inner) //INNER JOIN (SELECT * FROM [Area] WHERE 1=1 AND id=@id ) a1 ON a1.id=s2.areaid
.OrderBy(s1 => s1.id);
var list = queryable.Select<School, Area, V_Student>((s1, s2, a1) => new V_Student { id = s1.id, name = s1.name, SchoolName = s2.name, AreaName = a1.name })
.ToPageList(0, 200);
var count2 = queryable.Count();
//拼接例子
var queryable2 = db.Queryable<Student>().Where(it => true);
if (maxId.ObjToInt() == 1)
{
queryable2.Where(it => it.id == 1);
}
else
{
queryable2.Where(it => it.id == 2);
}
var listJoin = queryable2.ToList();
//queryable和SqlSugarClient解耦
var par = new Queryable<Student>().Where(it => it.id == 1);//声名没有connection对象的Queryable
par.DB = db;
var listPar = par.ToList();
//查看生成的sql和参数
var id = 1;
var sqlAndPars = db.Queryable<Student>().Where(it => it.id == id).OrderBy(it => it.id).ToSql();
//拉姆达支持的函数操作
var par1 = "2015-1-1"; var par2 = " 我 有空格A, ";
var r1 = db.Queryable<Student>().Where(it => it.name == par1.ObjToString()).ToList(); //ObjToString会将null转转成""
var r2 = db.Queryable<InsertTest>().Where(it => it.d1 == par1.ObjToDate()).ToList();
var r3 = db.Queryable<InsertTest>().Where(it => it.id == 1.ObjToInt()).ToList();//ObjToInt会将null转转成0
var r4 = db.Queryable<InsertTest>().Where(it => it.id == 2.ObjToDecimal()).ToList();
var r5 = db.Queryable<InsertTest>().Where(it => it.id == 3.ObjToMoney()).ToList();
var r6 = db.Queryable<InsertTest>().Where(it => it.v1 == par2.Trim()).ToList();
var convert1 = db.Queryable<Student>().Where(c => c.name == "a".ToString()).ToList();
var convert2 = db.Queryable<Student>().Where(c => c.id == Convert.ToInt32("1")).ToList();
var convert3 = db.Queryable<Student>().Where(c => c.name == par2.ToLower()).ToList();
var convert4 = db.Queryable<Student>().Where(c => c.name == par2.ToUpper()).ToList();
var convert5= db.Queryable<Student>().Where(c => DateTime.Now > Convert.ToDateTime("2015-1-1")).ToList();
var c1 = db.Queryable<Student>().Where(c => c.name.Contains("a")).ToList();
var c2 = db.Queryable<Student>().Where(c => c.name.StartsWith("a")).ToList();
var c3 = db.Queryable<Student>().Where(c => c.name.EndsWith("a")).ToList();
var c4 = db.Queryable<Student>().Where(c => !string.IsNullOrEmpty(c.name)).ToList();
var c5 = db.Queryable<Student>().Where(c => c.name.Equals("小杰")).ToList();
var c6 = db.Queryable<Student>().Where(c => c.name.Length > 4).ToList();
var time = db.Queryable<InsertTest>().Where(c => c.d1>DateTime.Now.AddDays(1)).ToList();
var time2 = db.Queryable<InsertTest>().Where(c => c.d1 > DateTime.Now.AddYears(1)).ToList();
var time3 = db.Queryable<InsertTest>().Where(c => c.d1 > DateTime.Now.AddMonths(1)).ToList();
}
}
