Hi,

I have three classes, A, B and C. A has an IDictionary of Bs. B's base class, B_Base, has a one-to-one relationship with a C. C has a BLOB member so I wish this object to be lazily loaded. My mapping files for B and C are as below:

<class name="B" table="B" >
<id name="Id" unsaved-value="-9999">
<column name="Id" sql-type="Int32" not-null="true"/>
<generator class="increment" />
</id>
<property name="Name" column="Name" />
<one-to-one name="C" class="C" cascade="all-delete-orphan" />
<many-to-one name="A" class="A" column="Id" />
</class>

<class name="C" table="C" proxy="C" lazy="true">
<id name="Id" column="Id" type="int" unsaved-value="-9999">
<generator class="foreign">
<param name="property">B</param>
</generator>
</id>
<property name="File" column="File" type="BinaryBlob" />
<one-to-one name="B" class="B" constrained="true" />
</class>

All properties and methods within the C C# class are declared 'virtual' so it can be used as a proxy.

The application works as so:
1. The main form of the application has a grid of A objects.

2. When the user double clicks an A object in the grid it calls session.load() to retrieve the A object from the database and displays it in a form. SQL Profiler shows that at this stage only the A object is accessed from the database which is as expected because the Bs are lazy.

3. This form has a grid containing the A object's children (B objects). It displays these in the grid by looping through the hashtable containing the Bs. SQL Profiler shows that a call is made to the database to retrieve the lazy Bs. However, in the SQL there is an outer join on to the C table, thus loading the C object belonging to each B as well. I would have thought that it should only load the Bs due to:
i) I have declared 'lazy="true"' in C's mapping
ii) I have made all properties and methods in C 'virtual' and declared it as a proxy
iii) I have 'constrained="true"' on the <one-to-one> to B in C's mapping file.

Any help as to why the C objects are being loaded at this stage will be much appreciated!

outer-join attribute from B to C has to be set to false:



-devon

Hi Deven,

Setting the outer-join to false does remove the join to the C table in the original sql statement but it adds a new sql statement for each record in the C table (as below)

exec sp_executesql N'SELECT c0_.Id as Id0_, c0_.File as File0_ FROM C c0_ WHERE c0_.Id=@p0', N'@p0 int', @p0 = 2

In the C# call below 'this.a.BCount' causes the above SQL to be created.

for (int i = 0; i < this.a.BCount; i++)
{
. . . .
}

Here's function a.BCount ('Bs' is a Hashtable object):

public int BCount
{
get { return this.Bs.Count; }
}

Thanks,
Steven

that is because the relation is lazy, so a proxy is generated instead of an sql select. But when you access the relation to get the count, nh will have to initialize it: so a query is generated.

Use the nh.util.isInitialized (something like that) to see if the association is a proxy or not.

And actually, I might be missing something but you would get the same thing by doing a a.Cs.Count wouldn't you? Since C is a 1-1 with B? so If you change your code to count Cs you could avoid that extra SELECT just to see how many Bs (Cs) there are.

I have a similar situation with PostedFile and FileContents objexts and when I want to see how many files a Member has posted, I simply count the collection between Member and PostedFile, leaving FileContents alone until I actually want the File.

Cheers,

-devon

Hi,

B is the equivalent of PostedFile and C is the equivalent of FileContents so wouldn't doing a.Cs.Count force all the Cs to be loaded? Plus, because a C belongs to B there is no way the A object can count the Cs without accessing the Bs (if that makes sense!!)?

Do you count your PostedFile objects in code or using a SQL query?

Many thanks.

well, yes, but why count C's when counting B's offers the same result (B is 1-1 with C)?



makes perfect sense and that is true. counting Bs would leave C as a proxy and still give you the result you are looking for, the number of Cs (implied through the number of Bs) associated with an A.



that depends on the situation, but in most instances and counting with a A.Bs.Count property of the IList. in some cases we offer "show me all files over 100K" which forces us to do an SQL IQuery asking for the particluar fields thus avoiding the load of the file contents.

-devon

ok, i misread your earlier post about BCount. i don't necessarily understand why the SQL for C is generated with doing Bs.Count in your method call because, as i understand it, NH will create a collection of lazily loaded objects by instantiating a proxy of the object only which would allow you to count. i'm going to have to look that up and will respond shortly.

-devon