Practicamente 6 meses despues del primer post de esta serie, me animo a escribir una nueva entrada y es que NHibernate despierta mis “amores” por lo simple de configurar que es 
, mas alla del sarcaso, empecemos entonces: Como esta es una serie de entradas relacionadas a NHibernate, aun continuo trabajando sobre el mismo proyecto que tenia anteriormente, con las 2 entidades anteriores y una adicional “Course” tal cual se muestra en el diagrama siguiente:
En esta ocacion planeo explorar las diferentes desafios opciones de borrado en cascada que tiene. La idea general es que yo deseo borrar un estudiante y por lo tanto al borrar el estudiante debo borrar sus inscripciones a los diferentes cursos que hay, esto en otras palabras significa eliminar un registro de la tabla Students y muchos registros de la tabla StudentCourse, como es “logico” no debo borrar en cascada ningun registro de la tabla Courses.
Save-Update
La primera aproximacion que tome es tener los archivos de mapeo de la siguiente manera:
1 <?xml version="1.0" encoding="utf-8" ?> 2 <hibernate-mapping xmlns="urn:nhibernate-mapping-2.2" assembly="DemoNHibernate.Dal.Entities" namespace="DemoNHibernate.Dal.Entities"> 3 <class name="Student" table="Students"> 4 <id name="StudentId" column="StudentId"> 5 <generator class="guid" /> 6 </id> 7 <property name="StudentName" column="StudentName" /> 8 <bag name="StudentCourses" cascade="save-update" > 9 <key column="StudentId" not-null="false" /> 10 <one-to-many class="StudentCourse" /> 11 </bag> 12 </class> 13 </hibernate-mapping>
1 <?xml version="1.0" encoding="utf-8" ?> 2 <hibernate-mapping xmlns="urn:nhibernate-mapping-2.2" assembly="DemoNHibernate.Dal.Entities" namespace="DemoNHibernate.Dal.Entities"> 3 <class name="StudentCourse" table="StudentCourse"> 4 <id name="StudentCourseId" column="StudentCourseId"> 5 <generator class="guid" /> 6 </id> 7 <property name="RegistrationDate" column="RegistrationDate" type="DateTime" /> 8 <many-to-one name="Student" not-null="false" class="Student" cascade="save-update"> 9 <column name="StudentId" not-null="false" /> 10 </many-to-one> 11 <many-to-one name="Course" not-null="true" class="Course" cascade="save-update"> 12 <column name="CourseId" /> 13 </many-to-one> 14 </class> 15 </hibernate-mapping>
1 <?xml version="1.0" encoding="utf-8" ?> 2 <hibernate-mapping xmlns="urn:nhibernate-mapping-2.2" assembly="DemoNHibernate.Dal.Entities" namespace="DemoNHibernate.Dal.Entities"> 3 <class name="Course" table="Courses"> 4 <id name="CourseId" column="CourseId"> 5 <generator class="guid" /> 6 </id> 7 <property name="CourseName" column="CourseName" /> 8 <bag name="StudentCourses" inverse="true" cascade="save-update"> 9 <key column="CourseId" /> 10 <one-to-many class="StudentCourse" /> 11 </bag> 12 </class> 13 </hibernate-mapping>
Las configuraciones mas importantes en estos archivos de mapeo son:
1. La definicion de cascade=”save-update” que define que solamente se hara el control del save y de los updates, pero el borrado se lo deja al comportamiento por defecto que tiene NHibernate. Cual es ese comportamiento por defecto? Es colocar o tratar de colocar en NULL todas las llaves foraneas de la tabla StudentCourse, que pertenecen al registro de student que se esta borrando.
2. Otro elemento importante es que no se especifica inverse=”true” en la relacion one-to-many.
1 [TestMethod] 2 public void DeletingParentChildElements() 3 { 4 Execute((context, tx) => 5 { 6 var st = (from s in context.Students 7 select s).FirstOrDefault(); 8 Assert.IsNotNull(st); 9 Session.Delete(st); 10 tx.Commit(); 11 }); 12 }
Al ejecutar la prueba unitaria obtenemos el siguiente log de sentencias SQL que NHibernate ejecuto:
1 NHibernate: select TOP (@p0) student0_.StudentId as StudentId1_, student0_.StudentName as StudentN2_1_ from Students student0_;@p0 = 1 [Type: Int32 (0)] 2 NHibernate: UPDATE StudentCourse SET StudentId = null WHERE StudentId = @p0;@p0 = 812c5ef7-0f1a-4f84-a383-002586213d35 [Type: Guid (0)] 3 NHibernate: DELETE FROM Students WHERE StudentId = @p0;@p0 = 812c5ef7-0f1a-4f84-a383-002586213d35 [Type: Guid (0)]
Observen lo interesante de esta tecnica, es que se han generado dos sentencias para poder borrar el registro padre y sus dependientes, aunque en realidad no se borraron los registros de la tabla hija, sino que estos quedaron con el valor NULL en su llave foranea, en terminos de NHibernate, estos registros quedaron huerfanos. En la captura de pantalla siguiente se ve solo un registro huerfano, pero les aseguro que hay muchos mas.
all-delete-orphan
Como segunda opcion he analizado una forma de no dejar esos registros huerfanos en la base de datos. En los foros y documentacion la configuracion de mapeo recomendada es como sigue:
1 <?xml version="1.0" encoding="utf-8" ?> 2 <hibernate-mapping xmlns="urn:nhibernate-mapping-2.2" assembly="DemoNHibernate.Dal.Entities" namespace="DemoNHibernate.Dal.Entities"> 3 <class name="Student" table="Students"> 4 <id name="StudentId" column="StudentId"> 5 <generator class="guid" /> 6 </id> 7 <property name="StudentName" column="StudentName" /> 8 <bag name="StudentCourses" inverse="true" cascade="all-delete-orphan" > 9 <key column="StudentId" not-null="false" /> 10 <one-to-many class="StudentCourse" /> 11 </bag> 12 </class> 13 </hibernate-mapping>
1 <?xml version="1.0" encoding="utf-8" ?> 2 <hibernate-mapping xmlns="urn:nhibernate-mapping-2.2" assembly="DemoNHibernate.Dal.Entities" namespace="DemoNHibernate.Dal.Entities"> 3 <class name="StudentCourse" table="StudentCourse"> 4 <id name="StudentCourseId" column="StudentCourseId"> 5 <generator class="guid" /> 6 </id> 7 <property name="RegistrationDate" column="RegistrationDate" type="DateTime" /> 8 <many-to-one name="Student" class="Student" cascade="save-update"> 9 <column name="StudentId" /> 10 </many-to-one> 11 <many-to-one name="Course" not-null="true" class="Course" cascade="save-update"> 12 <column name="CourseId" /> 13 </many-to-one> 14 </class> 15 </hibernate-mapping>
En el anterior archivo de mapeo los elementos a los que deben prestar atencion son: la definicion de cascade=”all-delete-orphan” y al elemento que define la llave foranea como not-null=”false”. El primer elemento borrara los registros huerfanos y el segundo sirve para especificar que la llave foranea permitira valores null.
Lo que se optiene como resultado luego de ejecutar la peticion de borrado, es sorprendentemente lo siguiente:
1 NHibernate: select TOP (@p0) student0_.StudentId as StudentId1_, student0_.StudentName as StudentN2_1_ from Students student0_;@p0 = 1 [Type: Int32 (0)] 2 NHibernate: SELECT studentcou0_.StudentId as StudentId1_, studentcou0_.StudentCourseId as StudentC1_1_, studentcou0_.StudentCourseId as StudentC1_2_0_, studentcou0_.RegistrationDate as Registra2_2_0_, studentcou0_.StudentId as StudentId2_0_, studentcou0_.CourseId as CourseId2_0_ FROM StudentCourse studentcou0_ WHERE studentcou0_.StudentId=@p0;@p0 = 62b4407e-1025-4ae2-bd60-0178fcefe0c2 [Type: Guid (0)] 3 NHibernate: DELETE FROM StudentCourse WHERE StudentCourseId = @p0;@p0 = afe0f28e-0f68-4799-849b-018eb3d61780 [Type: Guid (0)] 4 NHibernate: DELETE FROM StudentCourse WHERE StudentCourseId = @p0;@p0 = 24a8d202-0fc0-4895-92f6-0279715c894b [Type: Guid (0)] 5 NHibernate: DELETE FROM StudentCourse WHERE StudentCourseId = @p0;@p0 = 81b06673-2b99-439f-bc92-0954f1f6a72d [Type: Guid (0)] 6 NHibernate: DELETE FROM StudentCourse WHERE StudentCourseId = @p0;@p0 = 3a8101c1-fb86-4569-b62f-1a4a5f61e076 [Type: Guid (0)] 7 NHibernate: DELETE FROM StudentCourse WHERE StudentCourseId = @p0;@p0 = e23ead18-d36d-48f0-91b5-1e0f218a9490 [Type: Guid (0)] 8 NHibernate: DELETE FROM StudentCourse WHERE StudentCourseId = @p0;@p0 = 55b89be1-d753-4d13-9b73-2f98e65d94a4 [Type: Guid (0)] 9 NHibernate: DELETE FROM StudentCourse WHERE StudentCourseId = @p0;@p0 = ad56599b-afd5-42c1-94f2-36d82186e99c [Type: Guid (0)] 10 NHibernate: DELETE FROM StudentCourse WHERE StudentCourseId = @p0;@p0 = 8b0010d5-a3ff-4951-b908-377ac3c8b228 [Type: Guid (0)] 11 NHibernate: DELETE FROM StudentCourse WHERE StudentCourseId = @p0;@p0 = 50f3306e-a376-4662-85a0-3b4dd021313d [Type: Guid (0)] 12 NHibernate: DELETE FROM StudentCourse WHERE StudentCourseId = @p0;@p0 = c5c4ad94-d764-4fc5-868f-3e90bc849223 [Type: Guid (0)] 13 NHibernate: DELETE FROM StudentCourse WHERE StudentCourseId = @p0;@p0 = c2835509-49b2-47ae-93f6-3fa2690de749 [Type: Guid (0)] 14 NHibernate: DELETE FROM StudentCourse WHERE StudentCourseId = @p0;@p0 = d97f98e8-17e2-4cb0-9472-4cd1d18291d4 [Type: Guid (0)] 15 NHibernate: DELETE FROM StudentCourse WHERE StudentCourseId = @p0;@p0 = 45c7abf5-fdfd-4085-862f-4e1221d53cc7 [Type: Guid (0)] 16 NHibernate: DELETE FROM StudentCourse WHERE StudentCourseId = @p0;@p0 = 0cff1150-12cf-4e7e-8bbd-4ff363295506 [Type: Guid (0)] 17 NHibernate: DELETE FROM StudentCourse WHERE StudentCourseId = @p0;@p0 = 617af7f4-0b4b-4fbc-8f73-53bc0d72c0a7 [Type: Guid (0)] 18 NHibernate: DELETE FROM StudentCourse WHERE StudentCourseId = @p0;@p0 = 4fe5434d-6ccf-4850-9ee2-5769812893ee [Type: Guid (0)] 19 NHibernate: DELETE FROM StudentCourse WHERE StudentCourseId = @p0;@p0 = 326791ac-ff46-4859-b875-5bb760597dd6 [Type: Guid (0)] 20 NHibernate: DELETE FROM StudentCourse WHERE StudentCourseId = @p0;@p0 = a1fb2063-b779-49d0-b520-5e8c1379a4bc [Type: Guid (0)] 21 NHibernate: DELETE FROM StudentCourse WHERE StudentCourseId = @p0;@p0 = fe9273dd-c0b7-4c5c-8e0e-6252bd24ec01 [Type: Guid (0)] 22 NHibernate: DELETE FROM StudentCourse WHERE StudentCourseId = @p0;@p0 = ce4e980a-d5e5-45f9-bf48-63b30b1cdb2a [Type: Guid (0)] 23 NHibernate: DELETE FROM StudentCourse WHERE StudentCourseId = @p0;@p0 = b50dc1dd-7280-479b-a0d0-63dd305d27f3 [Type: Guid (0)] 24 NHibernate: DELETE FROM StudentCourse WHERE StudentCourseId = @p0;@p0 = cb63acdd-999f-4f89-9de9-67d213cbcb91 [Type: Guid (0)] 25 NHibernate: DELETE FROM StudentCourse WHERE StudentCourseId = @p0;@p0 = c4c84f6b-a1df-4468-a5c0-71e54da6f32e [Type: Guid (0)] 26 NHibernate: DELETE FROM StudentCourse WHERE StudentCourseId = @p0;@p0 = bc9f7bbb-6197-4324-a6e1-76eaa044b0fd [Type: Guid (0)] 27 NHibernate: DELETE FROM StudentCourse WHERE StudentCourseId = @p0;@p0 = 48ccb3c5-95f2-4efd-9907-85a551672dec [Type: Guid (0)] 28 NHibernate: DELETE FROM StudentCourse WHERE StudentCourseId = @p0;@p0 = 254cd9ae-179d-4ab7-9147-8d2da2fe50de [Type: Guid (0)] 29 NHibernate: DELETE FROM StudentCourse WHERE StudentCourseId = @p0;@p0 = 370a76db-01e2-4e63-8010-8fdf232066c0 [Type: Guid (0)] 30 NHibernate: DELETE FROM StudentCourse WHERE StudentCourseId = @p0;@p0 = 539d2b31-ec71-474f-911b-915e174ad929 [Type: Guid (0)] 31 NHibernate: DELETE FROM StudentCourse WHERE StudentCourseId = @p0;@p0 = 5bc0ff81-1517-4f28-91e5-91e482b6a8e1 [Type: Guid (0)] 32 NHibernate: DELETE FROM StudentCourse WHERE StudentCourseId = @p0;@p0 = 037d8dfd-4f5a-437f-baf9-9fb852fe1af0 [Type: Guid (0)] 33 NHibernate: DELETE FROM StudentCourse WHERE StudentCourseId = @p0;@p0 = 60f4a979-d5e8-4321-80ae-a02db446011b [Type: Guid (0)] 34 NHibernate: DELETE FROM StudentCourse WHERE StudentCourseId = @p0;@p0 = 839d4d5d-8c37-4962-87e5-a25f048f4308 [Type: Guid (0)] 35 NHibernate: DELETE FROM StudentCourse WHERE StudentCourseId = @p0;@p0 = 0f9f6b77-b181-403d-8c8f-a6f2d5da36d1 [Type: Guid (0)] 36 NHibernate: DELETE FROM StudentCourse WHERE StudentCourseId = @p0;@p0 = 9d469ea0-b078-43d7-8377-b209c6755305 [Type: Guid (0)] 37 NHibernate: DELETE FROM StudentCourse WHERE StudentCourseId = @p0;@p0 = 4ae8090a-d4b0-4022-bc69-b66c558c970a [Type: Guid (0)] 38 NHibernate: DELETE FROM StudentCourse WHERE StudentCourseId = @p0;@p0 = f066dc0b-12d8-41bf-aee9-be2e571bc80f [Type: Guid (0)] 39 NHibernate: DELETE FROM StudentCourse WHERE StudentCourseId = @p0;@p0 = a909893f-9395-4172-a327-bf3d3cf91465 [Type: Guid (0)] 40 NHibernate: DELETE FROM StudentCourse WHERE StudentCourseId = @p0;@p0 = 592c002f-9ff2-4891-98e6-c221a6314884 [Type: Guid (0)] 41 NHibernate: DELETE FROM StudentCourse WHERE StudentCourseId = @p0;@p0 = 7349388b-9443-4388-8591-c6bb6740fa8b [Type: Guid (0)] 42 NHibernate: DELETE FROM StudentCourse WHERE StudentCourseId = @p0;@p0 = d6e9da25-1353-4266-8a36-c6f40833fa5c [Type: Guid (0)] 43 NHibernate: DELETE FROM StudentCourse WHERE StudentCourseId = @p0;@p0 = 877123a5-198b-4889-af38-c81adf0cb9c9 [Type: Guid (0)] 44 NHibernate: DELETE FROM StudentCourse WHERE StudentCourseId = @p0;@p0 = e188ff07-0022-43a5-a132-d03715ce52d3 [Type: Guid (0)] 45 NHibernate: DELETE FROM StudentCourse WHERE StudentCourseId = @p0;@p0 = 315f4dfa-41be-47a5-8f1a-d9d08af64def [Type: Guid (0)] 46 NHibernate: DELETE FROM StudentCourse WHERE StudentCourseId = @p0;@p0 = b3c97332-3283-4662-8e76-da4c92a975c8 [Type: Guid (0)] 47 NHibernate: DELETE FROM StudentCourse WHERE StudentCourseId = @p0;@p0 = 86e7cfdc-387e-4111-9f3b-dadbb2b72868 [Type: Guid (0)] 48 NHibernate: DELETE FROM StudentCourse WHERE StudentCourseId = @p0;@p0 = cf9618ab-6d57-4daf-be3a-dbe69a271853 [Type: Guid (0)] 49 NHibernate: DELETE FROM StudentCourse WHERE StudentCourseId = @p0;@p0 = d6494f0a-31ed-4a9c-a200-ec5883531d6c [Type: Guid (0)] 50 NHibernate: DELETE FROM StudentCourse WHERE StudentCourseId = @p0;@p0 = 0f7c5562-0ce6-4e56-bdcb-f28effa44b89 [Type: Guid (0)] 51 NHibernate: DELETE FROM StudentCourse WHERE StudentCourseId = @p0;@p0 = ab76283b-d7c2-4ad6-a633-f369bf52a715 [Type: Guid (0)] 52 NHibernate: DELETE FROM StudentCourse WHERE StudentCourseId = @p0;@p0 = 1248e956-3fc0-4cee-afc4-f861e8ac5bed [Type: Guid (0)] 53 NHibernate: DELETE FROM Students WHERE StudentId = @p0;@p0 = 62b4407e-1025-4ae2-bd60-0178fcefe0c2 [Type: Guid (0)]
Dije sorprendentemente lento, porque primero ha generado una sentencia select para recuperar todos los registros hijos (linea 2: imaginen si fuesen cientos de registros!!!) y luego itera sobre esa lista generando tambien multiples sentencias DELETE para los registros de esa tabla. Realmente ineficiente.
Las malas noticias del uso de all-delete-orphan no terminan ahi, si utilizamos una configuracion de mapeo como la siguiente:
1 <?xml version="1.0" encoding="utf-8" ?> 2 <hibernate-mapping xmlns="urn:nhibernate-mapping-2.2" assembly="DemoNHibernate.Dal.Entities" namespace="DemoNHibernate.Dal.Entities"> 3 <class name="Student" table="Students"> 4 <id name="StudentId" column="StudentId"> 5 <generator class="guid" /> 6 </id> 7 <property name="StudentName" column="StudentName" /> 8 <bag name="StudentCourses" cascade="all-delete-orphan" > 9 <key column="StudentId" not-null="false" /> 10 <one-to-many class="StudentCourse" /> 11 </bag> 12 </class> 13 </hibernate-mapping>
1 <?xml version="1.0" encoding="utf-8" ?> 2 <hibernate-mapping xmlns="urn:nhibernate-mapping-2.2" assembly="DemoNHibernate.Dal.Entities" namespace="DemoNHibernate.Dal.Entities"> 3 <class name="StudentCourse" table="StudentCourse"> 4 <id name="StudentCourseId" column="StudentCourseId"> 5 <generator class="guid" /> 6 </id> 7 <property name="RegistrationDate" column="RegistrationDate" type="DateTime" /> 8 <many-to-one name="Student" class="Student" cascade="save-update"> 9 <column name="StudentId" /> 10 </many-to-one> 11 <many-to-one name="Course" not-null="true" class="Course" cascade="save-update"> 12 <column name="CourseId" /> 13 </many-to-one> 14 </class> 15 </hibernate-mapping>
El resultado es el siguiente:
1 NHibernate: select TOP (@p0) student0_.StudentId as StudentId1_, student0_.StudentName as StudentN2_1_ from Students student0_;@p0 = 1 [Type: Int32 (0)] 2 NHibernate: SELECT studentcou0_.StudentId as StudentId1_, studentcou0_.StudentCourseId as StudentC1_1_, studentcou0_.StudentCourseId as StudentC1_2_0_, studentcou0_.RegistrationDate as Registra2_2_0_, studentcou0_.StudentId as StudentId2_0_, studentcou0_.CourseId as CourseId2_0_ FROM StudentCourse studentcou0_ WHERE studentcou0_.StudentId=@p0;@p0 = 3df163e6-9e0c-4ea4-bbab-024d8a826478 [Type: Guid (0)] 3 NHibernate: UPDATE StudentCourse SET StudentId = null WHERE StudentId = @p0;@p0 = 3df163e6-9e0c-4ea4-bbab-024d8a826478 [Type: Guid (0)] 4 NHibernate: DELETE FROM StudentCourse WHERE StudentCourseId = @p0;@p0 = ed22628f-70fb-49da-8e9d-017cb8804153 [Type: Guid (0)] 5 NHibernate: DELETE FROM StudentCourse WHERE StudentCourseId = @p0;@p0 = 823132a9-81dd-4d15-8bc5-0560002b616b [Type: Guid (0)] 6 NHibernate: DELETE FROM StudentCourse WHERE StudentCourseId = @p0;@p0 = df8a6f0d-e437-4033-adfe-1226b7e6ccc5 [Type: Guid (0)] 7 NHibernate: DELETE FROM StudentCourse WHERE StudentCourseId = @p0;@p0 = e4e217f2-6035-49b7-93ed-1625a33bebf2 [Type: Guid (0)] 8 NHibernate: DELETE FROM StudentCourse WHERE StudentCourseId = @p0;@p0 = ff4367c5-e113-408d-afe5-17cde78b098d [Type: Guid (0)] 9 NHibernate: DELETE FROM StudentCourse WHERE StudentCourseId = @p0;@p0 = 76939286-24cb-446c-95a7-19c12c1cacc7 [Type: Guid (0)] 10 NHibernate: DELETE FROM StudentCourse WHERE StudentCourseId = @p0;@p0 = 406e5eab-3ee3-4425-9d41-1c435916cab7 [Type: Guid (0)] 11 NHibernate: DELETE FROM StudentCourse WHERE StudentCourseId = @p0;@p0 = 943ebddb-6ceb-4f53-a0a3-2df9514578ee [Type: Guid (0)] 12 NHibernate: DELETE FROM StudentCourse WHERE StudentCourseId = @p0;@p0 = 48a95446-1b72-4e1a-aaca-311b74788f22 [Type: Guid (0)] 13 NHibernate: DELETE FROM StudentCourse WHERE StudentCourseId = @p0;@p0 = d5f4b24a-ac78-4538-aed1-3c48c41267ef [Type: Guid (0)] 14 NHibernate: DELETE FROM StudentCourse WHERE StudentCourseId = @p0;@p0 = 4005baf4-fed6-4a6b-8970-3ec1cd8b6e6b [Type: Guid (0)] 15 NHibernate: DELETE FROM StudentCourse WHERE StudentCourseId = @p0;@p0 = bf5a3bec-4870-4765-a2c0-42f01ac649d9 [Type: Guid (0)] 16 NHibernate: DELETE FROM StudentCourse WHERE StudentCourseId = @p0;@p0 = c3a18b48-62bc-4aae-86fa-486f041271c2 [Type: Guid (0)] 17 NHibernate: DELETE FROM StudentCourse WHERE StudentCourseId = @p0;@p0 = 87ebe587-f0bc-4ea4-84b0-48db75689bc9 [Type: Guid (0)] 18 NHibernate: DELETE FROM StudentCourse WHERE StudentCourseId = @p0;@p0 = 90cf3e31-689e-45a8-a28e-4ba673dfd411 [Type: Guid (0)] 19 NHibernate: DELETE FROM StudentCourse WHERE StudentCourseId = @p0;@p0 = 0e9ca64c-b451-4113-a445-4c6c060fe0f4 [Type: Guid (0)] 20 NHibernate: DELETE FROM StudentCourse WHERE StudentCourseId = @p0;@p0 = 168b732b-de67-4a6c-9261-5318dae239ae [Type: Guid (0)] 21 NHibernate: DELETE FROM StudentCourse WHERE StudentCourseId = @p0;@p0 = dc2263c8-44d3-4aa0-9c7e-58d1e13f7764 [Type: Guid (0)] 22 NHibernate: DELETE FROM StudentCourse WHERE StudentCourseId = @p0;@p0 = b055aa2e-d8c2-4b9d-9c73-58f1aff63e3a [Type: Guid (0)] 23 NHibernate: DELETE FROM StudentCourse WHERE StudentCourseId = @p0;@p0 = 20283918-7c19-48e4-acd1-5d7c680df30a [Type: Guid (0)] 24 NHibernate: DELETE FROM StudentCourse WHERE StudentCourseId = @p0;@p0 = 4d62c9e8-e36e-4c6c-85ed-60dac8218b34 [Type: Guid (0)] 25 NHibernate: DELETE FROM StudentCourse WHERE StudentCourseId = @p0;@p0 = fec90de0-c22a-4cf1-b47c-63e55b0b34b4 [Type: Guid (0)] 26 NHibernate: DELETE FROM StudentCourse WHERE StudentCourseId = @p0;@p0 = 59a946c9-6a99-4f82-bdc0-64202a9c2eb3 [Type: Guid (0)] 27 NHibernate: DELETE FROM StudentCourse WHERE StudentCourseId = @p0;@p0 = 13c240f1-9808-47cf-ba10-653fb444991d [Type: Guid (0)] 28 NHibernate: DELETE FROM StudentCourse WHERE StudentCourseId = @p0;@p0 = 34c13a9d-0394-4984-85e7-66e2ee9f8f55 [Type: Guid (0)] 29 NHibernate: DELETE FROM StudentCourse WHERE StudentCourseId = @p0;@p0 = 59ac31ed-2636-49b4-a5e1-69f636f5dffa [Type: Guid (0)] 30 NHibernate: DELETE FROM StudentCourse WHERE StudentCourseId = @p0;@p0 = f44d99fb-3548-4bff-9df7-70c8dddccf9e [Type: Guid (0)] 31 NHibernate: DELETE FROM StudentCourse WHERE StudentCourseId = @p0;@p0 = b651ee73-b30b-4e0e-a86b-76d8158f408c [Type: Guid (0)] 32 NHibernate: DELETE FROM StudentCourse WHERE StudentCourseId = @p0;@p0 = c8ac848a-2a1a-4155-9b83-7ba18ed0ab40 [Type: Guid (0)] 33 NHibernate: DELETE FROM StudentCourse WHERE StudentCourseId = @p0;@p0 = a8a62220-e5d2-44f0-b7cd-7cbc54de1638 [Type: Guid (0)] 34 NHibernate: DELETE FROM StudentCourse WHERE StudentCourseId = @p0;@p0 = 4604521f-484f-4bcc-bc75-80c27070e3e4 [Type: Guid (0)] 35 NHibernate: DELETE FROM StudentCourse WHERE StudentCourseId = @p0;@p0 = 179b5622-66b1-4bf3-bbe6-820f682aad87 [Type: Guid (0)] 36 NHibernate: DELETE FROM StudentCourse WHERE StudentCourseId = @p0;@p0 = a76f36d2-9d5f-48f5-b9f4-8c1cdf05da7c [Type: Guid (0)] 37 NHibernate: DELETE FROM StudentCourse WHERE StudentCourseId = @p0;@p0 = db4600e1-3782-4986-8548-99e53fad2e7d [Type: Guid (0)] 38 NHibernate: DELETE FROM StudentCourse WHERE StudentCourseId = @p0;@p0 = e7e99834-5f65-4f85-9302-a4695ef45141 [Type: Guid (0)] 39 NHibernate: DELETE FROM StudentCourse WHERE StudentCourseId = @p0;@p0 = 28b13fe5-1daf-448c-80f6-a6d8de81eedd [Type: Guid (0)] 40 NHibernate: DELETE FROM StudentCourse WHERE StudentCourseId = @p0;@p0 = ad44e417-af7c-4db8-9d65-a884b494e0c0 [Type: Guid (0)] 41 NHibernate: DELETE FROM StudentCourse WHERE StudentCourseId = @p0;@p0 = 90815e58-8d09-43df-bee7-af0c484b4a9b [Type: Guid (0)] 42 NHibernate: DELETE FROM StudentCourse WHERE StudentCourseId = @p0;@p0 = 03c92453-75f7-455e-8c7f-ba043c6e4960 [Type: Guid (0)] 43 NHibernate: DELETE FROM StudentCourse WHERE StudentCourseId = @p0;@p0 = db405dcc-33d6-457f-b534-c1128a6f1646 [Type: Guid (0)] 44 NHibernate: DELETE FROM StudentCourse WHERE StudentCourseId = @p0;@p0 = ebf07a1c-1de3-4b4c-a231-c2e8a59b0a7d [Type: Guid (0)] 45 NHibernate: DELETE FROM StudentCourse WHERE StudentCourseId = @p0;@p0 = 19ade8a2-132e-42fa-a9e3-c513950ffd11 [Type: Guid (0)] 46 NHibernate: DELETE FROM StudentCourse WHERE StudentCourseId = @p0;@p0 = fb0eb4d3-aacd-457e-9c6c-cf09aadbf814 [Type: Guid (0)] 47 NHibernate: DELETE FROM StudentCourse WHERE StudentCourseId = @p0;@p0 = adcb700e-a3a9-4126-a2f2-d742559a1c4b [Type: Guid (0)] 48 NHibernate: DELETE FROM StudentCourse WHERE StudentCourseId = @p0;@p0 = 57c21829-56bc-474a-80d5-e359d4f02a62 [Type: Guid (0)] 49 NHibernate: DELETE FROM StudentCourse WHERE StudentCourseId = @p0;@p0 = c1960cce-931e-4969-924e-ec295d1da75b [Type: Guid (0)] 50 NHibernate: DELETE FROM StudentCourse WHERE StudentCourseId = @p0;@p0 = b17205cf-f08f-4601-97fa-ee851e0d8ae9 [Type: Guid (0)] 51 NHibernate: DELETE FROM StudentCourse WHERE StudentCourseId = @p0;@p0 = eab5b833-a03d-4f72-a75e-eeb29fe7ae3c [Type: Guid (0)] 52 NHibernate: DELETE FROM StudentCourse WHERE StudentCourseId = @p0;@p0 = c362251d-ce69-4f2e-b716-f6c6a33f7ac0 [Type: Guid (0)] 53 NHibernate: DELETE FROM StudentCourse WHERE StudentCourseId = @p0;@p0 = 9e7e6b92-3115-4312-bbe1-fbac3b2c2574 [Type: Guid (0)] 54 NHibernate: DELETE FROM Students WHERE StudentId = @p0;@p0 = 3df163e6-9e0c-4ea4-bbab-024d8a826478 [Type: Guid (0)]
Lo unico diferente en estos mapeos es la definicion del inverse =»true», lo cual ha generado una sentencia UPDATE adicional que se observa en la linea 3. Ahora esto me hace pensar cuantas personas tendran un mapeo que funciona, borra, pero lo hace cada vez de manera ineficiente?. Con razon mi carino especial a NHibernate.
On Delete = “Cascade”
Finalmente la opcion que yo recomiendo utilizar, aunque cabe decir que en algunos foros no la aconsejan, es utilizar las caracteristicas de borrado en cascada de la base de datos. Esta opcion consiste en definir los archivos de mapeo como siguen:
1 <?xml version="1.0" encoding="utf-8" ?> 2 <hibernate-mapping xmlns="urn:nhibernate-mapping-2.2" assembly="DemoNHibernate.Dal.Entities" namespace="DemoNHibernate.Dal.Entities"> 3 <class name="Student" table="Students"> 4 <id name="StudentId" column="StudentId"> 5 <generator class="guid" /> 6 </id> 7 <property name="StudentName" column="StudentName" /> 8 <bag name="StudentCourses" inverse="true" cascade="save-update" > 9 <key column="StudentId" not-null="false" on-delete="cascade" /> 10 <one-to-many class="StudentCourse" /> 11 </bag> 12 </class> 13 </hibernate-mapping>
En este archivo el punto mas importante es la definicion de on-delete=”cascade” y la especificacion de la relacion inversa con inverse=”true”, si esto ultimo no se coloca, al momento de utilizar la coleccion saldra una excepcion indicando que es necesario especificar este atributo.
A continuacion el resultado de la ejecucion de la peticion de borrado, en esta ocacion como se puede apreciar solo se tiene dos sentencias SQL generadas por NHibernate, una para recuperar el primer registro de la tabla estudiantes y la segunda sentencia para realizar el borrado fisico, no existe ninguna otra sentencia adicional y luego de que se produce el borrado no han quedado registros huerfanos.
1 NHibernate: select TOP (@p0) student0_.StudentId as StudentId1_, student0_.StudentName as StudentN2_1_ from Students student0_;@p0 = 1 [Type: Int32 (0)] 2 NHibernate: DELETE FROM Students WHERE StudentId = @p0;@p0 = 51aa39c1-3929-4970-8401-00e4cc1e20a6 [Type: Guid (0)]
Es necesario advertir que los temas de rendimiento (performance) al momento de borrar son tema de otro articulo, pero probablemente la opcion mas recomendable sigue siendo la tercera opcion, al menos por que no genera sentencias SQL explicitas y confia en la definicion de la base de datos.
Espero que estos “hallazgos” les sea de utilidad.
Saludos.