nhibernate and window functions

2018-06-06 13:24:59

Is there any way to get NHibernate to use a Window function?

Specifically, I'm looking to create a query like the following:

SELECT
  date,
  SUM(Price) OVER (ORDER BY date)
FROM purchases
GROUP BY date

I've Search the web and can't find anything about window functions and NHibernate

Specifically, I'm using the QueryOver interface for NHibernate.

If it's not possible, what other solutions are there to keep database independence in my code?

Good question but unfortunately I've never seen any code within NHibernate itself that would allow it to generate such a window function.

Your only option would be to use a named query. In your named query you could either type out the SQL by hand or call a stored procedure.

You stated that you wanted to keep your solution database independent. I've never personally cared much doing such as thing as I always work with SQL Server but I can understand where you're coming from. I've heard of several people who run their integration tests with an in memory SQLite solution and then run their actual code on SQL Server or Oracle.

In your scenario, I don't believe the SQL in your question is ANSI compliant anyway so you'd have to re-write it in a different way unfortunately if you used a named query to make it database independent.

As far as I know, you can't do that with the QueryOver API.

What I would do to keep as DB independant as possible would be to consider the windowed query as a join between your table and a group by query. Then to write a QueryOver which would lead this join.

Considering you query it would lead to this : (though the example does not seem to really show the need for a windowed query)

SELECT
  purchases.date,
  purchasesGroup.priceSum
FROM 
     purchases
INNER JOIN 
     (SELECT date, SUM(Price) as priceSum  FROM purchases GROUP BY date) 
       AS purchasesGroup
on purchases.date = purchasesGroup.date

You could potentially use a view for this. That could at least abstract away a little bit of it, although you are really migrating the dependency to the database schema.

I've run into this kind of issue as well lately, and I'm starting to realize that I am often trying to use NHibernate in a way that isn't really about persisting entities. If I were Ayende Rahien I would probably know what to do, but since I am not I usually end up mixing in a little Dapper when I just need something like a read-only aggregate result such as statistics about data.

链接地址: http://www.djcxy.com/p/20330.html

上一篇: 为什么Python readline模块在OS X上不可用？

下一篇: nhibernate和窗口功能