CODEWITHSUNDEEP
c#linq
lyon
lyon

Reputation: 13

How to compare a string column(as DateTime) in LINQ?

I have a database with a ValidDate field - it's a string(we made a mistake, it should be a datetime, but we can't modify the database now.)

and now I want to compare this filed with a parameter(validDateStart) from the website:

priceList = priceList.Where(p => Convert.ToDateTime(p.ValidDate) >= Convert.ToDateTime(validDateStart));
var list = initPriceList.ToList();

But I get an error: The method ToDateTime is not implemented.

Can anybody give me some help? Thanks!

Upvotes: 1

Views: 7703

Answers (2)

Giovanny Farto M.
Giovanny Farto M.

Reputation: 1598

If all the records in your database always start with year, month and day (for example: the date format is yyyy-MM-dd HH:mm:ss or yyyy/MM/dd or yyyyMMdd) no matter if it has separators or not. The thing is that the values should has a format where it starts with year, month and day.

You can do the following:

1: Convert your filter value (website) to the same format as you have in your database:

// DateTime format in database: yyyy-MM-dd HH:mm:ss:ffffff
var from = filtro.CreationDateFrom?.ToString("yyyy-MM-dd");
var to = filtro.CreationDateTo?.AddDays(1).ToString("yyyy-MM-dd");

2: And write your query like this (using CompareTo method):

var query = (from x in ctx.TskTaskQueues
             where x.CreationDatetime.CompareTo(from) >= 0 
             && x.CreationDatetime.CompareTo(to) <= 0
             select x);

It worked for me! I'm not using LinqToEntities but I'm using LinqConnect (for Oracle) that is similar to LinqEntities.

If you use a format like this dd-MM-yyyy, it probably will not work.

Upvotes: 0

BrokenGlass
BrokenGlass

Reputation: 160992

This is not supported in Linq to Entities (nor Linq to SQL to my knowledge). Remember that your query is executed on the database - where there is simply no equivalent for Convert.ToDateTime.

Any string parsing in your query would really just be a workaround - as a real solution make those columns not strings but datetime in the database and you would not have this problem in the first place.

A hacky workaround would be materializing all rows (you can use AsEnumerable() for that), then doing the parsing - this will have bad performance though but might work good enough if there are few rows:

var startDate = DateTime.Parse(validDateStart);
var list = priceList.AsEnumerable()
                    .Where(p => DateTime.Parse(p.ValidDate) >= startDate);
                    .ToList();

Edit:

With your example update it looks like you can just do string comparisons to do what you wanted - granted it's still a hack but would perform much better than materializing all rows. This is possible because your date format puts the most significant numbers first, then the less significant parts - it's year, then month, then day (should this not be the case and the day comes before the month in your example this solution will not work).

Assuming your input string validDateStart is in the same format as well you can just do:

var list = priceList.Where(p => p.ValidDate.CompareTo(validDateStart) >=0);
                    .ToList();

string comparison with String.CompareTo seems to be support both in Linq to Sql as well as Linq to Entities.

Upvotes: 3

Related Questions