Databases
StackOverflow2013
Postsdbo
POYour Thoughts: Entity Framework Data Context via a DataHelper class (centralized context and transactions)
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POYour Thoughts: Entity Framework Data Context via a DataHelper class (centralized context and transactions)
    text
    copied!<p>I want to get opinions on the code I have put together for a centralized DataContext via a DataHelper class that I have created to be re-used on projects. </p> <p><strong>NOTE</strong> - there is a ton of code here, sorry about that, but I really wanted to layout out the complete approach and uses for my ideas. I'm an not saying this is the right approach, but it works for me so far (still playing with the approach, nothing in production yet, but very similar to stuff I have built over the years) and I really want to get constructive feedback from the community on what I have built to see if it is insane, great, can be improved, etc...</p> <p>A few thoughts I put into this: </p> <ol> <li>Data Context needs to be stored in a common memory space, easily accessible</li> <li>Transactions should take the same approach</li> <li>It must be disposed of properly</li> <li>Allows for better separation of business logic for Saving and Deleting in transactions.</li> </ol> <p>Here is the code for each item:</p> <p>1 - First the data context stored in either the current HttpContext.Current.Items collection (so it only lives for the life of the page and only is fired up once at the first requested) or if the HttpContext doesn't exist uses a ThreadSlot (in which case that code most clean it up itself, like a console app using it...):</p> <pre class="lang-cs prettyprint-override"><code>public static class DataHelper { /// &lt;summary&gt; /// Current Data Context object in the HTTPContext or Current Thread /// &lt;/summary&gt; public static TemplateProjectContext Context { get { TemplateProjectContext context = null; if (HttpContext.Current == null) { LocalDataStoreSlot threadSlot = Thread.GetNamedDataSlot("DataHelper.CurrentContext"); if (Thread.GetData(threadSlot) == null) { context = new TemplateProjectContext(); Thread.SetData(threadSlot, context); } else { context = (TemplateProjectContext)Thread.GetData(threadSlot); } } else { if (HttpContext.Current.Items["DataHelper.CurrentContext"] == null) { context = new TemplateProjectContext(); HttpContext.Current.Items["DataHelper.CurrentContext"] = context; } else { context = (TemplateProjectContext)HttpContext.Current.Items["DataHelper.CurrentContext"]; } } return context; } set { if (HttpContext.Current == null) { if (value == null) { Thread.FreeNamedDataSlot("DataHelper.CurrentContext"); } else { LocalDataStoreSlot threadSlot = Thread.GetNamedDataSlot("DataHelper.CurrentContext"); Thread.SetData(threadSlot, value); } } else { if (value == null) HttpContext.Current.Items.Remove("DataHelper.CurrentContext"); else HttpContext.Current.Items["DataHelper.CurrentContext"] = value; } } } ... </code></pre> <p>2 - To support transactions, I use a similar approach, and also include helper methods to Begin, Commit and Rollback:</p> <pre class="lang-cs prettyprint-override"><code> /// &lt;summary&gt; /// Current Transaction object in the HTTPContext or Current Thread /// &lt;/summary&gt; public static DbTransaction Transaction { get { if (HttpContext.Current == null) { LocalDataStoreSlot threadSlot = Thread.GetNamedDataSlot("currentTransaction"); if (Thread.GetData(threadSlot) == null) { return null; } else { return (DbTransaction)Thread.GetData(threadSlot); } } else { if (HttpContext.Current.Items["currentTransaction"] == null) { return null; } else { return (DbTransaction)HttpContext.Current.Items["currentTransaction"]; } } } set { if (HttpContext.Current == null) { LocalDataStoreSlot threadSlot = Thread.GetNamedDataSlot("currentTransaction"); Thread.SetData(threadSlot, value); } else { HttpContext.Current.Items["currentTransaction"] = value; } } } /// &lt;summary&gt; /// Begins a transaction based on the common connection and transaction /// &lt;/summary&gt; public static void BeginTransaction() { DataHelper.Transaction = DataHelper.CreateSqlTransaction(); } /// &lt;summary&gt; /// Creates a SqlTransaction object based on the current common connection /// &lt;/summary&gt; /// &lt;returns&gt;A new SqlTransaction object for the current common connection&lt;/returns&gt; public static DbTransaction CreateSqlTransaction() { return CreateSqlTransaction(DataHelper.Context.Connection); } /// &lt;summary&gt; /// Creates a SqlTransaction object for the requested connection object /// &lt;/summary&gt; /// &lt;param name="connection"&gt;Reference to the connection object the transaction should be created for&lt;/param&gt; /// &lt;returns&gt;New transaction object for the requested connection&lt;/returns&gt; public static DbTransaction CreateSqlTransaction(DbConnection connection) { if (connection.State != ConnectionState.Open) connection.Open(); return connection.BeginTransaction(); } /// &lt;summary&gt; /// Rolls back and cleans up the current common transaction /// &lt;/summary&gt; public static void RollbackTransaction() { if (DataHelper.Transaction != null) { DataHelper.RollbackTransaction(DataHelper.Transaction); if (HttpContext.Current == null) { Thread.FreeNamedDataSlot("currentTransaction"); } else { HttpContext.Current.Items.Remove("currentTransaction"); } } } /// &lt;summary&gt; /// Rolls back and disposes of the requested transaction /// &lt;/summary&gt; /// &lt;param name="transaction"&gt;The transaction to rollback&lt;/param&gt; public static void RollbackTransaction(DbTransaction transaction) { transaction.Rollback(); transaction.Dispose(); } /// &lt;summary&gt; /// Commits and cleans up the current common transaction /// &lt;/summary&gt; public static void CommitTransaction() { if (DataHelper.Transaction != null) { DataHelper.CommitTransaction(DataHelper.Transaction); if (HttpContext.Current == null) { Thread.FreeNamedDataSlot("currentTransaction"); } else { HttpContext.Current.Items.Remove("currentTransaction"); } } } /// &lt;summary&gt; /// Commits and disposes of the requested transaction /// &lt;/summary&gt; /// &lt;param name="transaction"&gt;The transaction to commit&lt;/param&gt; public static void CommitTransaction(DbTransaction transaction) { transaction.Commit(); transaction.Dispose(); } </code></pre> <p>3 - Clean and easy Disposal</p> <pre class="lang-cs prettyprint-override"><code> /// &lt;summary&gt; /// Cleans up the currently active connection /// &lt;/summary&gt; public static void Dispose() { if (HttpContext.Current == null) { LocalDataStoreSlot threadSlot = Thread.GetNamedDataSlot("DataHelper.CurrentContext"); if (Thread.GetData(threadSlot) != null) { DbTransaction transaction = DataHelper.Transaction; if (transaction != null) { DataHelper.CommitTransaction(transaction); Thread.FreeNamedDataSlot("currentTransaction"); } ((TemplateProjectContext)Thread.GetData(threadSlot)).Dispose(); Thread.FreeNamedDataSlot("DataHelper.CurrentContext"); } } else { if (HttpContext.Current.Items["DataHelper.CurrentContext"] != null) { DbTransaction transaction = DataHelper.Transaction; if (transaction != null) { DataHelper.CommitTransaction(transaction); HttpContext.Current.Items.Remove("currentTransaction"); } ((TemplateProjectContext)HttpContext.Current.Items["DataHelper.CurrentContext"]).Dispose(); HttpContext.Current.Items.Remove("DataHelper.CurrentContext"); } } } </code></pre> <p>3b - I'm building this in MVC, so I have a "base" Controller class that all my controllers inherit from - this way the Context only lives from when first accessed on a request, and until the page is disposed, that way its not too "long running"</p> <pre class="lang-cs prettyprint-override"><code>using System.Web.Mvc; using Core.ClassLibrary; using TemplateProject.Business; using TemplateProject.ClassLibrary; namespace TemplateProject.Web.Mvc { public class SiteController : Controller { protected override void Dispose(bool disposing) { DataHelper.Dispose(); base.Dispose(disposing); } } } </code></pre> <p>4 - So I am big on business classes, separation of concerns, reusable code, all that wonderful stuff. I have an approach that I call "Entity Generic" that can be applied to any entity in my system - for example, Addresses and Phones</p> <p>A Customer can have 1 or more of each, along with a Store, Person, or anything really - so why add street, city, state, etc to every thing that needs it when you can just build an Address entity, that takes a Foreign Type and Key (what I call EntityType and EntityId) - then you have a re-usable business object, supporting UI control, etc - so you build it once and re-use it everywhere.</p> <p>This is where the centralized approach I am pushing for here really comes in handy and I think makes the code much cleaner than having to pass the current data context/transaction into every method.</p> <p>Take for example that you have a Page for a customer, the Model includes the Customer data, Contact, Address and a few Phone Numbers (main, fax, or cell, whatever)</p> <p>When getting a Customer Edit Model for the page, here is a bit of the code I have put together (see how I use the DataHelper.Context in the LINQ):</p> <pre class="lang-cs prettyprint-override"><code> public static CustomerEditModel FetchEditModel(int customerId) { if (customerId == 0) { CustomerEditModel model = new CustomerEditModel(); model.MainContact = new CustomerContactEditModel(); model.MainAddress = new AddressEditModel(); model.ShippingAddress = new AddressEditModel(); model.Phone = new PhoneEditModel(); model.Cell = new PhoneEditModel(); model.Fax = new PhoneEditModel(); return model; } else { var output = (from c in DataHelper.Context.Customers where c.CustomerId == customerId select new CustomerEditModel { CustomerId = c.CustomerId, CompanyName = c.CompanyName }).SingleOrDefault(); if (output != null) { output.MainContact = CustomerContact.FetchEditModelByPrimary(customerId) ?? new CustomerContactEditModel(); output.MainAddress = Address.FetchEditModelByType(BusinessEntityTypes.Customer, customerId, AddressTypes.Main) ?? new AddressEditModel(); output.ShippingAddress = Address.FetchEditModelByType(BusinessEntityTypes.Customer, customerId, AddressTypes.Shipping) ?? new AddressEditModel(); output.Phone = Phone.FetchEditModelByType(BusinessEntityTypes.Customer, customerId, PhoneTypes.Main) ?? new PhoneEditModel(); output.Cell = Phone.FetchEditModelByType(BusinessEntityTypes.Customer, customerId, PhoneTypes.Cell) ?? new PhoneEditModel(); output.Fax = Phone.FetchEditModelByType(BusinessEntityTypes.Customer, customerId, PhoneTypes.Fax) ?? new PhoneEditModel(); } return output; } } </code></pre> <p>And here is a sample of the the phone returning the Edit model to be used:</p> <pre class="lang-cs prettyprint-override"><code> public static PhoneEditModel FetchEditModelByType(byte entityType, int entityId, byte phoneType) { return (from p in DataHelper.Context.Phones where p.EntityType == entityType &amp;&amp; p.EntityId == entityId &amp;&amp; p.PhoneType == phoneType select new PhoneEditModel { PhoneId = p.PhoneId, PhoneNumber = p.PhoneNumber, Extension = p.Extension }).FirstOrDefault(); } </code></pre> <p>Now the page has posted back and this all needs to be save, so the Save method in my control just lets the business object handle this all:</p> <pre class="lang-cs prettyprint-override"><code> [Authorize(Roles = SiteRoles.SiteAdministrator + ", " + SiteRoles.Customers_Edit)] [HttpPost] public ActionResult Create(CustomerEditModel customer) { return CreateOrEdit(customer); } [Authorize(Roles = SiteRoles.SiteAdministrator + ", " + SiteRoles.Customers_Edit)] [HttpPost] public ActionResult Edit(CustomerEditModel customer) { return CreateOrEdit(customer); } private ActionResult CreateOrEdit(CustomerEditModel customer) { if (ModelState.IsValid) { SaveResult result = Customer.SaveEditModel(customer); if (result.Success) { return RedirectToAction("Index"); } else { foreach (KeyValuePair&lt;string, string&gt; error in result.ErrorMessages) ModelState.AddModelError(error.Key, error.Value); } } return View(customer); } </code></pre> <p>And inside the Customer business object - it handles the transaction centrally and lets the Contact, Address and Phone business classes do their thing and really not worry about the transaction:</p> <pre class="lang-cs prettyprint-override"><code> public static SaveResult SaveEditModel(CustomerEditModel model) { SaveResult output = new SaveResult(); DataHelper.BeginTransaction(); try { Customer customer = null; if (model.CustomerId == 0) customer = new Customer(); else customer = DataHelper.Context.Customers.Single(c =&gt; c.CustomerId == model.CustomerId); if (customer == null) { output.Success = false; output.ErrorMessages.Add("CustomerNotFound", "Unable to find the requested Customer record to update"); } else { customer.CompanyName = model.CompanyName; if (model.CustomerId == 0) { customer.SiteGroup = CoreSession.CoreSettings.CurrentSiteGroup; customer.CreatedDate = DateTime.Now; customer.CreatedBy = SiteLogin.Session.ActiveUser; DataHelper.Context.Customers.AddObject(customer); } else { customer.ModifiedDate = DateTime.Now; customer.ModifiedBy = SiteLogin.Session.ActiveUser; } DataHelper.Context.SaveChanges(); SaveResult result = Address.SaveEditModel(model.MainAddress, BusinessEntityTypes.Customer, customer.CustomerId, AddressTypes.Main, false); if (!result.Success) { output.Success = false; output.ErrorMessages.Concat(result.ErrorMessages); } result = Address.SaveEditModel(model.ShippingAddress, BusinessEntityTypes.Customer, customer.CustomerId, AddressTypes.Shipping, false); if (!result.Success) { output.Success = false; output.ErrorMessages.Concat(result.ErrorMessages); } result = Phone.SaveEditModel(model.Phone, BusinessEntityTypes.Customer, customer.CustomerId, PhoneTypes.Main, false); if (!result.Success) { output.Success = false; output.ErrorMessages.Concat(result.ErrorMessages); } result = Phone.SaveEditModel(model.Fax, BusinessEntityTypes.Customer, customer.CustomerId, PhoneTypes.Fax, false); if (!result.Success) { output.Success = false; output.ErrorMessages.Concat(result.ErrorMessages); } result = Phone.SaveEditModel(model.Cell, BusinessEntityTypes.Customer, customer.CustomerId, PhoneTypes.Cell, false); if (!result.Success) { output.Success = false; output.ErrorMessages.Concat(result.ErrorMessages); } result = CustomerContact.SaveEditModel(model.MainContact, customer.CustomerId, false); if (!result.Success) { output.Success = false; output.ErrorMessages.Concat(result.ErrorMessages); } if (output.Success) { DataHelper.Context.SaveChanges(); DataHelper.CommitTransaction(); } else { DataHelper.RollbackTransaction(); } } } catch (Exception exp) { DataHelper.RollbackTransaction(); ErrorHandler.Handle(exp, true); output.Success = false; output.ErrorMessages.Add(exp.GetType().ToString(), exp.Message); output.Exceptions.Add(exp); } return output; } </code></pre> <p>Notice how each Address, Phone, Etc is handled by its own business class, here is the Phone's save method - notice how it doesn't actually do the save here unless you tell it to (save is handled in the Customer's method so save is called just once for the context)</p> <pre class="lang-cs prettyprint-override"><code> public static SaveResult SaveEditModel(PhoneEditModel model, byte entityType, int entityId, byte phoneType, bool saveChanges) { SaveResult output = new SaveResult(); try { if (model != null) { Phone phone = null; if (model.PhoneId != 0) { phone = DataHelper.Context.Phones.Single(x =&gt; x.PhoneId == model.PhoneId); if (phone == null) { output.Success = false; output.ErrorMessages.Add("PhoneNotFound", "Unable to find the requested Phone record to update"); } } if (string.IsNullOrEmpty(model.PhoneNumber)) { if (model.PhoneId != 0 &amp;&amp; phone != null) { DataHelper.Context.Phones.DeleteObject(phone); if (saveChanges) DataHelper.Context.SaveChanges(); } } else { if (model.PhoneId == 0) phone = new Phone(); if (phone != null) { phone.EntityType = entityType; phone.EntityId = entityId; phone.PhoneType = phoneType; phone.PhoneNumber = model.PhoneNumber; phone.Extension = model.Extension; if (model.PhoneId == 0) { phone.CreatedDate = DateTime.Now; phone.CreatedBy = SiteLogin.Session.ActiveUser; DataHelper.Context.Phones.AddObject(phone); } else { phone.ModifiedDate = DateTime.Now; phone.ModifiedBy = SiteLogin.Session.ActiveUser; } if (saveChanges) DataHelper.Context.SaveChanges(); } } } } catch (Exception exp) { ErrorHandler.Handle(exp, true); output.Success = false; output.ErrorMessages.Add(exp.GetType().ToString(), exp.Message); output.Exceptions.Add(exp); } return output; } </code></pre> <p>FYI - SaveResult is just a little container class that I used to get detailed information back if a save fails:</p> <pre class="lang-cs prettyprint-override"><code>public class SaveResult { private bool _success = true; public bool Success { get { return _success; } set { _success = value; } } private Dictionary&lt;string, string&gt; _errorMessages = new Dictionary&lt;string, string&gt;(); public Dictionary&lt;string, string&gt; ErrorMessages { get { return _errorMessages; } set { _errorMessages = value; } } private List&lt;Exception&gt; _exceptions = new List&lt;Exception&gt;(); public List&lt;Exception&gt; Exceptions { get { return _exceptions; } set { _exceptions = value; } } } </code></pre> <p>The other piece to this is the re-usable UI code for the Phone, Address, etc - which handles all the validation, etc in just one location too.</p> <p>So, let your thoughts flow and thanks for taking the time to read/review this huge post!</p>
 

Querying!

 
Guidance
An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload