I'm getting a GenericADOException with the message:
could not execute query
related to the following query, being generated by NHibernate:
select aggregated0_.Id as col_0_0_
, aggregated0_.Name as col_1_0_
, aggregated0_.Event as col_2_0_
, cast(count(distinct aggregated0_.Code) as INT) as col_3_0_
from Events aggregated0_
where
aggregated0_.RelatedId=@p0
and aggregated0_.S_Id=@p1
and aggregated0_.HappenedAt >=@p2
group by
aggregated0_.Id
, aggregated0_.Name
, aggregated0_.Event
Name:p1 - Value:97c58b27-460d-4fae-8185-b215e2d11882
Name:p2 - Value:4d3b7583-4b46-46e2-8bcd-169b26fd3541
Name:p3 - Value:7/25/2017 11:00:00 PM
As you can see, the parameter list is, incorrectly, using p1, p2 and p3. However, the query needs p0, p1 and p2.
Why is NH creating an incorrect parameter list?
Mapping:
public AggregatedEventMap()
{
SchemaAction.None();
Table("Events");
ReadOnly();
Not.LazyLoad();
Id(e => e.E_Id).GeneratedBy.Assigned();
Map(m => m.RelatedId);
Map(m => m.S_Id);
Map(m => m.P_Id);
Map(m => m.Id);
Map(m => m.Name);
Map(m => m.Code);
Map(m => m.Event);
Map(m => m.EventTime).CustomType<UtcDateTimeType>();
Map(m => m.HappenedAt).CustomType<UtcDateTimeType>();
}
Query is built as follows:
var query =
Session
.Query<AggregatedEvent>()
.Where(e => e.RelatedId == parameters.RelatedId);
if (parameters.S_Id.HasValue)
{
query =
query.Where(e => e.S_Id == parameters.S_Id.Value);
}
if (parameters.P_Id.HasValue)
{
query = query.Where(e => e.P_Id == parameters.P_Id.Value);
}
if (parameters.Id.HasValue)
{
query = query.Where(e => e.Id == parameters.Id.Value);
}
if (parameters.FromDate.HasValue)
{
query = query.Where(e => e.HappenedAt >= parameters.FromDate.Value);
}
if (parameters.ToDate.HasValue)
{
query = query.Where(e => e.HappenedAt < parameters.ToDate.Value);
}
var results =
query
.GroupBy(e => new { e.Id, e.Name, e.Event })
.Select(
g =>
new AggregatedEventResult
{
Id = g.Key.Id,
Name = g.Key.Name,
Event = g.Key.Event,
Count = g.Select(e => e.Code).Distinct().Count()
})
.ToList();
Furthermore, this doesn't happen consistently and only happens in one runtime environment. It only happens when a certain combination of parameters is used:
RelatedId, S_ID and when HappenedAt is queried for events on, or after a given date.