Use new DateTime in LINQ Queries Where Clause

Question

I have a database here in which, for reasons I cannot understand, the year and the month for the validity of an entry are stored in TWO separate fields...

YearPart=2023
MonthPart=2

Now I need to create a LINQ query to retrieve data based on a DateTime input...

In LINQ Pad such things are possible:

var data2 = data.Where(t => new DateTime(t.DtmZlpServiceIBS.YearPart, t.DtmZlpServiceIBS.MonthPart, 1) <= fromDate);

Does anyone have any idea how I can implement this?

Answer 1

This should be translatable:

var fromYear = fromDate.Year;
var fromMonth = fromDate.Month;

var data2 = data.Where(t =>
    t.DtmZlpServiceIBS.YearPart < fromYear ||
    (t.DtmZlpServiceIBS.YearPart == fromYear && t.DtmZlpServiceIBS.MonthPart <= fromMonth));

Answer 2

Rather than constructing a DateTime from the stored values, which would break the ability to use any indexes those columns might have and force the database to build this new value for every row in the table, instead deconstruct the DateTime input components:

var data2 = data.Where(t => (t.DtmZlpServiceIBS.YearPart == fromDate.Year && t.DtmZlpServiceIBS.MonthPart <= fromDate.Month) || t.DtmZlpServiceIBS.YearPart < fromDate.Year)

I know the expression seems a lot more complicated, but it really will perform much better, and it should be usable with EF as well.