Geek Answers Handbook

How to use NHibernate to retrieve items with criteria in a list

I use NHibernate, and I have the following two classes that display my DataBase schema:

public class A
{
    public virtual int Id { get; set;}
    public virtual List<B> MyList { get; set; }
}

public class B
{
    public virtual int Id { get; set; }
    public virtual DateTime Date { get; set; }
    public virtual A FKtoA { get; set; }
}

I would like to get all the records of table A that have all the elements of their MyList property with a date less than the given value.

How can I do this with elegant NHibernate syntax?

+5
source share
4 answers

I owe you the "elegant" part ... :-)

HQL. , : "A, MyList ", "A, MyList ".

from A a
where a not in 
      (select a1
       from A a1, B b
       where b.Date >= :date
       and   b in elements(a1.MyList))

:

var results = session.CreateQuery("hql from above")
                     .SetParameter("date", DateTime.Today)
                     .List();

, A B ( A), :

from A a
where a not in 
      (select b.A
       from B b
       where b.Date >= :date)

: , :

session.CreateCriteria<A>().Add(
    Subqueries.PropertyNotIn("id",
                             DetachedCriteria.For<A>()
                                 .CreateCriteria("MyList")
                                 .SetProjection(Projections.Property("id"))
                                 .Add(Restrictions.Ge("Date", DateTime.Today))))
+1

ICriteria criteria =  session.CreateCriteria<ClassOfTableOne>();
criteria.CreateAlias("FieldNameOfTypeTable2","aliasName");
criteria.SetFetchMode("aliasName", FetchMode.Join);
criteria.Add(Restrictions.Lt("aliasName.Date", yourdate));
0

B ( MyList A FK)

public class B
{
    public virtual int Id { get; set; }
    public virtual DateTime Date { get; set; }
    public virtual A FK_ToA { get; set; }
}

, (HQL)

nhSes.CreateQuery("select b.FK_ToA from B b where b.Date < :passedDate")
     .SetTimestamp("passedDate", DateTime.Now).List<A>()
0

, , , " SQL".

, , , .

, , SQL :

SELECT
 A.Id
FROM A
 LEFT OUTER JOIN B ON A.Id = B.FKtoA
WHERE B.Date < @MyDate

" , A B, B Date ". API ICriteria:

ICriteria criteria =  session.CreateCriteria<A>();
criteria.CreateAlias("MyList", "b", JoinType.LeftOuterJoin)
criteria.Add(Restrictions.Lt("b.Date", myDate));
criteria.SetResultTransformer(new DistinctRootEntityResultTransformer());
criteria.List<A>();

NHibernate DistinctRootEntityResultTransformer: A B, , ICriteria (, , - ).

-1

More articles:


All Articles