Not sure this is even possible but I'll explain anyway.
I have a custom projection for doing simple arithmetic in a query
which works perfectly in a single projection.
namespace Custom.Projections
{
using System.Collections.Generic;
using System.Text;
using global::NHibernate;
using global::NHibernate.Criterion;
using global::NHibernate.SqlCommand;
using global::NHibernate.Type;
/// <summary>
/// Represents a CalculatedProjection
/// </summary>
public class CalculatedProjection : SimpleProjection
{
private readonly CalculationType calculationType;
private readonly IProjection firstProjection;
private readonly IProjection secondProjection;
private readonly string firstPropertyName;
private readonly string secondPropertyName;
/// <summary>
/// Initializes a new instance of the CalculatedProjection
class
/// </summary>
/// <param name="type">The type of calculation</param>
/// <param name="firstPropertyName">The name of the first
property</param>
/// <param name="secondPropertyName">The name of the
second property</param>
protected internal CalculatedProjection(CalculationType
type, string firstPropertyName, string secondPropertyName)
{
this.calculationType = type;
this.firstPropertyName = firstPropertyName;
this.secondPropertyName = secondPropertyName;
System.Diagnostics.Debugger.Launch();
}
/// <summary>
/// Initializes a new instance of the CalculatedProjection
class
/// </summary>
/// <param name="type">The type of calculation</param>
/// <param name="firstProjection">The first projection</
param>
/// <param name="secondProjection">The second projection</
param>
protected internal CalculatedProjection(CalculationType
type, IProjection firstProjection, IProjection secondProjection)
{
this.calculationType = type;
this.firstProjection = firstProjection;
this.secondProjection = secondProjection;
}
/// <summary>
/// The type of calculation
/// </summary>
public enum CalculationType
{
/// <summary>
/// Addition +
/// </summary>
Addition,
/// <summary>
/// Subtraction -
/// </summary>
Subtraction,
/// <summary>
/// Division /
/// </summary>
Division,
/// <summary>
/// Division *
/// </summary>
Multiplication,
}
/// <summary>
/// Gets a value indicating whether the projection is
grouped
/// </summary>
public override bool IsGrouped
{
get { return false; }
}
/// <summary>
/// Gets a value indicating whether IsAggregate.
/// </summary>
public override bool IsAggregate
{
get { return false; }
}
/// <summary>
/// Converts the calculation into a string
/// </summary>
/// <returns>The string representation of the calculation</
returns>
public override string ToString()
{
var firstValue = this.firstProjection != null ?
this.firstProjection.ToString() : this.firstPropertyName;
var secondValue = this.secondProjection != null ?
this.secondProjection.ToString() : this.secondPropertyName;
return "(" + firstValue +
TypeToString(this.calculationType) + secondValue + ")";
}
/// <summary>
/// Gets the types involved in the query
/// </summary>
/// <param name="criteria">The current criteria</param>
/// <param name="criteriaQuery">The criteria query</param>
/// <returns>An array of IType</returns>
public override IType[] GetTypes(ICriteria criteria,
ICriteriaQuery criteriaQuery)
{
var types = new List<IType>();
if (this.firstProjection != null)
{
types.AddRange(this.firstProjection.GetTypes(criteria,
criteriaQuery));
}
else
{
types.Add(criteriaQuery.GetType(criteria,
this.firstPropertyName));
}
if (this.secondProjection != null)
{
types.AddRange(this.secondProjection.GetTypes(criteria,
criteriaQuery));
}
else
{
types.Add(criteriaQuery.GetType(criteria,
this.secondPropertyName));
}
return types.ToArray();
}
/// <summary>
/// Converts the objects to an sql string representation
/// </summary>
/// <param name="criteria">The criteria being used in the
query</param>
/// <param name="loc">The location in the query</param>
/// <param name="criteriaQuery">The criteria query</param>
/// <param name="enabledFilters">List of enabled filters</
param>
/// <returns>The calculation as an sql string</returns>
public override SqlString ToSqlString(ICriteria criteria,
int loc, ICriteriaQuery criteriaQuery, IDictionary<string, IFilter>
enabledFilters)
{
string first, second;
if ((this.firstProjection != null) &&
(this.secondProjection != null))
{
first =
global::NHibernate.Util.StringHelper.RemoveAsAliasesFromSql(this.firstProjection.ToSqlString(criteria,
loc, criteriaQuery, enabledFilters)).ToString();
second =
global::NHibernate.Util.StringHelper.RemoveAsAliasesFromSql(this.secondProjection.ToSqlString(criteria,
loc, criteriaQuery, enabledFilters)).ToString();
}
else
{
first = criteriaQuery.GetColumn(criteria,
this.firstPropertyName);
second = criteriaQuery.GetColumn(criteria,
this.secondPropertyName);
}
return new SqlString(new object[] { "(", first,
TypeToString(this.calculationType), second, ") as y", loc.ToString(),
"_" });
}
/// <summary>
/// Converts the objects to an sql string representation
/// </summary>
/// <param name="criteria">The criteria being used in the
query</param>
/// <param name="criteriaQuery">The criteria query</param>
/// <param name="enabledFilters">List of enabled filters</
param>
/// <returns>The calculation as an sql string</returns>
public override SqlString ToGroupSqlString(ICriteria
criteria, ICriteriaQuery criteriaQuery, IDictionary<string, IFilter>
enabledFilters)
{
var sb = new StringBuilder();
return new SqlString(new object[] { sb.ToString() });
}
/// <summary>
/// Returns the string symbol of calculation type
/// </summary>
/// <param name="type">The type to use</param>
/// <returns>The string representation</returns>
private static string TypeToString(CalculationType type)
{
switch (type)
{
case CalculationType.Addition: return "+";
case CalculationType.Subtraction: return "-";
case CalculationType.Multiplication: return "*";
case CalculationType.Division: return "/";
default: return "+";
}
}
}
}
I am now trying to use this projection from within another using the
QueryOver syntax as below:
public override IList<DaySummaryDetail> Execute()
{
// ReSharper disable PossibleNullReferenceException
var calculated =
WheatleyProjections.Calculated(CalculatedProjection.CalculationType.Subtraction,
"Duration.EndTime", "Duration.StartTime");
DaySummaryDetail detail = null;
return Session.QueryOver<WorkingDay>()
.SelectList(list => list
.Select(Projections.Group<WorkingDay>(x =>
x.Date).WithAlias(() => detail.Date))
.Select(Projections.Sum(calculated)).WithAlias(() =>
detail.TotalMinutes))
.Where(x => x.Person.Id == 1)
.TransformUsing(Transformers.AliasToBean<DaySummaryDetail>())
.List<DaySummaryDetail>();
// ReSharper restore PossibleNullReferenceException
}
The query generated is correct:
SELECT
this_.DATE_TIME as y0_,
sum((this_.END_MINS-this_.START_MINS)) as y1_
FROM
WORKINGDAY this_
WHERE
this_.PERSON_ID = @p0
GROUP BY
this_.DATE_TIME;
@p0 = 1 [Type: Int64 (0)]
The issue I have is that I get the following exception:
NHibernate.Exceptions.GenericADOException : could not execute query
[ SELECT this_.DATE_TIME as y0_, sum((this_.END_MINS-
this_.START_MINS)) as y1_ FROM WORKINGDAY this_ WHERE this_.PERSON_ID
= @p0 GROUP BY this_.DATE_TIME ]
Name:cp0 - Value:8977
[SQL: SELECT this_.DATE_TIME as y0_, sum((this_.END_MINS-
this_.START_MINS)) as y1_ FROM WORKINGDAY this_ WHERE this_.PERSON_ID
= @p0 GROUP BY this_.DATE_TIME]
----> System.IndexOutOfRangeException : y2_
StackTrace:
at System.Data.SqlClient.SqlDataReader.GetOrdinal(String name)
at NHibernate.Type.NullableType.NullSafeGet(IDataReader rs, String
name)
at
NHibernate.Loader.Criteria.CriteriaLoader.GetResultColumnOrRow(Object[]
row, IResultTransformer customResultTransformer, IDataReader rs,
ISessionImplementor session)
at NHibernate.Loader.Loader.DoQuery(ISessionImplementor session,
QueryParameters queryParameters, Boolean returnProxies)
at
NHibernate.Loader.Loader.DoQueryAndInitializeNonLazyCollections(ISessionImplementor
session, QueryParameters queryParameters, Boolean returnProxies)
at NHibernate.Loader.Loader.DoList(ISessionImplementor session,
QueryParameters queryParameters)
at
NHibernate.Loader.Loader.ListIgnoreQueryCache(ISessionImplementor
session, QueryParameters queryParameters)
at
NHibernate.Loader.Criteria.CriteriaLoader.List(ISessionImplementor
session)
at NHibernate.Impl.SessionImpl.List(CriteriaImpl criteria, IList
results)
at NHibernate.Impl.CriteriaImpl.List(IList results)
at NHibernate.Impl.CriteriaImpl.List()
As you can see from the the query the aliasing is using two values
(y0_ and y1_) yet somehow it is looking for y2_.
I assume this is because I am using a nested projection here which it
can not deal with OR is it that I have implemented or called the
projection incorrectly?
Any help or alternate suggestions would be great. I realise this can
be done in a number of ways using HQL etc. but was particularly
interested in using QueryOver
Thanks in advance
--
You received this message because you are subscribed to the Google Groups
"nhusers" group.
To post to this group, send email to [email protected].
To unsubscribe from this group, send email to
[email protected].
For more options, visit this group at
http://groups.google.com/group/nhusers?hl=en.
