public IEnumerable <ValoresDiarios> GetAggregatesDiarios(int dispositivo, string sensorCode, DateTime desde, DateTime hasta)
{
var sensorDao = new SensorDAO();
var sensor = sensorDao.FindByCode(dispositivo, sensorCode);
if (sensor == null)
{
return(new List <ValoresDiarios>(0));
}
ValoresDiarios valoresDiarios = null;
var byDate = Projections.SqlFunction("date", NHibernateUtil.Date,
Projections.Group <Medicion>(a => a.FechaMedicion));
return(Session.QueryOver <Medicion>()
.Where(m => m.Dispositivo.Id == dispositivo && m.Sensor.Id == sensor.Id)
.And(m => m.FechaMedicion >= desde && m.FechaMedicion <= hasta)
.SelectList(x => x
.Select(Projections.Max <Medicion>(m => m.ValorNum1).WithAlias(() => valoresDiarios.Max))
.Select(Projections.Min <Medicion>(m => m.ValorNum1).WithAlias(() => valoresDiarios.Min))
.Select(Projections.Avg <Medicion>(m => m.ValorNum1).WithAlias(() => valoresDiarios.Avg))
.Select(Projections.GroupProperty(byDate).WithAlias(() => valoresDiarios.Date))
)
.OrderBy(byDate).Asc
.TransformUsing(Transformers.AliasToBean <ValoresDiarios>())
.List <ValoresDiarios>());
}
public void ProjectionsExpressions()
{
IProjection p = Projections.Avg("StudentNumber");
NHAssert.IsSerializable(p);
p = Projections.Count("StudentNumber");
NHAssert.IsSerializable(p);
p = Projections.CountDistinct("StudentNumber");
NHAssert.IsSerializable(p);
p = Projections.GroupProperty("Name");
NHAssert.IsSerializable(p);
p = Projections.Id();
NHAssert.IsSerializable(p);
p = Projections.Max("StudentNumber");
NHAssert.IsSerializable(p);
p = Projections.Min("StudentNumber");
NHAssert.IsSerializable(p);
p = Projections.Property("StudentNumber");
NHAssert.IsSerializable(p);
p = Projections.RowCount();
NHAssert.IsSerializable(p);
p = Projections.Sum("StudentNumber");
NHAssert.IsSerializable(p);
p = Projections.Alias(Projections.Count("StudentNumber"), "alias");
NHAssert.IsSerializable(p);
p = Projections.Distinct(Projections.Id());
NHAssert.IsSerializable(p);
p = Projections.ProjectionList().Add(Projections.Max("StudentNumber"));
NHAssert.IsSerializable(p);
}
public DateTime?GetMinDateJournal()
{
ICriteria criteria = Session.CreateCriteria(typeof(TJournal))
.SetProjection(Projections.Min("JournalDate"));
object obj = criteria.UniqueResult();
if (obj != null)
{
return(Convert.ToDateTime(obj));
}
else
{
return(null);
}
DateTime dt = criteria.FutureValue <DateTime>().Value;
return(dt);
try
{
}
catch (Exception)
{
return(null);
}
}
public void CanOrderBySubqueryProjection()
{
if (!Dialect.SupportsScalarSubSelects)
{
Assert.Ignore(Dialect.GetType().Name + " does not support scalar sub-queries");
}
using (ISession s = OpenSession())
{
using (new SqlLogSpy())
{
DetachedCriteria dc = DetachedCriteria.For <Person>("sub");
dc.CreateCriteria("Pets", "pets").SetProjection(Projections.Min("pets.Weight")).Add(
Restrictions.EqProperty("this.Id", "sub.Id"));
ICriteria c = s.CreateCriteria(typeof(Person)).AddOrder(Order.Asc(Projections.SubQuery(dc)));
Console.WriteLine("list()");
IList <Person> list = c.List <Person>();
int nullRelationOffSet = 2;
if (Dialect is Oracle8iDialect || Dialect is PostgreSQLDialect)
{
// Oracle order NULL Last (ASC)
nullRelationOffSet = 0;
}
Assert.AreEqual(list[nullRelationOffSet].Name, "Tim");
Assert.AreEqual(list[nullRelationOffSet + 1].Name, "Joe");
Assert.AreEqual(list[nullRelationOffSet + 2].Name, "Sally");
}
}
}
public Int64 GetUIDLocal(ISession session, String labelSystemName, Boolean max)
{
String imapDatabaseField;
Int64 lastDatabaseUID;
switch (labelSystemName)
{
case "Trash":
imapDatabaseField = "UidTrash";
break;
case "Junk":
imapDatabaseField = "UidSpam";
break;
default:
imapDatabaseField = "UidAll";
break;
}
if (max) //maximo
{
lastDatabaseUID = session.CreateCriteria <MailEntity>()
.Add(Restrictions.Eq("MailAccountEntity", this.Entity))
.SetProjection(Projections.Max(imapDatabaseField))
.UniqueResult <Int64>();
}
else //minimo
{
lastDatabaseUID = session.CreateCriteria <MailEntity>()
.Add(Restrictions.Eq("MailAccountEntity", this.Entity))
.SetProjection(Projections.Min(imapDatabaseField))
.UniqueResult <Int64>();
}
return(lastDatabaseUID); //devuelve 0 si no existen mails
}
public async Task CanOrderBySubqueryProjectionDescAsync()
{
if (!TestDialect.SupportsAggregatingScalarSubSelectsInOrderBy)
{
Assert.Ignore("Dialect does not support aggregating scalar sub-selects in order by");
}
using (ISession s = OpenSession())
{
DetachedCriteria dc = DetachedCriteria.For <Person>("sub");
dc.CreateCriteria("Pets", "pets").SetProjection(Projections.Min("pets.Weight")).Add(
Restrictions.EqProperty("this.Id", "sub.Id"));
ICriteria c = s.CreateCriteria(typeof(Person)).AddOrder(Order.Desc(Projections.SubQuery(dc)));
IList <Person> list = await(c.ListAsync <Person>());
int nullRelationOffSet = 0;
if (Dialect is Oracle8iDialect || Dialect is PostgreSQLDialect)
{
// Oracle order NULL First (DESC)
nullRelationOffSet = 2;
}
Assert.AreEqual(list[nullRelationOffSet + 2].Name, "Tim");
Assert.AreEqual(list[nullRelationOffSet + 1].Name, "Joe");
Assert.AreEqual(list[nullRelationOffSet].Name, "Sally");
}
}
public void TestReturnedOperations()
{
ProjectionExitOperationFactory factory = ProjectionExitOperationFactory.GetFactory();
var sessionFactory = Mock <ISessionFactoryImplementor>();
using (Mocks.Record())
{
}
using (Mocks.Playback())
{
Assert.IsInstanceOf(typeof(RowCountExitOperation),
factory.GetProjectionExitOperation(Projections.RowCount(), sessionFactory));
Assert.IsInstanceOf(typeof(AggregateExitOperation),
factory.GetProjectionExitOperation(Projections.Max("foo"), sessionFactory));
Assert.IsInstanceOf(typeof(AggregateExitOperation),
factory.GetProjectionExitOperation(Projections.Min("foo"), sessionFactory));
Assert.IsInstanceOf(typeof(AggregateExitOperation),
factory.GetProjectionExitOperation(Projections.Sum("foo"), sessionFactory));
try
{
factory.GetProjectionExitOperation(Projections.Avg("foo"), sessionFactory);
Assert.Fail("example of one that we don't yet support");
}
catch (ArgumentException e)
{
// good
}
}
}
public void minimum()
{
var fewestItemsInStock = Session.QueryOver <Product>()
.Select(Projections.Min <Product>(p => p.UnitsInStock))
.SingleOrDefault <int>();
Assert.AreEqual(_products.Min(p => p.UnitsInStock), fewestItemsInStock);
}
public void TestMin()
{
AggregateExitOperation exitOp = new AggregateExitOperation(Projections.Min("value"));
IList result = exitOp.Apply(data);
Assert.AreEqual(0, ((MyInt)result[0]).Value);
}
public void minimum()
{
var fewestItemsInStock = Session.CreateCriteria <Product>()
.SetProjection(Projections.Min("UnitsInStock"))
.UniqueResult <int>();
Assert.AreEqual(_products.Min(p => p.UnitsInStock), fewestItemsInStock);
}
public IList <Hotel> GetHotelsIdPaged(int count, int page)
{
return(session.CreateCriteria <Zajezd>()
.SetProjection(Projections.GroupProperty("hotel"))
.AddOrder(Order.Asc(Projections.Min("cena")))
.SetFirstResult((page - 1) * count)
.SetMaxResults(count)
.List <Hotel>());
}
public IList <Hotel> GetZajezdyWithDateTo(int count, int page, DateTime datumDo)
{
return(session.CreateCriteria <Zajezd>()
.Add(Restrictions.Le("doo", datumDo))
.SetProjection(Projections.GroupProperty("hotel"))
.AddOrder(Order.Asc(Projections.Min("cena")))
.SetFirstResult((page - 1) * count)
.SetMaxResults(count)
.List <Hotel>());
}
public TResult Min <TEntity, TResult>(string propertyName, IWhereGetter where) where TEntity : class
{
var iCriteria = this.Session.CreateCriteria <TEntity>();
iCriteria.SetProjection(Projections.Min(propertyName));
iCriteria = QueryParameterHelper.Add(iCriteria, where);
var result = iCriteria.UniqueResult <TResult>();
return(result);
}
public void MinTest()
{
ISession session = factory.OpenSession();
IProjection expression = Projections.Min("Pay");
CreateObjects(typeof(Simple), session);
SqlString sqlString = expression.ToSqlString(criteria, 0, criteriaQuery);
string expectedSql = "min(sql_alias.Pay) as y0_";
CompareSqlStrings(sqlString, expectedSql, 0);
session.Close();
}
public IList <MovMenu> FindMovMenuBySistema(List <int> perfiles)
{
MovMenu mv = null;
var dc = DetachedCriteria.For <MovMenu>()
.CreateAlias("Perfil", "p", JoinType.InnerJoin)
.CreateAlias("Funcion", "f", JoinType.InnerJoin)
.Add(Restrictions.IsNull("p.FechaBaja"))
.Add(Restrictions.IsNull("f.FechaBaja"));
if (perfiles.Any(p => p != -1))
{
dc.Add(Restrictions.In("p.Id", perfiles));
}
dc.SetProjection(Projections.Property("Id"));
var projectionList = Projections.ProjectionList().Add(
Projections.Max(Projections.Cast(NHibernateUtil.Int32, Projections.Property <MovMenu>(c => c.Alta))).WithAlias(() => mv.Alta))
.Add(Projections.Max(Projections.Cast(NHibernateUtil.Int32, Projections.Property <MovMenu>(c => c.Baja))).WithAlias(() => mv.Baja))
.Add(Projections.Max(Projections.Cast(NHibernateUtil.Int32, Projections.Property <MovMenu>(c => c.Consulta))).WithAlias(() => mv.Consulta))
.Add(Projections.Max(Projections.Cast(NHibernateUtil.Int32, Projections.Property <MovMenu>(c => c.Modificacion))).WithAlias(() => mv.Modificacion))
.Add(Projections.Max(Projections.Cast(NHibernateUtil.Int32, Projections.Property <MovMenu>(c => c.VerMapa))).WithAlias(() => mv.VerMapa))
.Add(Projections.Max(Projections.Cast(NHibernateUtil.Int32, Projections.Property <MovMenu>(c => c.Reporte))).WithAlias(() => mv.Reporte))
.Add(Projections.Min(Projections.Property <MovMenu>(c => c.Orden)).WithAlias(() => mv.Orden))
.Add(Projections.Group <MovMenu>(c => c.Funcion).WithAlias(() => mv.Funcion));
var preresult = Session.CreateCriteria <MovMenu>()
.Add(Subqueries.PropertyIn("Id", dc))
.SetFetchMode("Funcion", FetchMode.Select)
.SetProjection(projectionList);
var result = preresult.SetResultTransformer(Transformers.AliasToBean <tmpMovMenu>())
.List <tmpMovMenu>();
return(result.Select(m => new MovMenu
{
Alta = m.Alta == 1,
Baja = m.Baja == 1,
Consulta = m.Consulta == 1,
Modificacion = m.Modificacion == 1,
VerMapa = m.VerMapa == 1,
Reporte = m.Reporte == 1,
Orden = m.Orden,
Funcion = m.Funcion
})
.OrderBy(m => m.Funcion.Sistema.Orden)
.ThenBy(m => m.Funcion.Sistema.Descripcion)
.ThenBy(m => m.Orden)
.ThenBy(m => m.Funcion.Descripcion)
.ToList());
}
private int GetMinYear(string selectedRepository)
{
using (var session = DbService.Instance.SessionFactory.OpenSession())
{
var maxYear =
FilteringHelper.Instance.GenerateQuery(session, selectedRepository)
.Select(Projections.ProjectionList().Add(Projections.Min <Commit>(c => c.Date.Year)))
.List <int>()
.First();
return(maxYear);
}
}
public void TestMin()
{
DetachedCriteria expected =
DetachedCriteria.For <Person>()
.SetProjection(Projections.Min("Age"));
DetachedCriteria actual =
DetachedCriteria.For <Person>()
.SetProjection(LambdaProjection.Min <Person>(p => p.Age));
AssertCriteriaAreEqual(expected, actual);
}
public void TestMinUsingAlias()
{
DetachedCriteria expected =
DetachedCriteria.For <Person>("personAlias")
.SetProjection(Projections.Min("personAlias.Age"));
Person personAlias = null;
DetachedCriteria actual =
DetachedCriteria <Person> .Create(() => personAlias)
.SetProjection(LambdaProjection.Min(() => personAlias.Age));
AssertCriteriaAreEqual(expected, actual);
}
public async Task CanMinProjectionOnIdentityProjectionAsync()
{
using (ISession s = OpenSession())
{
ISQLFunction arithmaticAddition = new VarArgsSQLFunction("(", "+", ")");
ICriteria c =
s.CreateCriteria(typeof(Person)).SetProjection(
Projections.Min(Projections.SqlFunction(arithmaticAddition, NHibernateUtil.GuessType(typeof(double)),
Projections.Property("IQ"), Projections.Property("ShoeSize"))));
IList list = await(c.ListAsync());
Assert.AreEqual(19, list[0]);
}
}
public void ArbitraryProjections()
{
ICriteria expected =
CreateTestCriteria(typeof(Person), "personAlias")
.SetProjection(Projections.ProjectionList()
.Add(Projections.Alias(Projections.Avg("Age"), "personAgeProjectionAlias"))
.Add(Projections.Avg("Age"), "personAgeProjectionAlias")
.Add(Projections.Avg("personAlias.Age"), "Age")
.Add(Projections.Count("Age"))
.Add(Projections.Count("personAlias.Age"))
.Add(Projections.CountDistinct("Age"))
.Add(Projections.CountDistinct("personAlias.Age"))
.Add(Projections.GroupProperty("Age"))
.Add(Projections.GroupProperty("personAlias.Age"))
.Add(Projections.Max("Age"))
.Add(Projections.Max("personAlias.Age"))
.Add(Projections.Min("Age"))
.Add(Projections.Min("personAlias.Age"))
.Add(Projections.Property("Age"))
.Add(Projections.Property("personAlias.Age"))
.Add(Projections.SubQuery(DetachedCriteriaAge))
.Add(Projections.Sum("Age"))
.Add(Projections.Sum("personAlias.Age")));
Person personAlias = null;
Person personAgeProjectionAlias = null;
var actual =
CreateTestQueryOver <Person>(() => personAlias)
.Select(Projections.ProjectionList()
.Add(Projections.Avg <Person>(p => p.Age).WithAlias(() => personAgeProjectionAlias))
.Add(Projections.Avg <Person>(p => p.Age), () => personAgeProjectionAlias)
.Add(Projections.Avg(() => personAlias.Age).WithAlias(() => personAlias.Age))
.Add(Projections.Count <Person>(p => p.Age))
.Add(Projections.Count(() => personAlias.Age))
.Add(Projections.CountDistinct <Person>(p => p.Age))
.Add(Projections.CountDistinct(() => personAlias.Age))
.Add(Projections.Group <Person>(p => p.Age))
.Add(Projections.Group(() => personAlias.Age))
.Add(Projections.Max <Person>(p => p.Age))
.Add(Projections.Max(() => personAlias.Age))
.Add(Projections.Min <Person>(p => p.Age))
.Add(Projections.Min(() => personAlias.Age))
.Add(Projections.Property <Person>(p => p.Age))
.Add(Projections.Property(() => personAlias.Age))
.Add(Projections.SubQuery(DetachedQueryOverAge))
.Add(Projections.Sum <Person>(p => p.Age))
.Add(Projections.Sum(() => personAlias.Age)));
AssertCriteriaAreEqual(expected, actual);
}
public void InlineProjectionList()
{
ICriteria expected =
CreateTestCriteria(typeof(Person), "personAlias")
.SetProjection(Projections.ProjectionList()
.Add(Projections.Alias(Projections.Avg("Age"), "personAgeProjectionAlias"))
.Add(Projections.Avg("Age"))
.Add(Projections.Avg("personAlias.Age"), "Age")
.Add(Projections.Count("Age"))
.Add(Projections.Count("personAlias.Age"))
.Add(Projections.CountDistinct("Age"))
.Add(Projections.CountDistinct("personAlias.Age"))
.Add(Projections.GroupProperty("Age"))
.Add(Projections.GroupProperty("personAlias.Age"))
.Add(Projections.Max("Age"))
.Add(Projections.Max("personAlias.Age"))
.Add(Projections.Min("Age"))
.Add(Projections.Min("personAlias.Age"))
.Add(Projections.Property("Age"))
.Add(Projections.Property("personAlias.Age"))
.Add(Projections.SubQuery(DetachedCriteriaAge))
.Add(Projections.Sum("Age"))
.Add(Projections.Sum("personAlias.Age")));
Person personAlias = null;
Person personAgeProjectionAlias = null;
var actual =
CreateTestQueryOver <Person>(() => personAlias)
.SelectList(list => list
.SelectAvg(p => p.Age).WithAlias(() => personAgeProjectionAlias)
.Select(Projections.Avg("Age")) // allows private properties
.SelectAvg(() => personAlias.Age).WithAlias(() => personAlias.Age)
.SelectCount(p => p.Age)
.SelectCount(() => personAlias.Age)
.SelectCountDistinct(p => p.Age)
.SelectCountDistinct(() => personAlias.Age)
.SelectGroup(p => p.Age)
.SelectGroup(() => personAlias.Age)
.SelectMax(p => p.Age)
.SelectMax(() => personAlias.Age)
.SelectMin(p => p.Age)
.SelectMin(() => personAlias.Age)
.Select(p => p.Age)
.Select(() => personAlias.Age)
.SelectSubQuery(DetachedQueryOverAge)
.SelectSum(p => p.Age)
.SelectSum(() => personAlias.Age));
AssertCriteriaAreEqual(expected, actual);
}
private DateTime GetCriticalEndTime(Guid timeBoxId, DateTime time)
{
//大于End中最小的endValue
var endValue = Session.CreateCriteria <TimeBox>()
.Add(Restrictions.IdEq(timeBoxId))
.CreateAlias("TermSet", "t")
.Add(Restrictions.Eq("t.Level", 0))
.Add(Restrictions.Gt("t.End", time))
.SetProjection(Projections.Min("t.End"))
.UniqueResult <DateTime>();
if (endValue == DateTime.MinValue)
{
endValue = time;
}
return(endValue);
}
public void QueryTestWithStrongTypeReturnValue()
{
using (ISession session = OpenSession())
{
ICriteria c = session.CreateCriteria(typeof(ProjectionTestClass));
NHibernate.Transform.IResultTransformer trans = new NHibernate.Transform.AliasToBeanConstructorResultTransformer(
typeof(ProjectionReport).GetConstructors()[0]
);
c.SetProjection(Projections.ProjectionList()
.Add(Projections.Avg("Pay"))
.Add(Projections.Max("Pay"))
.Add(Projections.Min("Pay")));
c.SetResultTransformer(trans);
ProjectionReport report = c.UniqueResult <ProjectionReport>();
Assert.AreEqual(report.AvgPay, 2.5);
Assert.AreEqual(report.MaxPay, 4);
Assert.AreEqual(report.MinPay, 1);
}
}
public void QueryTest1()
{
using (ISession session = OpenSession())
{
ICriteria c = session.CreateCriteria(typeof(ProjectionTestClass));
c.SetProjection(Projections.ProjectionList()
.Add(Projections.Avg("Pay"))
.Add(Projections.Max("Pay"))
.Add(Projections.Min("Pay")))
;
IList result = c.List();// c.UniqueResult();
Assert.IsTrue(result.Count == 1, "More than one record was found, while just one was expected");
Assert.IsTrue(result[0] is object[],
"expected object[] as result, but found " + result[0].GetType().Name);
object[] results = (object[])result[0];
Assert.AreEqual(results.Length, 3);
Assert.AreEqual(results[0], 2.5);
Assert.AreEqual(results[1], 4);
Assert.AreEqual(results[2], 1);
}
}
public void CanOrderBySubqueryProjectionDesc()
{
using (ISession s = OpenSession())
{
DetachedCriteria dc = DetachedCriteria.For <Person>("sub");
dc.CreateCriteria("Pets", "pets").SetProjection(Projections.Min("pets.Weight")).Add(
Restrictions.EqProperty("this.Id", "sub.Id"));
ICriteria c = s.CreateCriteria(typeof(Person)).AddOrder(Order.Desc(Projections.SubQuery(dc)));
IList <Person> list = c.List <Person>();
int nullRelationOffSet = 0;
if (Dialect is Oracle8iDialect || Dialect is PostgreSQLDialect)
{
// Oracle order NULL First (DESC)
nullRelationOffSet = 2;
}
Assert.AreEqual(list[nullRelationOffSet + 2].Name, "Tim");
Assert.AreEqual(list[nullRelationOffSet + 1].Name, "Joe");
Assert.AreEqual(list[nullRelationOffSet].Name, "Sally");
}
}
public ValoresAgregados GetAggregates(int dispositivo, string sensorCode, DateTime desde, DateTime hasta)
{
var sensorDao = new SensorDAO();
var sensor = sensorDao.FindByCode(dispositivo, sensorCode);
if (sensor == null)
{
return(null);
}
ValoresAgregados valoresAgregados = null;
return(Session.QueryOver <Medicion>()
.Where(m => m.Dispositivo.Id == dispositivo && m.Sensor.Id == sensor.Id)
.And(m => m.FechaMedicion >= desde && m.FechaMedicion <= hasta)
.Select(Projections.Max <Medicion>(m => m.ValorNum1).WithAlias(() => valoresAgregados.Max),
Projections.Min <Medicion>(m => m.ValorNum1).WithAlias(() => valoresAgregados.Min),
Projections.Avg <Medicion>(m => m.ValorNum1).WithAlias(() => valoresAgregados.Avg)
)
.TransformUsing(Transformers.AliasToBean <ValoresAgregados>())
.List <ValoresAgregados>()
.FirstOrDefault());
}
private Luna.Core.Tuple <DateTime, DateTime> GetTime(Guid timeBoxId, DateTime start, DateTime end)
{
//小于start的最大的startValue
var startValue = Session.CreateCriteria <TimeBox>()
.Add(Restrictions.IdEq(timeBoxId))
.CreateAlias("TermSet", "t")
.Add(Restrictions.Eq("t.Level", 0))
.Add(Restrictions.Lt("t.Start", start))
.SetProjection(Projections.Max("t.Start"));
//大于End中最小的endValue
var endValue = Session.CreateCriteria <TimeBox>()
.Add(Restrictions.IdEq(timeBoxId))
.CreateAlias("TermSet", "t")
.Add(Restrictions.Eq("t.Level", 0))
.Add(Restrictions.Gt("t.End", end))
.SetProjection(Projections.Min("t.End"));
var query = Session.CreateMultiCriteria().Add(startValue).Add(endValue).List();
var a = ((IList)(query[0]))[0] == null ? start : (DateTime)((IList)(query[0]))[0];
var b = ((IList)(query[1]))[0] == null ? end : (DateTime)((IList)(query[1]))[0];
return(new Core.Tuple <DateTime, DateTime>(a, b));
}
public void TestCtor()
{
try
{
new AggregateExitOperation(new AvgProjection("foo"));
Assert.Fail();
}
catch (ArgumentException e)
{
// good
}
try
{
new AggregateExitOperation(new AvgProjection("foo"));
Assert.Fail();
}
catch (ArgumentException e)
{
// good
}
new AggregateExitOperation(Projections.Max("foo"));
new AggregateExitOperation(Projections.Min("foo"));
new AggregateExitOperation(Projections.Sum("foo"));
}
public async Task ExecutableCriteriaAsync()
{
// All query below don't have sense, are only to test if all needed classes are serializable
// Basic criterion
DetachedCriteria dc = DetachedCriteria.For(typeof(Student))
.Add(Expression.Between("Name", "aaaaa", "zzzzz"))
.Add(Expression.EqProperty("Name", "Name"))
.Add(Expression.Ge("Name", "a"))
.Add(Expression.GeProperty("Name", "Name"))
.Add(Expression.Gt("Name", "z"))
.Add(Expression.GtProperty("Name", "Name"))
.Add(Expression.IdEq(1))
.Add(Expression.In("Name", new string[] { "Gavin", "Ralph" }))
.Add(Expression.InsensitiveLike("Name", "GAVIN"))
.Add(Expression.IsEmpty("Enrolments"))
.Add(Expression.IsNotEmpty("Enrolments"))
.Add(Expression.IsNotNull("PreferredCourse"))
.Add(Expression.IsNull("PreferredCourse"))
.Add(Expression.Le("Name", "a"))
.Add(Expression.LeProperty("Name", "Name"))
.Add(Expression.Lt("Name", "z"))
.Add(Expression.LtProperty("Name", "Name"))
.Add(Expression.Like("Name", "G%"))
.Add(Expression.Not(Expression.Eq("Name", "Ralph")))
.Add(Expression.NotEqProperty("Name", "Name"))
.AddOrder(Order.Asc("StudentNumber"))
.SetProjection(Property.ForName("StudentNumber"));
await(SerializeAndListAsync(dc));
// Like match modes
dc = DetachedCriteria.For(typeof(Student))
.Add(Expression.Like("Name", "Gavin", MatchMode.Anywhere))
.Add(Expression.Like("Name", "Gavin", MatchMode.End))
.Add(Expression.Like("Name", "Gavin", MatchMode.Exact))
.Add(Expression.Like("Name", "Gavin", MatchMode.Start));
await(SerializeAndListAsync(dc));
// Logical Expression
dc = DetachedCriteria.For(typeof(Student))
.Add(Expression.Or(Expression.Eq("Name", "Ralph"), Expression.Eq("Name", "Gavin")))
.Add(
Expression.And(Expression.Gt("StudentNumber", 1L),
Expression.Lt("StudentNumber", 10L)));
await(SerializeAndListAsync(dc));
// Projections
dc = DetachedCriteria.For(typeof(Enrolment))
.SetProjection(Projections.Distinct(Projections.ProjectionList()
.Add(Projections.Property("StudentNumber"), "stNumber")
.Add(Projections.Property("CourseCode"), "cCode")))
.Add(Expression.Lt("StudentNumber", 668L));
await(SerializeAndListAsync(dc));
if (TestDialect.SupportsCountDistinct)
{
dc = DetachedCriteria.For(typeof(Enrolment))
.SetProjection(Projections.Count("StudentNumber").SetDistinct());
await(SerializeAndListAsync(dc));
}
dc = DetachedCriteria.For(typeof(Enrolment))
.SetProjection(Projections.ProjectionList()
.Add(Projections.Count("StudentNumber"))
.Add(Projections.Max("StudentNumber"))
.Add(Projections.Min("StudentNumber"))
.Add(Projections.Avg("StudentNumber")));
await(SerializeAndListAsync(dc));
// Junctions
dc = DetachedCriteria.For(typeof(Student))
.Add(Expression.Conjunction()
.Add(Expression.Eq("Name", "Ralph"))
.Add(Expression.Eq("StudentNumber", 1L)))
.Add(Expression.Disjunction()
.Add(Expression.Eq("Name", "Ralph"))
.Add(Expression.Eq("Name", "Gavin")));
await(SerializeAndListAsync(dc));
// Subquery
dc = DetachedCriteria.For(typeof(Student))
.Add(Property.ForName("StudentNumber").Eq(232L))
.SetProjection(Property.ForName("Name"));
DetachedCriteria dcs = DetachedCriteria.For(typeof(Student))
.Add(Subqueries.PropertyEqAll("Name", dc));
await(SerializeAndListAsync(dc));
// SQLCriterion
dc = DetachedCriteria.For(typeof(Student))
.Add(Expression.Sql("{alias}.Name = 'Gavin'"));
await(SerializeAndListAsync(dc));
// SQLProjection
dc = DetachedCriteria.For(typeof(Enrolment))
.SetProjection(Projections.SqlProjection("1 as constOne, count(*) as countStar",
new String[] { "constOne", "countStar" },
new IType[] { NHibernateUtil.Int32, NHibernateUtil.Int32 }));
await(SerializeAndListAsync(dc));
dc = DetachedCriteria.For(typeof(Student))
.SetProjection(
Projections.SqlGroupProjection("COUNT({alias}.studentId), {alias}.preferredCourseCode",
"{alias}.preferredCourseCode",
new string[] { "studentsOfCourse", "CourseCode" },
new IType[] { NHibernateUtil.Int32, NHibernateUtil.Int32 }));
await(SerializeAndListAsync(dc));
// Result transformers
dc = DetachedCriteria.For(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)));
await(SerializeAndListAsync(dc));
}
protected override TResult RecalculateValue()
{
return(min = Projections.Min(i => i.Value));
}