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())));