/// <summary> /// 取得Post By NodeId /// </summary> /// <param name="nodeId">NodeId</param> /// <param name="onlyShow">僅抓取上架</param> /// <param name="sortField">排序欄位</param> /// <param name="sortDesc">升降冪排序</param> /// <returns>Post清單</returns> public IList <PostVO> GetPostListByNodeId(int nodeId, bool onlyShow, string sortField, bool sortDesc) { DetachedCriteria dCriteria = DetachedCriteria.For <PostVO>(); dCriteria.CreateCriteria("Node").Add(Expression.Eq("NodeId", nodeId)); dCriteria.Add(Expression.Eq("IsTemp", false)); if (onlyShow) { dCriteria.Add(Expression.Eq("Flag", 1)); } if (!string.IsNullOrEmpty(sortField)) { if (sortDesc) { dCriteria.AddOrder(Order.Desc(sortField)); } else { dCriteria.AddOrder(Order.Asc(sortField)); } } int count = NHibernateDao.CountByDetachedCriteria(dCriteria); if (count == 0) { return(null); } return(NHibernateDao.SearchByDetachedCriteria <PostVO>(dCriteria)); }
public ApplicationsQueryBuilder WithProperty(string name) { _query.CreateCriteria("ApplicationProperties") .Add(Restrictions.Eq("Name", name)); _filteredByCollection = true; return(this); }
public async Task CanOrderBySubqueryProjectionDescAsync() { if (!TestDialect.SupportsAggregatingScalarSubSelectsInOrderBy) { Assert.Ignore("Dialect does not support aggregating scalar sub-selects in order by"); } using (ISession s = OpenSession()) { DetachedCriteria dc = DetachedCriteria.For <Person>("sub"); dc.CreateCriteria("Pets", "pets").SetProjection(Projections.Min("pets.Weight")).Add( Restrictions.EqProperty("this.Id", "sub.Id")); ICriteria c = s.CreateCriteria(typeof(Person)).AddOrder(Order.Desc(Projections.SubQuery(dc))); IList <Person> list = await(c.ListAsync <Person>()); int nullRelationOffSet = 0; if (Dialect is Oracle8iDialect || Dialect is PostgreSQLDialect) { // Oracle order NULL First (DESC) nullRelationOffSet = 2; } Assert.AreEqual(list[nullRelationOffSet + 2].Name, "Tim"); Assert.AreEqual(list[nullRelationOffSet + 1].Name, "Joe"); Assert.AreEqual(list[nullRelationOffSet].Name, "Sally"); } }
public void CanOrderBySubqueryProjection() { if (!Dialect.SupportsScalarSubSelects) { Assert.Ignore(Dialect.GetType().Name + " does not support scalar sub-queries"); } using (ISession s = OpenSession()) { using (new SqlLogSpy()) { DetachedCriteria dc = DetachedCriteria.For <Person>("sub"); dc.CreateCriteria("Pets", "pets").SetProjection(Projections.Min("pets.Weight")).Add( Restrictions.EqProperty("this.Id", "sub.Id")); ICriteria c = s.CreateCriteria(typeof(Person)).AddOrder(Order.Asc(Projections.SubQuery(dc))); Console.WriteLine("list()"); IList <Person> list = c.List <Person>(); int nullRelationOffSet = 2; if (Dialect is Oracle8iDialect || Dialect is PostgreSQLDialect) { // Oracle order NULL Last (ASC) nullRelationOffSet = 0; } Assert.AreEqual(list[nullRelationOffSet].Name, "Tim"); Assert.AreEqual(list[nullRelationOffSet + 1].Name, "Joe"); Assert.AreEqual(list[nullRelationOffSet + 2].Name, "Sally"); } } }
/// <summary> /// Creates the criteria. /// </summary> /// <typeparam name="T">Type creating criteria for.</typeparam> /// <typeparam name="TProperty">The type of the property.</typeparam> /// <param name="criteria">The criteria.</param> /// <param name="associationPropertyExpression">The association property expression.</param> /// <param name="joinType">Type of the join.</param> /// <returns>A <see cref="DetachedCriteria"/></returns> public static DetachedCriteria CreateCriteria <T, TProperty> ( this DetachedCriteria criteria, Expression <Func <T, TProperty> > associationPropertyExpression, JoinType joinType) { var associationPath = PropertyUtil.ExtractPropertyName(associationPropertyExpression); criteria.CreateCriteria(associationPath, joinType); return(criteria); }
public void Test() { DetachedCriteria criteria = DetachedCriteria.For(typeof(DbResource)); DetachedCriteria keyCriteria = criteria.CreateCriteria("keys"); keyCriteria.Add(Restrictions.Eq("Key0", "2")); keyCriteria.Add(Restrictions.Eq("Key1", "en")); using (ISession session = OpenSession()) { ICriteria icriteria = CriteriaTransformer.Clone(criteria).GetExecutableCriteria(session); icriteria.SetFirstResult(0); icriteria.SetMaxResults(1); icriteria.List <DbResource>(); // should not throw when parse the criteria } }
/// <summary> /// 取出子後台功能清單 By 父層Id /// </summary> /// <param name="parentId">父層Id</param> /// <returns>子後台功能清單</returns> public IList <MenuFuncVO> GetMenuFuncByParentId(int parentId) { DetachedCriteria dCriteria = DetachedCriteria.For <MenuFuncVO>(); dCriteria.CreateCriteria("ParentMenu").Add(Expression.Eq("MenuFuncId", parentId)); dCriteria.AddOrder(Order.Asc("ListOrder")); int count = NHibernateDao.CountByDetachedCriteria(dCriteria); if (count == 0) { return(null); } return(NHibernateDao.SearchByDetachedCriteria <MenuFuncVO>(dCriteria)); }
public async Task EagerFetchAnomalyAsync() { ISession s = OpenSession(); ITransaction t = s.BeginTransaction(); var mickey = new Passenger(); mickey.Name = new Name(); mickey.Name.First = "Mickey"; mickey.Name.Last = "Mouse"; mickey.FrequentFlyerNumber = "1234"; await(s.SaveAsync(mickey)); var reservation = new Reservation(); reservation.ConfirmationNumber = "11111111111111"; reservation.Passengers.Add(mickey); mickey.Reservation = reservation; await(s.SaveAsync(reservation)); await(t.CommitAsync()); s.Close(); s = OpenSession(); DetachedCriteria dc = DetachedCriteria.For <Reservation>().SetFetchMode("Passengers", FetchMode.Eager); dc.CreateCriteria("Passengers").Add(Property.ForName("FrequentFlyerNumber").Eq("1234")); IList <Reservation> results = await(dc.GetExecutableCriteria(s).ListAsync <Reservation>()); s.Close(); Assert.AreEqual(1, results.Count); foreach (var r in results) { Assert.AreEqual(1, r.Passengers.Count); } s = OpenSession(); t = s.BeginTransaction(); await(s.DeleteAsync(reservation)); await(t.CommitAsync()); s.Close(); }
/// <summary> /// 取得Post By 父層PostId /// </summary> /// <param name="parentPostId">父層PostId</param> /// <returns>Post清單</returns> public IList <PostVO> GetPostListByParentPostId(int parentPostId) { DetachedCriteria dCriteria = DetachedCriteria.For <PostVO>(); dCriteria.CreateCriteria("ParentPost").Add(Expression.Eq("PostId", parentPostId)); dCriteria.Add(Expression.Eq("IsTemp", false)); dCriteria.AddOrder(Order.Asc("SortNo")); int count = NHibernateDao.CountByDetachedCriteria(dCriteria); if (count == 0) { return(null); } return(NHibernateDao.SearchByDetachedCriteria <PostVO>(dCriteria)); }
/// <summary> /// 取得Post的留言筆數 /// </summary> /// <param name="postId">PostId</param> /// <param name="startDate">起始時間</param> /// <param name="endDate">結束時間</param> /// <returns>Post的留言筆數</returns> public int CountPostMessageByPostId(int postId, DateTime?startDate, DateTime?endDate) { DetachedCriteria dCriteria = DetachedCriteria.For <PostMessageVO>(); dCriteria.CreateCriteria("Post").Add(Expression.Eq("PostId", postId)); if (startDate != null) { dCriteria.Add(Expression.Ge("CreatedDate", startDate)); } if (endDate != null) { dCriteria.Add(Expression.Le("CreatedDate", endDate)); } return(NHibernateDao.CountByDetachedCriteria(dCriteria)); }
/// <summary> /// 取得Node By 父層Name /// </summary> /// <param name="name">父層Name</param> /// <returns>Node清單</returns> public IList <NodeVO> GetNodeListByParentName(string name) { DetachedCriteria dCriteria = DetachedCriteria.For <NodeVO>(); dCriteria.CreateCriteria("ParentNode").Add(Expression.Eq("Name", name)); dCriteria.AddOrder(Order.Asc("SortNo")); dCriteria.AddOrder(Order.Asc("Name")); int count = NHibernateDao.CountByDetachedCriteria(dCriteria); if (count == 0) { return(null); } return(NHibernateDao.SearchByDetachedCriteria <NodeVO>(dCriteria)); }
/// <summary> /// 取得Post筆數 By NodeId /// </summary> /// <param name="nodeId">NodeId</param> /// <param name="onlyShow">僅抓取上架</param> /// <param name="startDate">起始的上架日期</param> /// <returns>Post清單筆數</returns> public int CountPostListByNodeId(int nodeId, bool onlyShow, DateTime?startShowDate) { DetachedCriteria dCriteria = DetachedCriteria.For <PostVO>(); dCriteria.CreateCriteria("Node").Add(Expression.Eq("NodeId", nodeId)); dCriteria.Add(Expression.Eq("IsTemp", false)); if (onlyShow) { dCriteria.Add(Expression.Eq("Flag", 1)); } if (startShowDate != null) { dCriteria.Add(Expression.Le("ShowDate", startShowDate)); } return(NHibernateDao.CountByDetachedCriteria(dCriteria)); }
void FilterCompositeNode(CompositeNode compositenode, IClassMetadata metadata, DetachedCriteria query) { if (!metadata.PropertyNames.Contains(compositenode.Name)) { return; } var proptype = metadata.GetPropertyType(compositenode.Name); if (proptype is EntityType) { var etype = (EntityType)proptype; var childmetadata = AR.Holder.GetClassMetadata(etype.ReturnedClass); var childcriteria = _childCriterias.TryGet(compositenode.Name, () => query.CreateCriteria(compositenode.Name, JoinType.LeftOuterJoin)); foreach (var childnode in compositenode.ChildNodes) { RecursiveFilter(childcriteria, childnode, childmetadata); } } else if (proptype is CollectionType) { var ctype = (CollectionType)proptype; var reltype = ctype.ReturnedClass.GetGenericArguments().FirstOrDefault(); var childmetadata = AR.Holder.GetClassMetadata(reltype); if (childmetadata == null) { return; } var persister = SessionFactory.GetCollectionMetadata(ctype.Role) as ICollectionPersister; if (persister == null) { return; } if (persister.IsOneToMany) { } else if (persister.IsManyToMany) { } } }
/// <summary> /// 取得Post筆數 By ParentPostId /// </summary> /// <param name="parentPostId">父層PostId</param> /// <param name="onlyShow">僅抓取上架</param> /// <param name="showDate">目前顯示的日期</param> /// <returns>Post清單筆數</returns> public int CountPostListByParentPostId(int parentPostId, bool onlyShow, DateTime?showDate) { DetachedCriteria dCriteria = DetachedCriteria.For <PostVO>(); dCriteria.CreateCriteria("ParentPost").Add(Expression.Eq("PostId", parentPostId)); dCriteria.Add(Expression.Eq("IsTemp", false)); if (onlyShow) { dCriteria.Add(Expression.Eq("Flag", 1)); } if (showDate != null) { dCriteria.Add(Expression.Le("ShowDate", showDate.Value.Date)); dCriteria.Add(Expression.Ge("CloseDate", showDate)); } return(NHibernateDao.CountByDetachedCriteria(dCriteria)); }
public void CanOrderBySubqueryProjectionDesc() { using (ISession s = OpenSession()) { DetachedCriteria dc = DetachedCriteria.For <Person>("sub"); dc.CreateCriteria("Pets", "pets").SetProjection(Projections.Min("pets.Weight")).Add( Restrictions.EqProperty("this.Id", "sub.Id")); ICriteria c = s.CreateCriteria(typeof(Person)).AddOrder(Order.Desc(Projections.SubQuery(dc))); IList <Person> list = c.List <Person>(); int nullRelationOffSet = 0; if (Dialect is Oracle8iDialect || Dialect is PostgreSQLDialect) { // Oracle order NULL First (DESC) nullRelationOffSet = 2; } Assert.AreEqual(list[nullRelationOffSet + 2].Name, "Tim"); Assert.AreEqual(list[nullRelationOffSet + 1].Name, "Joe"); Assert.AreEqual(list[nullRelationOffSet].Name, "Sally"); } }
public IList <Permission> GetPermissionsByUserWithRoles(IUser user, string action) { ICriterion userIdMatches = Restrictions.Eq("Id", user.Id); ICriterion actionNameMatches = Restrictions.Eq("Name", action); ICriterion userIdAliasMatches = Restrictions.Eq("u.Id", user.Id); DetachedCriteria groupPermissionCriteria = DetachedCriteria.For <Permission>() .SetProjection(Projections.Property("Role")) .CreateCriteria("Role").CreateCriteria("Users").Add(userIdMatches); ICriterion groupSubquery = Subqueries.PropertyIn("Role", groupPermissionCriteria); DetachedCriteria permissionCriteria = DetachedCriteria.For <Permission>() .CreateAlias("User", "u", JoinType.LeftOuterJoin) .Add(Restrictions.Or(userIdAliasMatches, groupSubquery)); permissionCriteria.CreateCriteria("Action").Add(actionNameMatches); ICriteria executableCriteria = permissionCriteria.GetExecutableCriteria(Session); IList <Permission> permissions = executableCriteria.List <Permission>(); return(permissions); }
/// <summary> /// 取得Post By 父層ParentPostId /// </summary> /// <param name="parentPostId">父層PostId</param> /// <param name="onlyShow">僅抓取上架</param> /// <param name="showDate">目前顯示的日期</param> /// <param name="sortField">排序欄位</param> /// <param name="sortDesc">升降冪排序</param> /// <returns>Post清單</returns> public IList <PostVO> GetPostListByParentPostId(int parentPostId, bool onlyShow, DateTime?showDate, string sortField, bool sortDesc) { DetachedCriteria dCriteria = DetachedCriteria.For <PostVO>(); dCriteria.CreateCriteria("ParentPost").Add(Expression.Eq("PostId", parentPostId)); dCriteria.Add(Expression.Eq("IsTemp", false)); if (onlyShow) { dCriteria.Add(Expression.Eq("Flag", 1)); } if (showDate != null) { dCriteria.Add(Expression.Le("ShowDate", showDate.Value.Date)); dCriteria.Add(Expression.Ge("CloseDate", showDate)); } if (!string.IsNullOrEmpty(sortField)) { if (sortDesc) { dCriteria.AddOrder(Order.Desc(sortField)); } else { dCriteria.AddOrder(Order.Asc(sortField)); } } int count = NHibernateDao.CountByDetachedCriteria(dCriteria); if (count == 0) { return(null); } return(NHibernateDao.SearchByDetachedCriteria <PostVO>(dCriteria)); }
/// <summary> /// 取得Post的留言清單 /// </summary> /// <param name="postId">PostId</param> /// <param name="startDate">起始時間</param> /// <param name="endDate">結束時間</param> /// <param name="pageIndex">分頁索引</param> /// <param name="pageSize">分頁大小</param> /// <param name="sortField">排序欄位</param> /// <param name="sortDesc">升降冪排序</param> /// <returns>Post的留言清單</returns> public IList <PostMessageVO> GetPostMessageListByPostId(int postId, DateTime?startDate, DateTime?endDate , int pageIndex, int pageSize, string sortField, bool sortDesc) { DetachedCriteria dCriteria = DetachedCriteria.For <PostMessageVO>(); dCriteria.CreateCriteria("Post").Add(Expression.Eq("PostId", postId)); if (startDate != null) { dCriteria.Add(Expression.Ge("CreatedDate", startDate)); } if (endDate != null) { dCriteria.Add(Expression.Le("CreatedDate", endDate)); } if (!string.IsNullOrEmpty(sortField)) { if (sortDesc) { dCriteria.AddOrder(Order.Desc(sortField)); } else { dCriteria.AddOrder(Order.Asc(sortField)); } } int count = NHibernateDao.CountByDetachedCriteria(dCriteria); if (count == 0) { return(null); } return(NHibernateDao.SearchByDetachedCriteria <PostMessageVO>(dCriteria, pageIndex, pageSize)); }
private ICriterion GetExistsCriteria(MethodCallExpression expr) { EntityExpression rootEntity = EntityExpressionVisitor.FirstEntity(expr); string propertyName = MemberNameVisitor.GetMemberName(rootCriteria, expr); var rootEntityType = rootEntity.Type; if (rootEntity.MetaData.HasProxy) { rootEntityType = rootEntity.MetaData.GetMappedClass(EntityMode.Poco); } DetachedCriteria query = DetachedCriteria.For(rootEntityType) .SetProjection(Projections.Id()) .Add(Restrictions.IsNotEmpty(propertyName)); if (expr.Arguments.Count > 1) { var arg = (LambdaExpression)LinqUtil.StripQuotes(expr.Arguments[1]); string alias = arg.Parameters[0].Name; DetachedCriteria subquery = query.CreateCriteria(propertyName, alias); var temp = new WhereArgumentsVisitor(subquery.Adapt(session), session); temp.Visit(arg.Body); foreach (ICriterion c in temp.CurrentCriterions) { subquery.Add(c); } } string identifierName = rootEntity.GetAliasedIdentifierPropertyName(); return(Subqueries.PropertyIn(identifierName, query)); }
private void AddAccountQuery(DetachedCriteria query) { DetachedCriteria accountCriteria = null; if (AccountActive.HasValue) { accountCriteria = query.CreateCriteria("Accounts"); accountCriteria.Add(Expression.Eq("IsActive", AccountActive)); } if (AccountOpenedStart.HasValue) { accountCriteria = accountCriteria ?? query.CreateCriteria("Accounts"); accountCriteria.Add(Expression.Ge("Opened", AccountOpenedStart)); } if (AccountOpenedEnd.HasValue) { accountCriteria = accountCriteria ?? query.CreateCriteria("Accounts"); accountCriteria.Add(Expression.Le("Opened", AccountOpenedEnd)); } if (AccountSizeMin.HasValue) { accountCriteria = accountCriteria ?? query.CreateCriteria("Accounts"); accountCriteria.Add(Expression.Ge("Size", AccountSizeMin)); } if (AccountSizeMax.HasValue) { accountCriteria = accountCriteria ?? query.CreateCriteria("Accounts"); accountCriteria.Add(Expression.Le("Size", AccountSizeMax)); } if (AccountMoney.HasValue) { accountCriteria = accountCriteria ?? query.CreateCriteria("Accounts"); accountCriteria.Add(Expression.Eq("AccountType", AccountMoney)); } }
private void CreateNextPropertyLevel(DetachedCriteria criteria, List <string> propertyList, int listPointer, Junction dependencyCriterion) { Dictionary <string, AssociationEntry> ascs = GetAssociations(); string associationKey = GetAssociationKey(propertyList, listPointer); AssociationEntry e = null; if (ascs.ContainsKey(associationKey)) { e = ascs[associationKey]; if (listPointer == propertyList.Count - 1) { if (mCriterion == null) { mCriterion = BuildCriterion(e.Association); } if (dependencyCriterion != null) { dependencyCriterion.Add(mCriterion); } else { criteria.Add(mCriterion); } } else { DetachedCriteria subCriteria = e.Criteria; if (subCriteria == null) { subCriteria = criteria.CreateCriteria(e.Association, e.Alias, JoinType.InnerJoin); e.Criteria = subCriteria; } listPointer++; CreateNextPropertyLevel(subCriteria, propertyList, listPointer, dependencyCriterion); } } else { bool isThisId = associationKey.ToLower().EndsWith(".id"); if (listPointer == 0) { e = new AssociationEntry(propertyList[0], propertyList[0], string.Format("p{0}{1}", Stopwatch.GetTimestamp().ToString(), listPointer.ToString())); } else { string parentAssociation = GetAssociationKey(propertyList, listPointer - 1); AssociationEntry parentEntry = ascs[parentAssociation]; // léteznie kell, máskülönben ide sem juthattam volna e = new AssociationEntry(associationKey, string.Format("{0}.{1}", parentEntry.Alias, propertyList[listPointer]), string.Format("p{0}{1}", Stopwatch.GetTimestamp().ToString(), listPointer.ToString())); } if (!isThisId) { // az id asszociációkat nem mentjük le ascs[e.Key] = e; } if (listPointer == propertyList.Count - 1 || isThisId) { if (mCriterion == null) { mCriterion = BuildCriterion((isThisId && listPointer < propertyList.Count - 1) ? string.Format("{0}.{1}", e.Association, propertyList[listPointer + 1]) : e.Association); } if (dependencyCriterion != null) { dependencyCriterion.Add(mCriterion); } else { criteria.Add(mCriterion); } } else { DetachedCriteria subCriteria = e.Criteria; if (subCriteria == null) { subCriteria = criteria.CreateCriteria(e.Association, e.Alias, JoinType.InnerJoin); e.Criteria = subCriteria; } listPointer++; CreateNextPropertyLevel(subCriteria, propertyList, listPointer, dependencyCriterion); } } }
public ICriteria CreateCriteria(string associationPath, string alias, JoinType joinType) { return(detachedCriteria.CreateCriteria(associationPath, alias, joinType).Adapt(session)); }
/// <summary> /// Get Detached Criteria for Association Path /// </summary> /// <param name="dc">detached criteria</param> /// <param name="path"></param> /// <returns></returns> public static DetachedCriteria Path(this DetachedCriteria dc, string path) { return(dc.GetCriteriaByPath(path) ?? dc.CreateCriteria(path)); }
public static DetachedCriteria CreateCriteria <T>(this DetachedCriteria criteria, Expression <Func <T, object> > expression) { return(criteria.CreateCriteria(Clr <T> .Name(expression))); }