Logo

NHibernate

The object-relational mapper for .NET

Oh… beautiful SQL

[My Blog]

Given a complex mapping like this

<class name="Animal">
<
id name="id">
<
generator class="native"/>
</
id>
<
property name="description"/>
<
property name="bodyWeight" column="body_weight"/>
<
many-to-one name="mother" column="mother_id"/>
<
many-to-one name="father" column="father_id"/>
<
many-to-one name="zoo" column="zoo_id"/>
<
property name="serialNumber"/>
<
set name="offspring" order-by="father_id">
<
key column="mother_id"/>
<
one-to-many class="Animal"/>
</
set>
<
joined-subclass name="Reptile">
<
key column="animal"/>
<
property name="bodyTemperature"/>
<
joined-subclass name="Lizard">
<
key column="reptile"/>
</
joined-subclass>
</
joined-subclass>
<
joined-subclass name="Mammal">
<
key column="animal"/>
<
property name="pregnant"/>
<
property name="birthdate" type="date"/>
<
joined-subclass name="DomesticAnimal">
<
key column="mammal"/>
<
many-to-one name="owner"/>
<
joined-subclass name="Cat">
<
key column="mammal"/>
</
joined-subclass>
<
joined-subclass name="Dog">
<
key column="mammal"/>
</
joined-subclass>
</
joined-subclass>
<
joined-subclass name="Human">
<
key column="mammal"/>
<
component name="name">
<
property name="first" column="name_first"/>
<
property name="initial" column="name_initial"/>
<
property name="last" column="name_last"/>
</
component>
<
property name="nickName"/>
<
property name="height"/>

<
property name="intValue"/>
<
property name="floatValue"/>
<
property name="bigDecimalValue"/>
<
property name="bigIntegerValue"/>

<
bag name="friends">
<
key column="human1"/>
<
many-to-many column="human2" class="Human"/>
</
bag>
<
map name="family">
<
key column="human1"/>
<
map-key column="relationship" type="string"/>
<
many-to-many column="human2" class="Human"/>
</
map>
<
bag name="pets" inverse="true">
<
key column="owner"/>
<
one-to-many class="DomesticAnimal"/>
</
bag>
<
set name="nickNames" lazy="false" table="human_nick_names" sort="natural">
<
key column="human"/>
<
element column="nick_name" type="string" not-null="true"/>
</
set>
<
map name="addresses" table="addresses">
<
key column="human"/>
<
map-key type="string" column="type"/>
<
composite-element class="Address">
<
property name="street"/>
<
property name="city"/>
<
property name="postalCode"/>
<
property name="country"/>
<
many-to-one name="stateProvince" column="state_prov_id" class="StateProvince"/>
</
composite-element>
</
map>
</
joined-subclass>
</
joined-subclass>
</
class>

Which should be the result of s.CreateQuery("delete Animal").ExecuteUpdate() ?

Oh… beautiful SQL

create table #Animal (id BIGINT not null) 

insert into #Animal SELECT animal0_.id as id FROM Animal animal0_

DELETE FROM Human WHERE (mammal) IN (select id from #Animal)

DELETE FROM Dog WHERE (mammal) IN (select id from #Animal)

DELETE FROM Cat WHERE (mammal) IN (select id from #Animal)

DELETE FROM DomesticAnimal WHERE (mammal) IN (select id from #Animal)

DELETE FROM Mammal WHERE (animal) IN (select id from #Animal)

DELETE FROM Lizard WHERE (reptile) IN (select id from #Animal)

DELETE FROM Reptile WHERE (animal) IN (select id from #Animal)

DELETE FROM Animal WHERE (id) IN (select id from #Animal)

drop table #Animal

Do you see the temp table ? Do you see the order of queries ?

“se me cayó una lagrima”


Posted Fri, 08 May 2009 04:39:00 AM by fabiomaulo
Filed under: News NHibernate HQL

comments powered by Disqus
© NHibernate Community 2016