Linq to SQL优化：如何根据日期数组的年月筛选数据？

Great question! Your original code works for a fixed 3-element dates array, but as you pointed out, it’s redundant and fails to handle dynamic array lengths. Let’s fix that with a cleaner, scalable approach:

Core Idea #

Instead of hardcoding individual month/year variables, we’ll convert the dates array into a collection of year-month pairs, then check if each sh entry’s Report_Date matches any of these pairs. This adapts automatically to any length of dates.

Option 1: Use Tuples with Contains (EF Core Friendly) #

This approach generates efficient SQL IN clauses (for EF Core) and keeps your code concise:

// First, convert dates to a list of (Year, Month) tuples
var targetYearMonths = dates.Select(d => (d.Year, d.Month)).ToList();

// Early exit if there are no dates to filter by (avoids unnecessary DB call)
if (!targetYearMonths.Any())
{
    return Enumerable.Empty<YourEntityType>(); // Replace with your actual entity type
}

// Filter the context using the tuple collection
var segments = context.sh
    .Where(sh => targetYearMonths.Contains((sh.Report_Date.Year, sh.Report_Date.Month)))
    .ToList(); // Add other LINQ operations (like Select) as needed

Option 2: Use Any for Explicit Matching #

If you prefer a more readable inline check (EF Core also supports this conversion to SQL):

var segments = context.sh
    .Where(sh => dates.Any(d => d.Year == sh.Report_Date.Year && d.Month == sh.Report_Date.Month))
    .ToList();

Why This Is Better #

• No redundancy: No need to declare individual m0, y0, etc. variables
• Dynamic scalability: Works for any number of dates in the array (1, 5, 100, etc.)
• Maintainable: Easier to read and modify later
• EF Core compatibility: Both approaches translate to valid SQL (no client-side filtering unless you explicitly force it)

Edge Case Handling #

Don’t forget to handle empty dates arrays—without the early exit, you might end up fetching all records from the database (which is rarely intended). The early exit in Option 1 prevents that.

内容的提问来源于stack exchange，提问作者yantrab