Am I using QueryOver wrong?

Question

Basically I'm trying to generate a report with data from the database. However everytime I generate a report, I get this exception:

variable 'x' of type 'HealthCardLabelPrinter.Entities.CardProcessed' referenced from scope '', but it is not defined

My code that throws the exception is here:

 public BindingList<CardProcessed> GetByLocationAndDate(string location_id, string sdate, string edate)
        {
            var fromDb = session.QueryOver<CardProcessed>()
                .Where(x => (DateTime.Parse(x.draw_date) >= DateTime.Parse(sdate)))
                .And(x => DateTime.Parse(x.draw_date) <= DateTime.Parse(edate))
                .JoinQueryOver(m => m._location)
                .Where(m => m.location_id == location_id)
                .List<CardProcessed>();
            var newList = new BindingList<CardProcessed>();
            foreach (var record in fromDb)
            {
                newList.Add(record);
            }
            return newList;
        }

Two class maps:

public LocationMap()
    {
        Id(x => x.LocationId).Column("locationId").GeneratedBy.Identity();
        Map(x => x.name)
            .Not.Nullable();
        Map(x => x.location_id)
            .Not.Nullable();
        HasMany(x => x.records)
            .KeyColumns
            .Add("cardProcessedId")
            .ForeignKeyConstraintName("none");
    }

public CardProcessedMap()
    {
        Id(x => x.CardProcessedId).Column("cardProcessedId").GeneratedBy.Identity();
        References<hcData>(x => x._hcData, "dataId").ForeignKey("none");
        References<User>(x => x._user, "userId").ForeignKey("none");
        References<Location>(x => x._location, "locationId").ForeignKey("none");
        References<Doctor>(x => x._doctor, "doctorId").ForeignKey("none");
        Map(x => x.label_amount_encrypted)
            .Column("labelAmount");
        Map(x => x.draw_date)
            .Column("drawDate");
        Map(x => x.fee_encrypted)
            .Column("fee");
        Map(x => x.phone_encrypted)
            .Column("phone");
    }

Help me figure out how I should define the variable 'x' for the QueryOver.

Stacktrace for the error:

at System.Linq.Expressions.Compiler.VariableBinder.Reference(ParameterExpression node, VariableStorageKind storage)
   at System.Linq.Expressions.Compiler.VariableBinder.VisitParameter(ParameterExpression node)
   at System.Linq.Expressions.ParameterExpression.Accept(ExpressionVisitor visitor)
   at System.Linq.Expressions.ExpressionVisitor.Visit(Expression node)
   at System.Linq.Expressions.ExpressionVisitor.VisitMember(MemberExpression node)
   at System.Linq.Expressions.MemberExpression.Accept(ExpressionVisitor visitor)
   at System.Linq.Expressions.ExpressionVisitor.Visit(Expression node)
   at System.Linq.Expressions.ExpressionVisitor.VisitArguments(IArgumentProvider nodes)
   at System.Linq.Expressions.ExpressionVisitor.VisitMethodCall(MethodCallExpression node)
   at System.Linq.Expressions.MethodCallExpression.Accept(ExpressionVisitor visitor)
   at System.Linq.Expressions.ExpressionVisitor.Visit(Expression node)
   at System.Linq.Expressions.ExpressionVisitor.Visit(ReadOnlyCollection`1 nodes)
   at System.Linq.Expressions.Compiler.VariableBinder.VisitLambda[T](Expression`1 node)
   at System.Linq.Expressions.Expression`1.Accept(ExpressionVisitor visitor)
   at System.Linq.Expressions.ExpressionVisitor.Visit(Expression node)
   at System.Linq.Expressions.Compiler.LambdaCompiler.Compile(LambdaExpression lambda, DebugInfoGenerator debugInfoGenerator)
   at System.Linq.Expressions.LambdaExpression.Compile()
   at NHibernate.Impl.ExpressionProcessor.FindValue(Expression expression)
   at NHibernate.Impl.ExpressionProcessor.FindMemberProjection(Expression expression)
   at NHibernate.Impl.ExpressionProcessor.ProcessSimpleExpression(Expression left, Expression right, ExpressionType nodeType)
   at NHibernate.Impl.ExpressionProcessor.ProcessSimpleExpression(BinaryExpression be)
   at NHibernate.Impl.ExpressionProcessor.ProcessBinaryExpression(BinaryExpression expression)
   at NHibernate.Impl.ExpressionProcessor.ProcessExpression(Expression expression)
   at NHibernate.Impl.ExpressionProcessor.ProcessLambdaExpression(LambdaExpression expression)
   at NHibernate.Impl.ExpressionProcessor.ProcessExpression[T](Expression`1 expression)
   at NHibernate.Criterion.QueryOver`2.Add(Expression`1 expression)
   at NHibernate.Criterion.QueryOver`2.Where(Expression`1 expression)
   at NHibernate.Criterion.QueryOver`2.NHibernate.IQueryOver<TRoot,TSubType>.Where(Expression`1 expression)
   at HealthCardLabelPrinter.Repositories.CardProcessedRepository.GetAllByLocationAndDate(String location_id, String sdate, String edate) in c:\Users\Craig_2\Documents\Visual Studio 2012\Projects\HealthCardLabelPrinter\HealthCardLabelPrinter\Repositories\CardProcessedRepository.cs:line 52
   at HealthCardLabelPrinter.Report.RenderReport(DateTime start, DateTime end, Location location, String fpath) in c:\Users\Craig_2\Documents\Visual Studio 2012\Projects\HealthCardLabelPrinter\HealthCardLabelPrinter\Report.cs:line 39
   at HealthCardLabelPrinter.frmMain.frmMain_FormClosing(Object sender, FormClosingEventArgs e) in c:\Users\Craig_2\Documents\Visual Studio 2012\Projects\HealthCardLabelPrinter\HealthCardLabelPrinter\frmMain.cs:line 452
   at System.Windows.Forms.Form.OnFormClosing(FormClosingEventArgs e)
   at System.Windows.Forms.Form.CheckCloseDialog(Boolean closingOnly)

Answer 1

The point here is the conversion of the Expression into valid SQL Statement. The .Where() method expects the Expression<Func<CardProcessed>> to be passed in, and tries to convert that into native SQL Statement (which in this case does not succeed)

If I understand it clearly, the column in the table contains string, which should be compared as DateTime. So we can convert the filter condition into native SQL CAST statement and compare two dates this way:

...
.Where(Restrictions.Le(
    Projections.Cast(NHibernateUtil.DateTime, Projections.Property("draw_date"))
   , sdate)
)
...

And that will generate SQL statement like this:

WHERE cast( this_.drawDate as DATETIME) <= '2013-10-30' // expecting sdate is today