I have many entities that store BLOB values in separate tables, joined to the entity's main table as a one-to-one relationship. The whole point of doing this is so that the BLOB doesn't get selected unless you really want it.
Yet, when I run an HQL query that selects entities with one-to-one properties to entities holding the BLOB values, NHibernate executes a separate SQL statement selecting from the BLOB table for every row returned by the main query.
Here is an example an entity mapping that references a BLOB entity:
Code:
<hibernate-mapping
xmlns="urn:nhibernate-mapping-2.0"
default-cascade="none"
default-access="field.camelcase-underscore"
assembly="Domain"
namespace="MyCompany.Entities"
>
<class name="Activity" table="oncd_activity" lazy="true" dynamic-update="true" select-before-update="true">
<id name="Id" column="activity_id" type="String" length="10" unsaved-value="null" access="field.camelcase-underscore">
<generator class="MyCompany.Entities.DataAccess.NHibernate.IdentifierGenerator, EntityAccess"/>
</id>
...
<one-to-one name="Note" class="ActivityNote" cascade="delete" constrained="false" access="field.camelcase-underscore"/>
...
</class>
</hibernate-mapping>
Here is the BLOB entity mapping:
Code:
<hibernate-mapping
xmlns="urn:nhibernate-mapping-2.0"
default-cascade="none"
default-access="field.camelcase-underscore"
assembly="Domain"
namespace="MyCompany.Entities"
>
<class name="ActivityNote" table="oncd_activity_note" lazy="true" dynamic-update="true" select-before-update="true">
<id name="Id" column="activity_id" type="String" length="10" unsaved-value="null" access="field.camelcase-underscore">
<generator class="MyCompany.Entities.DataAccess.NHibernate.IdentifierGenerator, EntityAccess"/>
</id>
<property name="Note" column="note" type="StringClob" length="1073741823" access="field.camelcase-underscore"/>
<one-to-one name="Activity" class="Activity" cascade="delete" constrained="true" access="field.camelcase-underscore"/>
...
</class>
</hibernate-mapping>
Here is my HQL query:
Code:
FROM Activity AS Activity
JOIN Activity.Action AS Action
JOIN Activity.Result AS Result
LEFT JOIN Activity.Users AS ActivityUser
JOIN ActivityUser.User AS PrimaryUser
LEFT JOIN Activity.Contacts AS ActivityContact
JOIN ActivityContact.Contact AS PrimaryContact
WHERE ( ActivityUser.IsPrimary = true
OR ActivityUser.IsPrimary IS NULL)
AND ( ActivityContact.IsPrimary = true
OR ActivityContact.IsPrimary IS NULL)
AND Action.Description LIKE :actionDescription
ORDER BY Activity.DueDate DESC
And here is the log (my comments starting with ***)
Code:
***
*** This first select is expected, and just fine
***
2005-12-28 11:55:07,934 [4868] DEBUG NHibernate.SQL - select activity0_.activity_id as activity1_0_, setupactio1_.action_code as action_c1_1_, setupresul2_.result_code as result_c1_2_, users3_.activity_user_id as activity1_3_, setupuser4_.user_code as user_code4_, contacts5_.activity_contact_id as activity1_5_, contact6_.contact_id as contact_id6_, activity0_.completed_by_user_code as complet25_0_, activity0_.confirmed_time_to as confirm14_0_, activity0_.description as descript5_0_, activity0_.updated_date as updated_9_0_, activity0_.batch_address_type_code as batch_a18_0_, activity0_.confirmed_time as confirm12_0_, activity0_.source_code as source_24_0_, activity0_.duration as duration0_, activity0_.due_date as due_date0_, activity0_.created_by_user_code as created26_0_, activity0_.creation_date as creation6_0_, activity0_.batch_status_code as batch_s19_0_, activity0_.document_id as documen15_0_, activity0_.start_time as start_time0_, activity0_.completion_time as completi8_0_, activity0_.completion_date as completi7_0_, activity0_.project_code as project21_0_, activity0_.batch_result_code as batch_r22_0_, activity0_.result_code as result_23_0_, activity0_.opportunity_id as opportu29_0_, activity0_.action_code as action_17_0_, activity0_.confirmed_time_from as confirm13_0_, activity0_.campaign_code as campaig20_0_, activity0_.recur_id as recur_id0_, activity0_.notify_when_completed as notify_11_0_, activity0_.updated_by_user_code as updated27_0_, activity0_.milestone_activity_id as milesto16_0_, activity0_.priority as priority0_, activity0_.incident_id as inciden28_0_, setupactio1_.prompt_for_schedule as prompt_f5_1_, setupactio1_.source_code as source_c9_1_, setupactio1_.available_to_outlook as availabl6_1_, setupactio1_.campaign_code as campaig10_1_, setupactio1_.description as descript2_1_, setupactio1_.prompt_for_next as prompt_f8_1_, setupactio1_.chain_id as chain_id1_, setupactio1_.active as active1_, setupactio1_.available_to_mobile as availabl7_1_, setupactio1_.schedule_type as schedule4_1_, setupactio1_.sort_order as sort_order1_, setupactio1_.action_type_code as action_13_1_, setupresul2_.active as active2_, setupresul2_.description as descript2_2_, users3_.creation_date as creation5_3_, users3_.assignment_date as assignme2_3_, users3_.activity_id as activit11_3_, users3_.sort_order as sort_order3_, users3_.updated_date as updated_6_3_, users3_.primary_flag as primary_7_3_, users3_.user_code as user_code3_, users3_.attendance as attendance3_, users3_.created_by_user_code as created_8_3_, users3_.updated_by_user_code as updated_9_3_, setupuser4_.login_id as login_id4_, setupuser4_.last_name as last_name4_, setupuser4_.cti_user_code as cti_use14_4_, setupuser4_.full_name as full_name4_, setupuser4_.department_code as departm26_4_, setupuser4_.password_date as password4_4_, setupuser4_.display_name as display22_4_, setupuser4_.cti_password as cti_pas15_4_, setupuser4_.cti_extension as cti_ext17_4_, setupuser4_.cti_server as cti_server4_, setupuser4_.middle_name as middle_n8_4_, setupuser4_.clear_cache as clear_c20_4_, setupuser4_.title as title4_, setupuser4_.outlook_sync_confirm as outlook25_4_, setupuser4_.license_type as license23_4_, setupuser4_.description as descrip11_4_, setupuser4_.cti_station as cti_sta16_4_, setupuser4_.first_name as first_name4_, setupuser4_.action_set_code as action_18_4_, setupuser4_.startup_object_id as startup19_4_, setupuser4_.outlook_sync_frequency as outlook24_4_, setupuser4_.job_function_code as job_fun27_4_, setupuser4_.active as active4_, setupuser4_.password_expires as password5_4_, setupuser4_.password_value as password3_4_, setupuser4_.change_password as change_p6_4_, contacts5_.creation_date as creation5_5_, contacts5_.assignment_date as assignme2_5_, contacts5_.activity_id as activit10_5_, contacts5_.sort_order as sort_order5_, contacts5_.updated_date as updated_6_5_, contacts5_.primary_flag as primary_7_5_, contacts5_.contact_id as contact_id5_, contacts5_.attendance as attendance5_, contacts5_.created_by_user_code as created_8_5_, contacts5_.updated_by_user_code as updated_9_5_, contact6_.status_updated_by_user_code as status_21_6_, contact6_.contact_status_code as contact18_6_, contact6_.middle_name as middle_n4_6_, contact6_.status_updated_date as status_16_6_, contact6_.first_name_search as first_na7_6_, contact6_.do_not_solicit as do_not_12_6_, contact6_.updated_date as updated15_6_, contact6_.duplicate_check as duplica13_6_, contact6_.salutation_code as salutat19_6_, contact6_.external_id as externa11_6_, contact6_.last_name as last_name6_, contact6_.last_name_soundex as last_na10_6_, contact6_.last_name_search as last_nam8_6_, contact6_.creation_date as creatio14_6_, contact6_.suffix as suffix6_, contact6_.first_name as first_name6_, contact6_.greeting as greeting6_, contact6_.first_name_soundex as first_na9_6_, contact6_.updated_by_user_code as updated22_6_, contact6_.contact_method_code as contact17_6_, contact6_.created_by_user_code as created20_6_ from oncd_activity activity0_ inner join onca_action setupactio1_ on activity0_.action_code=setupactio1_.action_code inner join onca_result setupresul2_ on activity0_.result_code=setupresul2_.result_code left outer join oncd_activity_user users3_ on activity0_.activity_id=users3_.activity_id inner join onca_user setupuser4_ on users3_.user_code=setupuser4_.user_code left outer join oncd_activity_contact contacts5_ on activity0_.activity_id=contacts5_.activity_id inner join oncd_contact contact6_ on contacts5_.contact_id=contact6_.contact_id where ((users3_.primary_flag='Y')OR(users3_.primary_flag IS NULL))AND((contacts5_.primary_flag='Y')OR(contacts5_.primary_flag IS NULL))AND(setupactio1_.description LIKE @p0) order by activity0_.due_date DESC
***
*** The following selects occur for each row returned by the original select -- I don't want them to occur ...
***
2005-12-28 11:55:08,434 [4868] DEBUG NHibernate.SQL - SELECT activityno0_.activity_id as activity1_0_, activityno0_.updated_date as updated_4_0_, activityno0_.note as note0_, activityno0_.updated_by_user_code as updated_5_0_, activityno0_.creation_date as creation3_0_, activityno0_.created_by_user_code as created_6_0_ FROM oncd_activity_note activityno0_ WHERE activityno0_.activity_id=@p0
2005-12-28 11:55:08,762 [4868] DEBUG NHibernate.SQL - SELECT contactnot0_.contact_id as contact_id0_, contactnot0_.updated_date as updated_4_0_, contactnot0_.note as note0_, contactnot0_.created_by_user_code as created_5_0_, contactnot0_.updated_by_user_code as updated_6_0_, contactnot0_.creation_date as creation3_0_ FROM oncd_contact_note contactnot0_ WHERE contactnot0_.contact_id=@p0
2005-12-28 11:55:08,950 [4868] DEBUG NHibernate.SQL - SELECT activityno0_.activity_id as activity1_0_, activityno0_.updated_date as updated_4_0_, activityno0_.note as note0_, activityno0_.updated_by_user_code as updated_5_0_, activityno0_.creation_date as creation3_0_, activityno0_.created_by_user_code as created_6_0_ FROM oncd_activity_note activityno0_ WHERE activityno0_.activity_id=@p0
2005-12-28 11:55:08,950 [4868] DEBUG NHibernate.SQL - SELECT contactnot0_.contact_id as contact_id0_, contactnot0_.updated_date as updated_4_0_, contactnot0_.note as note0_, contactnot0_.created_by_user_code as created_5_0_, contactnot0_.updated_by_user_code as updated_6_0_, contactnot0_.creation_date as creation3_0_ FROM oncd_contact_note contactnot0_ WHERE contactnot0_.contact_id=@p0
...
Is there a way to prevent NHibernate from selecting from this BLOB table, or to include the selection as a join in the main SQL query? My HQL query actually selects two different entities with related BLOB child entities, but I thought SQL Server 2000 could select multiple BLOBs in the same SQL query, so that shouldn't be a reason for NHibernate to use separate SQL selects ...
Login
FAQ
Search
