Which ORM supports multiple row updates and deletes

Question

I have a query that deletes all rows marked for deletion. The table has a column named Fixed . This is a data type of type boolean, if it is true that the row should be deleted along with all related rows in different tables.

If the article line is marked for deletion, then comments on the article and votes should also be deleted. Which ORM can handle this effectively?

Edit

I need this for C # .NET

Answer 1

DataObjects.Net offers an intermediate solution:

• Currently, it cannot perform server-side deletion of entities selected by request. It will be implemented someday, but so far there is one more solution.
• On the other hand, the so-called generalized grouping is supported: requests that are sent are sent in batches at the same time up to 25 points, when possible. “Maybe” means “the query result is not needed right now.” This is almost always correct to create, update, and delete. Since such queries always lead to one (or several, if there is inheritance) search operations, they are quite cheap. If they are sent in packages, SQL Server can cache plans for the entire package, and not just for individual queries.

So, this is very fast, although not ideal:

• Now DO4 does not use IN (...) to optimize such exceptions.
• So far it does not support asynchronous batch execution. When this is done (I hope it will be done in a month or so), its speed on CUD (a subset of CRUD) will be almost the same as that of SqlBulkCopy (~ = 1.5 ... 2 times faster than now).

So, in case volume removal of DO is as follows:

var customersToRemove = from customer in Query<Customer>.All where customer.IsDeleted select customer; foreach (customer in customersToRemove) customer.Remove(); // This will be automatically batched 

I can name the advantage of this approach: any of these objects will be able to respond to deletion; Session subscribers will be notified of each sharing. Therefore, any common logic associated with deletions will work as expected. This is not possible if such an operation is performed on the server.

The code for soft deletion should look like this:

 var customersToRemove = from customer in Query<Customer>.All where ... select customer; foreach (customer in customersToRemove) customer.IsRemoved = true; // This will be automatically batched 

Obviously, this approach is slower than mass server-side updates. According to our estimates, what we have now is about 5 times slower than true server-side deletion in the worst case (table [bigint Id, bigint Value], cluster primary index, other indexes); in real cases (more columns, more indexes, more data) it should provide comparable performance right now (i.e. be 2-3 times slower). Asynchronous batch execution will improve this further.

Btw, we split tests for bulk CUD operations with entities for various ORM structures on ORMBattle.NET . Note that the tests there do not use bulk updates on the server side (in fact, such a test will check the database performance, not ORM); instead, they check to see if ORM can optimize this. In any case, the information provided there + test code may be useful if you are evaluating several ORM tools.

Answer 2

NHibernate supports HQL (Object Oriented H ibernate Q uery L anguage) updates and deletes.

There are several examples in this blog post from Fabio Maulo and this blog post from Ayende Rahien .

It probably looks like this:

 using (var session = OpenSession()) using (var tx = s.BeginTransaction()) { session .CreateQuery("delete from Whatever where IsDelete = true") .ExecuteUpdate(); tx.Commit(); } 

Note: this is not SQL. This is HQL containing class names and property names, and it converts (almost) any database.

Answer 3

As a rule, if you already use the IsDeleted flag paradigm, the elements are usually ignored by the application object model, and this is efficient and reliable because it does not require referential integrity checking (without a cascade) and there is no data permanently destroyed.

If you want IsDeleted lines to be cleaned on a regular basis, it is much more efficient to schedule them as batch jobs in the DBMS using native SQL if you delete things in the correct order so that referential integrity is not compromised. If you do not apply referential integrity at the DB level, then the order does not matter.

Even with strong referential integrity and limitations in all projects of my database over the years, I never used cascaded RI - it was never desirable in my projects.

Answer 4

• Which ORMs support “criteria” based on deletion ... Both of the ones I've worked with (Propel, Doctrine). I would think that almost everyone does it if they are not at an early stage of development, but in their rather simple thing. But what language do you work in?

• As for the removal cascade, this is best implemented at the database level using foreign keys. Most DBMSs support this. If you use something that does not support ORM, if support is not available. But my advice would be to simply use an RDBMS that supports it. Ultimately, it will be less headaches.

Answer 5

I am using LLBLgen, which can perform cascading deletes. You might want to try, this is very good. Example. Remove all users in username [] from all roles in role names []

 string[] usernames; string[] rolenames; UserRoleCollection userRoles = new UserRoleCollection (); PredicateExpression filter = new PredicateExpression(); filter.Add(new FieldCompareRangePredicate(UserFields.logincode, usernames)); filter.AddWithAnd(new FieldCompareRangePredicate(RoleFields.Name, rolenames)); userRoles.DeleteMulti(filter) 

Answer 6

Most ORMs allow you to either give SQL prompts or execute SQL within them.

For example, you can use the ExecuteQuery method in DLINQ to do what you want. Below is a short tutorial on using custom sql with DLINQ.

http://weblogs.asp.net/scottgu/archive/2007/08/27/linq-to-sql-part-8-executing-custom-sql-expressions.aspx

I would expect the Entity Framework to allow this as well, but I have never used it, but you can learn it.

Basically, find an ORM that has the necessary functions, and then you can ask how to make this query in the selected ORM. I think that choosing an ORM for this function is risky, as there are many other factors that should go into the selection.