title: MVC 3/EF/SQL Handling Connections, Disposal and Timeouts
body: <p>Currently, this is how I am handling data within my MVC 3 application. Being pretty new to both MVC 3 and the Entity Framework I am not quite sure this is the best approach to handling data within the application. In fact, the call to check UserExists below sometimes gives a SQLConnectionTimeout issue which seems to be completely random. I've tried tracing the problem through SQL profiler and it appears that the timeout occurs right after the connection is being made from EF -> SQL.</p>
  
  <p>I thought I had this solved in another question here on SO but it popped back up, so I wanted to get everyone's opinion on whether or not below is the best way to attempt data handling in my application or is there a better way that may solve the timeout issue.</p>
  
  <p>Here is a link to the other article if it helps: <a href="https://stackoverflow.com/questions/9068847/mvc-3-ef-sql-server-strange-connection-timeout-issue">MVC 3/EF/SQL Server strange connection timeout issue</a></p>
  
  <p>So to summarize my question(s): </p>
  
  <ul>
  <li>Is the code below acceptable? </li>
  <li>Should it work fine? </li>
  <li>Is there a better way? </li>
  <li>Will unnecessary connections to SQL remain open from EF? (SQL Profiler makes it look like it stays open a while even after the using statement has exited)</li>
  <li>Any idea on the timeout issue I posted in my other article?</li>
  </ul>
  
  <p><strong>Note:</strong> The repository implements IDisposable and has the dispose method listed below. It creates a new instance of the entity context in the repository constructor.</p>
  
  <p><strong>Controller</strong> (LogOn using Custom Membership Provider):</p>
  
  <pre><code>if (MembershipService.ValidateUser(model.UserName, model.Password))
  {
      User newUser = new User();                    
  
      using (AccountRepository repo = new AccountRepository())
      {
           newUser = repo.GetUser(model.UserName);
           ...
      }
  }
  </code></pre>
  
  <p><strong>Membership Provider ValidateUser:</strong></p>
  
  <pre><code>public override bool ValidateUser(string username, string password)
  {
      using (AccountRepository repo = new AccountRepository())
      {
          try
          {
              if (string.IsNullOrEmpty(password.Trim()) || string.IsNullOrEmpty(username.Trim()))
                  return false;
  
              string hash = FormsAuthentication.HashPasswordForStoringInConfigFile(password.Trim(), "md5");
  
              bool exists = false;
  
              exists = repo.UserExists(username, hash);
  
              return exists;
          }catch{
              return false;
          }
      }
  }
  </code></pre>
  
  <p><strong>Account Repository Methods for GetUser &amp; UserExists:</strong></p>
  
  <p><strong>Get User:</strong></p>
  
  <pre><code>public User GetUser(string userName)
      {
          try
          {
              return entities.Users.SingleOrDefault(user =&gt; user.UserName == userName);
          }
          catch (Exception Ex)
          {
              throw new Exception("An error occurred: " + Ex.Message);
          }           
      }
  </code></pre>
  
  <p><strong>User Exists:</strong></p>
  
  <pre><code> public bool UserExists(string userName, string userPassword)
   {
          if (userName == "" || userPassword == "")
              throw new ArgumentException(InvalidUsernamePassword);
  
          try
          {
              bool exists = (entities.Users.SingleOrDefault(u =&gt; u.UserName == userName &amp;&amp; u.Password == userPassword) != null);
              return exists;
          }
          catch (Exception Ex)
          {
              throw new Exception("An error occurred: " + Ex.Message);
          } 
      }
  </code></pre>
  
  <p><strong>Repository Snippets (Constructor, Dispose etc):</strong></p>
  
  <pre><code>    public class AccountRepository : IDisposable
      {
           private DbContext entities;
  
           public AccountRepository()
           {
              entities = new DbContext();
           }
  
           ...
  
           public void Dispose()
           {
              entities.Dispose();
           }
      }
  </code></pre>
  
  <p>Thanks everyone - I realize that this question crit's you for over 9000 with a giant wall of text!</p>