CODEWITHSUNDEEP
c#linq-to-sqllinq-to-objects
M05Pr1mty
M05Pr1mty

Reputation: 731

Linq to SQL Repository ~theory~ - Generic but now uses Linq to Objects?

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.

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.

[Edit: To achieve this i have used the link provided by Jacob below, coincidently!]

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.

1) Having refactored the code which used classic Linq to SQL queries:

    public Billing GetBilling(int id)
    {
        var result = (
        from bil in _bicDc.Billings
        where bil.BillingId == id
        select bil).SingleOrDefault();
        return (result);
    }

it now looks like: 

    public T GetRecordWhere<T>(Expression<Func<T, bool>> predicate) where T : class
    {
        T result;
        try
        {
            result = _dataContext.GetTable<T>().Where(predicate).SingleOrDefault();
        }
        catch (Exception ex)
        {
            throw ex;
        }
        return result;
    }

and is used by the controller with a query along the lines of:

_repository.GetRecordWhere<Billing>(x => x.BillingId == 1);

which is fine, and precisely what I wanted to achieve.

...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)...

viewModel.RecentRequests = _model.GetAllRecordsWhere<Billing>(x => x.BillingId == 1)
                .Where(x => x.BillingId == Convert.ToInt32(BillingType.Submitted))
                .OrderByDescending(x => x.DateCreated).
                Take(5).ToList();

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.

And if you've made it this far, well done. Thank you, and if you have any advice it is very much appreciated!!

Update: Inclusion of GetAllRecordsWhere method as requested

    public IQueryable<T> GetAllRecordsWhere<T>(Expression<Func<T, bool>> predicate) where T : class
    {
        return _dataContext.GetTable<T>().Where(predicate);
    }

which uses:

    public IQueryable<TName> GetTable<TName>() where TName : class
    {
        return _db.GetTable<TName>().AsQueryable();
    }

Upvotes: 3

Views: 483

Answers (3)

Steven
Steven

Reputation: 172646

Here is a good article that explains how to mock out your DataContext:

Faking your LINQ provider part 1

Upvotes: 0

Coding Flow
Coding Flow

Reputation: 21881

If _model.GetAllRecordsWhere returns an IQueryable then your subsequent querying is still just building up an expression tree (which is what i think you mean by using LinqToSql), it only gets turned into SQL an executed when you enumerate the collection by iterating over it or calling ToList() or ToArray().

As an aside don't do this:

catch (Exception ex)
{
    throw ex;
}

All you are doing is swallowing the stack trace. If you want to rethrow an exception just call throw, never throw ex. If you don't do anything in your catch other than rethrow then don't catch. The nowmal pattern for this would be catch, do some logging, rethrow.

Upvotes: 1

Jakub Konecki
Jakub Konecki

Reputation: 46008

If you want to mock database context see this:

http://andrewtokeley.net/archive/2008/07/06/mocking-linq-to-sql-datacontext.aspx

Upvotes: 0

Related Questions