dimanche 25 décembre 2016

UnitOfWork pattern for Entity Framework but with direct SQL queries

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?

Aucun commentaire:

Enregistrer un commentaire