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.
9 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
ERROR
Could not execute query[SQL: SQL not available]
public IList listdataserviceplan(String custid)
{
using (ISession session = NHibernateHelper.OpenSession())
{
string query = ” select a.ServicePlanId as ServicePlanId ,a.ServiceDetail as ServiceDetail,”
+ ” a.DateServiceFix as DateServiceFix ,a.DateService as DateService,”
+ ” a.CaseNotSupport as CaseNotSupport ,a.ServiceChangeName as ServiceChangeName,”
+ ” a.DateServiceNew as DateServiceNew ,a.MaterialChange as MaterialChange,”
+ ” a.ServiceGuarantee as ServiceGuarantee ,a.ServiceMaintenance as ServiceMaintenance,”
+ ” a.ServiceCharge as ServiceCharge”
+ ” from BicIsu.Core.Domain.ServicePlan as a”
+ ” where 1=1″
+ ” and a.CustId = ‘” + custid + “‘ ”
+ ” order by a.ServicePlanId”;
var cons = session.CreateQuery(query).List();
return cons;
}
}
Really nice post boss! keep it up..
Hi,
I am new bee in nhibernate. I have a simple entity called EmployeeEntity with properties (ID,Name,Age,Organisation,Designation). I am just querying the database using the query
IQuery query = session.CreateQuery(“select Name ,Designation ,Age ,Organisation FROM EmployeeEntity group by Name ,Designation ,Age ,Organisation”);
Ilist employee= query.List(); //Throws error
but on convertion it to my type its throwing exception as “ex = {“Could not execute query[SQL: SQL not available]“}” on looking the inner message it says “InnerException = {“The value \”System.Object[]\” is not of type \”NHibernateTest.EmployeeEntity\” and cannot be used in this generic collection.\r\nParameter name: value”}”
though it works fine using with this query
IQuery query = session.CreateQuery(“select e FROM EmployeeEntity e group by e);
Ilist employee= query.List();
but I don’t want to select all the columns because i don’t need it.
Can anyone help regarding this.
Thanks so much! I was struggling with this bit as a newbie. Wonderful explanation to to a very vague error
God bless you! You saved me, thanks, and congratulations.
Can i solve this trying to use List() instead of List(AType) and using generics objects?
Nice blog :)
Nice one, i figured ti was the .List just googled before i tried anything, cheers for the short cut to knowledge :D