CODEWITHSUNDEEP
c#linqnhibernatedatetimenullable
usr-local-ΕΨΗΕΛΩΝ
usr-local-ΕΨΗΕΛΩΝ

Reputation: 26874

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).TotalDays, p5)))))), )]

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

Upvotes: 1

Views: 4089

Answers (5)

Dani
Dani

Reputation: 340

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.

Upvotes: 0

Dani
Dani

Reputation: 340

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??

Upvotes: 0

JackD
JackD

Reputation: 597

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

Upvotes: 0

Saeed Amiri
Saeed Amiri

Reputation: 22555

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 :)

Upvotes: 1

Diego Mijelshon
Diego Mijelshon

Reputation: 52725

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.

Upvotes: 0

Related Questions