CODEWITHSUNDEEP
c#nhibernatenhibernate-mapping
Steve
Steve

Reputation: 923

nHibernate SQL Not Available error

I have the following code to execute a simple select statement. This query fails given the "SQL Not Available" error. This same code as a pattern works just fine in about 20 different models. In fact if I alter the design of the view implemented in SQL to say hide the LoadID column the query fails stating "Can execute ... ... because LoadID doesn't exist.

My problem is when this code, the hbm.xml model, and the design of the view are all aligned the ses.Query() fails stating "SQL not available"

var rows = ses.Query<vInventory>().Where(c => c.CustomerRecid == recid).Select(c => new vInventory()
{
    InventoryRecid = c.InventoryRecid
    , ItemID = c.ItemID
    , LoadID = c.LoadID
    , CustomerRecid = c.CustomerRecid
}).ToList();

This is the mapping.

<?xml version="1.0" encoding="utf-8" ?>
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2" auto-import="true" assembly="Asmbly" namespace="Asmbly.Models">
  <class name ="vInventory" table="dbo.vInventory" dynamic-update="true">
    <cache usage="read-only"/>
    <id name="InventoryRecid" column="InventoryRecid" type="Int64">
      <generator class="native" />
    </id>
    <property name="ItemID" />
    <property name="LoadID" />
    <property name="CustomerRecid" />
  </class>
</hibernate-mapping>

Update. I took the output of the SQL view and dumped it into a table. Then modified the hbm.xlm to point to the table. Now the ses.Query() works fine. This is NOT a workable possibility.

Please comment on InventoryRecid. In the SQL view this attribute is

ROW_NUMBER() over (...)

How can I get nHibernate to use this view and utilize the InventoryRecid attribute? I will never have attempt an update to this entity.

Update #2: I changed the hbm.xlm mapping from

<class name ="vInventory" table="dbo.vInventory" dynamic-update="true">

to

<class name ="vInventory" table="dbo.vInventory" dynamic-update="false">

recompiled and my problem is gone.

Unfortunately, I change it back to true and recompile and my problem doesn't come back.

Upvotes: 0

Views: 2368

Answers (1)

Pidon
Pidon

Reputation: 275

Is there a special reason you are creating new instances of vInventory in the Select method? nHibernate should already return objects of this type based on your mapping. I think it may screw up the linq provider as a constructor call can't be translated to SQL. The linq provider will handle the Select statement as if it were a normal IEnumerable.

Now what happens is: nHibernate gets a query where only this: .Where(c => c.CustomerRecid == recid) needs to get executed. So nHibernates only queries for the IDs of the objects (because as far as nHibernate knows, it doesn't need any more properties). Which will give you 1, 2, 3, 4, 5, ... and so on.

As soon as your Select gets executed (not on the database) nHibernate figures it needs the rest of the properties so it queries for them one by one by their ID (not very efficient with many results). Now your using a ROW_NUMBER as an ID wchich can be dangerous because it only takes the current resultset into account. When querying for a single record the ROW_NUMBER will always be 1, so nHibernate can't find the rest of your objects anymore.

Upvotes: 1

Related Questions