Using Where clause for DateTime field using a LINQ statement-CodePudding

I am trying to return records for users based on their telephone numbers as well as a restriction to the PolicyEnd Field (DateTime Format) to return only those that are greater than or equal to 2022. However, I keep on running into several errors:

&& DateTime.ParseExact(s: ti0.Outer.Inner.PolicyEnd,format: "yyy-MM-dd",provider: __InvariantCulture_0) > DateTime.Now)' could not be translated.

        var QUERY = from client in _ipacontext.Inclients
                join policy in _ipacontext.Inpolicies on client.ClientId equals policy.AccountNo
                join types in _ipacontext.InpolicyTypes on policy.PolicyType equals types.TypeId
                where client.Telephone2 == "0000000" && DateTime.ParseExact(policy.PolicyEnd, "yyy-MM-dd", CultureInfo.InvariantCulture) > 2022

I have also tried this below but in vain :

where client.Telephone2 == "000000"  &&  Convert.ToDateTime(policy.PolicyEnd).Year >=2022

An example of the Date Format is as below:

2022-08-31 00:00:00.000

Any help on other workarounds?

CodePudding user response：

Dates have no format, they're binary types in all databases (except SQLite). SQL Server has date, datetime2, datetimeoffset, time and the legacy datetime for storing dates and time-of-day. Storing dates as strings in a string field is a critical bug that must be fixed. There's no way to control what goes into a string field, which means it's quite easy for garbage or strings with the wrong format to end up in the database.

Trying to parse such strings will result in bad performance and increased blocking even if indexes are used. Indexes are built using the stored values, not function results. Trying to parse PolicyEnd and filter by a specific date would have to scan the entire table, parse the values and only then decide which values to include. It will take Shared locks on the entire table while doing so, which would block any UPDATE or DELETE calls that tried to run at the same time, even if they were outside the date range.

If the field uses a date type, the PolicyEnd property should be a DateTime. In that casefiltering to find all dates after 2022 would be just :

var fromDate=new DateTime(2023,1,1);

var query = ....
            where client.Telephone2 == "000000"  
                  && policy.PolicyEnd >=fromDate

This will result in a parameterized query that can use any indexes covering PolicyEnd to only touch policy rows whose PolicyEnd value matches the criteria.

The JOINs aren't necessary either. It's EF's job to generate the JOINs from the relations between entities. A Client should have a Policies collection. A Policy should have a PolicyType. A LINQ query that returns clients without a second phone whose policies end in the future should be :

var clients=from client in _context.Clients
            from policy in client.Policies
            where client.Telephone2 == "000000"  
                  && policy.PolicyEnd >=fromDate
            select ...;

CodePudding user response：

Since your db table column format datetime, just try to use function

var dt = new DateTime(2022,01,01);
....

 &&    EF.Functions.DateDiffYear(policy.PolicyEnd, dt) >= 0