casting string to datetime

[dr130873]

Hi there, Can someone please help . We are trying to use linqkit to cast a string to date but the sql that is being generated does not contain the cast/convert statement.

we tried following the solution posted in https://github.com/scottksmith95/LINQKit/issues/13 but I think it only works for type casting but there is no direct casting from string to date

sample 1:

                                        condition.And(
                                        p => p.JobAttributes.Any()
                                            && p.JobAttributes.Any(a => a.Type == "Date" &&
                                            a.Name.Contains(attrName) &&
                                            DateTime.Parse(a.Value) > DateTime.Parse(value)))

the sql statement generated only contains up to the condition of the Name

Sample 2:

created a static function in the context

public static DateTime? StringToDate(string value)
        {
            if (DateTime.TryParse(value, out var outDateTime))
            {
                return outDateTime;
            }
            return null;
        }

Expression<Func<string, DateTime?>> expressionToDate = attr => Context.StringToDate(attr);
                   condition.And(
                   p => p.JobAttributes.Any()  && 
                            p.JobAttributes.Any(a => a.Type == "Date" &&
                                            a.Name.Contains(attrName) &&
                                            expressionToDate.(a.Value) > Context.StringToDate(value)))

this one works using the inMemoryProvider but when testing this against a database the sql statement also does not include the date conversion and comparison

please advise, how can we get the sql to generate the string to date conversion

thanks

[Thorium]

Hi, just a guess, would (DateTime)a.Value work better than DateTime.Parse(a.Value) ?

[btihanyi]

@Thorium That would be a good solution, I just tested it with EF Core 3+. Although you'd need to cast the string value with this little trick: (DateTime) (object) a.Value, because C# can't (and shouldn't) cast directly a string object to DateTime.

Before EF Core version 3.0, client-side evaluation was enabled for all queries. This means the generated SQL queried the table without the untranslatable predicate, then on the client side the CLR could handle the DateTime.Parse() method. Fortunately, this behavior changed with the 3.0 version, and now it would throw and exception (as it should have always done).

Further discussion about the issue: the sample works well in the In-Memory Database Provider, because it's not a real relational SQL database provider, just a simple C# mock for quick testing. Here is a page that warns you about testing with the In-Memory Provider.

[StefH]

@btihanyi and @Thorium Thanks for researching.

@dr130873 Can this issue be closed?

[StefH]

closing...