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

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?

Answers


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


Need Your Help

how to insert selected columns from a very large pipe delimited text/csv file to mysql using LOAD DATA INFILE

php mysql

There is a similar question already but I would like to know how to accomplish this for very large data sets.

How do you make a Django Form for a model and all of its children following a particular foreign key?

django django-forms

For example, lets say you want to create a ModelForm for Supervisor that also allows you to create or update 3 or more Underlings in the same form.