public void CriteriaControl() { using (var testData = new TestData(this)) { testData.Prepare(); // the subquery... var subquery = DetachedCriteria .For <Salesperson>() .SetProjection(Property.ForName("Name")); using (var session = OpenSession()) using (var transaction = session.BeginTransaction()) { session.EnableFilter("fulfilledOrders").SetParameter("asOfDate", testData.lastMonth.Date); session.EnableFilter("regionlist").SetParameter("regions", "APAC"); var result = session .CreateCriteria <Order>() .Add(Subqueries.In("steve", subquery)) .List(); Assert.That(result.Count, Is.EqualTo(1)); transaction.Commit(); } } }
public void ValueCriterion() { ICriteria expected = CreateTestCriteria(typeof(Person)) .Add(Subqueries.Eq("Name", DetachedCriteriaName)) .Add(Subqueries.Ge("Age", DetachedCriteriaAge)) .Add(Subqueries.Gt("Age", DetachedCriteriaAge)) .Add(Subqueries.In("Name", DetachedCriteriaName)) .Add(Subqueries.Le("Age", DetachedCriteriaAge)) .Add(Subqueries.Lt("Age", DetachedCriteriaAge)) .Add(Subqueries.Ne("Name", DetachedCriteriaName)) .Add(Subqueries.NotIn("Name", DetachedCriteriaName)); var actual = CreateTestQueryOver <Person>() .And(Subqueries.WhereValue("Name").Eq(DetachedQueryOverName)) .And(Subqueries.WhereValue("Age").Ge(DetachedQueryOverAge)) .And(Subqueries.WhereValue("Age").Gt(DetachedQueryOverAge)) .And(Subqueries.WhereValue("Name").In(DetachedQueryOverName)) .And(Subqueries.WhereValue("Age").Le(DetachedQueryOverAge)) .And(Subqueries.WhereValue("Age").Lt(DetachedQueryOverAge)) .And(Subqueries.WhereValue("Name").Ne(DetachedQueryOverName)) .And(Subqueries.WhereValue("Name").NotIn(DetachedQueryOverName)); AssertCriteriaAreEqual(expected, actual); }
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); }
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); }
public FlowDetail GetFlowDetailByItem(string flowCode, string itemCode, string locationFromCode, string locationToCode) { Flow flow = flowDao.LoadFlow(flowCode); FlowDetail flowDetail = null; DetachedCriteria criteria = DetachedCriteria.For(typeof(FlowDetail)); criteria.CreateAlias("Flow", "fl"); criteria.CreateAlias("fl.LocationFrom", "lf"); criteria.CreateAlias("fl.LocationTo", "lt"); criteria.Add(Expression.Eq("fl.Code", flowCode)); criteria.Add(Expression.Eq("Item.Code", itemCode)); if (locationFromCode != null && locationFromCode != string.Empty) { criteria.Add( Expression.Or( Expression.Eq("LocationFrom.Code", locationFromCode), Expression.And( Expression.IsNull("LocationFrom"), Expression.Eq("lf.Code", locationFromCode)))); } else { criteria.Add(Expression.And( Expression.IsNull("LocationFrom"), Expression.IsNull("lf.Code"))); } if (locationToCode != null && locationToCode != string.Empty) { criteria.Add( Expression.Or( Expression.Eq("LocationTo.Code", locationToCode), Expression.And( Expression.IsNull("LocationTo"), Expression.Eq("lt.Code", locationToCode)))); } else { criteria.Add(Expression.And( Expression.IsNull("LocationTo"), Expression.IsNull("lt.Code"))); } //todo ур╣╫ж╠╫с╥╣╩ь IList <FlowDetail> flowDetailList = criteriaMgr.FindAll <FlowDetail>(criteria); if (flowDetailList.Count > 0) { flowDetail = (FlowDetail)flowDetailList[0]; } else if (flowDetailList.Count == 0 && flow.ReferenceFlow != null && flow.ReferenceFlow.Trim() != string.Empty) { criteria = DetachedCriteria.For(typeof(FlowDetail)); criteria.CreateAlias("Flow", "fl"); criteria.CreateAlias("fl.LocationFrom", "lf"); criteria.CreateAlias("fl.LocationTo", "lt"); criteria.Add(Expression.Eq("fl.Code", flowCode)); criteria.Add(Subqueries.In(itemCode, DetachedCriteria.For(typeof(FlowDetail)) .CreateAlias("Item", "i") .CreateAlias("Flow", "f") .Add(Expression.Eq("f.Code", flow.ReferenceFlow)) .SetProjection(Projections.ProjectionList().Add(Projections.Property("i.Code"))) )); if (locationFromCode != null && locationFromCode != string.Empty) { criteria.Add( Expression.Or( Expression.Eq("LocationFrom.Code", locationFromCode), Expression.And( Expression.IsNull("LocationFrom"), Expression.Eq("lf.Code", locationFromCode)))); } else { criteria.Add(Expression.And( Expression.IsNull("LocationFrom"), Expression.IsNull("lf.Code"))); } if (locationToCode != null && locationToCode != string.Empty) { criteria.Add( Expression.Or( Expression.Eq("LocationTo.Code", locationToCode), Expression.And( Expression.IsNull("LocationTo"), Expression.Eq("lt.Code", locationToCode)))); } else { criteria.Add(Expression.And( Expression.IsNull("LocationTo"), Expression.IsNull("lt.Code"))); } flowDetailList = criteriaMgr.FindAll <FlowDetail>(criteria); if (flowDetailList.Count != 0) { flowDetail = (FlowDetail)flowDetailList[0]; } } return(flowDetail); }