UnitOfWork pattern for Entity Framework but with direct SQL queries

Question

I'm trying to incorporate the Unit of Work pattern for my ASP.NET MVC project, and it is a bit different than other typical UoW design with Entity Framework.

My database has a highly normalised and pure relational structure in that it is not really EF friendly. Because of this I have created views that are mapped to entities so that I can still have all the EF and LINQ goodness when querying, but I have to use direct sql queries (e.g. Context.Database.ExecuteSqlCommand) when updating the entities.

This poses a challenge to my UoW design. As far as I know a general approach to UoW with EF is to basically call Context.SaveChanges() only when UoW.Commit() is called. This way all the tracked entity changes will be committed as a single transaction to the database at once.

However since I am using Context.Database.ExecuteSqlCommand, whenever I update an entity the transaction will happen immediately, hence losing the whole point of UoW. I'll give an example:

Traditional UoW with EF:

public void CreateOrder()
{
    var customer = new Customer();
    // this only adds the entity to the Context for tracking
    // e.g. Context.Customers.Add(customer);
    UoW.CustomerRepo.Add(customer); 

    // this too only adds the entity to the Context
    var order = new Order();
    UoW.OrderRepo.Add(order);

    // Commit. this internally calls Context.SaveChanges()
    // sending all changes to the db in a single transaction
    // Perhaps also with a TransactionScope.
    UoW.Commit(); 
}

My UoW with EF:

public void CreateOrder()
{
    var customer = new Customer();
    // this inserts a customer to the db immediately
    // e.g. Context.Database.ExecuteSqlCommand(insertSql);
    UoW.CustomerRepo.Add(customer); 

    // This too inserts an order immediately
    var order = new Order();
    UoW.OrderRepo.Add(order);

    // There is no point calling Context.SaveChanges()
    // here as all my changes are already executed with direct sql.
    UoW.Commit(); 
}

Anyone has come across similar issues? Should I just abandon UoW here and simply wrap all my repository actions in a single TransactionScope?

Take a look on this SO (the answer with 61 votes): http://stackoverflow.com/questions/815586/entity-framework-using-transactions-or-savechangesfalse-and-acceptallchanges — Klinger, Dec 26 '16 at 01:33
You can combine both EF and ExecuteSqlCommand as long as you begin a transaction before issuing the commands. And, if I am not mistaken, when EF is asked to save changes it will use an existing transaction if available. — Klinger, Dec 26 '16 at 01:36
Thanks mate that post was very helpful. I ended up ditching UoW and go with transaction scope only. — Joel Min, Dec 26 '16 at 08:20

score 1 · Accepted Answer · answered Dec 26 '16 at 01:03

1

UoW will not work with straight SQL as ADO.net queries/commands are not lazy. You need ADO.net transactions to wrap all your SQL queries. UoW is essentially a transactional pattern that wraps over your repositories to produce transaction like behaviour.

answered Dec 26 '16 at 01:03

Chirdeep Tomar

4,281
8
37
66

Thanks for the straightforward answer, I knew I could still get help from SO even during Christmas :) – Joel Min Dec 26 '16 at 08:19

UnitOfWork pattern for Entity Framework but with direct SQL queries

1 Answers1