public void ThreeLevelSubquery() { if (!Dialect.SupportsScalarSubSelects) { Assert.Ignore("Dialect does not support scalar sub-select"); } Person p = null; var detachedCriteria2 = DetachedCriteria.For <Person>("vf_inner_2") .SetProjection(Projections.Id()) .Add(Restrictions.Eq($@"mk.{nameof(p.Age)}", 20)) .Add(Restrictions.EqProperty("vf_inner_2.Id", "vf_inner.Id")); var detachedCriteria1 = DetachedCriteria.For <Person>("vf_inner") .SetProjection(Projections.Id()) .Add(Subqueries.Exists(detachedCriteria2)) .Add(Restrictions.EqProperty("vf_inner.Id", "vf.Id")); using (var s = OpenSession()) { s.CreateCriteria <Person>("vf") .CreateAlias($"vf.{nameof(p.Father)}", "mk") .AddOrder(Order.Asc(Projections.SubQuery(detachedCriteria1))) .List <Person>(); } }
/// <summary> /// Compiles this <see cref="IsExpression" /> into a <see cref="ICriterion" /> instance. /// </summary> /// <param name="subquery"> /// The subquery to filter. /// </param> /// <returns> /// The compiled <see cref="ICriterion" /> instance. /// </returns> public virtual ICriterion Compile(DetachedCriteria subquery) { if (Negated) { return(Subqueries.Exists(subquery)); } return(Subqueries.NotExists(subquery)); }
public void Can_Get_Company_with_Identification_1() { DetachedCriteria detachedCriteria = DetachedCriteria .For <CompanyIdentification>() .Add(Property.ForName("Identification").Eq("1")) .SetProjection(Projections.Id()); Company company = Repository.Get(qry => qry.Add(Subqueries.Exists(detachedCriteria))); Assert.IsNotNull(company); Assert.IsFalse(NHibernateUtil.IsInitialized(company.Identifications)); }
private ICriteria GetEvents(Int32 top, DetachedCriteria dc, Order order, IProjection p, bool existsWithDate) { dc.Add(Restrictions.EqProperty("lm.Id", "dlm.Id")); if (existsWithDate) { dc.Add(Restrictions.EqProperty("lm.Fecha", "dlm.Fecha")); } dc.SetProjection(Projections.Property("dlm.Id")); return(GetEvents(top, Subqueries.Exists(dc), order, p)); }
// TODO: Devuelve 1? es el correcto? public virtual Empleado FindEmpleadoByDevice(Dispositivo dispositivo) { var dc = DetachedCriteria.For <Empleado>("de") .Add(Restrictions.Eq("Dispositivo", dispositivo)) .Add(Restrictions.EqProperty("de.Id", "e.Id")) .SetProjection(Projections.Property("Id")); var crit = Session.CreateCriteria <Empleado>("e") .Add(Subqueries.Exists(dc)) .SetMaxResults(1); return(crit.UniqueResult <Empleado>()); }
public AbstractCriterion GetPermissionsCriteria(ICorePrincipal user, int operationCode, Type permissibleObjectType, String permissibleIdPropertyName, String permissibleOwnerPropertyName) { if (user != null) { if (user.IsInRole(SystemRole.Administrator.ToString())) { return(null); } var rolesSubQuery = DetachedCriteria.For <Role>() .CreateAlias("Users", "user") .Add(Restrictions.Eq("user.id", user.PrincipalId)) .SetProjection(Projections.Id()); var userUserGroupsSubQuery = DetachedCriteria.For <UserGroup>() .CreateAlias("Users", "userGroupUser", JoinType.LeftOuterJoin) .Add(Restrictions.Eq("userGroupUser.id", user.PrincipalId)) .SetProjection(Projections.Id()); var userGroupsRolesSubQuery = DetachedCriteria.For <Role>() .CreateAlias("UserGroups", "userGroup", JoinType.LeftOuterJoin) .Add(Subqueries.PropertyIn("userGroup.id", userUserGroupsSubQuery)) .SetProjection(Projections.Id()); var permissionsSubQuery = DetachedCriteria.For <Permission>() .Add(Restrictions.EqProperty("EntityId", permissibleIdPropertyName)).CreateAlias("EntityType", "et").Add(Restrictions.Eq("et.Name", PermissionsHelper.GetEntityType(permissibleObjectType))). Add(Restrictions.Or(Restrictions.Or( Restrictions.Or(Subqueries.PropertyIn("Role.Id", rolesSubQuery), Subqueries.PropertyIn("Role.Id", userGroupsRolesSubQuery)), Restrictions.Eq("Role.Id", (Int64)SystemRole.User)), !String.IsNullOrEmpty(permissibleOwnerPropertyName) ? Restrictions.And(Restrictions.IsNotNull(permissibleOwnerPropertyName), Restrictions.And(Restrictions.Eq(permissibleOwnerPropertyName, user.PrincipalId), Restrictions.Eq("Role.Id", (Int64)SystemRole.Owner))) : null )).Add( Restrictions.Eq(Projections.SqlProjection(String.Format("Permissions & {0} as result", operationCode), new[] { "result" }, new IType[] { NHibernateUtil.Int32 }), operationCode)) .SetProjection(Projections.Id()); return(Subqueries.Exists(permissionsSubQuery)); } else { var permissionsSubQuery = DetachedCriteria.For <Permission>() .Add(Restrictions.EqProperty("EntityId", permissibleIdPropertyName)).CreateAlias("EntityType", "et").Add(Restrictions.Eq("et.Name", PermissionsHelper.GetEntityType(permissibleObjectType))). Add(Restrictions.Eq("Role.Id", (Int64)SystemRole.Guest)).Add( Restrictions.Eq(Projections.SqlProjection(String.Format("Permissions & {0} as result", operationCode), new[] { "result" }, new IType[] { NHibernateUtil.Int32 }), operationCode)) .SetProjection(Projections.Id()); return(Subqueries.Exists(permissionsSubQuery)); } }
public void UntypedSubqueriesCriterion() { ICriteria expected = CreateTestCriteria(typeof(Person)) .Add(Subqueries.Exists(DetachedCriteriaChild)) .Add(Subqueries.NotExists(DetachedCriteriaChild)); var actual = CreateTestQueryOver <Person>() .And(Subqueries.WhereExists(DetachedQueryOverChild)) .And(Subqueries.WhereNotExists(DetachedQueryOverChild)); AssertCriteriaAreEqual(expected, actual); }
private ICriteria GetCriteria(int top, DetachedCriteria dc, Order order) { var c = Session.CreateCriteria <ViajeDistribucion>("vd") .Add(Subqueries.Exists(dc)); if (top > 0) { c.SetMaxResults(top); } if (order != null) { c.AddOrder(order); } return(c); }
public async Task CanQueryBlogByItsPostsAsync() { DetachedCriteria dc = DetachedCriteria.For(typeof(Post), "posts") .SetProjection(Property.ForName("id")) .Add(Expression.Eq("id", post1.PostId)) .Add(Property.ForName("posts.Blog.id").EqProperty("blog.id")); using (ISession s = Sfi.OpenSession()) { IList list = await(s.CreateCriteria(typeof(Blog), "blog") .Add(Subqueries.Exists(dc)) .ListAsync()); Assert.AreEqual(1, list.Count); } }
/// <summary> /// /// </summary> /// <param name="stack"></param> /// <returns></returns> internal override WalkedToken Walk(WalkedToken left, WalkedToken right) { if (left.TokenType != TokenType.PropertyPath) { throw new ApplicationException("Cannot apply any to token of type " + left.TokenType); } //now create the criterion DetachedCriteria d = DetachedCriteria.For(rootCriteria.CriteriaClass) .SetProjection(Projections.Id()) .Add(Expression.IsNotEmpty(left.GetValue <String>())) .Add(right.Criterion); return(WalkedToken.FromCriterion(Subqueries.Exists(d))); //return WalkedToken.FromCriterion(Expression.IsNotEmpty(token.GetValue<String>())); }
public async Task ComplexSubQuery_QueryingByGrandChildrenAsync() { DetachedCriteria comment = DetachedCriteria.For(typeof(Comment), "comment") .SetProjection(Property.ForName("id")) .Add(Property.ForName("Post.id").EqProperty("post.id")) .Add(Expression.Eq("Text", "foo")); using (ISession s = OpenSession()) { DetachedCriteria dc = DetachedCriteria.For(typeof(Blog)) .CreateCriteria("Posts", "post") .Add(Subqueries.Exists(comment)); IList list = await(dc.GetExecutableCriteria(s).ListAsync()); Assert.AreEqual(1, list.Count); } }
private ICriteria GetEntregaDistribucionCriteria(int top, DetachedCriteria dc, Order order) { var crit = Session.CreateCriteria <EntregaDistribucion>("ed") .Add(Subqueries.Exists(dc)); if (top > 0) { crit.SetMaxResults(top); } if (order != null) { crit.AddOrder(order); } return(crit); }
private ICriteria GetMensajeCriteria(int top, DetachedCriteria dc, Order order) { var crit = Session.CreateCriteria <Mensaje>("m") .Add(Subqueries.Exists(dc)); if (top > 0) { crit.SetMaxResults(top); } if (order != null) { crit.AddOrder(order); } return(crit); }
private ICriteria GetDatamartCriteria(int top, DetachedCriteria dc, Order order) { ICriteria c = Session.CreateCriteria <Datamart>("dm"); if (dc != null) { c.Add(Subqueries.Exists(dc)); } if (top > 0) { c.SetMaxResults(top); } if (order != null) { c.AddOrder(order); } return(c); }
/// <summary> /// Vengono filtrate le workflow activity ai quali l'utente ha diritto: /// </summary> private IQueryOver <WorkflowActivity, WorkflowActivity> FilterByUserPermission(IQueryOver <WorkflowActivity, WorkflowActivity> queryOver) { try { if (!string.IsNullOrEmpty(_currentUserName)) { DetachedCriteria dCriteria = DetachedCriteria.For <WorkflowAuthorization>().Add( Restrictions.Where <WorkflowAuthorization>(wa => wa.Account == _currentUserName && wa.WorkflowActivity.Id == workflowActivityAlias.Id)) .SetProjection(Projections.Property <WorkflowAuthorization>(wa => wa.Id)); queryOver.And(Restrictions.Or( Restrictions.Where <WorkflowActivity>(wa => wa.RegistrationUser == RequestorUser), Subqueries.Exists(dCriteria))); } } catch (Exception ex) { throw new DocSuiteException("Errore in FilterByUserPermission", ex); } return(queryOver); }
public IDictionary <string, IList <string> > getDegreeWithBatch() { Utility.logFile(Constant.METHOD_ENTER + Utility.getCurrentMethodName(1) + "(StudentManagerImpl)", null, Constant.LOGTYPE_INFO); DetachedCriteria subCriteria = DetachedCriteria.For <Entity_Student>(); subCriteria.SetProjection(Projections.Distinct(Projections.Property("myConfigObj.currentBatch"))); DetachedCriteria mainCriteria = DetachedCriteria.For <Entity_Student>(); ProjectionList projectionList = Projections.ProjectionList(); projectionList.Add(Projections.Distinct(Projections.Property("myConfigObj.currentDegree"))); projectionList.Add(Projections.Property("myConfigObj.currentBatch")); mainCriteria.SetProjection(projectionList); mainCriteria.Add(Subqueries.Exists(subCriteria)); IList resultList = persistence.findByCriteria(mainCriteria); if (resultList != null && resultList.Count > 0) { IList <string> degreeList; IDictionary <string, IList <string> > batchDegreeMap = new Dictionary <string, IList <string> >(); foreach (Object[] arr in resultList) { string key = arr[1].ToString(); string value = arr[0].ToString(); if (batchDegreeMap.ContainsKey(key)) { batchDegreeMap[key].Add(value); } else { degreeList = new List <string>(); degreeList.Add(value); batchDegreeMap.Add(key, degreeList); } } return(batchDegreeMap); } return(null); }
public void CriteriaParameterOrder() { ISession session = OpenSession(); session.EnableFilter("EnabledObjects").SetParameter("Enabled", true); DetachedCriteria detached = DetachedCriteria.For(typeof(B), "b"); detached.Add(Restrictions.LtProperty("a.ValueA", "b.ValueB")).Add(Restrictions.Gt("ValueB", 5)).SetProjection( Projections.Property("ValueB")); ICriteria crit = session.CreateCriteria(typeof(A), "a"); crit.Add(Restrictions.Lt("ValueA", 6)).Add(Subqueries.Exists(detached)); // // Query: // {select a0_.id as id0_, a0_.val_a as val2_0_, a0_.enabled as enabled0_ // from table_a a0_ // where a0_.enabled = ? and ((a0_.val_a<? )and // (exists(select b1_.val_b // from table_b b1_ // where b1_.enabled = ? and // ((a0_.val_a<b1_.val_b )and(b1_.val_b>? )))))} // // Parameter: // 1) "this_.enabled = :EnabledObjects.Enabled" [filter #1] // 2) "this_.val_a < (?)" [positional #1] // 3) "this_0_.enabled = :EnabledObjects.Enabled" [filter #2] // 4) "this_0_.val_b > (?)" [positional #2] // // => OK, parameter are in correct order: filter #1, pos #1, filter #2, pos #2 // IList <A> result = crit.List <A>(); Assert.AreEqual(1, result.Count); }
public static void UpdateCounters(TranslateSet set, Language language) { var keyCount = set.TranslateKeys.Count; var keys = DetachedCriteria.For <TranslateKey>("s") .Add(Restrictions.Eq("Set", set)) .Add(Restrictions.EqProperty("s.Id", "t.Key.Id")) .SetProjection(Projections.Id()); var translated = Global.CurrentSession.CreateCriteria <Translation>("t") .Add(Restrictions.Eq("IsPublished", true)) .Add(Restrictions.Eq("NeedsReviewing", false)) .Add(Subqueries.Exists(keys)) .Add(Restrictions.Eq("Language", language)) .SetProjection(Projections.RowCount()) .UniqueResult <int>(); set.NeedsTranslations = keyCount - translated; set.Reviewed = translated; Global.CurrentSession.Update(set); }
public IDictionary <string, int> getStudentBranchData() { Utility.logFile(Constant.METHOD_ENTER + Utility.getCurrentMethodName(1) + "(StatisticsManagerImpl)", null, Constant.LOGTYPE_INFO); DetachedCriteria subCriteria = DetachedCriteria.For <Entity_PlacementDetails>("placedObj"); subCriteria.Add(Restrictions.EqProperty("placedObj.studentId", "student.studentId")); subCriteria.SetProjection(Projections.Distinct(Projections.Property("placedObj.studentId"))); DetachedCriteria mainCriteria = DetachedCriteria.For <Entity_Student>("student"); ProjectionList projectionList = Projections.ProjectionList(); projectionList.Add(Projections.GroupProperty(Projections.Property("student.branch"))); projectionList.Add(Projections.Count(Projections.Property("student.branch"))); mainCriteria.SetProjection(projectionList); mainCriteria.Add(Subqueries.Exists(subCriteria)); mainCriteria.Add(Restrictions.Eq("student.myConfigObj.currentBatch", Common.loggedUser.myConfigObj.currentBatch)); mainCriteria.Add(Restrictions.Eq("student.myConfigObj.currentDegree", Common.loggedUser.myConfigObj.currentDegree)); mainCriteria.AddOrder(Order.Asc("student.branch")); IList list = persistence.findByCriteria(mainCriteria); if (list != null && list.Count > 0) { IDictionary <string, int> resultMap = new Dictionary <string, int>(); foreach (Object[] item in list) { resultMap.Add(item[0].ToString(), Convert.ToInt32(item[1])); } return(resultMap); } else { return(null); } }
private ICriterion CreateAnyOrAllCriterion(CollectionMethod method, ResolvedMemberExpression resolvedMember, LambdaExpression lambdaExpression) { Require.That(method.MethodType == MethodType.Any || method.MethodType == MethodType.All, "Invalid method type", "method"); // Resolved member's name may contain multiple dots if it's inside a component (i.e. 'root.Component.Collection') int p = resolvedMember.Member.IndexOf('.'); if (p == -1) { throw new ODataException(string.Format("Member '{0}' must have an alias.", resolvedMember.Member)); } var collectionHolderAliasName = resolvedMember.Member.Substring(0, p); var collectionMemberName = resolvedMember.Member.Substring(p + 1); Alias collectionHolderAlias; _context.AliasesByName.TryGetValue(collectionHolderAliasName, out collectionHolderAlias); if (collectionHolderAlias == null) { throw new ODataException(string.Format("Unknown alias '{0}'.", collectionHolderAliasName)); } var subCriteriaAlias = _context.CreateUniqueAliasName(); var detachedCriteria = DetachedCriteria.For(collectionHolderAlias.ReturnedType, subCriteriaAlias); MappedClassMetadata metadata; _context.SessionFactoryContext.MappedClassMetadata.TryGetValue(collectionHolderAlias.ReturnedType, out metadata); if (metadata == null) { throw new ODataException(string.Format("The type '{0}' isn't a NHibernate-mapped class.", collectionHolderAlias.ReturnedType.FullName)); } if (metadata.IdentifierPropertyName == null) { throw new ODataException(string.Format("The type '{0}' doesn't have an identifier property.", collectionHolderAlias.ReturnedType.FullName)); } detachedCriteria.Add(Restrictions.EqProperty( subCriteriaAlias + "." + metadata.IdentifierPropertyName, collectionHolderAliasName + "." + metadata.IdentifierPropertyName )); var lambdaAlias = _context.CreateUniqueAliasName(); System.Type itemType; if (resolvedMember.ReturnedType == null || (itemType = TypeUtil.TryGetCollectionItemType(resolvedMember.ReturnedType)) == null) { throw new ODataException("Cannot get collection item type"); } _context.AddAlias(new Alias(lambdaAlias, string.Empty, itemType)); // The inner joined alias to collection items must be created in any case (whether the lambda expression is specified or not) detachedCriteria.CreateAlias(subCriteriaAlias + "." + collectionMemberName, lambdaAlias, JoinType.InnerJoin); detachedCriteria.SetProjection(Projections.Constant(1)); if (lambdaExpression != null) { if (method.MethodType == MethodType.All) { lambdaExpression = (LambdaExpression)InverseVisitor.Invert(lambdaExpression); } _context.PushLambdaContext(lambdaExpression.ParameterName, itemType, lambdaAlias); try { var lambdaNormalizeVisitor = new AliasingNormalizeVisitor( _context, collectionHolderAlias.ReturnedType, collectionHolderAliasName ); var bodyExpression = lambdaExpression.Body.Visit(lambdaNormalizeVisitor); var criterion = bodyExpression.Visit(new CriterionVisitor(_context)); if (criterion != null) { detachedCriteria.Add(criterion); foreach (var alias in lambdaNormalizeVisitor.Aliases.Values) { detachedCriteria.CreateAlias(alias.AssociationPath, alias.Name, JoinType.LeftOuterJoin); } } } finally { _context.PopLambdaContext(); } } if (method.MethodType == MethodType.Any) { return(Subqueries.Exists(detachedCriteria)); } else { return(Subqueries.NotExists(detachedCriteria)); } }
/// <summary> /// Add an Exists subquery criterion /// </summary> public TReturn WhereExists <U>(QueryOver <U> detachedQuery) { root.And(Subqueries.Exists(detachedQuery.DetachedCriteria)); return(root); }
public void SubqueriesExpressions() { DetachedCriteria dc = DetachedCriteria.For(typeof(Student)) .Add(Expression.Eq("Name", "Gavin King")); ICriterion c = Subqueries.Eq("Gavin King", dc); NHAssert.IsSerializable(c); c = Subqueries.EqAll("Gavin King", dc); NHAssert.IsSerializable(c); c = Subqueries.Exists(dc); NHAssert.IsSerializable(c); c = Subqueries.Ge("Gavin King", dc); NHAssert.IsSerializable(c); c = Subqueries.GeAll("Gavin King", dc); NHAssert.IsSerializable(c); c = Subqueries.GeSome("Gavin King", dc); NHAssert.IsSerializable(c); c = Subqueries.Gt("Gavin King", dc); NHAssert.IsSerializable(c); c = Subqueries.GtAll("Gavin King", dc); NHAssert.IsSerializable(c); c = Subqueries.GtSome("Gavin King", dc); NHAssert.IsSerializable(c); c = Subqueries.In("Gavin King", dc); NHAssert.IsSerializable(c); c = Subqueries.Le("Gavin King", dc); NHAssert.IsSerializable(c); c = Subqueries.LeAll("Gavin King", dc); NHAssert.IsSerializable(c); c = Subqueries.LeSome("Gavin King", dc); NHAssert.IsSerializable(c); c = Subqueries.Lt("Gavin King", dc); NHAssert.IsSerializable(c); c = Subqueries.LtAll("Gavin King", dc); NHAssert.IsSerializable(c); c = Subqueries.LtSome("Gavin King", dc); NHAssert.IsSerializable(c); c = Subqueries.Ne("Gavin King", dc); NHAssert.IsSerializable(c); c = Subqueries.NotExists(dc); NHAssert.IsSerializable(c); c = Subqueries.NotIn("Gavin King", dc); NHAssert.IsSerializable(c); c = Subqueries.PropertyEq("Name", dc); NHAssert.IsSerializable(c); c = Subqueries.PropertyEqAll("Name", dc); NHAssert.IsSerializable(c); c = Subqueries.PropertyGe("Name", dc); NHAssert.IsSerializable(c); c = Subqueries.PropertyGeAll("Name", dc); NHAssert.IsSerializable(c); c = Subqueries.PropertyGeSome("Name", dc); NHAssert.IsSerializable(c); c = Subqueries.PropertyGt("Name", dc); NHAssert.IsSerializable(c); c = Subqueries.PropertyGtAll("Name", dc); NHAssert.IsSerializable(c); c = Subqueries.PropertyGtSome("Name", dc); NHAssert.IsSerializable(c); c = Subqueries.PropertyIn("Name", dc); NHAssert.IsSerializable(c); c = Subqueries.PropertyLe("Name", dc); NHAssert.IsSerializable(c); c = Subqueries.PropertyLeAll("Name", dc); NHAssert.IsSerializable(c); c = Subqueries.PropertyLeSome("Name", dc); NHAssert.IsSerializable(c); c = Subqueries.PropertyLt("Name", dc); NHAssert.IsSerializable(c); c = Subqueries.PropertyLtAll("Name", dc); NHAssert.IsSerializable(c); c = Subqueries.PropertyLtSome("Name", dc); NHAssert.IsSerializable(c); c = Subqueries.PropertyNe("Name", dc); NHAssert.IsSerializable(c); c = Subqueries.PropertyNotIn("Name", dc); NHAssert.IsSerializable(c); }
private ICriteria InternalGetLastNPositionsBetweenDates(int?vehicleId, int?n, DateTime?dateFrom, DateTime?dateTo, int?maxMonths, Order order, DateTime?dateFromReceived, DateTime?dateToReceived) { if (maxMonths != null) { var limite = DateTime.UtcNow.AddMonths(-maxMonths.Value); dateFrom = dateFrom == null ? limite : (dateFrom.Value < limite ? limite : dateFrom.Value); if (dateFromReceived != null || dateToReceived != null) { var limiteReceived = DateTime.UtcNow.AddMonths(-maxMonths.Value); dateFromReceived = dateFromReceived == null ? limiteReceived : (dateFromReceived.Value < limiteReceived ? limiteReceived : dateFromReceived.Value); } } var dc = DetachedCriteria.For <LogPosicion>("dlp") .SetProjection(Projections.Property("Id")); if (vehicleId != null) { dc.Add(Restrictions.Eq("Coche.Id", vehicleId)); } if (dateFrom != null && dateTo != null) { dc.Add(Restrictions.Between("dlp.FechaMensaje", dateFrom, dateTo)); } else { if (dateFrom != null) { dc.Add(Restrictions.Ge("dlp.FechaMensaje", dateFrom)); } else if (dateTo != null) { dc.Add(Restrictions.Le("dlp.FechaMensaje", dateTo)); } } if (dateFromReceived != null || dateToReceived != null) { dc.Add(dateToReceived != null ? Restrictions.Between("dlp.FechaRecepcion", dateFromReceived, dateToReceived) : Restrictions.Ge("dlp.FechaRecepcion", dateFromReceived)); } else { dc.Add(Restrictions.EqProperty("dlp.FechaMensaje", "lp.FechaMensaje")); } dc.Add(Restrictions.EqProperty("dlp.Id", "lp.Id")); var rc = Session.CreateCriteria <LogPosicion>("lp") .Add(Subqueries.Exists(dc)); if (n != null) { rc.SetMaxResults(n.Value); } if (order != null) { rc.AddOrder(order); } return(rc); }
public async Task CriteriaSubqueryWithFiltersAsync() { //~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ // Criteria-subquery test //~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ log.Info("Starting Criteria-subquery filter tests"); using (var session = OpenSession()) { session.EnableFilter("region").SetParameter("region", "APAC"); log.Info("Criteria query against Department with a subquery on Salesperson in the APAC reqion..."); var salespersonSubquery = DetachedCriteria.For <Salesperson>() .Add(Restrictions.Eq("Name", "steve")) .SetProjection(Property.ForName("Department")); var departmentsQuery = session.CreateCriteria <Department>(). Add(Subqueries.PropertyIn("Id", salespersonSubquery)); var departments = await(departmentsQuery.ListAsync <Department>()); Assert.That(departments.Count, Is.EqualTo(1), "Incorrect department count"); log.Info("Criteria query against Department with a subquery on Salesperson in the FooBar reqion..."); session.EnableFilter("region").SetParameter("region", "Foobar"); departments = await(departmentsQuery.ListAsync <Department>()); Assert.That(departments.Count, Is.EqualTo(0), "Incorrect department count"); log.Info("Criteria query against Order with a subquery for line items with a subquery on product and sold by a given sales person..."); session.EnableFilter("region").SetParameter("region", "APAC"); var lineItemSubquery = DetachedCriteria.For <LineItem>() .Add(Restrictions.Ge("Quantity", 1L)) .CreateCriteria("Product") .Add(Restrictions.Eq("Name", "Acme Hair Gel")) .SetProjection(Property.ForName("Id")); var orders = await(session.CreateCriteria <Order>() .Add(Subqueries.Exists(lineItemSubquery)) .Add(Restrictions.Eq("Buyer", "gavin")) .ListAsync <Order>()); Assert.That(orders.Count, Is.EqualTo(1), "Incorrect orders count"); log.Info("query against Order with a subquery for line items with a subquery line items where the product name is Acme Hair Gel and the quantity is greater than 1 in a given region and the product is effective as of last month"); session.EnableFilter("region").SetParameter("region", "APAC"); session.EnableFilter("effectiveDate").SetParameter("asOfDate", testData.lastMonth.Date); var productSubquery = DetachedCriteria.For <Product>() . Add(Restrictions.Eq("Name", "Acme Hair Gel")) .SetProjection(Property.ForName("id")); lineItemSubquery = DetachedCriteria.For <LineItem>() .Add(Restrictions.Ge("Quantity", 1L)) .CreateCriteria("Product") .Add(Subqueries.PropertyIn("Id", productSubquery)) .SetProjection(Property.ForName("Id")); orders = await(session .CreateCriteria <Order>() .Add(Subqueries.Exists(lineItemSubquery)) .Add(Restrictions.Eq("Buyer", "gavin")) .ListAsync <Order>()); Assert.That(orders.Count, Is.EqualTo(1), "Incorrect orders count"); log.Info("query against Order with a subquery for line items with a subquery line items where the product name is Acme Hair Gel and the quantity is greater than 1 in a given region and the product is effective as of 4 months ago"); session.EnableFilter("region").SetParameter("region", "APAC"); session.EnableFilter("effectiveDate").SetParameter("asOfDate", testData.fourMonthsAgo.Date); orders = await(session.CreateCriteria <Order>() .Add(Subqueries.Exists(lineItemSubquery)) .Add(Restrictions.Eq("Buyer", "gavin")) .ListAsync <Order>()); Assert.That(orders.Count, Is.EqualTo(0), "Incorrect orders count"); session.Close(); } }
public IList <T> Find <T>(ISession session) { var criteria = DetachedCriteria.For <T>() .CreateAlias("Payer", "p", JoinType.InnerJoin); if (Year != null && Interval != null) { criteria.Add(Expression.Eq("Period", new Period(Year.Value, Interval.Value))); } else if (Year != null) { criteria.Add(Expression.Sql("{alias}.Period like " + String.Format("'{0}-%'", Year))); } else if (Interval != null) { criteria.Add(Expression.Sql("{alias}.Period like " + String.Format("'%-{0}'", (int)Interval))); } if (Region != null) { criteria.Add(Subqueries.Exists(DetachedCriteria.For <Client>() .SetProjection(Property.ForName("Id")) .CreateAlias("Payers", "pc") .Add(Expression.Eq("HomeRegion", Region)) .Add(Expression.EqProperty("pc.Id", "p.Id")))); } if (Recipient != null) { criteria.Add(Expression.Eq("Recipient", Recipient)); } if (!String.IsNullOrEmpty(SearchText)) { var parts = SearchText.Split(new[] { ',' }, StringSplitOptions.RemoveEmptyEntries); var ids = parts .Select(p => { uint id; uint.TryParse(p, out id); return(id); }) .Where(p => p > 0) .ToArray(); if (ids.Length == parts.Length) { criteria.Add(FindActInvoiceIfIds ? Expression.In("Id", ids) : Expression.In("p.Id", ids)); } else { criteria.Add(Expression.Like("p.Name", SearchText, MatchMode.Anywhere)); } } if (CreatedOn != null) { criteria.Add(Expression.Ge("CreatedOn", CreatedOn)); } var items = Find <T>(criteria); Count = RowsCount; Sum = criteria.SetProjection(Projections.Sum("Sum")) .GetExecutableCriteria(session) .UniqueResult <decimal>(); return(items); }
protected DetachedCriteria CreateCompositeSiteCriteria( string stateFilter, string countyFilter, string siteFilter, string botanicalNameFilter, string commonNameFilter, string botanicalName, string commonName, int?stateId, int?siteId, int?treeId) => CreateSiteCriteria(stateFilter, countyFilter, stateId, siteFilter, siteId) .Add(Subqueries.Exists(CreateTreeCriteria(botanicalNameFilter, commonNameFilter, botanicalName, commonName, treeId) .Add(Restrictions.EqProperty($"tree.{nameof(Tree.Site)}.{nameof(Site.Id)}", $"site.{nameof(Site.Id)}")) .SetProjection(Projections.Id())));