public void NestedSubqueriesShouldStayInOrder()
{
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 = 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();
}
}
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 void MultipleSubqueriesShouldStayInOrder()
{
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 = 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();
}
}
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 static void Process(Document doc, IList <OrderHead> orders)
{
if (doc == null)
{
return;
}
//сопоставляем идентификаторы названиям продуктов в накладной
doc.SetProductId();
//расчет недостающих значений
doc.CalculateValues();
if (!doc.DocumentDate.HasValue)
{
doc.DocumentDate = DateTime.Now;
}
//сопоставляем позиции в накладной с позициями в заказе
var totalQuantity = doc.Lines.Sum(l => l.Quantity);
var lineCount = doc.Lines.Count;
var isDuplicate = SessionHelper.WithSession(s => {
return(s.CreateCriteria <Document>("d")
.Add(Expression.Eq("d.ProviderDocumentId", doc.ProviderDocumentId))
.Add(Expression.Eq("d.FirmCode", doc.FirmCode))
.Add(Expression.Eq("d.DocumentDate", doc.DocumentDate))
.Add(Subqueries.Eq(totalQuantity,
DetachedCriteria.For <DocumentLine>("l")
.Add(Expression.EqProperty("d.Id", "l.Document.Id"))
.SetProjection(Projections.Sum("Quantity"))))
.Add(Subqueries.Eq(lineCount,
DetachedCriteria.For <DocumentLine>("l")
.Add(Expression.EqProperty("d.Id", "l.Document.Id"))
.SetProjection(Projections.Count("l.Id"))))
.SetProjection(Projections.Count("d.Id"))
.UniqueResult <int>() > 0);
});
if (!isDuplicate)
{
WaybillOrderMatcher.SafeComparisonWithOrders(doc, orders.SelectMany(o => o.Items).ToList());
}
//сопоставление сертификатов для позиций накладной
CertificateSourceDetector.DetectAndParse(doc);
}
private ICriterion GetPermissionQueryInternal(UsersGroup usersgroup, string operation, string securityKeyProperty)
{
var operationNames = Strings.GetHierarchicalOperationNames(operation);
var criteria = DetachedCriteria.For <Permission>("permission")
.CreateAlias("Operation", "op")
.CreateAlias("EntitiesGroup", "entityGroup", JoinType.LeftOuterJoin)
.CreateAlias("entityGroup.Entities", "entityKey", JoinType.LeftOuterJoin)
.SetProjection(Projections.Property("Allow"))
.Add(Expression.In("op.Name", operationNames))
.Add(Expression.Eq("UsersGroup", usersgroup))
.Add(
Property.ForName(securityKeyProperty).EqProperty("permission.EntitySecurityKey") ||
Property.ForName(securityKeyProperty).EqProperty("entityKey.EntitySecurityKey") ||
(
Expression.IsNull("permission.EntitySecurityKey") &&
Expression.IsNull("permission.EntitiesGroup")
)
)
.SetMaxResults(1)
.AddOrder(Order.Desc("Level"))
.AddOrder(Order.Asc("Allow"));
return(Subqueries.Eq(true, criteria));
}
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);
}
