How to design unit of work to support bulk operati

Question

I have 2 different units of work: one based on ADO.NET, calling stored procedures mostly (uowADO) and another one using Entity Framework 6 (uowEF), added recently in order to support Oracle db, so that I don't have to rewrite all the SPs (my knowledge is limited there).

So, business layer is loading only one of them (based on configuration) when performing operations over database (but I can't use them in parallel, because uowADO does not support Oracle)

After adding the new uowEF I noticed big performance issues, of course mainly on the bulk operations.

Basically I have only Commit and Rollback methods on the current IUnitOfWork for now... very close to what this article recommends.

So, I am thinking to rework this unit of work. For example, I read about disabling dbContext.Configuration.AutoDetectChangesEnabled sometimes when bulk operations are involved, and other such optimization tips regarding EF which may help.

Unfortunately I am not sure how to design such Unit of Work to make it generic so that I could use it in all cases from BL and for both data access layers: ADO.NET and EF.

Any thoughts, recommendations, good links on this?

Answer 1

There are no clear answer. It is always the compromise between flexibility and performance. All these patterns (repository, unit of work) are good for flexibility, but not for performance as concrete implementations ussually require some tweaks to provide maximum performance and these tweaks may not (actually they will not) be compatible with the generic interface. You can adapt the interface, but it may not work with other implementations. All these ORMs are very different ant it is very hard (almost impossible) to implement generic repository/UOF interface to support all of them, especially if you have ADO (low level ORM, actually it is difficult to call it ORM) and EF (high level ORM). Setting the AutoDetectChangesEnabled to false is just a small part of what you can do with EF. To get more performance from it, you also have to implement enities in a specific way (add some properties, some attributes). If we look at Linq2Sql (another ORM), it requires compiled queries, so, forget about methods like this:

T Single(Expression<Func<T, bool>>  predicate);

in your repositories. And I'm just talking about repositories for relational databases. What about repositories for NoSql databases? What about repositories based on completely different data sources? Yes, it is possible to provide generic interface with generic logic, but it would be very slow for some data sources. If you really want to implement generic solution and get maximum perfomance, your repositories for UOF should have very specific interface like:

IEnumerable<T> GetStudentsByName(srting name)

void InsertStudents<T>(IEnumerable<T> students)

where T - business level entities which should not depend on concrete repository implementation. The repositories should be responsible for converting the T into entities acceptabe for ORM it implements access to. But keep in mind that solution will be too complex and hard supportable.

If I was you, I would choose one main ORM which fits my requirements best and would design repositories around this ORM to get maximum perfromance from it. But, I will keep the interface flexible enough to have possibility to implement at least slow (but working) access to other data sources or ORMs.

Answer 2

Unfortunately I am not sure how to design such Unit of Work to make it generic so that I could use it in all cases from BL and for both data access layers: ADO.NET and EF.

start by realizing that EF has hno way to access the database. It relies on ADO.NET for that. AT the end, it uses commands, datareaders etc. to do the low level work.

It is trivial to set things up in such a way that you can extract the connection under a EF context and then use ADO.NET yourself directly.

As EF has zero support in any way for doing efficient even low volume bulk operations - you can / have to rely on ADO.NET anyway. EF itself is a perfect anti-example from a SQL point of view on how to handle the database for larger volumes. It isuses one update / insert per row - although (even ignoring SqlBulkCopy for example) it could for most databases issue one for multiple rows as the syntax perfectly allows that.

As aked for it: Getting to the underlying connection is really trivial when you like reading documentation of samples. This is the code I use for bulk inserts. The ObjectBulkCopy class is a little more complex, but here one can see how get the database connection:

public static void BulkInsert<T>(this Repository repository, IEnumerable<T> objects) where T : class
        {
            var bulkCopy = new ObjectBulkCopy<T>();
            var connection = (SqlConnection) repository.Database.Connection;
            bulkCopy.Insert (objects, connection);
        }

Seriously trivial - it is there in any repository as a property, ready for casting.