Handling DateTime? in NHibernate/LINQ queries

this is a follow-up to question Better DateTime? or use default(DateTime) for NULL?

I tried to implement a simple query that works with DateTime? with a different example rather than the one of the other question.

In my case I have public virtual DateTime? Expiration property mapped as

<property name="Expiration" not-null="false" type="DateTime" />

Now my LINQ query is the following

return (from product in session.Query<Product>()
                    where
                        !product.Hidden &&
                        (product.Expiration != null ||
                        (product.Expiration.Value - DateTime.Now).TotalDays < 5 && (product.Expiration.Value - DateTime.Now).TotalDays >= 0)
                    select product).ToList();

Query says: pick all products that are not hidden and not expiring in 5 days (<5 verify expired products so I have to add the second check)

I get an exception from ANTLR:

'Antlr.Runtime.NoViableAltException'. [.Where(NHibernate.Linq.NhQueryable`1[Model.Product], Quote((product, ) => (AndAlso(Not(product.Hidden), OrElse(DateTime.op_Inequality(product.Expiration, NULL), AndAlso(LessThan(DateTime.op_Subtraction(product.Expiration.Value, p2).TotalDays, p3), GreaterThanOrEqual(DateTime.op_Subtraction(product.Expiration.Value, p4).开发者_JAVA百科TotalDays, p5)))))), )] 

How do I handle a DateTime? in NHibernate 3.0 with LINQ?

You can't do product.Expiration.Value - DateTime.Now and also you can't do (product.Expiration.Value - DateTime.Now).TotalDays because there is no support for this functions in nhibernate2linq (I don't know , in Entity Framework you can use them by canonical entity functions but I don't know about nhibernate3 may be they fix them, just one thing you can create a wrapper for your actions :)

It would be good if you posted the actual exception, but my guess is that .TotalDays is not being mapped to SQL.

When writing server-side LINQ queries, keep in mind that not every operation can be converted to a SQL call, only those that have been explicitly covered.

Try to use variables instead of direct DateTime calling. It can be a reason of error.

I have the same problem. This is a piece of my Linq over Nhibernate 3.0 query

.Where(c => c.EndDate.Value >= DateTime.Now && c.BeginDate.Value <= DateTime.Now)

What I found out, that this piece gets transformed to

and session2_.LCSE_END_DATE >= '26.02.YYYY 09:34:12' 
and session2_.LCSE_BEGIN_DATE <= '26.02.YYYY 09:34:12') 

It seems like that he is not able to transform correctly DateTime.Now! Might be a bug??

Hallo I found actually my problem, which seems not related with NHibernate Linq. I got confused, because Nhibernate profiler showed the following query to be executed on the DB

and session2_.LCSE_END_DATE >= '26.02.YYYY 09:34:12' 
and session2_.LCSE_BEGIN_DATE <= '26.02.YYYY 09:34:12') 

Finally, I suppose it is a bug of NhibernateProfiler.