public void ProjectionsTest()
{
ISession s = OpenSession();
ITransaction t = s.BeginTransaction();
Course course = new Course();
course.CourseCode = "HIB";
course.Description = "Hibernate Training";
s.Save(course);
Student gavin = new Student();
gavin.Name = "Gavin King";
gavin.StudentNumber = 667;
s.Save(gavin);
Student xam = new Student();
xam.Name = "Max Rydahl Andersen";
xam.StudentNumber = 101;
s.Save(xam);
Enrolment enrolment = new Enrolment();
enrolment.Course = course;
enrolment.CourseCode = course.CourseCode;
enrolment.Semester = 1;
enrolment.Year = 1999;
enrolment.Student = xam;
enrolment.StudentNumber = xam.StudentNumber;
xam.Enrolments.Add(enrolment);
s.Save(enrolment);
enrolment = new Enrolment();
enrolment.Course = course;
enrolment.CourseCode = course.CourseCode;
enrolment.Semester = 3;
enrolment.Year = 1998;
enrolment.Student = gavin;
enrolment.StudentNumber = gavin.StudentNumber;
gavin.Enrolments.Add(enrolment);
s.Save(enrolment);
//s.flush();
int count = (int)s.CreateCriteria(typeof(Enrolment))
.SetProjection(Projections.Count("StudentNumber").SetDistinct())
.UniqueResult();
Assert.AreEqual(2, count);
object obj = s.CreateCriteria(typeof(Enrolment))
.SetProjection(Projections.ProjectionList()
.Add(Projections.Count("StudentNumber"))
.Add(Projections.Max("StudentNumber"))
.Add(Projections.Min("StudentNumber"))
.Add(Projections.Avg("StudentNumber"))
)
.UniqueResult();
object[] result = (object[])obj;
Assert.AreEqual(2, result[0]);
Assert.AreEqual(667L, result[1]);
Assert.AreEqual(101L, result[2]);
Assert.AreEqual(384.0D, (Double)result[3], 0.01D);
IList resultWithMaps = s.CreateCriteria(typeof(Enrolment))
.SetProjection(Projections.Distinct(Projections.ProjectionList()
.Add(Projections.Property("StudentNumber"), "stNumber")
.Add(Projections.Property("CourseCode"), "cCode"))
)
.Add(Expression.Gt("StudentNumber", 665L))
.Add(Expression.Lt("StudentNumber", 668L))
.AddOrder(Order.Asc("stNumber"))
.SetResultTransformer(CriteriaSpecification.AliasToEntityMap)
.List();
Assert.AreEqual(1, resultWithMaps.Count);
IDictionary m1 = (IDictionary)resultWithMaps[0];
Assert.AreEqual(667L, m1["stNumber"]);
Assert.AreEqual(course.CourseCode, m1["cCode"]);
resultWithMaps = s.CreateCriteria(typeof(Enrolment))
.SetProjection(Projections.Property("StudentNumber").As("stNumber"))
.AddOrder(Order.Desc("stNumber"))
.SetResultTransformer(CriteriaSpecification.AliasToEntityMap)
.List();
Assert.AreEqual(2, resultWithMaps.Count);
IDictionary m0 = (IDictionary)resultWithMaps[0];
m1 = (IDictionary)resultWithMaps[1];
Assert.AreEqual(101L, m1["stNumber"]);
Assert.AreEqual(667L, m0["stNumber"]);
IList resultWithAliasedBean = s.CreateCriteria(typeof(Enrolment))
.CreateAlias("Student", "st")
.CreateAlias("Course", "co")
.SetProjection(Projections.ProjectionList()
.Add(Projections.Property("st.Name"), "studentName")
.Add(Projections.Property("co.Description"), "courseDescription")
)
.AddOrder(Order.Desc("studentName"))
.SetResultTransformer(Transformers.AliasToBean(typeof(StudentDTO)))
.List();
Assert.AreEqual(2, resultWithAliasedBean.Count);
StudentDTO dto = (StudentDTO)resultWithAliasedBean[0];
Assert.IsNotNull(dto.Description);
Assert.IsNotNull(dto.Name);
s.CreateCriteria(typeof(Student))
.Add(Expression.Like("Name", "Gavin", MatchMode.Start))
.AddOrder(Order.Asc("Name"))
.CreateCriteria("Enrolments", "e")
.AddOrder(Order.Desc("Year"))
.AddOrder(Order.Desc("Semester"))
.CreateCriteria("Course", "c")
.AddOrder(Order.Asc("Description"))
.SetProjection(Projections.ProjectionList()
.Add(Projections.Property("this.Name"))
.Add(Projections.Property("e.Year"))
.Add(Projections.Property("e.Semester"))
.Add(Projections.Property("c.CourseCode"))
.Add(Projections.Property("c.Description"))
)
.UniqueResult();
ProjectionList p1 = Projections.ProjectionList()
.Add(Projections.Count("StudentNumber"))
.Add(Projections.Max("StudentNumber"))
.Add(Projections.RowCount());
ProjectionList p2 = Projections.ProjectionList()
.Add(Projections.Min("StudentNumber"))
.Add(Projections.Avg("StudentNumber"))
.Add(Projections.SqlProjection(
"1 as constOne, count(*) as countStar",
new String[] { "constOne", "countStar" },
new IType[] { NHibernateUtil.Int32, NHibernateUtil.Int32 }
));
object[] array = (object[])s.CreateCriteria(typeof(Enrolment))
.SetProjection(Projections.ProjectionList().Add(p1).Add(p2))
.UniqueResult();
Assert.AreEqual(7, array.Length);
ProjectionList pp1 = Projections.ProjectionList().Add(Projections.RowCountInt64());
object r = s.CreateCriteria(typeof(Enrolment))
.SetProjection(pp1)
.UniqueResult();
Assert.AreEqual(typeof(Int64), r.GetType());
IList list = s.CreateCriteria(typeof(Enrolment))
.CreateAlias("Student", "st")
.CreateAlias("Course", "co")
.SetProjection(Projections.ProjectionList()
.Add(Projections.GroupProperty("co.CourseCode"))
.Add(Projections.Count("st.StudentNumber").SetDistinct())
.Add(Projections.GroupProperty("Year"))
)
.List();
Assert.AreEqual(2, list.Count);
object g = s.CreateCriteria(typeof(Student))
.Add(Expression.IdEq(667L))
.SetFetchMode("enrolments", FetchMode.Join)
//.setFetchMode("enrolments.course", FetchMode.JOIN) //TODO: would love to make that work...
.UniqueResult();
Assert.AreSame(gavin, g);
s.Delete(gavin);
s.Delete(xam);
s.Delete(course);
t.Commit();
s.Close();
}
public void ProjectionCache()
{
ISession s = OpenSession();
ITransaction t = s.BeginTransaction();
Course course = new Course();
course.CourseCode = "HIB";
course.Description = "Hibernate Training";
s.Save(course);
Student gavin = new Student();
gavin.Name = "Gavin King";
gavin.StudentNumber = 666;
s.Save(gavin);
Student xam = new Student();
xam.Name = "Max Rydahl Andersen";
xam.StudentNumber = 101;
s.Save(xam);
Enrolment enrolment1 = new Enrolment();
enrolment1.Course = course;
enrolment1.CourseCode = course.CourseCode;
enrolment1.Semester = 1;
enrolment1.Year = 1999;
enrolment1.Student = xam;
enrolment1.StudentNumber = xam.StudentNumber;
xam.Enrolments.Add(enrolment1);
s.Save(enrolment1);
Enrolment enrolment2 = new Enrolment();
enrolment2.Course = course;
enrolment2.CourseCode = course.CourseCode;
enrolment2.Semester = 3;
enrolment2.Year = 1998;
enrolment2.Student = gavin;
enrolment2.StudentNumber = gavin.StudentNumber;
gavin.Enrolments.Add(enrolment2);
s.Save(enrolment2);
IList list = s.CreateCriteria(typeof(Enrolment))
.CreateAlias("Student", "s")
.CreateAlias("Course", "c")
.Add(Expression.IsNotEmpty("s.Enrolments"))
.SetProjection(Projections.ProjectionList()
.Add(Projections.Property("s.Name"))
.Add(Projections.Property("c.Description")))
.SetCacheable(true)
.List();
Assert.AreEqual(2, list.Count);
Assert.AreEqual(2, ((object[])list[0]).Length);
Assert.AreEqual(2, ((object[])list[1]).Length);
t.Commit();
s.Close();
s = OpenSession();
t = s.BeginTransaction();
s.CreateCriteria(typeof(Enrolment))
.CreateAlias("Student", "s")
.CreateAlias("Course", "c")
.Add(Expression.IsNotEmpty("s.Enrolments"))
.SetProjection(Projections.ProjectionList()
.Add(Projections.Property("s.Name"))
.Add(Projections.Property("c.Description")))
.SetCacheable(true)
.List();
Assert.AreEqual(2, list.Count);
Assert.AreEqual(2, ((object[])list[0]).Length);
Assert.AreEqual(2, ((object[])list[1]).Length);
t.Commit();
s.Close();
s = OpenSession();
t = s.BeginTransaction();
s.CreateCriteria(typeof(Enrolment))
.CreateAlias("Student", "s")
.CreateAlias("Course", "c")
.Add(Expression.IsNotEmpty("s.Enrolments"))
.SetProjection(Projections.ProjectionList()
.Add(Projections.Property("s.Name"))
.Add(Projections.Property("c.Description")))
.SetCacheable(true)
.List();
Assert.AreEqual(2, list.Count);
Assert.AreEqual(2, ((object[])list[0]).Length);
Assert.AreEqual(2, ((object[])list[1]).Length);
s.Delete(enrolment1);
s.Delete(enrolment2);
s.Delete(course);
s.Delete(gavin);
s.Delete(xam);
t.Commit();
s.Close();
}
public void NH_1155_ShouldNotLoadAllChildrenInPagedSubSelect()
{
if (this.Dialect.GetType().Equals((typeof(MsSql2000Dialect))))
Assert.Ignore("This is not fixed for SQL 2000 Dialect");
using (ISession s = OpenSession())
using (ITransaction tx = s.BeginTransaction())
{
Course course = new Course();
course.CourseCode = "HIB";
course.Description = "Hibernate Training";
s.Save(course);
Student gavin = new Student();
gavin.Name = "Gavin King";
gavin.StudentNumber = 667;
s.Save(gavin);
Student ayende = new Student();
ayende.Name = "Ayende Rahien";
ayende.StudentNumber = 1337;
s.Save(ayende);
Student xam = new Student();
xam.Name = "Max Rydahl Andersen";
xam.StudentNumber = 101;
s.Save(xam);
Enrolment enrolment = new Enrolment();
enrolment.Course = course;
enrolment.CourseCode = course.CourseCode;
enrolment.Semester = 1;
enrolment.Year = 1999;
enrolment.Student = xam;
enrolment.StudentNumber = xam.StudentNumber;
xam.Enrolments.Add(enrolment);
s.Save(enrolment);
enrolment = new Enrolment();
enrolment.Course = course;
enrolment.CourseCode = course.CourseCode;
enrolment.Semester = 3;
enrolment.Year = 1998;
enrolment.Student = ayende;
enrolment.StudentNumber = ayende.StudentNumber;
ayende.Enrolments.Add(enrolment);
s.Save(enrolment);
tx.Commit();
}
using (ISession s = OpenSession())
{
IList<Student> list = s.CreateCriteria(typeof(Student))
.SetFirstResult(1)
.SetMaxResults(10)
.AddOrder(Order.Asc("StudentNumber"))
.List<Student>();
foreach (Student student in list)
{
foreach (Enrolment enrolment in student.Enrolments)
{
NHibernateUtil.Initialize(enrolment);
}
}
Enrolment key = new Enrolment();
key.CourseCode = "HIB";
key.StudentNumber = 101;// xam
//since we didn't load xam's entrollments before (skipped by orderring)
//it should not be already loaded
Enrolment shouldNotBeLoaded = (Enrolment)s.Load(typeof(Enrolment), key);
Assert.IsFalse(NHibernateUtil.IsInitialized(shouldNotBeLoaded));
}
using (ISession s = OpenSession())
using (ITransaction tx = s.BeginTransaction())
{
s.Delete("from Enrolment");
s.Delete("from Student");
s.Delete("from Course");
tx.Commit();
}
}
public void ProjectedListIncludesEmbeddedCompositeId()
{
using (ISession s = OpenSession())
using (ITransaction t = s.BeginTransaction())
{
Course course = new Course();
course.CourseCode = "HIB";
course.Description = "Hibernate Training";
s.Save(course);
Student gavin = new Student();
gavin.Name = "Gavin King";
gavin.StudentNumber = 667;
s.Save(gavin);
Student xam = new Student();
xam.Name = "Max Rydahl Andersen";
xam.StudentNumber = 101;
s.Save(xam);
Enrolment enrolment = new Enrolment();
enrolment.Course = course;
enrolment.CourseCode = course.CourseCode;
enrolment.Semester = 1;
enrolment.Year = 1999;
enrolment.Student = xam;
enrolment.StudentNumber = xam.StudentNumber;
gavin.Enrolments.Add(enrolment);
s.Save(enrolment);
enrolment = new Enrolment();
enrolment.Course = course;
enrolment.CourseCode = course.CourseCode;
enrolment.Semester = 3;
enrolment.Year = 1998;
enrolment.Student = gavin;
enrolment.StudentNumber = gavin.StudentNumber;
gavin.Enrolments.Add(enrolment);
s.Save(enrolment);
s.Flush();
IList data = (IList)s.CreateCriteria<Enrolment>()
.SetProjection(Projections.ProjectionList()
.Add(Projections.Property("Semester"))
.Add(Projections.Property("Year"))
.Add(Projections.Id()))
.List();
t.Rollback();
}
}
public void SubselectWithComponent()
{
Course course = null;
Student gavin = null;
DetachedCriteria dc = null;
CityState odessaWa = null;
Enrolment enrolment2 = null;
using (ISession session = OpenSession())
using (ITransaction t = session.BeginTransaction())
{
course = new Course();
course.CourseCode = "HIB";
course.Description = "Hibernate Training";
session.Save(course);
odessaWa = new CityState("Odessa", "WA");
gavin = new Student();
gavin.Name = "Gavin King";
gavin.StudentNumber = 232;
gavin.CityState = odessaWa;
session.Save(gavin);
enrolment2 = new Enrolment();
enrolment2.Course = course;
enrolment2.CourseCode = course.CourseCode;
enrolment2.Semester = 3;
enrolment2.Year = 1998;
enrolment2.Student = gavin;
enrolment2.StudentNumber = gavin.StudentNumber;
gavin.Enrolments.Add(enrolment2);
session.Persist(enrolment2);
dc = DetachedCriteria.For<Student>()
.Add(Property.ForName("CityState").Eq(odessaWa))
.SetProjection(Property.ForName("CityState"));
session.CreateCriteria<Student>()
.Add(Subqueries.Exists(dc))
.List();
t.Commit();
}
if (TestDialect.SupportsOperatorAll)
{
using (ISession session = OpenSession())
using (ITransaction t = session.BeginTransaction())
{
try
{
session.CreateCriteria<Student>()
.Add(Subqueries.PropertyEqAll("CityState", dc))
.List();
Assert.Fail("should have failed because cannot compare subquery results with multiple columns");
}
catch (QueryException)
{
// expected
}
t.Rollback();
}
}
if (TestDialect.SupportsOperatorAll)
{
using (ISession session = OpenSession())
using (ITransaction t = session.BeginTransaction())
{
try
{
session.CreateCriteria<Student>()
.Add(Property.ForName("CityState").EqAll(dc))
.List();
Assert.Fail("should have failed because cannot compare subquery results with multiple columns");
}
catch (QueryException)
{
// expected
}
finally
{
t.Rollback();
}
}
}
using (ISession session = OpenSession())
using (ITransaction t = session.BeginTransaction())
{
try
{
session.CreateCriteria<Student>()
.Add(Subqueries.In(odessaWa, dc))
.List();
Assert.Fail("should have failed because cannot compare subquery results with multiple columns");
}
catch (NHibernate.Exceptions.GenericADOException)
{
// expected
}
finally
{
t.Rollback();
}
}
using (ISession session = OpenSession())
using (ITransaction t = session.BeginTransaction())
{
DetachedCriteria dc2 = DetachedCriteria.For<Student>("st1")
.Add(Property.ForName("st1.CityState").EqProperty("st2.CityState"))
.SetProjection(Property.ForName("CityState"));
try
{
session.CreateCriteria<Student>("st2")
.Add( Subqueries.Eq(odessaWa, dc2))
.List();
Assert.Fail("should have failed because cannot compare subquery results with multiple columns");
}
catch (NHibernate.Exceptions.GenericADOException)
{
// expected
}
finally
{
t.Rollback();
}
}
using (ISession session = OpenSession())
using (ITransaction t = session.BeginTransaction())
{
DetachedCriteria dc3 = DetachedCriteria.For<Student>("st")
.CreateCriteria("Enrolments")
.CreateCriteria("Course")
.Add(Property.ForName("Description").Eq("Hibernate Training"))
.SetProjection(Property.ForName("st.CityState"));
try
{
session.CreateCriteria<Enrolment>("e")
.Add(Subqueries.Eq(odessaWa, dc3))
.List();
Assert.Fail("should have failed because cannot compare subquery results with multiple columns");
}
catch (NHibernate.Exceptions.GenericADOException)
{
// expected
}
finally
{
t.Rollback();
}
}
using (ISession session = OpenSession())
using (ITransaction t = session.BeginTransaction())
{
session.Delete(enrolment2);
session.Delete(gavin);
session.Delete(course);
t.Commit();
}
}
public void CloningProjectionsUsingProperty()
{
ISession s = OpenSession();
ITransaction t = s.BeginTransaction();
Course course = new Course();
course.CourseCode = "HIB";
course.Description = "Hibernate Training";
s.Save(course);
Student gavin = new Student();
gavin.Name = "Gavin King";
gavin.StudentNumber = 667;
s.Save(gavin);
Student xam = new Student();
xam.Name = "Max Rydahl Andersen";
xam.StudentNumber = 101;
s.Save(xam);
Enrolment enrolment = new Enrolment();
enrolment.Course = course;
enrolment.CourseCode = course.CourseCode;
enrolment.Semester = 1;
enrolment.Year = 1999;
enrolment.Student = xam;
enrolment.StudentNumber = xam.StudentNumber;
xam.Enrolments.Add(enrolment);
s.Save(enrolment);
enrolment = new Enrolment();
enrolment.Course = course;
enrolment.CourseCode = course.CourseCode;
enrolment.Semester = 3;
enrolment.Year = 1998;
enrolment.Student = gavin;
enrolment.StudentNumber = gavin.StudentNumber;
gavin.Enrolments.Add(enrolment);
s.Save(enrolment);
s.Flush();
int count = (int)CriteriaTransformer.Clone(s.CreateCriteria(typeof(Enrolment))
.SetProjection(Property.ForName("StudentNumber").Count().SetDistinct())
)
.UniqueResult();
Assert.AreEqual(2, count);
object obj = CriteriaTransformer.Clone(s.CreateCriteria(typeof(Enrolment))
.SetProjection(Projections.ProjectionList()
.Add(Property.ForName("StudentNumber").Count())
.Add(Property.ForName("StudentNumber").Max())
.Add(Property.ForName("StudentNumber").Min())
.Add(Property.ForName("StudentNumber").Avg())
)
)
.UniqueResult();
object[] result = (object[])obj;
Assert.AreEqual(2, result[0]);
Assert.AreEqual(667L, result[1]);
Assert.AreEqual(101L, result[2]);
Assert.AreEqual(384.0D, (double)result[3], 0.01D);
CriteriaTransformer.Clone(
s.CreateCriteria(typeof(Enrolment))
.Add(Property.ForName("StudentNumber").Gt(665L))
.Add(Property.ForName("StudentNumber").Lt(668L))
.Add(Property.ForName("CourseCode").Like("HIB", MatchMode.Start))
.Add(Property.ForName("Year").Eq((short)1999))
.AddOrder(Property.ForName("StudentNumber").Asc())
)
.UniqueResult();
ICriteria clonedCriteriaProjection = CriteriaTransformer.Clone(s.CreateCriteria(typeof(Enrolment))
.SetProjection(Projections.ProjectionList()
.Add(Property.ForName("StudentNumber").As("stNumber"))
.Add(Property.ForName("CourseCode").As("cCode"))
)
.Add(Property.ForName("StudentNumber").Gt(665L))
.Add(Property.ForName("StudentNumber").Lt(668L))
.AddOrder(Property.ForName("StudentNumber").Asc())
.SetResultTransformer(CriteriaSpecification.AliasToEntityMap)
);
IList resultWithMaps = clonedCriteriaProjection
.List();
Assert.AreEqual(1, resultWithMaps.Count);
IDictionary m1 = (IDictionary)resultWithMaps[0];
Assert.AreEqual(667L, m1["stNumber"]);
Assert.AreEqual(course.CourseCode, m1["cCode"]);
resultWithMaps = CriteriaTransformer.Clone(s.CreateCriteria(typeof(Enrolment))
.SetProjection(Property.ForName("StudentNumber").As("stNumber"))
.AddOrder(Order.Desc("stNumber"))
.SetResultTransformer(CriteriaSpecification.AliasToEntityMap)
)
.List();
Assert.AreEqual(2, resultWithMaps.Count);
IDictionary m0 = (IDictionary)resultWithMaps[0];
m1 = (IDictionary)resultWithMaps[1];
Assert.AreEqual(101L, m1["stNumber"]);
Assert.AreEqual(667L, m0["stNumber"]);
IList resultWithAliasedBean = CriteriaTransformer.Clone(s.CreateCriteria(typeof(Enrolment))
.CreateAlias("Student", "st")
.CreateAlias("Course", "co")
.SetProjection(Projections.ProjectionList()
.Add(Property.ForName("st.Name").As("studentName"))
.Add(Property.ForName("co.Description").As("courseDescription"))
)
.AddOrder(Order.Desc("studentName"))
.SetResultTransformer(Transformers.AliasToBean(typeof(StudentDTO)))
)
.List();
Assert.AreEqual(2, resultWithAliasedBean.Count);
StudentDTO dto = (StudentDTO)resultWithAliasedBean[0];
Assert.IsNotNull(dto.Description);
Assert.IsNotNull(dto.Name);
ICriteria complexCriteriaWithProjections = s.CreateCriteria(typeof(Student))
.Add(Expression.Like("Name", "Gavin", MatchMode.Start))
.AddOrder(Order.Asc("Name"))
.CreateCriteria("Enrolments", "e")
.AddOrder(Order.Desc("Year"))
.AddOrder(Order.Desc("Semester"))
.CreateCriteria("Course", "c")
.AddOrder(Order.Asc("Description"))
.SetProjection(Projections.ProjectionList()
.Add(Property.ForName("this.Name"))
.Add(Property.ForName("e.Year"))
.Add(Property.ForName("e.Semester"))
.Add(Property.ForName("c.CourseCode"))
.Add(Property.ForName("c.Description"))
);
CriteriaTransformer.Clone(complexCriteriaWithProjections)
.UniqueResult();
ProjectionList p1 = Projections.ProjectionList()
.Add(Property.ForName("StudentNumber").Count())
.Add(Property.ForName("StudentNumber").Max())
.Add(Projections.RowCount());
ProjectionList p2 = Projections.ProjectionList()
.Add(Property.ForName("StudentNumber").Min())
.Add(Property.ForName("StudentNumber").Avg())
.Add(Projections.SqlProjection(
"1 as constOne, count(*) as countStar",
new String[] { "constOne", "countStar" },
new IType[] { NHibernateUtil.Int32, NHibernateUtil.Int32 }
));
object[] array = (object[])CriteriaTransformer.Clone(s.CreateCriteria(typeof(Enrolment))
.SetProjection(Projections.ProjectionList().Add(p1).Add(p2))
)
.UniqueResult();
Assert.AreEqual(7, array.Length);
IList list = CriteriaTransformer.Clone(s.CreateCriteria(typeof(Enrolment))
.CreateAlias("Student", "st")
.CreateAlias("Course", "co")
.SetProjection(Projections.ProjectionList()
.Add(Property.ForName("co.CourseCode").Group())
.Add(Property.ForName("st.StudentNumber").Count().SetDistinct())
.Add(Property.ForName("Year").Group())
)
)
.List();
Assert.AreEqual(2, list.Count);
s.Delete(gavin);
s.Delete(xam);
s.Delete(course);
t.Commit();
s.Close();
}
public void GroupByComponent()
{
ISession s = OpenSession();
ITransaction t = s.BeginTransaction();
Course course = new Course();
course.CourseCode = "HIB";
course.Description = "Hibernate Training";
s.Save(course);
Student gavin = new Student();
gavin.Name = "Gavin King";
gavin.StudentNumber = 667;
gavin.CityState = new CityState("Odessa", "WA");;
s.Save(gavin);
Student xam = new Student();
xam.Name = "Max Rydahl Andersen";
xam.StudentNumber = 101;
xam.PreferredCourse = course;
xam.CityState = new CityState("Odessa", "WA");;
s.Save(xam);
Enrolment enrolment = new Enrolment();
enrolment.Course = course;
enrolment.CourseCode = course.CourseCode;
enrolment.Semester = 1;
enrolment.Year = 1999;
enrolment.Student = xam;
enrolment.StudentNumber = xam.StudentNumber;
xam.Enrolments.Add(enrolment);
s.Save(enrolment);
enrolment = new Enrolment();
enrolment.Course = course;
enrolment.CourseCode = course.CourseCode;
enrolment.Semester = 3;
enrolment.Year = 1998;
enrolment.Student = gavin;
enrolment.StudentNumber = gavin.StudentNumber;
gavin.Enrolments.Add(enrolment);
s.Save(enrolment);
s.Flush();
object result = s.CreateCriteria<Student>()
.SetProjection(Projections.GroupProperty("CityState"))
.UniqueResult();
Assert.That(result, Is.InstanceOf<CityState>());
Assert.That(((CityState)result).City, Is.EqualTo("Odessa"));
Assert.That(((CityState)result).State, Is.EqualTo("WA"));
result = s.CreateCriteria<Student>("st")
.SetProjection(Projections.GroupProperty("st.CityState"))
.UniqueResult();
Assert.That(result, Is.InstanceOf<CityState>());
Assert.That(((CityState)result).City, Is.EqualTo("Odessa"));
Assert.That(((CityState)result).State, Is.EqualTo("WA"));
result = s.CreateCriteria<Student>("st")
.SetProjection(Projections.GroupProperty("st.CityState"))
.AddOrder(Order.Asc("CityState"))
.UniqueResult();
Assert.That(result, Is.InstanceOf<CityState>());
Assert.That(((CityState)result).City, Is.EqualTo("Odessa"));
Assert.That(((CityState)result).State, Is.EqualTo("WA"));
result = s.CreateCriteria<Student>("st")
.SetProjection(Projections.GroupProperty("st.CityState").As("cityState"))
.AddOrder(Order.Asc("cityState"))
.UniqueResult();
Assert.That(result, Is.InstanceOf<CityState>());
Assert.That(((CityState)result).City, Is.EqualTo("Odessa"));
Assert.That(((CityState)result).State, Is.EqualTo("WA"));
result = s.CreateCriteria<Student>("st")
.SetProjection(Projections.GroupProperty("st.CityState").As("cityState"))
.AddOrder(Order.Asc("cityState"))
.UniqueResult();
Assert.That(result, Is.InstanceOf<CityState>());
Assert.That(((CityState)result).City, Is.EqualTo("Odessa"));
Assert.That(((CityState)result).State, Is.EqualTo("WA"));
result = s.CreateCriteria<Student>("st")
.SetProjection(Projections.GroupProperty("st.CityState").As("cityState"))
.Add(Restrictions.Eq("st.CityState", new CityState("Odessa", "WA")))
.AddOrder(Order.Asc("cityState"))
.UniqueResult();
Assert.That(result, Is.InstanceOf<CityState>());
Assert.That(((CityState)result).City, Is.EqualTo("Odessa"));
Assert.That(((CityState)result).State, Is.EqualTo("WA"));
IList list = s.CreateCriteria<Enrolment>()
.CreateAlias("Student", "st")
.CreateAlias("Course", "co")
.SetProjection(Projections.ProjectionList()
.Add(Property.ForName("co.CourseCode").Group())
.Add(Property.ForName("st.CityState").Group())
.Add(Property.ForName("Year").Group())
)
.List();
s.Delete(gavin);
s.Delete(xam);
s.Delete(course);
t.Commit();
s.Close();
}
public void DistinctProjectionsOfComponents()
{
ISession s = OpenSession();
ITransaction t = s.BeginTransaction();
Course course = new Course();
course.CourseCode = "HIB";
course.Description = "Hibernate Training";
s.Save(course);
Student gavin = new Student();
gavin.Name = "Gavin King";
gavin.StudentNumber = 667;
gavin.CityState = new CityState("Odessa", "WA");;
s.Save(gavin);
Student xam = new Student();
xam.Name = "Max Rydahl Andersen";
xam.StudentNumber = 101;
xam.PreferredCourse = course;
xam.CityState = new CityState("Odessa", "WA");;
s.Save(xam);
Enrolment enrolment = new Enrolment();
enrolment.Course = course;
enrolment.CourseCode = course.CourseCode;
enrolment.Semester = 1;
enrolment.Year = 1999;
enrolment.Student = xam;
enrolment.StudentNumber = xam.StudentNumber;
xam.Enrolments.Add(enrolment);
s.Save(enrolment);
enrolment = new Enrolment();
enrolment.Course = course;
enrolment.CourseCode = course.CourseCode;
enrolment.Semester = 3;
enrolment.Year = 1998;
enrolment.Student = gavin;
enrolment.StudentNumber = gavin.StudentNumber;
gavin.Enrolments.Add(enrolment);
s.Save(enrolment);
s.Flush();
object result = s.CreateCriteria<Student>()
.SetProjection(Projections.Distinct(Property.ForName("CityState")))
.UniqueResult();
Assert.That(result, Is.InstanceOf<CityState>());
Assert.That(((CityState)result).City, Is.EqualTo("Odessa"));
Assert.That(((CityState)result).State, Is.EqualTo("WA"));
result = s.CreateCriteria<Student>()
.SetProjection(Projections.Distinct(Property.ForName("CityState").As("cityState")))
.AddOrder(Order.Asc("cityState"))
.UniqueResult();
Assert.That(result, Is.InstanceOf<CityState>());
Assert.That(((CityState)result).City, Is.EqualTo("Odessa"));
Assert.That(((CityState)result).State, Is.EqualTo("WA"));
result = s.CreateCriteria<Student>()
.SetProjection(Projections.Count("CityState.City"))
.UniqueResult();
Assert.That(result, Is.EqualTo(2));
result = s.CreateCriteria<Student>()
.SetProjection(Projections.CountDistinct("CityState.City"))
.UniqueResult();
Assert.That(result, Is.EqualTo(1));
t.Commit();
s.Close();
// s = OpenSession();
// t = s.BeginTransaction();
// try
// {
// result = s.CreateCriteria<Student>()
// .SetProjection(Projections.Count("CityState"))
// .UniqueResult();
//
// if (!Dialect.SupportsTupleCounts)
// {
// fail( "expected SQLGrammarException" );
// }
//
// Assert.That((long)result, Is.EqualTo(1L));
// }
// catch (NHibernate.Exceptions.SQLGrammarException ex)
// {
// throw ex;
// if (!Dialect.SupportsTupleCounts)
// {
// // expected
// }
// else
// {
// throw ex;
// }
// }
// finally
// {
// t.Rollback();
// s.Close();
// }
// s = OpenSession();
// t = s.BeginTransaction();
// try
// {
// result = s.CreateCriteria<Student>()
// .SetProjection(Projections.CountDistinct("CityState"))
// .UniqueResult();
//
// if (!Dialect.SupportsTupleDistinctCounts)
// {
// fail("expected SQLGrammarException");
// }
//
// Assert.That((long)result, Is.EqualTo(1L));
// }
// catch (NHibernate.Exceptions.SQLGrammarException ex)
// {
// throw ex;
// if (Dialect.SupportsTupleDistinctCounts)
// {
// // expected
// }
// else
// {
// throw ex;
// }
// }
// finally
// {
// t.Rollback();
// s.Close();
// }
s = OpenSession();
t = s.BeginTransaction();
s.Delete(gavin);
s.Delete(xam);
s.Delete(course);
t.Commit();
s.Close();
}
public void Subselect()
{
ISession session = OpenSession();
ITransaction t = session.BeginTransaction();
Course course = new Course();
course.CourseCode = "HIB";
course.Description = "Hibernate Training";
session.Save(course);
Student gavin = new Student();
gavin.Name = "Gavin King";
gavin.StudentNumber = 232;
session.Save(gavin);
Enrolment enrolment2 = new Enrolment();
enrolment2.Course = course;
enrolment2.CourseCode = course.CourseCode;
enrolment2.Semester = 3;
enrolment2.Year = 1998;
enrolment2.Student = gavin;
enrolment2.StudentNumber = gavin.StudentNumber;
gavin.Enrolments.Add(enrolment2);
session.Save(enrolment2);
DetachedCriteria dc = DetachedCriteria.For(typeof(Student))
.Add(Property.ForName("StudentNumber").Eq(232L))
.SetProjection(Property.ForName("Name"));
if (TestDialect.SupportsOperatorAll)
{
session.CreateCriteria(typeof (Student))
.Add(Subqueries.PropertyEqAll("Name", dc))
.List();
}
session.CreateCriteria(typeof(Student))
.Add(Subqueries.Exists(dc))
.List();
if (TestDialect.SupportsOperatorAll)
{
session.CreateCriteria(typeof (Student))
.Add(Property.ForName("Name").EqAll(dc))
.List();
}
session.CreateCriteria(typeof(Student))
.Add(Subqueries.In("Gavin King", dc))
.List();
DetachedCriteria dc2 = DetachedCriteria.For(typeof(Student), "st")
.Add(Property.ForName("st.StudentNumber").EqProperty("e.StudentNumber"))
.SetProjection(Property.ForName("Name"));
session.CreateCriteria(typeof(Enrolment), "e")
.Add(Subqueries.Eq("Gavin King", dc2))
.List();
DetachedCriteria dc3 = DetachedCriteria.For(typeof(Student), "st")
.CreateCriteria("Enrolments")
.CreateCriteria("Course")
.Add(Property.ForName("Description").Eq("Hibernate Training"))
.SetProjection(Property.ForName("st.Name"));
session.CreateCriteria(typeof(Enrolment), "e")
.Add(Subqueries.Eq("Gavin King", dc3))
.List();
DetachedCriteria courseCriteria = DetachedCriteria.For(typeof(Course))
.Add(Property.ForName("Description").Eq("Hibernate Training"))
.SetProjection(Projections.Property("CourseCode"));
DetachedCriteria enrolmentCriteria = DetachedCriteria.For(typeof(Enrolment))
.Add(Property.ForName("CourseCode").Eq(courseCriteria))
.SetProjection(Projections.Property("CourseCode"));
DetachedCriteria studentCriteria = DetachedCriteria.For(typeof(Student))
.Add(Subqueries.Exists(enrolmentCriteria));
object result = studentCriteria.GetExecutableCriteria(session).UniqueResult();
Assert.AreSame(gavin, result);
session.Delete(enrolment2);
session.Delete(gavin);
session.Delete(course);
t.Commit();
session.Close();
}
public void ProjectionsUsingProperty()
{
ISession s = OpenSession();
ITransaction t = s.BeginTransaction();
Course course = new Course();
course.CourseCode = "HIB";
course.Description = "Hibernate Training";
course.CourseMeetings.Add(new CourseMeeting(course, "Monday", 1, "1313 Mockingbird Lane"));
s.Save(course);
Student gavin = new Student();
gavin.Name = "Gavin King";
gavin.StudentNumber = 667;
CityState odessaWa = new CityState("Odessa", "WA");
gavin.CityState = odessaWa;
gavin.PreferredCourse = course;
s.Save(gavin);
Student xam = new Student();
xam.Name = "Max Rydahl Andersen";
xam.StudentNumber = 101;
s.Save(xam);
Enrolment enrolment = new Enrolment();
enrolment.Course = course;
enrolment.CourseCode = course.CourseCode;
enrolment.Semester = 1;
enrolment.Year = 1999;
enrolment.Student = xam;
enrolment.StudentNumber = xam.StudentNumber;
xam.Enrolments.Add(enrolment);
s.Save(enrolment);
enrolment = new Enrolment();
enrolment.Course = course;
enrolment.CourseCode = course.CourseCode;
enrolment.Semester = 3;
enrolment.Year = 1998;
enrolment.Student = gavin;
enrolment.StudentNumber = gavin.StudentNumber;
gavin.Enrolments.Add(enrolment);
s.Save(enrolment);
s.Flush();
// Subtest #1
IList resultList = s.CreateCriteria<Enrolment>()
.SetProjection(Projections.ProjectionList()
.Add(Property.ForName("Student"), "student")
.Add(Property.ForName("Course"), "course")
.Add(Property.ForName("Semester"), "semester")
.Add(Property.ForName("Year"), "year")
).List();
Assert.That(resultList.Count, Is.EqualTo(2));
foreach (object[] objects in resultList)
{
Assert.That(objects.Length, Is.EqualTo(4));
Assert.That(objects[0], Is.InstanceOf<Student>());
Assert.That(objects[1], Is.InstanceOf<Course>());
Assert.That(objects[2], Is.InstanceOf<short>());
Assert.That(objects[3], Is.InstanceOf<short>());
}
// Subtest #2
resultList = s.CreateCriteria<Student>()
.SetProjection(Projections.ProjectionList()
.Add(Projections.Id().As("StudentNumber"))
.Add(Property.ForName("Name"), "name")
.Add(Property.ForName("CityState"), "cityState")
.Add(Property.ForName("PreferredCourse"), "preferredCourse")
).List();
Assert.That(resultList.Count, Is.EqualTo(2));
foreach(object[] objects in resultList)
{
Assert.That(objects.Length, Is.EqualTo(4));
Assert.That(objects[0], Is.InstanceOf<long>());
Assert.That(objects[1], Is.InstanceOf<string>());
if ("Gavin King".Equals(objects[1]))
{
Assert.That(objects[2], Is.InstanceOf<CityState>());
Assert.That(objects[3], Is.InstanceOf<Course>());
}
else
{
Assert.That(objects[2], Is.Null);
Assert.That(objects[3], Is.Null);
}
}
// Subtest #3
resultList = s.CreateCriteria<Student>()
.Add(Restrictions.Eq("Name", "Gavin King"))
.SetProjection(Projections.ProjectionList()
.Add(Projections.Id().As("StudentNumber"))
.Add(Property.ForName("Name"), "name")
.Add(Property.ForName("CityState"), "cityState")
.Add(Property.ForName("PreferredCourse"), "preferredCourse")
).List();
Assert.That(resultList.Count, Is.EqualTo(1));
// Subtest #4
object[] aResult = (object[])s.CreateCriteria<Student>()
.Add(Restrictions.IdEq(667L))
.SetProjection(Projections.ProjectionList()
.Add(Projections.Id().As("StudentNumber"))
.Add(Property.ForName("Name"), "name")
.Add(Property.ForName("CityState"), "cityState")
.Add(Property.ForName("PreferredCourse"), "preferredCourse")
).UniqueResult();
Assert.That(aResult, Is.Not.Null);
Assert.That(aResult.Length, Is.EqualTo(4));
Assert.That(aResult[0], Is.InstanceOf<long>());
Assert.That(aResult[1], Is.InstanceOf<string>());
Assert.That(aResult[2], Is.InstanceOf<CityState>());
Assert.That(aResult[3], Is.InstanceOf<Course>());
// Subtest #5
int count = (int)s.CreateCriteria(typeof(Enrolment))
.SetProjection(Property.ForName("StudentNumber").Count().SetDistinct())
.UniqueResult();
Assert.AreEqual(2, count);
// Subtest #6
object obj = s.CreateCriteria(typeof(Enrolment))
.SetProjection(Projections.ProjectionList()
.Add(Property.ForName("StudentNumber").Count())
.Add(Property.ForName("StudentNumber").Max())
.Add(Property.ForName("StudentNumber").Min())
.Add(Property.ForName("StudentNumber").Avg())
)
.UniqueResult();
object[] result = (object[])obj;
Assert.AreEqual(2, result[0]);
Assert.AreEqual(667L, result[1]);
Assert.AreEqual(101L, result[2]);
Assert.AreEqual(384.0D, (double)result[3], 0.01D);
// Subtest #7
s.CreateCriteria(typeof(Enrolment))
.Add(Property.ForName("StudentNumber").Gt(665L))
.Add(Property.ForName("StudentNumber").Lt(668L))
.Add(Property.ForName("CourseCode").Like("HIB", MatchMode.Start))
.Add(Property.ForName("Year").Eq((short)1999))
.AddOrder(Property.ForName("StudentNumber").Asc())
.UniqueResult();
// Subtest #8
IList resultWithMaps = s.CreateCriteria(typeof(Enrolment))
.SetProjection(Projections.ProjectionList()
.Add(Property.ForName("StudentNumber").As("stNumber"))
.Add(Property.ForName("CourseCode").As("cCode"))
)
.Add(Property.ForName("StudentNumber").Gt(665L))
.Add(Property.ForName("StudentNumber").Lt(668L))
.AddOrder(Property.ForName("StudentNumber").Asc())
.SetResultTransformer(CriteriaSpecification.AliasToEntityMap)
.List();
Assert.AreEqual(1, resultWithMaps.Count);
IDictionary m1 = (IDictionary)resultWithMaps[0];
Assert.AreEqual(667L, m1["stNumber"]);
Assert.AreEqual(course.CourseCode, m1["cCode"]);
// Subtest #9
resultWithMaps = s.CreateCriteria(typeof(Enrolment))
.SetProjection(Property.ForName("StudentNumber").As("stNumber"))
.AddOrder(Order.Desc("stNumber"))
.SetResultTransformer(CriteriaSpecification.AliasToEntityMap)
.List();
Assert.AreEqual(2, resultWithMaps.Count);
IDictionary m0 = (IDictionary)resultWithMaps[0];
m1 = (IDictionary)resultWithMaps[1];
Assert.AreEqual(101L, m1["stNumber"]);
Assert.AreEqual(667L, m0["stNumber"]);
// Subtest #10
IList resultWithAliasedBean = s.CreateCriteria(typeof(Enrolment))
.CreateAlias("Student", "st")
.CreateAlias("Course", "co")
.SetProjection(Projections.ProjectionList()
.Add(Property.ForName("st.Name").As("studentName"))
.Add(Property.ForName("co.Description").As("courseDescription"))
)
.AddOrder(Order.Desc("studentName"))
.SetResultTransformer(Transformers.AliasToBean(typeof(StudentDTO)))
.List();
Assert.AreEqual(2, resultWithAliasedBean.Count);
// Subtest #11
StudentDTO dto = (StudentDTO)resultWithAliasedBean[0];
Assert.IsNotNull(dto.Description);
Assert.IsNotNull(dto.Name);
// Subtest #12
CourseMeeting courseMeetingDto = s.CreateCriteria<CourseMeeting>()
.SetProjection(Projections.ProjectionList()
.Add(Property.ForName("Id").As("id"))
.Add(Property.ForName("Course").As("course"))
)
.AddOrder(Order.Desc("id"))
.SetResultTransformer(Transformers.AliasToBean<CourseMeeting>())
.UniqueResult<CourseMeeting>();
Assert.That(courseMeetingDto.Id, Is.Not.Null);
Assert.That(courseMeetingDto.Id.CourseCode, Is.EqualTo(course.CourseCode));
Assert.That(courseMeetingDto.Id.Day, Is.EqualTo("Monday"));
Assert.That(courseMeetingDto.Id.Location, Is.EqualTo("1313 Mockingbird Lane"));
Assert.That(courseMeetingDto.Id.Period, Is.EqualTo(1));
Assert.That(courseMeetingDto.Course.Description, Is.EqualTo(course.Description));
// Subtest #13
s.CreateCriteria(typeof(Student))
.Add(Expression.Like("Name", "Gavin", MatchMode.Start))
.AddOrder(Order.Asc("Name"))
.CreateCriteria("Enrolments", "e")
.AddOrder(Order.Desc("Year"))
.AddOrder(Order.Desc("Semester"))
.CreateCriteria("Course", "c")
.AddOrder(Order.Asc("Description"))
.SetProjection(Projections.ProjectionList()
.Add(Property.ForName("this.Name"))
.Add(Property.ForName("e.Year"))
.Add(Property.ForName("e.Semester"))
.Add(Property.ForName("c.CourseCode"))
.Add(Property.ForName("c.Description"))
)
.UniqueResult();
// Subtest #14
ProjectionList p1 = Projections.ProjectionList()
.Add(Property.ForName("StudentNumber").Count())
.Add(Property.ForName("StudentNumber").Max())
.Add(Projections.RowCount());
ProjectionList p2 = Projections.ProjectionList()
.Add(Property.ForName("StudentNumber").Min())
.Add(Property.ForName("StudentNumber").Avg())
.Add(Projections.SqlProjection(
"1 as constOne, count(*) as countStar",
new String[] { "constOne", "countStar" },
new IType[] { NHibernateUtil.Int32, NHibernateUtil.Int32 }
));
object[] array = (object[])s.CreateCriteria(typeof(Enrolment))
.SetProjection(Projections.ProjectionList().Add(p1).Add(p2))
.UniqueResult();
Assert.AreEqual(7, array.Length);
// Subtest #15
IList list = s.CreateCriteria(typeof(Enrolment))
.CreateAlias("Student", "st")
.CreateAlias("Course", "co")
.SetProjection(Projections.ProjectionList()
.Add(Property.ForName("co.CourseCode").Group())
.Add(Property.ForName("st.StudentNumber").Count().SetDistinct())
.Add(Property.ForName("Year").Group())
)
.List();
Assert.AreEqual(2, list.Count);
// Subtest #16
list = s.CreateCriteria<Enrolment>()
.CreateAlias("Student", "st")
.CreateAlias("Course", "co")
.SetProjection(Projections.ProjectionList()
.Add(Property.ForName("co.CourseCode").Group().As("courseCode"))
.Add(Property.ForName("st.StudentNumber").Count().SetDistinct().As("studentNumber"))
.Add(Property.ForName("Year").Group())
)
.AddOrder(Order.Asc("courseCode"))
.AddOrder(Order.Asc("studentNumber"))
.List();
Assert.That(list.Count, Is.EqualTo(2));
// Subtest #17
list = s.CreateCriteria<Enrolment>()
.CreateAlias("Student", "st")
.CreateAlias("Course", "co")
.SetProjection(Projections.ProjectionList()
.Add(Property.ForName("co.CourseCode").Group().As("cCode"))
.Add(Property.ForName("st.StudentNumber").Count().SetDistinct().As("stNumber"))
.Add(Property.ForName("Year").Group())
)
.AddOrder(Order.Asc("cCode"))
.AddOrder(Order.Asc("stNumber"))
.List();
Assert.That(list.Count, Is.EqualTo(2));
s.Delete(gavin);
s.Delete(xam);
s.Delete(course);
t.Commit();
s.Close();
}
