2

I want to read records from an ENTITY where field VarCharDate is Between two user input dates. If I execute the sql directly it returns successfully all the records between two date.

i.e. 

SELECT * FROM ENTITY WHERE VarCharDate BETWEEN '2010-09-01' AND '2012-08-01'

Here the VarCharDate field is of type VARCHAR that is predefined and I can't change it to DateTime. The Date Format is fixed to 'YYYY-MM-DD' for storing values in this field.

The Problem:

EDIT: I am using FluentNhibernate for Mapping entities. Also I build the schema on application startup by configuration.

I am using Nhibernate Linq for retrieving records in my C# application. I tried something like:

session.Query<ENTITY>()
            .Where(e=> 
               Convert.ToDateTime(e.VarCharDate) >= Convert.ToDateTime(loInputDateString) &&
               Convert.ToDateTime(e.VarCharDate) <= Convert.ToDateTime(hiInputDateString)
             ).ToList();

Executing above statement results in runtime error NotSupportedException.

Google shows result regarding ExtensionMethods and I found it all confusing so unable to implement it yet.

How could I achieve something like:

session.Query<ENTITY>(e=> e.VarCharDate.Between(loInputDateString,hiInputDateString)).ToList();

I also tried something mentioned in the link LINQ Between Operator

But it's not working though.

Community
  • 1
  • 1
Shikha
  • 339
  • 2
  • 14

1 Answers1

0

You didn't mentioned how you mapped your entities(HBM, FluentNHibernate, ...). But you can use a TypeConverter. If its possible to change the VarCharDate Property definition on your class, then change it to DateTime and let NHibernate convert it from string to DateTime and back.

Rabban
  • 2,451
  • 1
  • 19
  • 21
  • Hi Rabban, Have updated the question also want to mention that I am using FluentNhibernate for Mapping Entities. – Shikha Sep 29 '16 at 11:32
  • Did you tried it with a TypeConverter or do you want to stay with the Between extension? And can you change the Property (not the field) to a DateTime? – Rabban Sep 29 '16 at 12:37