public void TestQueryAsync() { if (!Dialect.SupportsSubSelectsWithPagingAsInPredicateRhs) { Assert.Ignore("Current dialect does not support paging within IN sub-queries"); } using (ISession session = OpenSession()) { DetachedCriteria page = DetachedCriteria.For <T1>() .SetFirstResult(3) .SetMaxResults(7) .AddOrder(NHibernate.Criterion.Order.Asc(Projections.Id())) .SetProjection(Projections.Id()); ICriteria crit = session.CreateCriteria <T1>() .Add(Subqueries.PropertyIn("id", page)) .SetResultTransformer(new DistinctRootEntityResultTransformer()) .SetFetchMode("Children", NHibernate.FetchMode.Join); session.EnableFilter("nameFilter").SetParameter("name", "Another child"); Assert.That(() => crit.ListAsync <T1>(), Throws.Nothing); } }
public IAggregateRoot LoadById(Guid id) { DetachedCriteria boundaryCriteria = DetachedCriteria.For <Event>() .SetProjection(Projections.Id()) .Add(Expression.Eq("IsSnapshot", true)) .Add(Expression.Eq("EntityId", id)) .AddOrder(new Order("SequenceNumber", false)) .SetFirstResult(0) .SetMaxResults(1); ICriteria criteria = _readSession.CreateCriteria(typeof(Event)) .Add(Expression.Eq("EntityId", id)) .Add(Subqueries.PropertyGe("SequenceNumber", boundaryCriteria)) .AddOrder(new Order("SequenceNumber", true)); IList <Event> events = criteria.List <Event>(); if (events.Count < 1) { throw new ArgumentException(string.Format("Entity with id {0} not found.", id)); } IAggregateRoot lastSnapshot = (IAggregateRoot)events[0].Data; lastSnapshot.Id = id; lastSnapshot.LoadFromEventStream(events.Skip(1).Select(x => x.Data)); lastSnapshot.Version = events.Last().Version; _trackedObjects.Add(lastSnapshot); return(lastSnapshot); }
private static ICriterion GetPermissionQueryInternal(IUser user, string operation, string securityKeyProperty) { string[] operationNames = Strings.GetHierarchicalOperationNames(operation); DetachedCriteria criteria = DetachedCriteria.For <Permission>("permission") .CreateAlias("Operation", "op") .CreateAlias("EntitiesGroup", "entityGroup", JoinType.LeftOuterJoin) .CreateAlias("entityGroup.Entities", "entityKey", JoinType.LeftOuterJoin) .SetProjection(Projections.Property("Allow")) .Add(Restrictions.In("op.Name", operationNames)) .Add(Restrictions.Eq("User", user) || Subqueries.PropertyIn("UsersGroup.Id", SecurityCriterions.AllGroups(user).SetProjection(Projections.Id()))) .Add( Property.ForName(securityKeyProperty).EqProperty("permission.EntitySecurityKey") || Property.ForName(securityKeyProperty).EqProperty("entityKey.EntitySecurityKey") || ( Restrictions.IsNull("permission.EntitySecurityKey") && Restrictions.IsNull("permission.EntitiesGroup") ) ) .SetMaxResults(1) .AddOrder(Order.Desc("Level")) .AddOrder(Order.Asc("Allow")); return(Subqueries.Eq(true, criteria)); }
public override SalaEstudo BuscarSalaDoInscrito(int idEvento, int idInscricao) { SalaEstudo aliasSala = null; var consultaParticipantes = QueryOver.Of <SalaEstudo>() .Where(x => x.Id == aliasSala.Id) .JoinQueryOver(x => x.Participantes) .Where(y => y.Id == idInscricao) .SelectList(lista => lista .Select(x => x.Id)); var consultaCoordenadores = QueryOver.Of <AtividadeInscricaoSalaEstudoCoordenacao>() .Where(x => x.SalaEscolhida.Id == aliasSala.Id && x.Inscrito.Id == idInscricao) .SelectList(lista => lista .Select(x => x.Inscrito.Id)); return(mSessao.QueryOver <SalaEstudo>(() => aliasSala) .Where(Restrictions.Conjunction() .Add <SalaEstudo>(x => x.Evento.Id == idEvento) .Add(Restrictions.Disjunction() .Add(Subqueries.WhereExists(consultaCoordenadores)) .Add(Subqueries.WhereExists(consultaParticipantes) ) ) ) .SingleOrDefault()); }
/// <summary> /// 锁定一个给定的聚合根,数据库级别的锁; /// 该方法内部不需要考虑事务,因为框架在调用该方法之前已经确保当前事务是开启的; /// 默认实现是通过ROWLOCK+UPDLOCK的方式实现行级锁; /// </summary> protected virtual long LockAggregateRootAfterTransactionStarted(string aggregateRootId) { var aggregateRootEventType = _aggregateRootEventTypeProvider.GetAggregateRootEventType(typeof(T)); var aggregateRootName = GetAggregateRootName(); DetachedCriteria detachedCriteria = DetachedCriteria.For(aggregateRootEventType) .Add(Restrictions.Eq("AggregateRootName", aggregateRootName)) .Add(Restrictions.Eq("AggregateRootId", aggregateRootId)) .Add(Subqueries.PropertyEq("Version", DetachedCriteria.For(aggregateRootEventType) .SetProjection(Projections.Max("Version")) .Add(Restrictions.Eq("AggregateRootName", aggregateRootName)) .Add(Restrictions.Eq("AggregateRootId", aggregateRootId)))); detachedCriteria.SetLockMode(LockMode.Upgrade); var criteria = detachedCriteria.GetExecutableCriteria(OpenSession()); var eventList = typeof(EventQueryHelper).GetMethod("GetList").MakeGenericMethod(aggregateRootEventType).Invoke(new EventQueryHelper(), new object[] { criteria }) as IList; if (eventList != null && eventList.Count > 0) { return((long)((AggregateRootEvent)eventList[0]).Version); } return(0); }
public void Criteria_ComplexTest() { //Генерируем актуальный критерий var subQuery = repository.Object.SubQuery <Article>() .Like(x => x.Number, "123") .Select(x => x.Id); var crit = repository.Object.Query <ReceiptWaybillRow>(); crit.Restriction <ReceiptWaybill>(x => x.ReceiptWaybill).PropertyIn(x => x.ReceiptStorage.Id, subQuery); var actual = (crit as Criteria <ReceiptWaybillRow>).GetCriteria(); //Генерируем ожидаемый критерий var dc = DetachedCriteria.For <Article>(); dc.Add(Expression.Like("Number", "123", MatchMode.Anywhere)).SetProjection(Property.ForName("Id")); var expected = session.CreateCriteria <ReceiptWaybillRow>(); expected.Add(Expression.IsNull("DeletionDate")); expected.CreateCriteria("ReceiptWaybill").Add(Subqueries.PropertyIn("ReceiptStorage.Id", dc)); var sq = repository.Object.SubQuery <Article>(); crit.Or( y => y.PropertyIn(x => x.ReceiptWaybill, sq), y => y.PropertyIn(x => x.ReceiptWaybill, sq)); Assert.AreEqual(expected.ToString(), actual.ToString()); }
public void NestedSubqueriesShouldStayInOrder() { using (ISession s = OpenSession()) { using (ITransaction tx = s.BeginTransaction()) { DetachedCriteria dc1 = DetachedCriteria.For(typeof(Person)) .Add(Property.ForName("IQ").Eq(40)) .SetProjection(Property.ForName("IQ")); DetachedCriteria dc2 = DetachedCriteria.For(typeof(Person)) .Add(Subqueries.PropertyEq("ShoeSize", dc1)) .SetProjection( new SqlFunctionProjection("concat", NHibernateUtil.String, Projections.GroupProperty("Name"), new ConstantProjection(" "), Projections.GroupProperty("Name") )); IList list = s.CreateCriteria(typeof(Person)) .Add(Subqueries.Eq("Fred Fred", dc2)) .List(); Assert.AreEqual(5, list.Count); //yeah, it returns all five results. The key is that it didn't crash tx.Commit(); } } }
public void MultipleSubqueriesShouldStayInOrder() { using (ISession s = OpenSession()) { using (ITransaction tx = s.BeginTransaction()) { DetachedCriteria dc1 = DetachedCriteria.For(typeof(Person)) .Add(Property.ForName("IQ").Eq(10)) .SetProjection(Property.ForName("Name")); DetachedCriteria dc2 = DetachedCriteria.For(typeof(Person)) .Add(Property.ForName("ShoeSize").Eq(7)) .SetProjection(Projections.Property("Name")); IList list = s.CreateCriteria(typeof(Person), "p") .Add(Subqueries.PropertyEq("Name", dc1)) .Add(Restrictions.Not(Subqueries.Eq("Sally", dc2))) .List(); Assert.AreEqual(1, list.Count); Assert.AreEqual("Joe", ((Person)list[0]).Name); tx.Commit(); } } }
/// <summary> /// Versuch über ein Detached Criteria die Where-Klauseln zu verarbeiten, damit es mit dem Order nicht zu /// komplikationen kommt. /// </summary> protected virtual IPage <T> Find(IPageable pageable, Action <DetachedCriteria> criteriaBuilder, Action <ICriteria> ordersBuilder = null) { Require.NotNull(pageable, "pageable"); Require.NotNull(criteriaBuilder, "criteriaBuilder"); HibernateDelegate <IPage <T> > finder = delegate(ISession session) { DetachedCriteria whereCriteria = DetachedCriteria.For(typeof(T)); criteriaBuilder(whereCriteria); whereCriteria.SetProjection(Projections.Property("Id")); ICriteria elementsCriteria = session.CreateCriteria(typeof(T)); elementsCriteria.Add(Subqueries.PropertyIn("Id", whereCriteria)); if (ordersBuilder != null) { ordersBuilder(elementsCriteria); } ApplyPaging(pageable, elementsCriteria); ICriteria countCriteria = session.CreateCriteria(typeof(T)); countCriteria.Add(Subqueries.PropertyIn("Id", whereCriteria)); countCriteria.SetProjection(Projections.RowCountInt64()); IFutureValue <long> futureTotalCount = countCriteria.FutureValue <long>(); IEnumerable <T> futureElements = elementsCriteria.Future <T>(); Page <T> page = new Page <T>(futureElements.ToList(), pageable, futureTotalCount.Value); return(page); }; return(HibernateTemplate.Execute(finder)); }
public async Task MultipleSubqueriesShouldStayInOrderAsync() { if (!Dialect.SupportsScalarSubSelects) { Assert.Ignore("Dialect does not support scalar sub-select"); } using (ISession s = OpenSession()) using (ITransaction tx = s.BeginTransaction()) { DetachedCriteria dc1 = DetachedCriteria.For(typeof(Person)) .Add(Property.ForName("IQ").Eq(10)) .SetProjection(Property.ForName("Name")); DetachedCriteria dc2 = DetachedCriteria.For(typeof(Person)) .Add(Property.ForName("ShoeSize").Eq(7)) .SetProjection(Projections.Property("Name")); IList list = await(s.CreateCriteria(typeof(Person), "p") .Add(Subqueries.PropertyEq("Name", dc1)) .Add(Restrictions.Not(Subqueries.Eq("Sally", dc2))) .ListAsync()); Assert.AreEqual(1, list.Count); Assert.AreEqual("Joe", ((Person)list[0]).Name); await(tx.CommitAsync()); } }
public IList <PersonRelationship> GetPersonRelationships(Person p) { PersonRelationship rel = null; PersonRelationshipType t = null; var subquery = QueryOver.Of <PersonResponsibility>() .Where(x => x.Person != p) .And(x => !x.Archive) .Select(x => x.Person); return(Session.QueryOver <PersonRelationship>(() => rel) .JoinAlias(() => rel.PersonRelationshipType, () => t, JoinType.InnerJoin) .Where(() => !rel.Archive) .And(Restrictions.Disjunction() .Add(() => rel.SubjectPerson == p) .Add(() => rel.ObjectPerson == p) ) .AndRestrictionOn(() => t.Code).IsIn(new string[] { PersonRelationshipType.CODE_ACTED_TOGETHER_WITH, PersonRelationshipType.CODE_BELONGED_TO_THE_SAME_GROUP_AS, PersonRelationshipType.CODE_FOUGHT_IN_THE_SAME_GROUP_AS, PersonRelationshipType.CODE_FOUGHT_WITH, PersonRelationshipType.CODE_IS_A_SUBORDINATE_OF, PersonRelationshipType.CODE_IS_A_SUPERIOR_TO, PersonRelationshipType.CODE_IS_THE_BODYGUARD_OF, PersonRelationshipType.CODE_IS_THE_DEPUTY_OF, PersonRelationshipType.CODE_PROVIDED_WEAPONS_AND_AMMUNITION_TO }) .And(Restrictions.Disjunction() .Add(Subqueries.WhereProperty(() => rel.SubjectPerson).In(subquery)) .Add(Subqueries.WhereProperty(() => rel.ObjectPerson).In(subquery)) ) .List()); }
public override QueryOver <NodeVersion> VisitBinary(BinaryExpression node) { var left = Visit(node.Left); var right = Visit(node.Right); switch (node.NodeType) { case ExpressionType.And: case ExpressionType.AndAlso: return(QueryOver.Of <NodeVersion>() .Where(Restrictions.Conjunction() .Add(Subqueries.PropertyIn(Projections.Property <NodeVersion>(x => x.Id).PropertyName, left.DetachedCriteria)) .Add(Subqueries.PropertyIn(Projections.Property <NodeVersion>(x => x.Id).PropertyName, right.DetachedCriteria))) .Select(x => x.Id)); case ExpressionType.Or: case ExpressionType.OrElse: { return(QueryOver.Of <NodeVersion>() .Where(Restrictions.Disjunction() .Add(Subqueries.PropertyIn(Projections.Property <NodeVersion>(x => x.Id).PropertyName, left.DetachedCriteria)) .Add(Subqueries.PropertyIn(Projections.Property <NodeVersion>(x => x.Id).PropertyName, right.DetachedCriteria))) .Select(x => x.Id)); } } throw new InvalidOperationException("This provider only supports binary expressions with And, AndAlso, Or, OrElse expression types. ExpressionType was {0}".InvariantFormat(node.NodeType.ToString())); }
public IList <RecipeItem> SearchForRecipeItems(ItemSearchRequest query) { // No idea when recipes were added, the user probably wants owned items only. if (query.RecentOnly) { return(new List <RecipeItem>()); } using (ISession session = SessionCreator.OpenSession()) { using (ITransaction transaction = session.BeginTransaction()) { ICriteria criterias = session.CreateCriteria <RecipeItem>(); if (!string.IsNullOrEmpty(query.Wildcard)) { criterias.Add(Subqueries.PropertyIn("BaseRecord", DetachedCriteria.For <DatabaseItem>() .Add(Restrictions.InsensitiveLike("Name", string.Format("%{0}%", query.Wildcard.Replace(' ', '%')))) .SetProjection(Projections.Property("Record")))); } AddItemSearchCriterias(criterias, query); criterias.Add(Restrictions.Eq("IsHardcore", query.IsHardcore)); IList <RecipeItem> items = criterias.List <RecipeItem>(); return(items); } } }
public QueryOver <Equipment, Equipment> AvailableEquipmentQuery() { Vodovoz.Domain.Orders.Order orderAlias = null; Equipment equipmentAlias = null; WarehouseMovementOperation operationAddAlias = null; OrderEquipment orderEquipmentAlias = null; var equipmentInStockCriterion = Subqueries.IsNotNull( QueryOver.Of <WarehouseMovementOperation>(() => operationAddAlias) .OrderBy(() => operationAddAlias.OperationTime).Desc .Where(() => equipmentAlias.Id == operationAddAlias.Equipment.Id) .Select(op => op.IncomingWarehouse) .Take(1).DetachedCriteria ); var subqueryAllReservedEquipment = QueryOver.Of(() => orderAlias) .Where(() => orderAlias.OrderStatus == OrderStatus.Accepted || orderAlias.OrderStatus == OrderStatus.InTravelList || orderAlias.OrderStatus == OrderStatus.OnLoading) // чтобы не добавить в доп соглашение оборудование добавленное в уже созданный заказ. .JoinAlias(() => orderAlias.OrderEquipments, () => orderEquipmentAlias) .Where(() => orderEquipmentAlias.Direction == Direction.Deliver) .Select(Projections.Property(() => orderEquipmentAlias.Equipment.Id)); return(QueryOver.Of <Equipment>(() => equipmentAlias) .Where(equipmentInStockCriterion) .Where(e => e.AssignedToClient == null) .WithSubquery.WhereProperty(() => equipmentAlias.Id).NotIn(subqueryAllReservedEquipment)); }
private static QueryOver <Person> QuerySearchByFullnameAndParentName(long familyId, string keyword, string parentName) { string[] fullname = Utilities.ConvertFullNameToFamilyNameMiddleNameGivenName(keyword); string[] parentFullname = Utilities.ConvertFullNameToFamilyNameMiddleNameGivenName(parentName); // parent name input is father or mother that is a member of family // search person table for parent name (parent name is in person table) // parentId of child object mapping with a record in person table var parentIdP1SubQuery = QueryOver.Of <Person>().Where( (new LikeExpression("FamilyName", "%" + parentFullname[0] + "%") && (new LikeExpression("MiddleName", "%" + parentFullname[1] + "%") || new NullExpression("MiddleName")) && new LikeExpression("GivenName", "%" + parentFullname[2] + "%")) || (new LikeExpression("MiddleName", "%" + parentFullname[0] + "%") && new LikeExpression("GivenName", "%" + parentFullname[2] + "%")) ).And(p1 => p1.Family.FamilyId == familyId ).JoinQueryOver(p1 => p1.PersonDetail).Where(pd => pd.DeleteFlag == 0 ).Select(Projections.Distinct(Projections.Property <Person>(p1 => p1.InFamilyId))); // parent name input is father or mother that is not a member of family // search partner table for parent name (parent name is in partner table) // parentPartnerId of child object mapping with a record in partner table var parentIdPmSubQuery = QueryOver.Of <Partner>().Where( (new LikeExpression("FamilyName", "%" + parentFullname[0] + "%") && (new LikeExpression("MiddleName", "%" + parentFullname[1] + "%") || new NullExpression("MiddleName")) && new LikeExpression("GivenName", "%" + parentFullname[2] + "%")) || (new LikeExpression("MiddleName", "%" + parentFullname[0] + "%") && new LikeExpression("GivenName", "%" + parentFullname[2] + "%")) ).JoinQueryOver(pm => pm.PartnerDetail).Where(pmd => pmd.DeleteFlag == 0 ).Select(Projections.Distinct(Projections.Property <Partner>(pm => pm.PartnerId))); // parent name input is father or mother that is a member of family // search person table for parent name (parent name is in person table) // parentPartnerId of child object mapping with a record in person table var parentIdP2SubQuery = QueryOver.Of <Person>().Where( (new LikeExpression("FamilyName", "%" + parentFullname[0] + "%") && (new LikeExpression("MiddleName", "%" + parentFullname[1] + "%") || new NullExpression("MiddleName")) && new LikeExpression("GivenName", "%" + parentFullname[2] + "%")) || (new LikeExpression("MiddleName", "%" + parentFullname[0] + "%") && new LikeExpression("GivenName", "%" + parentFullname[2] + "%")) ).And(p2 => p2.Family.FamilyId == familyId ).JoinQueryOver(p2 => p2.PersonDetail).Where(pd => pd.DeleteFlag == 0 ).Select(Projections.Distinct(Projections.Property <Person>(p2 => p2.InFamilyId))); // search parent name in 3 cases // parentId in person or parentPartnerId in partner or parentPartnerId in person var or = Restrictions.Disjunction(); or.Add(Subqueries.WhereProperty <Person>(c => c.ParentId).In(parentIdP1SubQuery)); or.Add(Subqueries.WhereProperty <Person>(c => c.ParentPartnerId).In(parentIdPmSubQuery)); or.Add(Subqueries.WhereProperty <Person>(c => c.ParentPartnerId).In(parentIdP2SubQuery)); return(QueryOver.Of <Person>().Where( (new LikeExpression("FamilyName", "%" + fullname[0] + "%") && (new LikeExpression("MiddleName", "%" + fullname[1] + "%") || new NullExpression("MiddleName")) && new LikeExpression("GivenName", "%" + fullname[2] + "%")) || (new LikeExpression("MiddleName", "%" + fullname[0] + "%") && new LikeExpression("GivenName", "%" + fullname[2] + "%")) ).And(c => c.Family.FamilyId == familyId ).Where(Restrictions.Disjunction().Add(or) ).JoinQueryOver(c => c.PersonDetail).Where(cd => cd.DeleteFlag == 0 )); }
public IList BookingSearch(ICriterion criterion, string customer, int blocked, int pageSize, int pageIndex, Order order) { ICriteria criteria = _session.OpenSession().CreateCriteria(typeof(Booking), "booking"); if (criterion != null) { criteria.Add(criterion); } criteria.CreateAlias("Agency", "agency", JoinType.LeftOuterJoin); if (!string.IsNullOrEmpty(customer)) { DetachedCriteria detached = DetachedCriteria.For(typeof(Booking)) .CreateAlias("Customers", "customer") .SetProjection(Projections.ProjectionList() .Add(Projections.RowCount())) .Add(Property.ForName("Id").EqProperty("booking.Id")) .Add(Expression.Like("customer.Fullname", customer, MatchMode.Anywhere)); ICriterion crit2 = Expression.Like("agency.Name", customer, MatchMode.Anywhere); criteria.Add(Expression.Or(Subqueries.Le(1, detached), crit2)); } criteria.CreateAlias("BookingSale", "bookingsale"); if (blocked > 0) { // Như vậy, bản thân booking đó không được là charter criteria.Add(Expression.Eq("IsCharter", false)); DetachedCriteria detached = DetachedCriteria.For(typeof(Booking)) .SetProjection(Projections.ProjectionList() .Add(Projections.RowCount())) .Add(Property.ForName("StartDate").GeProperty("booking.StartDate")) .Add(Property.ForName("StartDate").LtProperty("booking.EndDate")) .Add(Property.ForName("EndDate").GtProperty("booking.StartDate")) .Add(Property.ForName("EndDate").LeProperty("booking.EndDate")) .Add(Property.ForName("Cruise").EqProperty("booking.Cruise")) .Add(Expression.Eq("IsCharter", true)) .Add(Expression.Eq("Status", StatusType.Approved)); // Nếu trong khoảng trùng ngày có một booking charter đã approved criteria.Add(Subqueries.Le(1, detached)); } if (order != null) { criteria.AddOrder(order); } if (pageSize > 0) { if (pageIndex < 0) { pageIndex = 0; } criteria.SetMaxResults(pageSize); criteria.SetFirstResult(pageIndex * pageSize); } return(criteria.List()); }
public async Task NestedSubqueriesShouldStayInOrderAsync() { if (!Dialect.SupportsScalarSubSelects) { Assert.Ignore("Dialect does not support scalar sub-select"); } using (ISession s = OpenSession()) using (ITransaction tx = s.BeginTransaction()) { DetachedCriteria dc1 = DetachedCriteria.For(typeof(Person)) .Add(Property.ForName("IQ").Eq(40)) .SetProjection(Property.ForName("IQ")); DetachedCriteria dc2 = DetachedCriteria.For(typeof(Person)) .Add(Subqueries.PropertyEq("ShoeSize", dc1)) .SetProjection( new SqlFunctionProjection( "concat", NHibernateUtil.String, Projections.GroupProperty("Name"), new ConstantProjection(" "), Projections.GroupProperty("Name"))); IList list = await(s.CreateCriteria(typeof(Person)) .Add(Subqueries.Eq("Fred Fred", dc2)) .ListAsync()); Assert.AreEqual(5, list.Count); //yeah, it returns all five results. The key is that it didn't crash await(tx.CommitAsync()); } }
public ICriteria GetSearchCriteria(String searchString, ICorePrincipal user, int operationCode) { ICriteria criteria = Session.CreateCriteria <FormLocale>().CreateAlias("Form", "form"); DetachedCriteria filter = DetachedCriteria.For <FormLocale>("filteredLocale").CreateAlias("Form", "filteredForm") .SetProjection(Projections.Id()).SetMaxResults(1).AddOrder(Order.Desc("filteredLocale.Priority")).Add( Restrictions.EqProperty("filteredForm.Id", "form.Id")); criteria.Add(Subqueries.PropertyIn("Id", filter)); //apply permissions criteria var formsCriteria = DetachedCriteria.For <Form>("forms"); var permissionCommonService = ServiceLocator.Current.GetInstance <IPermissionCommonService>(); var permissionCriteria = permissionCommonService.GetPermissionsCriteria(user, operationCode, typeof(Form), "forms.Id", "forms.UserId"); if (permissionCriteria != null) { formsCriteria.Add(permissionCriteria).SetProjection(Projections.Id()); criteria.Add(Subqueries.PropertyIn("form.Id", formsCriteria)); } if (!String.IsNullOrEmpty(searchString)) { criteria.Add(Restrictions.Like("Title", searchString, MatchMode.Anywhere)); } return(criteria.SetCacheable(true)); }
public IList <Event> GetEvents(Person p) { var unknownSubquery = QueryOver.Of <AdminUnknown>() .Where(x => !x.Archive) .Select(x => x.UnknownValue); var locationSubquery = QueryOver.Of <Career>() .Where(x => x.Person == p) .And(x => !x.Archive) .Select(x => x.Location); Career c = null; Location cl = null; var locationTerritorySubquery = QueryOver.Of <Location>(() => cl) .JoinAlias(() => cl.Careers, () => c, JoinType.InnerJoin) .Where(() => c.Person == p) .And(() => !c.Archive) .WithSubquery.WhereProperty(() => cl.LocationName).NotIn(unknownSubquery) .Select(x => x.Territory); Event e = null; Location l = null; return(Session.QueryOver <Event>(() => e) .JoinAlias(() => e.Location, () => l, JoinType.InnerJoin) .Where(() => !e.Archive) .And(() => !l.Archive) .WithSubquery.WhereProperty(() => l.LocationName).NotIn(unknownSubquery) .And(Restrictions.Disjunction() .Add(Subqueries.WhereProperty(() => e.Location).In(locationSubquery)) .Add(Subqueries.WhereProperty(() => l.Territory).In(locationTerritorySubquery)) ) .List <Event>()); }
private ICriteria ListLevelOnePagesCriteria(string search, CatalogPage parent, CategoryPageStatus?status) { ICriteria crit = GetCriteria(); if (!string.IsNullOrEmpty(search)) { Disjunction d = new Disjunction(); d.Add(Expression.InsensitiveLike("Name", search, MatchMode.Anywhere)); d.Add(Expression.InsensitiveLike("Description", search, MatchMode.Anywhere)); d.Add(Expression.InsensitiveLike("NameEnglish", search, MatchMode.Anywhere)); d.Add(Expression.InsensitiveLike("DescripionEnglish", search, MatchMode.Anywhere)); crit.Add(d); } if (parent != null) { crit.Add(Expression.Eq("Parent", parent)); } if (status != null) { crit.Add(Expression.Eq("CategoryPageStatus", status)); } DetachedCriteria levelOneCriteria = DetachedCriteria.For <CatalogPage>().SetProjection(Projections.Property("ID")) .Add(Expression.IsNull("Parent")); ICriterion levelOneSubquery = Subqueries.PropertyIn("Parent", levelOneCriteria); crit.Add(levelOneSubquery); return(crit); }
public int getNotificationCount() { Utility.logFile(Constant.METHOD_ENTER + Utility.getCurrentMethodName(1) + "(NotificationManagerImpl)", null, Constant.LOGTYPE_INFO); DetachedCriteria mainCriteria = DetachedCriteria.For <Entity_Notification>("notification"); mainCriteria.Add(Restrictions.Eq("notification.myConfigObj.currentBatch", Common.loggedUser.myConfigObj.currentBatch)); mainCriteria.Add(Restrictions.Eq("notification.myConfigObj.currentDegree", Common.loggedUser.myConfigObj.currentDegree)); mainCriteria.SetProjection(Projections.Count("notification.notificationId")); DetachedCriteria subCriteria = DetachedCriteria.For <Entity_UserNotification>("userNotification"); subCriteria.Add(Restrictions.EqProperty("userNotification.notificationId", "notification.notificationId")); subCriteria.Add(Restrictions.Eq("userNotification.staffId", Common.loggedUser.staffId)); subCriteria.SetProjection(Projections.Distinct(Projections.Property("userNotification.notificationId"))); mainCriteria.Add(Subqueries.NotExists(subCriteria)); IList list = persistence.findByCriteria(mainCriteria); if (list != null && list.Count > 0) { return(Convert.ToInt32(list[0])); } else { return(0); } }
public void PropertyCriterion() { ICriteria expected = CreateTestCriteria(typeof(Person)) .Add(Subqueries.PropertyEq("Name", DetachedCriteriaName)) .Add(Subqueries.PropertyGe("Age", DetachedCriteriaAge)) .Add(Subqueries.PropertyGt("Age", DetachedCriteriaAge)) .Add(Subqueries.PropertyIn("Name", DetachedCriteriaName)) .Add(Subqueries.PropertyLe("Age", DetachedCriteriaAge)) .Add(Subqueries.PropertyLt("Age", DetachedCriteriaAge)) .Add(Subqueries.PropertyNe("Name", DetachedCriteriaName)) .Add(Subqueries.PropertyNotIn("Name", DetachedCriteriaName)); var actual = CreateTestQueryOver <Person>() .And(Subqueries.WhereProperty <Person>(p => p.Name).Eq(DetachedQueryOverName)) .And(Subqueries.WhereProperty <Person>(p => p.Age).Ge(DetachedQueryOverAge)) .And(Subqueries.WhereProperty <Person>(p => p.Age).Gt(DetachedQueryOverAge)) .And(Subqueries.WhereProperty <Person>(p => p.Name).In(DetachedQueryOverName)) .And(Subqueries.WhereProperty <Person>(p => p.Age).Le(DetachedQueryOverAge)) .And(Subqueries.WhereProperty <Person>(p => p.Age).Lt(DetachedQueryOverAge)) .And(Subqueries.WhereProperty <Person>(p => p.Name).Ne(DetachedQueryOverName)) .And(Subqueries.WhereProperty <Person>(p => p.Name).NotIn(DetachedQueryOverName)); AssertCriteriaAreEqual(expected, actual); }
private void SetCriteria() { DetachedCriteria criteria = DetachedCriteria.For(typeof(InProcessLocationDetail)); criteria.CreateAlias("OrderLocationTransaction", "olt"); criteria.CreateAlias("InProcessLocation", "ip"); criteria.Add(Expression.Eq("ip.Status", BusinessConstants.CODE_MASTER_STATUS_VALUE_CREATE)); criteria.Add(Expression.Eq("ip.Type", BusinessConstants.CODE_MASTER_INPROCESS_LOCATION_TYPE_VALUE_NORMAL)); if (this.IOType == BusinessConstants.IO_TYPE_IN) { DetachedCriteria subCriteria = DetachedCriteria.For(typeof(OrderLocationTransaction)); subCriteria.CreateAlias("OrderDetail", "od"); subCriteria.CreateAlias("od.OrderHead", "oh"); OrderHelper.SetOpenOrderStatusCriteria(subCriteria, "oh.Status"); subCriteria.Add(Expression.Eq("IOType", BusinessConstants.IO_TYPE_IN)); subCriteria.Add(Expression.Eq("Item.Code", this.Item)); subCriteria.Add(Expression.Eq("Location.Code", this.Location)); subCriteria.SetProjection(Projections.ProjectionList().Add(Projections.GroupProperty("OrderDetail.Id"))); criteria.Add(Subqueries.PropertyIn("olt.OrderDetail.Id", subCriteria)); } else { criteria.Add(Expression.Eq("olt.Item.Code", this.Item)); criteria.Add(Expression.Eq("olt.Location.Code", this.Location)); } DetachedCriteria selectCountCriteria = CloneHelper.DeepClone <DetachedCriteria>(criteria); selectCountCriteria.SetProjection(Projections.Count("Id")); this.SetSearchCriteria(criteria, selectCountCriteria); }
private IEnumerable <ViewModels.TranslationsViewModel.TranslationDTO> BuildTranslations(int id, string language) { var me = TranslatorService.FindByEmail(HttpContext.User.Identity.Name); var keys = DbSession.QueryOver <TranslateKey>() .Where(x => x.Set.Id == id) .Future(); var neutralTranslations = DbSession.QueryOver <Translation>() .Where(x => x.Language == LanguageService.GetNeutralLanguage()) .And(x => x.NeedsReviewing == false) .OrderBy(x => x.Votes).Desc .Future(); var leadingTranslations = DbSession.QueryOver <Translation>() .Where(x => x.Language == LanguageService.GetLanguageByIsoCode(language)) .And(x => x.IsPublished) .And(x => x.NeedsReviewing == false) .OrderBy(x => x.Votes).Desc .Future(); var userTranslations = DbSession.QueryOver <Translation>() .Where(x => x.Language == LanguageService.GetLanguageByIsoCode(language)) .And(x => x.Translator == me) .And(x => x.NeedsReviewing == false) .OrderBy(x => x.Votes).Desc .Future(); var dc = DetachedCriteria.For <TranslationVote>() .Add(Restrictions.Eq("Translator", me)) .CreateAlias("Translation", "t") .Add(Restrictions.Eq("t.Language", LanguageService.GetLanguageByIsoCode(language))) .SetProjection(Projections.Property("t.Key.Id")); var votedOnKeys = DbSession.CreateCriteria <TranslateKey>() .Add(Restrictions.Eq("Set.Id", id)) .Add(Subqueries.PropertyIn("Id", dc)) .SetProjection(Projections.Id()) .Future <int>().ToList(); var translations = ( from key in keys let neutralTranslation = neutralTranslations.Where(x => x.Key == key).FirstOrDefault() let leadingTranslation = leadingTranslations.Where(x => x.Key == key).FirstOrDefault() let userTranslation = userTranslations.Where(x => x.Key == key).FirstOrDefault() ?? leadingTranslation select new ViewModels.TranslationsViewModel.TranslationDTO { KeyId = key.Id, Term = neutralTranslation == null ? string.Empty : neutralTranslation.Value, Value = userTranslation == null ? string.Empty : userTranslation.Value, LeadingValue = leadingTranslation == null ? string.Empty : leadingTranslation.Value, Votes = leadingTranslation == null ? 0 : userTranslation.Votes, Voted = votedOnKeys.Contains(key.Id), SetId = id } ).ToList(); return(translations); }
/// <summary> /// Gets all permissions for the specified group /// </summary> /// <param name="group">The group</param> /// <returns></returns> public Permission[] GetPermissionsFor(UsersGroup group) { DetachedCriteria criteria = DetachedCriteria.For <Permission>() .Add(Subqueries.PropertyIn("UsersGroup.Id", SecurityCriterions.AllGroups(group).SetProjection(Projections.Id()))); return(FindResults(criteria)); }
public IList <Party> GetFromParty(string orderType, string userCode, bool includeInactive) { DetachedCriteria criteria = DetachedCriteria.For <Party>(); if (orderType == BusinessConstants.CODE_MASTER_FLOW_TYPE_VALUE_PROCUREMENT || orderType == BusinessConstants.CODE_MASTER_FLOW_TYPE_VALUE_SUBCONCTRACTING) { DetachedCriteria[] pfCrieteria = SecurityHelper.GetSupplierPermissionCriteria(userCode); criteria.Add( Expression.Or( Subqueries.PropertyIn("Code", pfCrieteria[0]), Subqueries.PropertyIn("Code", pfCrieteria[1]) )); } else if (orderType == BusinessConstants.CODE_MASTER_FLOW_TYPE_VALUE_DISTRIBUTION) { DetachedCriteria[] pfCrieteria = SecurityHelper.GetRegionPermissionCriteria(userCode); criteria.Add( Expression.Or( Subqueries.PropertyIn("Code", pfCrieteria[0]), Subqueries.PropertyIn("Code", pfCrieteria[1]) )); } else if (orderType == BusinessConstants.CODE_MASTER_FLOW_TYPE_VALUE_PRODUCTION) { DetachedCriteria[] regionCrieteria = SecurityHelper.GetRegionPermissionCriteria(userCode); criteria.Add( Expression.Or( Subqueries.PropertyIn("Code", regionCrieteria[0]), Subqueries.PropertyIn("Code", regionCrieteria[1]) )); } else if (orderType == BusinessConstants.CODE_MASTER_FLOW_TYPE_VALUE_TRANSFER) { DetachedCriteria[] pfCrieteria = SecurityHelper.GetRegionPermissionCriteria(userCode); criteria.Add( Expression.Or( Subqueries.PropertyIn("Code", pfCrieteria[0]), Subqueries.PropertyIn("Code", pfCrieteria[1]) )); } else if (orderType == BusinessConstants.CODE_MASTER_FLOW_TYPE_VALUE_CUSTOMERGOODS) { DetachedCriteria[] pfCrieteria = SecurityHelper.GetCustomerPermissionCriteria(userCode); criteria.Add( Expression.Or( Subqueries.PropertyIn("Code", pfCrieteria[0]), Subqueries.PropertyIn("Code", pfCrieteria[1]) )); } if (!includeInactive) { criteria.Add(Expression.Eq("IsActive", true)); } return(this.criteriaMgr.FindAll <Party>(criteria)); }
private IEnumerable <TranslationChange> BuildTranslations() { var me = TranslatorService.FindByEmail(HttpContext.User.Identity.Name); var changes = DbSession.CreateCriteria <Translation>() .Add(Restrictions.Eq("IsPublished", false)) .Add(Restrictions.Eq("Translator", me)) .CreateAlias("Key", "k") .CreateAlias("k.Set", "s") .Future <Translation>(); var votedOn = DetachedCriteria.For <TranslationVote>() .Add(Restrictions.Eq("IsPublished", false)) .Add(Restrictions.Eq("Translator", me)) .CreateAlias("Translation", "t") .SetProjection(Projections.Property("t.Id")); var votes = DbSession.CreateCriteria <Translation>() .Add(Subqueries.PropertyIn("Id", votedOn)) .Future <Translation>(); changes = changes.Concat(votes); var neutralTranslations = DbSession.QueryOver <Translation>() .Where(Restrictions.On <Translation>(x => x.Key).IsIn(changes.Select(x => x.Key).ToArray())) .And(x => x.Language == LanguageService.GetNeutralLanguage()) .And(x => x.NeedsReviewing == false) .OrderBy(x => x.Votes).Desc .Future(); var leadingTranslations = DbSession.QueryOver <Translation>() .Where(Restrictions.On <Translation>(x => x.Key).IsIn(changes.Select(x => x.Key).ToArray())) .And(x => x.IsPublished) .And(x => x.NeedsReviewing == false) .OrderBy(x => x.Votes).Desc .Future(); var translations = ( from change in changes let neutralTranslation = neutralTranslations.Where(x => x.Key == change.Key).FirstOrDefault() let leadingTranslation = leadingTranslations.Where(x => x.Key == change.Key && x.Language == change.Language).FirstOrDefault() let voted = votes.Any(x => x.Key == neutralTranslation.Key) select new TranslationChange { KeyId = change.Key.Id, SetId = change.Key.Set.Id, SetName = change.Key.Set.Name, Language = change.Language.IsoCode, Term = neutralTranslation.Value, LeadingValue = leadingTranslation == null ? (voted ? change.Value : string.Empty) : leadingTranslation.Value, Value = change.Value, Voted = voted } ).ToList(); return(translations); }
/// <summary> /// Gets the permissions for the specified user /// </summary> /// <param name="user">The user.</param> /// <returns></returns> public Permission[] GetPermissionsFor(IUser user) { DetachedCriteria criteria = DetachedCriteria.For <Permission>() .Add(Expression.Eq("User", user) || Subqueries.PropertyIn("UsersGroup.Id", SecurityCriterions.AllGroups(user).SetProjection(Projections.Id()))); return(FindResults(criteria)); }
/// <summary> /// Finds all active device types. /// </summary> /// <returns></returns> public override IQueryable <TipoDispositivo> FindAll() { var dc = getBaseDetachedCriteria(0); var c = Session.CreateCriteria <TipoDispositivo>() .Add(Subqueries.PropertyIn("Id", dc)); return(c.Future <TipoDispositivo>().AsQueryable()); }
public IEnumerable <Empresa> FindList() { var dc = DetachedCriteria.For <Empresa>().Add(Restrictions.Eq("Baja", false)).SetProjection(Projections.Property("Id")); var crit = Session.CreateCriteria <Empresa>().Add(Subqueries.In("Id", dc)).SetCacheable(true); var result = crit.List <Empresa>(); return(result); }