does linq where call reduce calls to my database (Custom built)

Question

I have a method that gets rows from my database. It looks like this:

public static IEnumerable<Dictionary<string, object>> GetRowsIter()
{
    _resultSet.ReadFirst();
    do
    {
        var resultList = new Dictionary<string, object>();
        for (int fieldIndex = 0; fieldIndex < _resultSet.FieldCount; fieldIndex++)
        {

            resultList.Add(_resultSet.GetName(fieldIndex), 
                           _resultSet.GetValue(fieldIndex));
        }
        yield return resultList;
    } while (_resultSet.ReadRelative(1));
    yield break;
}

This is great when I want to return all the rows. But sometimes I want to return only some of the rows.

I am planning on writing my own method (GetRowsIterWhere(string column, object whereValue)), but I am wondering if I can use the linq where on my method.

I admit I don't know how it would work, becuase I am advancing the reader with a ReadRelative(1) to get to the next value. So even if it "thinks" it is skipping rows, it will not really skip them.

I am really concerned with performance here (I am currently refactoring from Linq-To-Datasets because it was way way way too slow.)

So, my question is, do I need to write my own Where type method or can I change the one above to work with the linq where method?

Answer 1

Yes you can use LINQ Where, but you'll need to build the predicate yourself. It isn't tricky. Something like (from memory; no compiler to hand):

var param = Expression.Parameter(typeof(T), "row");
var body = Expression.Equal(
               Expression.PropertyOrField(param, column),
               Expression.Constant(whereValue));
var lambda = Expression.Lambda<Func<T,bool>>(body, param);

then:

IQueryable<T> source = ...
var filtered = source.Where(lambda);

This will cause the Where to be executed at the server (for example, in TSQL), removing most of the network IO (asusming a sensible filter).