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?

Which ORM do you use? EF6, EF Core, other? And which version? — Svyatoslav Danyliv
– Svyatoslav Danyliv, Commented Mar 13, 2024 at 16:59

Johnathan Barclay · Accepted Answer · 2024-03-13 17:01:36Z

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));

answered Mar 13, 2024 at 17:01

Johnathan Barclay

20.5k2 gold badges27 silver badges40 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Joel Coehoorn · Accepted Answer · 2024-03-13 17:28:28Z

1

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.

answered Mar 13, 2024 at 17:28

Joel Coehoorn

418k114 gold badges582 silver badges820 bronze badges

Collectives™ on Stack Overflow

Use new DateTime in LINQ Queries Where Clause

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related