Linq to SQL filter DateTime field by Year/Month

Question

I have Statistic table with many fields and one of them is DateTime. I need to find all records which are for current Year and Month. I am using query below but not sure is this optimal solution.

Statistics.Count(p => p.DateStamp.Year == DateTime.UtcNow.Year && p.DateStamp.Month == DateTime.UtcNow.Month);

I don't think you can do more from Linq, but look at this answer how you can improve this query.. if really needed http://stackoverflow.com/a/9524353/782754 — Adrian Iftode, Mar 05 '12 at 09:36

score 0 · Accepted Answer · answered Mar 05 '12 at 10:56

You could use SqlMethods.DateDiffMonth:

Counts the number of month boundaries between two non-nullable dates. Corresponds to the SQL Server DATEDIFF function; using month to specify the type of time boundary crossed.

so your query will be like this:

Statistics.Count(p => 
    SqlMethods.DateDiffMonth(DateTime.UtcNow, p.DateStamp) == 0
);

Linq to SQL filter DateTime field by Year/Month

1 Answers1