How do you compare using .NET types in an NHibernate ICriteria query for an ICompositeUserType?
- by gabe
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?
