I have an answered StackOverflow question about how to combine to legacy CHAR database date and time fields into one .NET DateTime property in my POCO
here (thanks much Berryl!). Now i am trying to get a custom ICritera query to work against that very DateTime property to no avail. here's my query:
ICriteria criteria =
Session.CreateCriteria<InputFileLog>()
.Add(Expression.Gt(MembersOf<InputFileLog>.GetName(x => x.FileCreationDateTime), DateTime.Now.AddDays(-14)))
.AddOrder(Order.Desc(Projections.Id()))
.CreateCriteria(typeof(InputFile).Name)
.Add(Expression.Eq(MembersOf<InputFile>.GetName(x => x.Id), inputFileName));
IList<InputFileLog> list = criteria.List<InputFileLog>();
And here's the query it's generating:
SELECT this_.input_file_token as input1_9_2_,
this_.file_creation_date as file2_9_2_,
this_.file_creation_time as file3_9_2_,
this_.approval_ind as approval4_9_2_,
this_.file_id as file5_9_2_,
this_.process_name as process6_9_2_,
this_.process_status as process7_9_2_,
this_.input_file_name as input8_9_2_,
gonogo3_.input_file_token as input1_6_0_,
gonogo3_.go_nogo_ind as go2_6_0_,
inputfile1_.input_file_name as input1_3_1_,
inputfile1_.src_code as src2_3_1_,
inputfile1_.process_cat_code as process3_3_1_
FROM input_file_log this_
left outer join go_nogo gonogo3_ on this_.input_file_token=gonogo3_.input_file_token
inner join input_file inputfile1_ on this_.input_file_name=inputfile1_.input_file_name
WHERE this_.file_creation_date > :p0 and
this_.file_creation_time > :p1 and
inputfile1_.input_file_name = :p2
ORDER BY this_.input_file_token desc;
:p0 = '20100401',
:p1 = '15:15:27',
:p2 = 'LMCONV_JR'
The query is exactly what i would expect, actually, except it doesn't actually give me what i want (all the rows in the last 2 weeks) because in the DB it's doing a greater than comparison using CHARs instead of DATEs. I have no idea how to get the query to convert the CHAR values into a DATE in the query without doing a CreateSQLQuery(), which I would like to avoid. Anyone know how to do this?
UPDATE:
I've been looking into trying to use Projections.SqlFunction() or formulas to accomplish this, but to no avail so far. Here's the code i have using SqlFunction(), but i get an NHibernate.QueryException : property does not map to a single column: FileCreationDateTime error:
DateTime twoWeeksAgo = DateTime.Now.AddDays(-14);
ICriteria criteria =
Session.CreateCriteria<InputFileLog>()
.Add(Restrictions.Gt(Projections.SqlFunction("to_date", NHibernateUtil.DateTime, Projections.Property(MembersOf<InputFileLog>.GetName(x => x.FileCreationDateTime))), twoWeeksAgo))
//.Add(Expression.Gt(MembersOf<InputFileLog>.GetName(x => x.FileCreationDateTime), DateTime.Now.AddDays(-14)))
.AddOrder(Order.Desc(Projections.Id()))
.CreateCriteria(typeof(InputFile).Name)
.Add(Expression.Eq(MembersOf<InputFile>.GetName(x => x.Id), inputFileName));
I'm sure i'm doing something wrong here and it doesn't like it still anyway because FileCreationDateTime uses a custom ICompositeUserType which splits the .NET DateTime property into two Oracle SQL CHAR columns (see this StackOverflow question for details).
I finally figure this out! here's the code (for some reason StackOverflow is making some of the methods names in the this first code snippet the syntax color of a type):
IList<InputFileLog> list = null;
DateTime twoWeeksAgo = DateTime.Now.AddDays(-14);
IProjection datePropProj =
DefaultStringFileCreationDateTimeType.GetFileCreationDateToDateSQLProjection();
IProjection timePropProj =
DefaultStringFileCreationDateTimeType.GetFileCreationTimeToDateSQLProjection();
IProjection dateConstProj =
DefaultStringFileCreationDateTimeType.GetFileCreationDateToDateSQLFunction(twoWeeksAgo);
IProjection timeConstProj =
DefaultStringFileCreationDateTimeType.GetFileCreationTimeToDateSQLFunction(twoWeeksAgo);
ICriteria criteria =
Session.CreateCriteria<InputFileLog>()
.Add(Restrictions.Or(Restrictions.GtProperty(datePropProj, dateConstProj),
Restrictions.And(Restrictions.EqProperty(datePropProj, dateConstProj),
Restrictions.GeProperty(timePropProj, timeConstProj))))
.AddOrder(Order.Desc(Projections.Id()))
.CreateCriteria(typeof(InputFile).Name)
.Add(Expression.Eq(MembersOf<InputFile>.GetName(x => x.Id), inputFileName));
list = criteria.List<InputFileLog>();
And here's the methods i used to create the SQLProjections and SQLFunctions. i put them in my ICompositeUserType (DefaultStringFileCreationDateTime) that i used for the custom type mapping on the FileCreationDateTime property.
public class DefaultStringFileCreationDateTime : ICompositeUserType
{
.
.
.
public const string DotNetDateFormat = "yyyyMMdd";
public const string DotNetTimeFormat = "HH:mm:ss";
public const string DbDateFormat = "YYYYMMDD";
public const string DbTimeFormat = "HH24:MI:SS";
private const string _nullDateRepresentationInDb = "00000000";
public struct DatabaseFieldNames
{
/// <summary>
/// File creation date column name.
/// </summary>
public const string FileCreationDate = "file_creation_date";
/// <summary>
/// File creation time column name.
/// </summary>
public const string FileCreationTime = "file_creation_time";
}
public static IProjection GetFileCreationDateToDateSQLProjection()
{
return ProjectionUtil.GetToDateSQLProjection(DatabaseFieldNames.FileCreationDate, DbDateFormat, NHibernateUtil.DateTime);
}
public static IProjection GetFileCreationTimeToDateSQLProjection()
{
return ProjectionUtil.GetToDateSQLProjection(DatabaseFieldNames.FileCreationTime, DbTimeFormat, NHibernateUtil.DateTime);
}
public static IProjection GetFileCreationDateToDateSQLFunction(DateTime dt)
{
return ProjectionUtil.GetToDateSQLFunction(dt, DotNetDateFormat, DbDateFormat);
}
public static IProjection GetFileCreationTimeToDateSQLFunction(DateTime dt)
{
return ProjectionUtil.GetToDateSQLFunction(dt, DotNetTimeFormat, DbTimeFormat);
}
}
I was already using the consts DatabaseFieldNames struct for the PropertyNames member implementation, so I was able to reuse these hard-coded column names for the Projections i needed as well.
Here's the Projection utility class where the generic to_date methods live:
public class ProjectionUtil
{
public static IProjection GetToDateSQLProjection(
string columnName, string dbToDateFormat, IType returnType)
{
return Projections.SqlProjection(
string.Format("to_date({0}, '{1}') as {0}", columnName, dbToDateFormat),
new string[] { columnName },
new IType[] { returnType });
}
public static IProjection GetToDateSQLFunction(
DateTime dt, string dotNetFormatString, string dbFormatString)
{
return Projections.SqlFunction(
"to_date",
NHibernateUtil.DateTime,
Projections.Constant(dt.ToString(dotNetFormatString)),
Projections.Constant(dbFormatString));
}
}
Finally, here's the Oracle SQL that NHibernate generates:
SELECT
this_.input_file_token as input1_9_2_,
this_.file_creation_date as file2_9_2_,
this_.file_creation_time as file3_9_2_,
this_.approval_ind as approval4_9_2_,
this_.file_id as file5_9_2_,
this_.process_name as process6_9_2_,
this_.process_status as process7_9_2_,
this_.input_file_name as input8_9_2_,
gonogo3_.input_file_token as input1_6_0_,
gonogo3_.go_nogo_ind as go2_6_0_,
inputfile1_.input_file_name as input1_3_1_,
inputfile1_.src_code as src2_3_1_,
inputfile1_.process_cat_code as process3_3_1_
FROM
input_file_log this_
left outer join go_nogo gonogo3_ on this_.input_file_token=gonogo3_.input_file_token
inner join input_file inputfile1_ on this_.input_file_name=inputfile1_.input_file_name
WHERE
(
to_date(file_creation_date, 'YYYYMMDD') > to_date(:p0, :p1) or
(
to_date(file_creation_date, 'YYYYMMDD') = to_date(:p2, :p3) and
to_date(file_creation_time, 'HH24:MI:SS') >= to_date(:p4, :p5)
)
) and
inputfile1_.input_file_name = :p6
ORDER BY this_.input_file_token desc;
:p0 = '20100415',
:p1 = 'YYYYMMDD',
:p2 = '20100415',
:p3 = 'YYYYMMDD',
:p4 = '18:48:48',
:p5 = 'HH24:MI:SS',
:p6 = 'LMCONV_JR'
can't believe i got this one! i thought i was going to have to resort to an ISQLQuery for sure!