I've got a custom query I'm trying to execute to get a summary of results,
however the results that are getting mapped do not line up with the results
returning from our database. I've tried to narrow an example down to only
the essentials, however the issue still seems to present itself. Here is
the query that is being generated (captured running a profiler on SQL
Server).
exec sp_executesql N'SELECT DATEADD(MINUTE, -1 * DATEDIFF(MINUTE, 0,
this_.ProcessDate) % 15, DATEADD(MINUTE, DATEDIFF(MINUTE, 0,
this_.ProcessDate), 0)) as y0_, count(this_.ID) as y1_ FROM MyModel this_
WHERE this_.ProcessDate >= @p0 GROUP BY DATEADD(MINUTE, -1 *
DATEDIFF(MINUTE, 0, this_.ProcessDate) % 15, DATEADD(MINUTE,
DATEDIFF(MINUTE, 0, this_.ProcessDate), 0))',N'@p0 datetime',@p0='2014-11-19
00:00:00'
Executing this query will give me several rows of data, grouping values in
15 minute increments.
When NHibernate executes this, and runs the results through the AliasToBean
transformer, I get the same two rows of data back each time. One for 7:30
AM, and one for 9:30 AM, both with a value of 1 in the Totals property.
Logic tells me that the AliasToBean transformer is not playing well with
the custom SqlFunction that is being executed. If I change that part of
the query to group results for a single day (see commented code),
everything returns as expected.
I've been scratching my head on this for the past few hours and need to
turn to the community for guidance. Could someone take a look and point me
in the right direction?
Thanks,
Cory
public class MyModel {
public virtual int Id { get; set; }
public virtual DateTime ProcessDate {get; set; }
}
public class MyModelMap : ClassMap<MyModel> {
public MyModelMap() {
Id(x => x.Id);
Map(x => x.ProcessDate)
}
}
public class MySummaryModel {
public virtual DateTime ProcessDate { get; set; }
public virtual int Totals { get; set; }
}
/* Repository Method */
public void Test() {
MyModel model = null;
var query = session.QueryOver<SubmitterFile>(() => model)
.Where(() => model.ProcessDate >= DateTime.Now.Date);
MySummaryModel summary = null;
var mSelectList = new QueryOverProjectionBuilder<MyModel>();
IProjection mTimeProjection;
var mFunctionTemplate = new SQLFunctionTemplate(NHibernateUtil.DateTime2,
"DATEADD(MINUTE,
-1 * DATEDIFF(MINUTE, 0, ?1) % 15, DATEADD(MINUTE, DATEDIFF(MINUTE, 0, ?1),
0))");
mTimeProjection = Projections.GroupProperty(Projections.SqlFunction(
mFunctionTemplate, NHibernateUtil.DateTime2, Projections.Property(() =>
model.ProcessDate))).WithAlias(() => summary.ProcessDate);
/* Changing my mTimeProjection to the following works, giving me the full
summary for the day, but not broken into 15 minute intervals
mTimeProjection =
Projections.GroupProperty(Projections.Cast(NHibernateUtil.Date,
Projections.Property(() => model.ProcessDate))).WithAlias(() =>
summary.ProcessDate);
*/
mSelectList = mSelectList
.Select(mTimeProjection)
.Select(Projections.Count(() => model.Id).WithAlias(() => summary.Totals));
var results = query.SelectList(x => mSelectList)
.TransformUsing(Transformers.AliasToBean<MySummaryModel>())
.List<MySummaryModel>();
}
--
You received this message because you are subscribed to the Google Groups
"nhusers" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To post to this group, send email to [email protected].
Visit this group at http://groups.google.com/group/nhusers.
For more options, visit https://groups.google.com/d/optout.
