Delete multiple records from database

Topics: Writing modules
Jul 23, 2012 at 8:54 PM
Edited Jul 24, 2012 at 6:27 PM

Hi,

I have custom tables, and 1-n relationship between them in Orchard db.

I want to delete multiple records from child table using one query.

First, I created query, and tried to delete records in this way

using (var session = _sessionLocator.For(typeof(DataRecord))) {            
                var qry = session.CreateSQLQuery(string.Format(
                    @"
                        DELETE
                        FROM {0}D_Service_DataRecord
                        WHERE DeviceRecord_Id = :deviceid
                    ", _shellSettings.DataTablePrefix != "" ? _shellSettings.DataTablePrefix + "_" : ""))
                    .SetParameter("deviceid", id);

                qry.ExecuteUpdate();
                session.Flush();
            }

When i try step-by-step code excecution, when call qry.ExecuteUpdate(); records really deleting, but then again thay appearing in the database.

Then I tried delete records like this

using (var session = _sessionLocator.For(typeof(DataRecord))) {
                string hSql = string.Format("delete from {0} where DeviceRecord_Id = {1}", typeof(DataRecord).FullName, id);
                IQuery query = session.CreateQuery(hSql);
                query.ExecuteUpdate();
                session.Flush();
            }

but I have exception D.Service.Models.DataRecord is not mapped [delete from D.Service.Models.DataRecord where DeviceRecord_Id = 8]

Edit:

it seems because I do not have an id in my DataRecord class. Is there any workaround with tables without id's?

But

even if I inherit DataRecord from ContentPartRecord and modify database, I stop receiving exception but records after deleting from table appearing again.

What I'm doing wrong? And why, records appearing again?

And what is the best way of deleting a lot of data by some criteria from table.

If you suggest cascad deleting, using database, what code in the migration.cs should be in order to achieve this.

Thank You.

 

Edit: Can that be, (records not deleting) because of Orchard relation between tables (using naming rules)?

Jul 24, 2012 at 9:36 PM
Edited Jul 24, 2012 at 9:39 PM

I managed to solve my problem. Items weren't deleting, because I have many level of relations in my database. While deleting the whole branch, on some level I forgot to use

session.Flush();