Quick Tip: NHibernate, “SQL not available,” and “cannot be used in this generic collection” error
Consider the following:
string hql; hql = @" from Item as i inner join i.Barcodes as b inner join i.ItemGroup.Merchant as m where b.Text = :barcode and m.Code = :merchantCode "; query = session.CreateQuery(hql) .SetString("barcode", barcode) .SetString("merchantCode", merchantCode); var items = query.List<Item>(); // explodes
Let’s assume that Barcodes is a composite-element of the Item.
This will explode with an exception that reads “Could not execute query[SQL: SQL not available]” while the inner exception reads “The value “System.Object[]” is not of type “Skiviez.Armadillo.Model.Item” and cannot be used in this generic collection. Parameter name: value”.
That is because I am calling List<Item>(), but as specified, my HQL query is returning a list of Items and Merchants.
To fix it, my HQL needs to be
string hql; hql = @" select i from Item as i inner join i.Barcodes as b inner join i.ItemGroup.Merchant as m where b.Text = :barcode and m.Code = :merchantCode ";
The error message was a little vague so it took me a while to figure that out. Essentially, it means that the SQL query was probably executed fine, but NHibernate is blowing up while populating the list of result objects.
3 Comments
→
What a great title for your blog. I like the double entendre. I am not by any means a coder though. Good luck!
Thx for this i was struggling with the error aswell.
thanks helpme a lot.
on writing a query, i have to change something on my side but help me a lot.
string hql;
hql = @”
select
A
from Apple as A. Balls as B
where A.Id = B.AppleId
order by B.Id
“;
thanks a lot