如何在LiteDB中排除大列以优化Xamarin.Forms查询性能？

Hey there! I get where you're coming from—coming from SQL Server, it's totally natural to want to fetch only the columns you need to keep things fast. The issue with your current code is that you're loading entire documents first (even that big, unused column) before filtering and selecting fields, which is why it's running slowly. Let's fix that by using LiteDB's projection feature to grab only what you need right from the query.

Why Your Current Code Is Slow #

When you call DataConnection.GetList("Report") (I assume this returns all documents in the collection), you're pulling every single field—including that large, unneeded column—into memory. Even if you later use Select to pick just ID and Name, the damage is already done: that big column was already loaded, wasting time and resources.

The Fix: Project Fields During the Query #

LiteDB lets you specify exactly which fields to fetch when running a query, just like SQL's SELECT clause. Here's how to adjust your code to get the performance boost you need:

// Get the collection directly (use GetCollection instead of GetList to work with queries efficiently)
var reportCollection = DataConnection.GetCollection("Report");

// Run the query with filter AND projection to only fetch the fields you need
var reps4 = reportCollection
    .Find(
        // Your filter condition: only documents where ParentRecordType is null
        Query.Where("ParentRecordType", x => x.AsString == null),
        // Projection: tell LiteDB to only include these fields (1 = include, 0 = exclude)
        new BsonDocument { ["_id"] = 1, ["Name"] = 1 }
    )
    // Map the projected BsonDocument to your desired anonymous type
    .Select(x => new { ID = x["_id"], Name = x["Name"].AsString })
    .ToList();

Key Takeaways: #

• Projection First: By passing the BsonDocument as the second parameter to Find, you tell LiteDB to only load the _id (which matches your ID()) and Name fields. That big, unused column never gets loaded into memory at all.
• Filter Before Projecting: We apply the ParentRecordType filter first, so LiteDB only processes documents that match the condition, then projects just the fields we need. This is way more efficient than loading everything upfront.
• Avoid Full Collection Fetches: Unless you truly need every field and every document, don't use GetList or FindAll on large collections. Always pair queries with filters and projections to keep things snappy.

Bonus: If You Switch to Strongly-Typed Entities Later #

Just in case you move to using strongly-typed classes with LiteDB down the line, here's how you'd do the same projection for cleaner code:

// Assume you have a Report class with Id, Name, and that big unused column
var reps = reportCollection
    .Find(r => r.ParentRecordType == null)
    .Project(r => new { r.Id, r.Name })
    .ToList();

This works the same way—LiteDB will only fetch the Id and Name fields from the database, skipping the large column entirely.

Hope this gets your queries running fast again! 😊

内容的提问来源于stack exchange，提问作者Yisroel M. Olewski