How to simplify filtered iqueryable date linq expression?

Question

I try to filter date in variable IQueryable<MyType> itemsFiltered from DB:

itemsFiltered = itemsFiltered.Where(i => i.Dataout.Value.ToString("dd.MM.yyyy").Contains(date));

But I get an error The LINQ expression could not be translated. Either rewrite the query in a form that can be translated, or switch to client evaluation explicitly by inserting a call to either AsEnumerable(), AsAsyncEnumerable(), ToList(), or ToListAsync()

I can rewrite expression like this:

                itemsFiltered = itemsFiltered.Where(i => (
                    (
                    i.Dataout.Value.Day.ToString().Length > 1 ?
                    i.Dataout.Value.Day.ToString() :
                    "0" + i.Dataout.Value.Day.ToString()
                    ) + "." + (
                    i.Dataout.Value.Month.ToString().Length > 1 ?
                    i.Dataout.Value.Month.ToString() :
                    "0" + i.Dataout.Value.Month.ToString()
                    ) + "." +
                    i.Dataout.Value.Year.ToString()).Contains(date));

And it works, but looks awful. How to simplify this?

ADD

The problem is: when the user inputs 11, it is unknown, if it means day, month (November) or year (2011). date is string, it can be "11" or "11.11" or "01.01" or "11.01.201" and so on.

What if you write `var dateToCheck = DateTime.ParseExact(date, "dd.MM.yyyy"); itemsFiltered = itemsFiltered.Where(i => i.Dataout.Value.Day == dateToCheck);` — user1672994, Jul 22 '20 at 14:07
What is `date`? Why are you using `Contains` instead of `==`? Note `ToString` with a format parameter cannot be translated to SQL. — NetMage, Jul 22 '20 at 18:12
@user1672994 The problem is: when the user inputs 11, it is unknown, if it means day, month (November) or year (2011). — MDspb, Jul 23 '20 at 06:17
@NetMage date is string, please look to the **ADD** in start post — MDspb, Jul 23 '20 at 06:21
What LINQ are you using? Is it LINQ to SQL / EF 6.x / EF Core 2.0 / EF Core 2.1 / EF Core 3.x? — NetMage, Jul 23 '20 at 23:59
So you want `11` to match 11th of the month or November or 2011? Can I humbly suggest this is a terrible idea? **Why** do you want this feature? _If you really want it, I'd suggest using a computed column (varchar rather than date) and searching on it - since a LIKE on a varchar column makes more sense)._ — mjwills, Jul 24 '20 at 00:36
@mjwills I disagree on the horrible idea. The default search in a couple of our internal web sites is essentially a `LIKE '%x%'` across all fields, regardless of type. This is very convenient as an end user. — NetMage, Jul 24 '20 at 00:40
@NetMage Sure - but against a `date` column? I can understand with varchar or FTS - but with a date column it smells horrible to me. — mjwills, Jul 24 '20 at 00:40
@mjwills Perhaps `11` wasn't a great example (in US Format, however, `11/` isn't bad) but `2020` is great... — NetMage, Jul 24 '20 at 00:41
@NetMage 11 is the OP's example. 2020 for sure may make _more_ sense - but isn't what the OP asked for... — mjwills, Jul 24 '20 at 00:41
@mjwills I convert desktop app to web-based. In the desktop app all of filter fields are string, including date. I am not sure that changing text date search to for example datepicker would be convenient for user. Also date stored in db like datetime, but renders in UI in `dd.MM.yyyy` format. — MDspb, Jul 24 '20 at 08:38

NetMage · Answer 1 · 2020-07-25T00:18:46.967

For LINQ to EF Core, you can convert the Date property of the DateTime with the default string conversion:

itemsFiltered = itemsFiltered.Where(i => i.Dataout.Value.Date.ToString().Contains(date));

If you need the specific format so the search matches the display for the user's convenience (e.g. they might search for "11.07", then use Substring on the standard conversion (I am not sure if the default will match your locale):

itemsFiltered = itemsFiltered.Select(i => new { i, sd = i.Dataout.Value.Date.ToString() }).Where(isd => (isd.sd.Substring(8,2) + "." + isd.sd.Substring(5,2) + "." + isd.sd.Substring(0,4)).Contains(date));

How to simplify filtered iqueryable date linq expression?

1 Answers1