public void ProjectionAndGroupTest()
{
using var session = NHibernateHelper.OpenSession();
var actual = session.CreateCriteria <Cat>()
.SetProjection(Projections.ProjectionList()
.Add(Projections.RowCount())
.Add(Projections.Avg("Weight"))
.Add(Projections.Max("Weight"))
.Add(Projections.GroupProperty("Sex")))
.List();
var expected = _cats
.GroupBy(c => c.Sex)
.Select(x => new
{
Count = x.Count(),
Avg = x.Average(c => c.Weight),
Max = x.Max(c => c.Weight),
Sex = x.Key,
})
.ToList();
actual.Count.ShouldBe(expected.Count);
foreach (var item in actual)
{
var row = (item as IList);
var expectedItem = expected.First(c => c.Sex == (char)row[3]);
row[0].ShouldBe(expectedItem.Count);
Math.Round((double)row[1], 3).ShouldBe(Math.Round(expectedItem.Avg, 3));
Math.Round((float)row[2], 3).ShouldBe(Math.Round(expectedItem.Max, 3));
}
}
/// <summary>
/// Gets aggregate activity for each specified transport company.
/// </summary>
/// <param name="desde"></param>
/// <param name="hasta"></param>
/// <param name="ids"></param>
/// <returns></returns>
public IEnumerable <TransportActivity> GetTransportActivities(DateTime desde, DateTime hasta, List <int> ids)
{
TransportActivity ta = null;
DetachedCriteria dc = GetDatamartDetachedCriteria(ids.ToArray()).FilterBeginBetween(desde, hasta);
ProjectionList pl = Projections.ProjectionList();
pl.Add(Projections.Sum <Datamart>(dm => dm.MovementHours).WithAlias(() => ta.HsActivos));
pl.Add(Projections.Sum <Datamart>(dm => dm.StoppedHours).WithAlias(() => ta.HsInactivos));
pl.Add(Projections.Sum <Datamart>(dm => dm.InfractionMinutes).WithAlias(() => ta.HsInfraccion));
pl.Add(Projections.Sum <Datamart>(dm => dm.Infractions).WithAlias(() => ta.Infracciones));
pl.Add(Projections.Sum <Datamart>(dm => dm.Kilometers).WithAlias(() => ta.Recorrido));
pl.Add(Projections.Max <Datamart>(dm => dm.MaxSpeed).WithAlias(() => ta.VelocidadMaxima));
pl.Add(Projections.Avg <Datamart>(dm => dm.AverageSpeed).WithAlias(() => ta.VelocidadPromedio));
pl.Add(Projections.Group <Datamart>(dm => dm.Vehicle.Id).WithAlias(() => ta.IdVehiculo));
ICriteria crit = GetDatamartCriteria(0, dc, pl, null, typeof(TransportActivity));
IList <TransportActivity> results = crit.List <TransportActivity>();
var cocheDAO = new CocheDAO();
foreach (TransportActivity r in results)
{
Coche coche = cocheDAO.FindById(r.Id);
r.HsInfraccion /= 60;
r.Transport = coche.Transportista.Descripcion;
r.CentroDeCostos = coche.CentroDeCostos != null ? coche.CentroDeCostos.Descripcion : null;
r.TipoVehiculo = coche.TipoCoche != null ? coche.TipoCoche.Descripcion : null;
}
return(results);
}
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 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 IEnumerable <RechazoPromedioRolModel> GetPromedioPorRol(int distritoId, int baseId)
{
RechazoMov mov = null;
Empleado empEgreso = null;
TipoEmpleado tEmpleado = null;
TicketRechazo ticket = null;
var q = Session
.QueryOver(() => mov)
.Inner.JoinAlias(() => mov.EmpledoEgreso, () => empEgreso)
.Left.JoinAlias(() => empEgreso.TipoEmpleado, () => tEmpleado)
.Inner.JoinAlias(() => mov.Ticket, () => ticket)
.Select(Projections.ProjectionList()
.Add(Projections.Count(() => mov.Id).As("Cantidad"))
.Add(Projections.Avg(() => mov.Lapso).As("Promedio"))
.Add(Projections.Group(() => tEmpleado.Codigo).As("TipoEmpleado"))
);
if (distritoId != -1)
{
q = q.Where(m => ticket.Empresa.Id == distritoId);
}
if (baseId != -1)
{
q = q.Where(m => ticket.Linea.Id == baseId);
}
q = q.TransformUsing(Transformers.AliasToBean <RechazoPromedioRolModel>());
return(q.Future <RechazoPromedioRolModel>());
}
public List <long> GetTubineBladesWithMaxTempInAVGorMAX()
{
using (ITransaction transaction = session.BeginTransaction())
{
List <long> results = null;
TurbineBlade turbineBladeAlias = null;
QueryOver <TurbineBlade> subqueryAVG =
QueryOver.Of <TurbineBlade>()
.SelectList(list => list.SelectMax(tb => tb.MaxTemp));
QueryOver <TurbineBlade> subqueryMAX =
QueryOver.Of <TurbineBlade>()
.SelectList(list => list.SelectMax(tb => tb.MaxTemp));
QueryOver <TurbineBlade> subqueryAVGMAX =
QueryOver.Of <TurbineBlade>(() => turbineBladeAlias)
.Select(Projections.Avg(Projections.Property(() => turbineBladeAlias.MaxTemp)))
.Select(Projections.Max(Projections.Property(() => turbineBladeAlias.MaxTemp)));
results = session.QueryOver <TurbineBlade>(() => turbineBladeAlias)
//.Where(() => turbineBladeAlias.MaxTemp.IsIn(new object[] {subqueryMAX, subqueryAVG}))
.WithSubquery
.WhereProperty(() => turbineBladeAlias.MaxTemp).In(subqueryAVGMAX)
.Select(tb => tb.Id)
.List <long>().ToList();
transaction.Commit();
return(results);
}
}
public IEnumerable <PromedioPorEstadoModel> GetPromedioPorEstado(int distritoId, int baseId)
{
RechazoMov mov = null;
TicketRechazo ticket = null;
var q = Session
.QueryOver(() => mov)
.Inner.JoinAlias(() => mov.Ticket, () => ticket)
.Select(Projections.ProjectionList()
.Add(Projections.Count(() => mov.Id).As("Cantidad"))
.Add(Projections.Avg(() => mov.Lapso).As("Promedio"))
.Add(Projections.Group(() => mov.EstadoEgreso).As("EstadoEnum"))
.Add(Projections.Group(() => ticket.Empresa.Id).As("EmpresaId"))
.Add(Projections.Group(() => ticket.Linea.Id).As("BaseId"))
).OrderBy(Projections.Avg(() => mov.Lapso).As("Promedio")).Desc;
if (distritoId != -1)
{
q = q.Where(m => ticket.Empresa.Id == distritoId);
}
if (baseId != -1)
{
q = q.Where(m => ticket.Linea.Id == baseId);
}
q = q.TransformUsing(Transformers.AliasToBean <PromedioPorEstadoModel>());
return(q.Future <PromedioPorEstadoModel>());
}
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 DetachQueryAsSubqueryTest()
{
using var session = NHibernateHelper.OpenSession();
var avgWeight = DetachedCriteria
.For <Cat>()
.SetProjection(Projections.Avg("Weight"));
var actual = session
.CreateCriteria <Cat>()
.Add(Subqueries.PropertyGt("Weight", avgWeight))
.List <Cat>();
var avgWeightExpected = _cats.Average(c => c.Weight);
var expected = _cats.Where(c => c.Weight > avgWeightExpected);
AssertTwoListContainSameItems(actual.Select(c => c.Id), expected.Select(c => c.Id));
var weights = DetachedCriteria
.For <Cat>()
.SetProjection(Projections.Property("Weight"));
actual = session
.CreateCriteria <Cat>()
.Add(Subqueries.PropertyGeAll("Weight", weights))
.List <Cat>();
var expectedCat = _cats.OrderByDescending(c => c.Weight).First();
actual.Count.ShouldBe(1);
actual.First().Id.ShouldBe(expectedCat.Id);
}
public void average()
{
var averageItemsInStock = Session.QueryOver <Product>()
.Select(Projections.Avg <Product>(p => p.UnitsInStock))
.SingleOrDefault <double>();
Assert.AreEqual(_products.Average(p => p.UnitsInStock), averageItemsInStock);
}
public decimal GetAverageMoviePrice()
{
var result = _session.QueryOver <Movie>()
.Select(Projections.Avg <Movie>(m => m.UnitPrice))
.SingleOrDefault <double>();
return(Convert.ToDecimal(result));
}
public void call_stored_procedure_that_returns_non_entity_value()
{
var averageSalary = Session.QueryOver <Employee>()
.Select(Projections.Avg <Employee>(e => e.Salary))
.SingleOrDefault <double>();
Assert.AreEqual(averageSalary, Session.GetNamedQuery("GET_AVERAGE_SALARY").UniqueResult());
}
public void average()
{
var averageItemsInStock = Session.CreateCriteria <Product>()
.SetProjection(Projections.Avg("UnitsInStock"))
.UniqueResult <double>();
Assert.AreEqual(_products.Average(p => p.UnitsInStock), averageItemsInStock);
}
public IList <User> GeAvgAgeUsers()
{
var crit = session.CreateCriteria <User>();
var detachCriteria = DetachedCriteria.For <User>() //подзапрос
.SetProjection(Projections.Avg("Age"));
crit.Add(Subqueries.PropertyGe("Age", detachCriteria));
return(crit.List <User>());
}
public List <ProductStatisticWeekly> CalculateStatistic(int week, int year, Period period)
{
int interval = 0;
if (Convert.ToInt32(period) == 1)
{
interval = 4;
}
else if (Convert.ToInt32(period) == 2)
{
interval = 8;
}
else if (Convert.ToInt32(period) == 3)
{
interval = 13;
}
else if (Convert.ToInt32(period) == 4)
{
interval = 26;
}
else if (Convert.ToInt32(period) == 5)
{
interval = 53;
}
ICriteria crit = GetCriteria();
int weekDif = (week - interval);
if (weekDif <= 0)
{
Disjunction dis = new Disjunction();
dis.Add(
new AndExpression(new BetweenExpression("Week", 53 + weekDif, 53),
new EqExpression("Year", year - 1)));
dis.Add(new AndExpression(new BetweenExpression("Week", 1, week), new EqExpression("Year", year)));
crit.Add(dis);
}
else
{
crit.Add(
new AndExpression(new BetweenExpression("Week", week - interval, week),
new EqExpression("Year", year)));
}
crit.SetProjection(Projections.ProjectionList()
.Add(Projections.Avg("Purchase"))
.Add(Projections.Avg("Sale"))
.Add(Projections.GroupProperty("ProductID"))
);
crit.SetResultTransformer(
new AliasToBeanConstructorResultTransformer(typeof(ProductStatisticWeekly).GetConstructors()[1]));
return(crit.List <ProductStatisticWeekly>() as List <ProductStatisticWeekly>);
}
public TResult Avg <TEntity, TResult>(string propertyName, IWhereGetter where = null) where TEntity : class
{
var iCriteria = this.Session.CreateCriteria <TEntity>();
iCriteria.SetProjection(Projections.Avg(propertyName));
iCriteria = QueryParameterHelper.Add(iCriteria, where);
var result = iCriteria.UniqueResult <TResult>();
return(result);
}
public IList <User> GeAvgAgeUsers()
{
var crit = session.CreateCriteria <User>();
var detachCriteria = DetachedCriteria.For <User>()
.SetProjection(Projections.Avg("Age"));
crit.Add(Subqueries.PropertyGe("Age", detachCriteria));
return(crit.List <User>());
//"select * from User where Age >= (select avg(Age) from User)"
}
public BatchCompletionStatus GetCompletionStatus(BatchRun batchRun)
{
IFutureValue <decimal?> timeTaken =
GetResultsQuery(batchRun)
.Where(result => result.MillisecondsTaken != null)
.Select(Projections.Sum <BatchRunResult>(result => result.MillisecondsTaken))
.Cacheable()
.FutureValue <decimal?>();
IFutureValue <double?> averageTimeTaken =
GetResultsQuery(batchRun)
.Where(result => result.MillisecondsTaken != null)
.Select(Projections.Avg <BatchRunResult>(result => result.MillisecondsTaken))
.Cacheable()
.FutureValue <double?>();
IFutureValue <int> pending =
GetResultsQuery(batchRun)
.Where(result => result.Status == JobExecutionStatus.Pending)
.Select(Projections.Count <BatchRunResult>(result => result.Id))
.Cacheable()
.FutureValue <int>();
IFutureValue <int> failed =
GetResultsQuery(batchRun)
.Where(result => result.Status == JobExecutionStatus.Failed)
.Select(Projections.Count <BatchRunResult>(result => result.Id))
.Cacheable()
.FutureValue <int>();
IFutureValue <int> succeeded =
GetResultsQuery(batchRun)
.Where(result => result.Status == JobExecutionStatus.Succeeded)
.Select(Projections.Count <BatchRunResult>(result => result.Id))
.Cacheable()
.FutureValue <int>();
IFutureValue <int> total =
GetResultsQuery(batchRun)
.Select(Projections.Count <BatchRunResult>(result => result.Id))
.Cacheable()
.FutureValue <int>();
double averageTime = averageTimeTaken.Value.GetValueOrDefault();
int pendingNumber = pending.Value;
return(new BatchCompletionStatus
{
Total = total.Value,
Failed = failed.Value,
Pending = pendingNumber,
Succeeded = succeeded.Value,
TimeTaken = TimeSpan.FromMilliseconds(Convert.ToDouble(timeTaken.Value.GetValueOrDefault())),
AverageTimeTaken = averageTime.ToString("0.00ms"),
EstimatedTimeRemaining = TimeSpan.FromMilliseconds(averageTime * pendingNumber)
});
}
public void Test_Avg()
{
DetachedCriteria expected =
DetachedCriteria.For <Person>()
.SetProjection(Projections.Avg("Age"));
DetachedCriteria actual =
DetachedCriteria.For <Person>()
.SetProjection(LambdaProjection.Avg <Person>(p => p.Age));
AssertCriteriaAreEqual(expected, actual);
}
public void DifferentProjections()
{
ICriteria expected =
CreateTestCriteria(typeof(Person))
.SetProjection(Projections.Avg("Age"));
ICriteria actual =
CreateTestCriteria(typeof(Person))
.SetProjection(Projections.Max("Age"));
AssertCriteriaAreNotEqual(expected, actual);
}
public void TestAvgUsingAlias()
{
DetachedCriteria expected =
DetachedCriteria.For <Person>("personAlias")
.SetProjection(Projections.Avg("personAlias.Age"));
Person personAlias = null;
DetachedCriteria actual =
DetachedCriteria <Person> .Create(() => personAlias)
.SetProjection(LambdaProjection.Avg(() => personAlias.Age));
AssertCriteriaAreEqual(expected, actual);
}
public async Task CanAvgProjectionOnSqlFunctionAsync()
{
using (ISession s = OpenSession())
{
ISQLFunction arithmaticAddition = new VarArgsSQLFunction("(", "+", ")");
ICriteria c =
s.CreateCriteria(typeof(Person)).SetProjection(
Projections.Avg(Projections.SqlFunction(arithmaticAddition, NHibernateUtil.GuessType(typeof(double)),
Projections.Property("IQ"), Projections.Property("ShoeSize"))));
IList list = await(c.ListAsync());
Assert.AreEqual(((double)334) / 5, list[0]);
}
}
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);
}
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 HavingOnLtAverage()
{
using (ISession s = OpenSession())
using (ITransaction tx = s.BeginTransaction())
{
//Find the iq that two people share
string name = s.CreateCriteria(typeof(Person))
.SetProjection(Projections.GroupProperty("Name"))
.Add(Restrictions.Lt(Projections.Avg("IQ"), 20))
.UniqueResult <string>();
Assert.AreEqual("Joe", name);
tx.Commit();
}
}
static void ICriteriaTest(ISessionFactory sessionFactory)
{
Console.WriteLine("Running ICriteria sample");
using (ISession session = sessionFactory.OpenSession())
{
var salary = session.CreateCriteria(typeof(Employee))
.Add(Restrictions.Between("Age", 25, 30))
.SetProjection(Projections.Avg("Salary"))
.UniqueResult <double>();
Console.WriteLine(salary);
Console.WriteLine();
Console.WriteLine();
}
}
public void AvgTest()
{
ISession session = factory.OpenSession();
IProjection expression = Projections.Avg("Pay");
CreateObjects(typeof(Simple), session);
IType nhType = NHibernateUtil.GuessType(typeof(double));
SqlType[] sqlTypes = nhType.SqlTypes(this.factoryImpl);
string sqlTypeString = factoryImpl.Dialect.GetCastTypeName(sqlTypes[0]);
SqlString sqlString = expression.ToSqlString(criteria, 0, criteriaQuery);
string expectedSql = string.Format("avg(cast(sql_alias.Pay as {0})) as y0_", sqlTypeString);
CompareSqlStrings(sqlString, expectedSql, 0);
session.Close();
}
public List <SaleOrderItem> PendingSaleOrderQuantity(DateTime startdate)
{
ICriteria crit = GetCriteria();
crit.Add(new GtPropertyExpression("Quantity", "QuantityDelivery"));
crit.CreateCriteria("SaleOrder").Add(new GeExpression("Date", startdate));
crit.SetProjection(Projections.ProjectionList()
.Add(Projections.Sum("Quantity"))
.Add(Projections.Avg("QuantityDelivery"))
.Add(Projections.GroupProperty("Product"))
);
crit.SetResultTransformer(
new AliasToBeanConstructorResultTransformer(typeof(SaleOrderItem).GetConstructors()[2]));
return(crit.List <SaleOrderItem>() as List <SaleOrderItem>);
}
public void AvgWithConditionalDoesNotThrow()
{
using (var session = OpenSession())
using (session.BeginTransaction())
{
MappingEntity mappingEntity = null;
Assert.DoesNotThrow(
() =>
session.QueryOver <Entity>().SelectList(
builder =>
builder.Select(
Projections.Avg(
Projections.Conditional(
Restrictions.Eq(Projections.Property <Entity>(x => x.Name), "FOO"),
Projections.Constant("", NHibernateUtil.String),
Projections.Constant(null, NHibernateUtil.String))).WithAlias(() => mappingEntity.Count))
).TransformUsing(Transformers.AliasToBean <MappingEntity>()).List <MappingEntity>()
);
}
}
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);
}
}