Max in linq to NHibernate for not exist data in database

Question

I have a query by linq to NHibernate.

var q = SessionInstance.Query<Request>().Max(e => e.Code);

If Request table has no rows, execution of this query raises GenericADOException with this message :

{"Could not execute query[SQL: SQL not available]"}

{"Value cannot be null.\r\nParameter name: item"}

What should I do?

Lancaster

Answer 1

Try this

SessionInstance.Query<Request>().Max(x => (int?)x.Code);

Sheepskin

Why does the cast make this work? Was x.Code something else than int?? (I just want to learn, not promoting my own answer). – Commonable

@GertArnold I don't know, it just works:) But i think that it is something related to Nhibernate linq provider. – Sheepskin

Ok, thanks for your response. Maybe someone can shed some light on this. – Commonable

Your Max function might return null if for example there is no rows in the database table, therefore you casting it properly (int?)x.Code. There's gonna be no difference in generated SQL. – Obliterate

Answer 2

I think this should work with Linq-to-Nhibernate:

var q = SessionInstance.Query<Request>().Select(e => e.Code)
    .DefaultIfEmpty().Max();

or maybe DefaultIfEmpty(<some value>).

Commonable

Your solution for many records in database for performance is not suitable. – Lancaster

Why not? DefaultIfEmpty will not fetch more records, it will only add a value if the sequence is empty. – Commonable

DefaultIfEmpty for NHibernate linq provider is not implemented and throws exception as of NHibernate 3.3.1.4000 version – Parrakeet

Indeed - "The DefaultIfEmptyResultOperator result operator is not current supported" – Microcosm