Update. How to access values returned by native SQL NHibernate query?

Question

I have this table (table name is price):

value productId ShopID      Timestamp
1.30    1          5        2015-05-30 05:20:28.000
1.20    1          5        2015-05-29 16:09:34.000
1.00    1          5        2015-05-29 16:09:43.000
1.20    1          5        2015-05-29 16:09:50.000
1.20    1          5        2015-05-29 16:09:58.000
1.00    2          5        2015-05-29 16:10:13.000
1.00    2          5        2015-05-29 16:10:17.000
1.00    1          6        2015-05-29 16:10:42.000
1.00    1          5        2015-05-30 15:02:44.000
1.30    1          5        2015-05-30 15:03:24.000

I want to get value that has been entered the most times in the latest date for each shop for single product. Note that the I want to convert timestamp to date.

Right now I have this SQL query:

select count(*), value, Productid, shopid
from price 
where shopid = 5 
    and productId = 1 
    and cast(floor(cast(Timestamp as float)) as datetime) 
        in (select max(cast(floor(cast(Timestamp as float)) as datetime)) as [date] from price)
group by value, Productid, shopid
order by count(*) desc

It returns ordered count for single product and single shop like this:

Count   value   Productid   shopid
2       1.30        1          5
1       1.00        1          5

In ideal case I would like to get only the row with biggest count, but I guess this would be ok as well.

Up to this point I've been using QueryOver queries in my solution, but I guess anything that can be used in c# would be ok.

TIA

Update

Following suggestions in comments I am trying to use native SQL. I've got this code to get distinct shop ids, and this is working fine.

var shopIds =
                _session.CreateSQLQuery("select distinct shopid from price where productid = " + productId).List();

Then I am trying to execute main query for each shop, after which I would like to find a single instance of most frequent price like this:

List<Price> query;

            foreach (var shopId in shopIds)
            {


                 var querySubResult = _session.CreateSQLQuery("select value, productId, shopId" +
                                                        "from price " +
                                                        "where shopid = " + shopId +
                                                        "and productId = " + productId +
                                                        "and cast(floor(cast(Timestamp as float)) as datetime) " +
                                                        "in (select max(cast(floor(cast(Timestamp as float)) as datetime)) as [date] from price)" +
                                                        "group by value, Productid, shopid" +
                                                        "order by count(*) desc")
                                                        .AddScalar("value", NHibernateUtil.Decimal)
                                                        .AddScalar("productId", NHibernateUtil.Int64)
                                                        .AddScalar("shopId", NHibernateUtil.Int64).List();

            query.Add(_session.QueryOver<Price>()
                .Where(x => x.Value == querySubResult[1].value)
                .And(x => x.Product.ProductId == querySubResult[1].productId)
                .And(x => x.Shop.ShopId == querySubResult[1].shopId));
            }

As I understand AddScallar defines output properties for returned data from sql statement, but I can't access none of the variables in the list.

What is wrong? Should it be aproached in diferent way? Any help would be appretiated.

There are other ways for C# to send queries to SQL Server without using Hibernate, for instance, using SqlConnection. — Gordon Linoff, May 30 '15 at 12:45
I am already using NHibernate in my solution and I would like to stick to that, since it is already configured and working, but thanks for suggestion. — user3677314, May 30 '15 at 12:48
Unless it needs to be database agnostic you could parameterize the existing query and use CreateSQLQuery. — A Bunch, May 30 '15 at 13:38
@user3677314 . . . If you have native SQL queries running, I would suggest using a native interface, unless you have a good design reason for using Hibernate. The conversion sounds troublesome, and there is an easy work-around. — Gordon Linoff, May 30 '15 at 14:29
Thank you both. I am trying to use native SQL, but have run into some problems. Please see updated post for them, maybe you know the solution. — user3677314, May 30 '15 at 15:06

Oskar Berggren · Answer 1 · 2015-06-02T10:02:23.090

0

This is documented in NHibernate Reference, Scalar Queries:

This will return an IList of Object arrays (object[]) with scalar values for each column in the (CATS) table.

Example:

var result = _session.CreateSQLQuery("...")
                     .AddScalar(...).AddScalar(...)
                     .List();

var row0property0 = ((object[])result[0])[0];
var row0property1 = ((object[])result[0])[1];

I think you could also do ...List<object[]> to avoid having to cast to object[] when accessing property values.

The greatest use of raw SQL queries in NHibernate is when you tell NHibernate to actually build entity instances from the returned data.

edited Jun 02 '15 at 10:02

answered May 31 '15 at 12:45

Oskar Berggren

5,583
1
19
36

I get red squiggly line under "[0]" in querySubResult[1][0] if I convert querySubResult[1].value to querySubResult[1][0]. Why could that be? – user3677314 May 31 '15 at 16:42
I don't know - what does Visual Studio/the compiler tell you is wrong? – Oskar Berggren May 31 '15 at 18:07
Visual studio shows. cannot convert from 'NHibernate.IQueryOver' to 'WebApiPrice.Data.Entities.Price' – user3677314 Jun 02 '15 at 05:47
Cannot convert lambda expression to type 'NHibernate.Criterion.ICriterion' because it is not a delegate type – user3677314 Jun 02 '15 at 05:50
Delegate 'System.Func' does not take 1 arguments – user3677314 Jun 02 '15 at 05:50
Cannot apply indexing with [] to an expression of type 'object' – user3677314 Jun 02 '15 at 05:50
@user3677314 I've updated my answer to include the casting that should be required when using the non-generic version of `List()`. As for the "cannot convert" error, it looks like you are trying to add a QueryOver instance to a list of Price instances. – Oskar Berggren Jun 02 '15 at 10:06

Update. How to access values returned by native SQL NHibernate query?

1 Answers1