2

I've been reading that Ienumerables don't run straight away. So I'm trying to find the best way to query a list.

Below is my getAll method. Followed by my filter method. Followed by a preferred filter method (Readability).

My question is, will the 3rd method be the same as querying directly? In other words, will it, or won't it Load ALL myObjects from the DB and then filter? Or filter when getting from the DB.

   public static IEnumerable<myObject> getAll()
    {
        using (var db = Database.OpenConnectionString(blahblah))
        {
            var queryResults = db.Query("SELECT * FROM vu_myObjects");

            return queryResults .Select(x => new myObject(x.myObjectName, x.myObjectF));
        }
    }

    public static IEnumerable<myObject> getAllForFilter(String filter)
    {
        using (var db = Database.OpenConnectionString(blahblah))
        {
            var queryResults  = db.Query("SELECT * FROM vu_myObjects WHERE ObjectF = @0", filter);

            return queryResults.Select(x => new myObject(x.myObjectName, x.ObjectF));
        }
    }

   public static IEnumerable<myObject> getAllForFilter(String filter)
    { // Not checked syntax.
        return getAll().Where(x => x.ObjectF = filter);
    }

Alternatively, if you have a better suggestion, I'm all ears.

Cœur
  • 37,241
  • 25
  • 195
  • 267
Mcloving
  • 1,390
  • 1
  • 13
  • 30

2 Answers2

3

will it, or wont it Load ALL myObjects from the DB and then filter?

It won't as you haven't actually queried the database yet. IQueryable<T> has deferred exection therefore in order to actually return a result set you need to execute the query. This can be done in a couple of ways e.g. calling ToList or iterating the list.

Any filtering i.e. your Where clause, would be done DB side.

Actually, the code you have at the moment won't even work - it would throw an ObjectDisposedException because you are trying to apply a filter on a delayed query after disposing the data context. For your code to work you would need to either pass in the where clause as a parameter to your getAll method e.g.

public static IEnumerable<myObject> getAll(Expression<Func<myObject,bool>> where)
{
    using (var db = Database.OpenConnectionString(blahblah))
    {
        var queryResults = db.Query("SELECT * FROM vu_myObjects");
        if (where != null)
        {
            queryResults.Where(where);
        }
        return queryResults .Select(x => new myObject(x.myObjectName, x.myObjectF));
    }
}

public static IEnumerable<myObject> getAllForFilter(String filter)
{ // Not checked syntax.
    return getAll(x => x.ObjectF = filter);
}

Update

As you have just pointed out that you are using WebMatrix.Database none of the above applies as there is no deferred execution happening here. The Query method executes immediately and returns a result set of IEnumerable<T> so any filtering applied afterwords would be done in memory. So in terms of efficiency, the first filter method would be better as it does everything at the DB side.

You may be able to get both readability & efficiency by converting the Where clause to the actual string and appending it to your query.

James
  • 80,725
  • 18
  • 167
  • 237
  • I should probably mention the `using WebMatrix.Data;` is the `Database` object I use. Is that IQueryable? So is this true that I it has deferred execution? – Mcloving Jan 10 '13 at 11:42
  • 1
    Thanks man. Your information and ideas have helped me to produce some readable, efficient methods. Thanks. – Mcloving Jan 10 '13 at 12:05
  • On Profiling, the DB is hit at `db.Query()` so what `Database` class should I be using, that keeps easy to use API? – Mcloving Jan 11 '13 at 10:35
  • @Mcloving I am not sure what you are asking here? – James Jan 11 '13 at 11:06
  • The query triggers on the database at db.query according to the sqldb profiler. Its fine though, I took the idea of using the where clause as string, plus `object[]` as query parameters, for sqlinjection protection, so the query filters when its run. – Mcloving Jan 11 '13 at 14:57
  • "*The query triggers on the database at db.query*" - yep I pointed that in my answer. "*I took the idea of using the where clause as string*" - Could you elaborate on this? I was referring to converting the actual `Expression` object into the string equivalent which would allow you do `getAllForFilter(x => x.Property == 4)` e.g. [Converting Expression to String](http://stackoverflow.com/questions/4793981/converting-expressiont-bool-to-string). However, as your using parameterized queries (good decision btw) you would need to do something custom string parsing to extract the values. – James Jan 11 '13 at 15:23
  • i changed query to be `db.Query("SELECT * FROM vu_myObjects " + where, parameters);` with method params as where = "`WHERE x = @0`" and parameters = `new object { 0 }`. So I can specify (null,null), for all. or where string with parameters. And the `.Query` parameterises this for me. And the rest of the code maps it to myObject and returns the list. – Mcloving Jan 12 '13 at 22:04
  • 1
    @Mcloving Seems fine, I guess one small improvement would be to give the `where` & `parameters` default values of `null` in the method signature so you can call the method without having to specify null values yourself (you would need to be using at least C# 4.0 to do this). Otherwise you could just add an overloaded method. Also, I would advise doing some argument checking in that method and throwing appropriate exceptions where necessary i.e. if you have a where clause your parameter list should not be null or empty etc. – James Jan 12 '13 at 22:38
2

They're basically all the same. Until you actually interrogate the IEnumerable that's returned from the getAlLForFilter call, it'll be a lazily evaluated query. If you call .ToList() on it or access the contents, then at that point it'll make the call to the database. If you set a breakpoint on the 3rd call, you'll see that it won't contain the results of the query until you tell the debugger to evaluate the query.

levelnis
  • 7,665
  • 6
  • 37
  • 61