title: Linq to SQL Repository ~theory~ - Generic but now uses Linq to Objects?
body: <p>The project I am currently working on used Linq to SQL as an ORM data access technology. Its an MVC3 Web app. The problem I faced was primarily due to the inability to mock (for testing) the DataContext which gets autogenerated by the DBML designer.</p>
  
  <p>So to solve this issue (after much reading) I refactored the repository system which was in place - single repository with seperate and duplicated access methods for each table which ended up with something like 300 methods only 10 of which were unique - into a single repository with generic methods taking the  table and returning more generic types to the upper reaches of the application. The DataContext is now wrapped, and easily mocked.</p>
  
  <p>[Edit: To achieve this i have used the link provided by Jacob below, coincidently!]</p>
  
  <p>My question revolves more around the design I've used to get thus far and the differences I'm noticing in the structure of the app. </p>
  
  <p>1) Having refactored the code which used classic Linq to SQL queries:</p>
  
  <pre><code>    public Billing GetBilling(int id)
      {
          var result = (
          from bil in _bicDc.Billings
          where bil.BillingId == id
          select bil).SingleOrDefault();
          return (result);
      }
  </code></pre>
  
  <p>it now looks like: </p>
  
  <pre><code>    public T GetRecordWhere&lt;T&gt;(Expression&lt;Func&lt;T, bool&gt;&gt; predicate) where T : class
      {
          T result;
          try
          {
              result = _dataContext.GetTable&lt;T&gt;().Where(predicate).SingleOrDefault();
          }
          catch (Exception ex)
          {
              throw ex;
          }
          return result;
      }
  </code></pre>
  
  <p>and is used by the controller with a query along the lines of:</p>
  
  <pre><code>_repository.GetRecordWhere&lt;Billing&gt;(x =&gt; x.BillingId == 1);
  </code></pre>
  
  <p>which is fine, and precisely what I wanted to achieve.</p>
  
  <p>...however.... I'm also having to do the following to get precisely the result set i require in the controller class (the highest point of the app in essence)...</p>
  
  <pre><code>viewModel.RecentRequests = _model.GetAllRecordsWhere&lt;Billing&gt;(x =&gt; x.BillingId == 1)
                  .Where(x =&gt; x.BillingId == Convert.ToInt32(BillingType.Submitted))
                  .OrderByDescending(x =&gt; x.DateCreated).
                  Take(5).ToList();
  </code></pre>
  
  <p>This - as far as my understanding is correct - is now using Linq to Objects rather than the Linq to SQL queries I was previously? Is this okay practise? It feels wrong to me but I dont know why. Probably because the logic of the queries is in the very highest tier of the app, rather than the lowest, but... I defer to you good people for advice. One of the issues I considered was bringing the entire table into memory but I understand that using the Iqeryable return type the where clause is taken to the database and evaluated there. Thus returning only the resultset i require... i may be wrong.</p>
  
  <p>And if you've made it this far, well done. Thank you, and if you have any advice it is very much appreciated!!</p>
  
  <p><strong>Update: Inclusion of GetAllRecordsWhere method as requested</strong></p>
  
  <pre><code>    public IQueryable&lt;T&gt; GetAllRecordsWhere&lt;T&gt;(Expression&lt;Func&lt;T, bool&gt;&gt; predicate) where T : class
      {
          return _dataContext.GetTable&lt;T&gt;().Where(predicate);
      }
  </code></pre>
  
  <p>which uses:</p>
  
  <pre><code>    public IQueryable&lt;TName&gt; GetTable&lt;TName&gt;() where TName : class
      {
          return _db.GetTable&lt;TName&gt;().AsQueryable();
      }
  </code></pre>