logo
down
shadow

NHibernate: Subqueries.Exists not working


NHibernate: Subqueries.Exists not working

By : Yogita
Date : October 15 2020, 11:12 PM
seems to work fine Try to create criteria or alias on the path of Foo in the Bar class in your subquery and then apply the eaual restriction.
code :


Share : facebook icon twitter icon
How to do subqueries in nhibernate?

How to do subqueries in nhibernate?


By : user3061266
Date : March 29 2020, 07:55 AM
Does that help I need to do a subquery on a sub collection but I can't get it to work.
code :
session.QueryOver<Task>(() => tAlias)
    .WhereRestrictionsOn(x => x.Course.Id).IsIn(courseIds)
    .WithSubquery.WhereExists(QueryOver.Of<CompletedTask>()
        .Where(x => x.id == tAlias.id) //not sure how you need to link Task to CompletedTask
        .Where(x => x.Student.StudentId == settings.StudentId)
        .Select(x => x.id)) //exists requires some kind of projection (i.e. select clause)
    .List<Task>();
Task taskAlias = null;

session.QueryOver<CompletedTask>()
    .JoinAlias(x => x.Task, () => taskAlias)
    .WhereRestrictionsOn(() => taskAlias.Course.Id).IsIn(courseIds)
    .Where(x => x.Student.StudentId == settings.StudentId)
    .List<CompletedTask>();
NHibernate Query, using OfType and Subqueries

NHibernate Query, using OfType and Subqueries


By : Mark Dale
Date : March 29 2020, 07:55 AM
Hope that helps I'm trying to write a NHibernate Query which determines if a specified record is relevant to source of the request. In this case the specified record is a customer and a customer is relevant if they have any orders sourced from the specified source. , It is possible to do this by splitting the Nhibernate queries up e.g.
code :
var Items = Session.Query<SourceOrderItem>().Where(I=> I.Source.ID == Source.ID).ToList();
foreach(var Item in Items)
{
    IsRelevant = Session.Query<Order>().Any(R => R.Customer.ID == C.ID && R.Items.Any(I => I.ID == Item.ID));
    if (IsRelevant)
        break;
}
Issues with NHibernate QueryOver and Subqueries

Issues with NHibernate QueryOver and Subqueries


By : Tom.M
Date : March 29 2020, 07:55 AM
Does that help I have the following model: , I would try something like this with a subquery (not tested though)
code :
SubmissionNote subNoteAlias = null, subNoteAliasMax =null;
User userAlias = null;
String[] userNames = new[]{"John","Joe"};

var subquery =
    QueryOver.Of(() => subNoteAliasMax)
       .Where(subNote => subNote.BelongsTo.Id == subNoteAlias.BelongsTo.Id)
       .Select(Projections.Max<SubmissionNote>(subNote => subNote.CreatedOn));

var result = session
 .QueryOver<SubmissionNote>(() => subNoteAlias)
 .WithSubquery.WhereProperty(subNote=>subNote.CreatedOn).Eq(subquery)
 .JoinQueryOver(
      subNote=>subNote.Creator
      ,()=>userAlias
      ,JoinType.InnerJoin
      ,Restrictions.In(Projections.Property(() => userAlias.Username ), userNames))
  .List();
SELECT SubmissionNote.*
  FROM SubmissionNote INNER JOIN User
  ON SubmissionNote.creatorId = User.userId
  AND User.userName in ('john','joe')
  WHERE SubmissionNote.CreatedOn = (SELECT MAX(CreatedOn) FROM SubmissionNote snAlias     
     WHERE snAlias.SubmissionId = SubmissionNote.SubmissionId)
Should I use OR with multiple subqueries or UNION all the subqueries, when using Exists?

Should I use OR with multiple subqueries or UNION all the subqueries, when using Exists?


By : vipasha
Date : March 29 2020, 07:55 AM
will help you By using left-joins, it uses the index only (provided your table has an index on each respective "ID", "col2" and "othercol" basis. The index is very fast and does not need to go to raw pages to confirm it "exists" or not. The where clause just cares about any of the elements NOT NULL (indicating it DOES exists in the underlying table)
code :
SELECT 
      tbl.* 
   from 
      table as tbl
         LEFT JOIN t1
            ON tbl.id = t1.id
         LEFT JOIN t2
            ON tbl.col2 = t2.col2
         LEFT JOIN t3
            ON tbl.somecol = t3.othercol
   WHERE
         t1.ID is not null
      OR t2.col2 is not null
      OR t3.othercol is not null
NHibernate multiple subqueries with in clause

NHibernate multiple subqueries with in clause


By : Maurice Williams
Date : March 29 2020, 07:55 AM
Does that help You got it almost right, you are only missing the .Where(Restrictions.Disjunction()...) for the or in SQL.
Based on your code (assuming that you have a property Id in LocationEntity):
code :
// get IDs to look for in CurrentLocation
var subQuery1 = QueryOver.Of<LocationEntity>()
    .Where(l => l.Name == LocationNameEnum.LA)
    .Select(x => x.Id);

// get IDs to look for in NextDestination
var subQuery2 = QueryOver.Of<LocationEntity>()
    .Where(l => l.Name == LocationNameEnum.CHG || l.Name == LocationNameEnum.NY)
    .Select(x => x.Id);

var poc = session.QueryOver<TruckEntity>()
    .Where(Restrictions.Disjunction() // this takes care of the OR
        .Add(Subqueries.WhereProperty<TruckEntity>(x => x.CurrentLocation.Id).In(subQuery1))
        .Add(Subqueries.WhereProperty<TruckEntity>(x => x.NextDestination.Id).In(subQuery2))
    )
    .List<TruckEntity>();
shadow
Privacy Policy - Terms - Contact Us © bighow.org