Linq to Sql: Am I doing this right?

Question

Hey everyone,

I am working on a database access layer project and decided to use Linq to SQL for it. One of the things I wanted to do was provide an API that can take Linq Expressions as arguments to retrieve data. For instance, I wrote this API now that looks like this:

 public Result GetResults(System.Linq.Expressions.Expression<Func<Result, bool>> predicate)
    {
        Result result = db.Results.SingleOrDefault(predicate);
        return result;
    }

You can then use this API to query the database for a Result row that satisfies certain conditions, for example:

Result result = Provider.GetResults(r => r.ID == 11);

This works very well. I am able to get the one row I want based on my conditions.

Next step was taking this to be able to get multiple objects back from the database.

The way I got it to work was like this:

public List<Result> GetResults(System.Linq.Expressions.Expression<Func<Result, bool>> predicate)
    {
        List<Result> results = db.Results.Select(r => r).Where(r => r.ID == 11).ToList<Result>();
        return results;
    }

As you can see, I call a Select with r => r, this gives me back everything and then I use a Where to filter to what I need.

It works... but something tells me that I am doing it really ugly. I could be wrong, but doesn't this pull EVERYTHING out of the Results table then filters it? or does it put together the correct SQL statement that does filter at the database level?

Anyway... I would highly appreciate some guidance on how I can accomplish this task. How do I write an API that takes a Linq Expression as an argument and returns a set of objects from the database based on that expression.

Thanks!

Answer 1

The Select(r=>r) does nothing (except change from Table<T> to IQueryable<T> - but nothing useful). And I assume you intended to pass predicate to the Where?

Indeed, this doesn't pull everything out and filter it - the appropriate WHERE (TSQL) clause is generated. This is possible because of "deferred execution" and "composability" - meaning: it doesn't actually execute anything until you start iterating the data (in the ToList()) - until then you are simply shaping the query.

You can see this by doing somthing like:

db.Log = Console.Out;

and look at the TSQL. Or run a TSQL trace. To make it prettier, simplify it to:

return db.Results.Where(predicate).ToList();

Answer 2

I know you said you wanted to pass a predicate and return a List but have you considered returning an IQueryable

Then you could call:

 GetResults().Where(r => r.SomeProperty == "SomeValue")
     .OrderBy(r => r.SomeOtherProperty)
     .Skip(10) 
     .Take(10); //etc.

With your current API design you would return all records and then have to get 10, where as the above would return only the 10 you needed...

Just some thoughts...