1

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?

Community
  • 1
  • 1
usr-local-ΕΨΗΕΛΩΝ
  • 26,101
  • 30
  • 154
  • 305

5 Answers5

1

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

Saeed Amiri
  • 22,252
  • 5
  • 45
  • 83
  • Yea, I already did a workaround. I first select all results from DB (fortunately, they are not many in this case) and then re-query the resulting list against my timestamp criteria – usr-local-ΕΨΗΕΛΩΝ Jan 30 '11 at 20:03
0

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.

Diego Mijelshon
  • 52,548
  • 16
  • 116
  • 154
0

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

JackD
  • 597
  • 4
  • 7
0

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

Dani
  • 340
  • 3
  • 6
0

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.

Dani
  • 340
  • 3
  • 6