Using LinqToSql without load base in memory

Question

I have one problem. If i'm using LinqToSql, my program load my database in memory. little example:

//pageNumber = 1; pageSize = 100;                
                var result =
                    (
                    from a in db.Stats.AsEnumerable()
                    where (DictionaryFilter(a, sourceDictionary) && DateFilter(a, beginTime, endTime) && ErrorFilter(a, WarnLevel))
                    select a
                    );
                var size = result.Count(); // size = 1007
                var resultList = result.Skip((pageNumber-1)*pageSize).Take(pageSize).ToList();
                return resultList;

DictionaryFilter, DateFilter and ErrorFilter are functions that filter my datebase. after this my program use ~250Mb of Ram. if i dont use:

var size = result.Count(); 

My program use ~120MB Ram. Before use this code, my program use ~35MB Ram.

How can I use count and take functions not loading all my datebase in memory?

static bool DateFilter(Stat table, DateTime begin, DateTime end)
{

    if ((table.RecordTime >= begin.ToFileTime()) && (table.RecordTime <= end.ToFileTime()))
    {
        return true;
    }
    return false;
}
static bool ErrorFilter(Stat table, bool[] WarnLevel)
{
    if (WarnLevel[table.WarnLevel]) return true;
    else return false;
}
static bool DictionaryFilter(Stat table, Dictionary<GetSourcesNameResult, bool> sourceDictionary)
{
    foreach (var word in sourceDictionary)
    {
        if (table.SourceName == word.Key.SourceName)
        {
            return word.Value;
        }
    }
    //
    return false;
}

Answer 1

Simple: don't use .AsEnumerable(). That means "switch to LINQ-to-Objects". Before that, db.Stats was IQueryable<T>, which is a composable API, and would do what you expect.

That, however, means that you can't use C# methods like DictionaryFilter and DateFilter, and must instead compose things in terms of the Expression API. If you can illustrate what they do I can probably advise further.

With your edit, the filtering can be tweaked, for example:

static IQueryable<Stat> ErrorFilter(IQueryable<Stat> source, bool[] WarnLevel) {
    // extract the enabled indices (match to values)
    int[] levels = WarnLevel.Select((val, index) => new { val, index })
                            .Where(pair => pair.val)
                            .Select(pair => pair.index).ToArray();

    switch(levels.Length)
    {
        case 0:
            return source.Where(x => false);
        case 1:
            int level = levels[0];
            return source.Where(x => x.WarnLevel == level);
        case 2:
            int level0 = levels[0], level1 = levels[1];
            return source.Where(
                  x => x.WarnLevel == level0 || x.WarnLevel == level1);
        default:
            return source.Where(x => levels.Contains(x.WarnLevel));
    }
}

the date filter is simpler:

static IQueryable<Stat> DateFilter(IQueryable<Stat> source,
      DateTime begin, DateTime end)
{
    var from = begin.ToFileTime(), to = end.ToFileTime();
    return source.Where(table => table.RecordTime >= from
        && table.RecordTime <= to);
}

and the dictionary is a bit like the levels:

static IQueryable<Stat> DictionaryFilter(IQueryable<Stat> source,
    Dictionary<GetSourcesNameResult, bool> sourceDictionary)
{
    var words = (from word in sourceDictionary
                 where word.Value
                 select word.Key.SourceName).ToArray();

    switch (words.Length)
    {
        case 0:
            return source.Where(x => false);
        case 1:
            string word = words[0];
            return source.Where(x => x.SourceName == word);
        case 2:
            string word0 = words[0], word1 = words[1];
            return source.Where(
                  x => x.SourceName == word0 || x.SourceName == word1);
        default:
            return source.Where(x => words.Contains(x.SourceName));
    }
}

and:

IQueryable<Stat> result = db.Stats;
result = ErrorFilter(result, WarnLevel);
result = DateFiter(result, beginTime, endTime);
result = DictionaryFilter(result, sourceDictionary);
// etc - note we're *composing* a filter here

var size = result.Count(); // size = 1007
var resultList = result.Skip((pageNumber-1)*pageSize).Take(pageSize).ToList();
return resultList;

The point is we're now using IQueryable<T> and Expression exclusively.

Answer 2

The following SO question might explain things: Understanding .AsEnumerable in Linq To Sql

.AsEnumerable() loads the entire table.