Adding support for DateTime.Add(), AddDays(), etc in the Linq provider for MsSql dialects (NH-2849)

[Andreas Ravnestad]

Hi,

This post concerns https://nhibernate.jira.com/browse/NH-2849. At my company we often stumble upon scenarios where support for this functionality would enable us to write cleaner code as we prefer LINQ over QueryOver and HQL. Also, this functionality seems to be expected to "just work" judging by numerous Stack Overflow posts on the topic.

We have considered writing a custom dialect, but if we are going to go through that hassle again, then I would like to know if there is any interest for adding it directly to NHibernate instead?

I am familiar with the guidelines for contributing and how to go about to get this done, although I have not contributed to NHibernate before.

Thanks,
Andreas

[Ricardo Peres]

The problem is what Oskar said in the issue: Since LINQ is translated to HQL and HQL has no support for date operations like, for example, SQL Server's DATEADD, this might be tricky, because possibly - and I'm not 100% sure on this - Dialects need to be involved.

RP

[Andreas Ravnestad]

Hi Ricardo,

It is only mentioned briefly in the subject of my post, but I was planning to scope the implementation to the Microsoft SQL Server dialect since that's the database engine we are using. Perhaps this is undesirable?

-Andreas

[Oskar Berggren]

What I meant in the issue was that the current lack of support wasn't a bug, but adding support for it would be regarded as a new feature. Seems doable to me.

You need to decide on a set of names for the HQL functions that will underpin the linq support. These functions must have the same semantics regardless of SQL dialect. The dialect classes will need to translate them into appropriate SQL constructs depending on SQL variety. Usually it's not very difficult. Implementations should be provided for at least 4-5 of the most common dialects, the more the better, before inclusion.

The linq layer then needs a few fairly simple additions to teach it about the HQL methods. Doesn't need to know about different dialects.

You can perhaps review how Substring() is handled in linq and the dialects for inspiration.

/Oskar

--

---
You received this message because you are subscribed to the Google Groups "nhibernate-development" group.
To unsubscribe from this group and stop receiving emails from it, send an email to nhibernate-develo...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[Andreas Ravnestad]

Thank you for your informative reply Oskar. This is my tentative plan so far:

• Propose a new scalar HQL function called dateadd() modelled after DATEADD() in SQL Server. (This will probably break a few custom dialects around the world.. commentary especially welcome on the choice of name)
  
• Create HQL unit tests that covers as much as possible of the functionality of DATEADD() in SQL Server.
• Attempt to implement the dateadd() function and make the unit tests pass for the SQL Server dialect.
• Attempt to implement as much as possible of the dateadd() function for MySQL, PostGreSQL, Oracle and SQLite.
• When all that is done and works well, an attempt can be made to implement LINQ support, in a phase 2 of sorts. Most of the useful .Net DateTime API should translate easily to dateadd().
  

Is this a reasonable plan? Am I way off the mark? Is there something I'm forgetting or should consider further? I realize that I am engaging in a slightly bigger task than I initially thought, so that's why I am scoping the task to dateadd() first to see how that works out.

-Andreas

To unsubscribe from this group and stop receiving emails from it, send an email to nhibernate-development+unsub...@googlegroups.com.