.NET C#构建MongoDB多条件包含查询并获取结果计数

Question #

I have the following variables: string SKU, string Purchase, string Slider, string Category. I need to build a MongoDB query in .NET that:

• Matches documents where the "ID" field contains the SKU value (only if SKU is not empty or null)
• Matches documents where the "URL" field contains the Purchase value (only if Purchase is not empty or null)
• Matches documents where the "Refer" field contains the Slider value (only if Slider is not empty or null)
• Matches documents where the "Category" field contains the Category variable's value (only if this variable is not empty or null)

Finally, I need to get the count of matching documents. How can I construct this query properly?

Answer #

Great question! When dealing with optional filters in the MongoDB .NET Driver, the cleanest approach is to build your filter incrementally—adding conditions only when the corresponding variable has a valid, non-empty value. Here's a step-by-step implementation:

1. Start with an empty filter #

First, initialize an empty FilterDefinition<T> (replace T with your actual document model type, like Product or whatever your collection uses):

var filter = Builders<T>.Filter.Empty;

2. Add conditional filters for each variable #

Check each variable and append the appropriate "contains" condition using regex (since we're looking for substring matches). Use the &= operator to combine filters with AND logic:

// Filter for SKU (ID field contains SKU value)
if (!string.IsNullOrEmpty(SKU))
{
    // Use "i" flag for case-insensitive match; remove it for case-sensitive
    filter &= Builders<T>.Filter.Regex("ID", new BsonRegularExpression(SKU, "i"));
}

// Filter for Purchase (URL field contains Purchase value)
if (!string.IsNullOrEmpty(Purchase))
{
    filter &= Builders<T>.Filter.Regex("URL", new BsonRegularExpression(Purchase, "i"));
}

// Filter for Slider (Refer field contains Slider value)
if (!string.IsNullOrEmpty(Slider))
{
    filter &= Builders<T>.Filter.Regex("Refer", new BsonRegularExpression(Slider, "i"));
}

// Filter for Category (Category field contains the variable value)
if (!string.IsNullOrEmpty(Category))
{
    filter &= Builders<T>.Filter.Regex("Category", new BsonRegularExpression(Category, "i"));
}

3. Execute the count query #

Use CountDocumentsAsync (for async operations) or CountDocuments (synchronous) on your collection to get the number of matching documents:

// Assuming you have an IMongoCollection<T> instance named "collection"
long matchingCount = await collection.CountDocumentsAsync(filter);

// For synchronous execution:
// long matchingCount = collection.CountDocuments(filter);

Bonus: Strongly-typed field references #

If you're using a strongly-typed document model, you can replace the string field names with lambda expressions to avoid typos and make refactoring easier:

// Example with a strongly-typed Product model
if (!string.IsNullOrEmpty(SKU))
{
    filter &= Builders<Product>.Filter.Regex(p => p.ID, new BsonRegularExpression(SKU, "i"));
}

Key Notes: #

• If you need exact matches instead of substring contains, replace Regex with Eq (e.g., Builders<T>.Filter.Eq("ID", SKU)).
• The &= operator ensures all applicable conditions are satisfied (AND logic), which aligns with your requirements.
• The "i" regex flag makes matches case-insensitive—adjust this based on whether you need case-sensitive checks.

内容的提问来源于stack exchange，提问作者Arjun K P