Why does Where() generate a lot of GC?

[CodingOctocat]

I've found that whenever the Where() method is called, even with the simplest of conditions, it will generate a lot of GC.

My data size is about 15,000 rows.

var records = col.Query().Where(x=> x.SomeStringProperty == "");
var results = records.Limit(10).ToList();

Where():

Without Where():

[CodingOctocat]

I'm already using Ef Core/Sqlite and it performs well, I find LiteDb using ToList, Count performs poorly and generates a lot of GC activity, I don't think there's anything wrong with my code, it's just a simple query and count.

[JKamsker]

Have you tried creating indexes?

[CodingOctocat]

Yes, I remember creating the index, here is my git history code snippet:

By the way, calling EnsureIndex also generates a lot of GC and is slow.

if (!String.IsNullOrWhiteSpace(Model.ReportNo))
{
    LiteDbHelper.Handle(col => col.EnsureIndex(x => x.ReportNumbers));
    records = records.Where(x => x.ReportNumbers.Any(x => x.Contains(Model.ReportNo, StringComparison.OrdinalIgnoreCase)));
}

QueryResponse = new(records.Limit(10).ToList(), 1, 1, records.Count());

public static T Handle<T>(Func<ILiteCollection<Model>, T> func, string fileName = "mydb.db", string tableName = "mytable")
{
    IsDbBusy = true;

    using var db = new LiteDatabase($"Filename={fileName}; Connection=Shared;");
    var col = db.GetCollection<Model>(tableName);

    var result = func(col);

    IsDbBusy = false;

    return result;
}