How to convert string to DateTime in C# EF Core query

Question

var res = Context.Exampletable
                 .Where(s => s.CompanyId == CompanyId &&  
                             Convert.ToDateTime(s.TextDate) >= DateTime.Now)
                 .Select(x => new Exampletable { TextDate = x.TextDate })
                 .FirstOrDefault();

This is the Linq for one of my problem statements. I want to fetch records future date records from current date & timestamp, so I am converting and comparing it to Datetime but I get this error:

The LINQ expression 'DbSet
.Where(a => Convert.ToDateTime(a.TextDate) > Convert.ToDateTime(DateTime.Now))' > 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()

Note: in Postgresql DB TextDate column has string datatype and contains values like '4/1/2020 10:00 AM'.

Please provide a solution for this.

Answer 1

If you really can't change the underlying column type, then instead of unsupported Convert.ToDateTime use C# cast operator which maps to PostgreSQL CAST operator:

(DateTime)(object)s.TextDate >= DateTime.Now

Note that the "intermediate" cast to object is needed just to make the C# compiler happy.

---

P.S. I really have no idea why some methods of Convert like ToInt32 are supported, and other like ToDateTime are not. I guess just yet another EF Core inconsistency.