Convert a datetime in a subcollection of collection and use it in LINQ to SQL

Question

I have a collection with subcollections in it, one of which is a date field stored in the DB as a string in the format yyyymmdd (which also contains a few random things such as "E" or 20085, etc.). This date is also part of one of the subcollections in the collection. This date field will now be used for searching, so I need to make it into a real date to use it in LINQ statements.

I since learned that LINQ to SQL doesn't support statements that it cannot translate into SQL, so I can't insert a function that returns a properly converted date and I haven't yet found a standard convert function that will transform the string into a valid date object.

I also tried layered converting, though this smelled bad, such as this:

search = from c in search 
         where c.Object.Any(p=> new DateTime(Convert.ToInt32(p.theDate, Substring(0,4))))... etc.

I just received conversion errors no matter what I did here.

My basic question is: does LINQ to SQL support anything inline for such a conversion? Another option is to change the datamodel or make a view, but I'd rather see if there are ways to handle this in code first. What I'm trying to do is something like the following:

search = from c in search
         where c.subcollection.Any(p=>p.theDate >= min) && c.subcollection.Any(p=>p.theDate <= max)
         select c;

Where min and max are passed in date values.

Thank you!

I dont really get it, converting string to date object is as simple as Convert.ToDateTime(String). The return is a valid DateTime object. — Eric Herlitz, Aug 15 '12 at 21:45
And for the record. The expressions are called Lambda, http://msdn.microsoft.com/en-us/library/bb397687.aspx — Eric Herlitz, Aug 15 '12 at 21:51
I don't understand why you posted your second comment, so I'll just leave it alone. And I could not get the code in your first comment to work. It looks like a simple conversion will not do it in this case, though I agree that on the surface it looks simple. — ewomack, Aug 15 '12 at 22:21
Oh sorry, did post the second comment to help you in your quest — Eric Herlitz, Aug 16 '12 at 05:48

Fun Pants · Answer 1 · 2012-08-16T02:46:30.407

If you wish to compare a date using LINQ but the SQL date column is a string value, use .CompareTo("2002-01-01") >= 0

This will translate and return any dates that match 2002-01-01 or occured later in time. Not sure how this works in with your subcollection but as an example:

search = from c in search 
     where c.stringDateColumn.CompareTo("2002-01-01") >= 0 
     select c;

Edit: I did some tests on a table with dates in strings like "2002-09-04E", "2004-04-01 34", "20050604e" and "2005-06-01444" and the results do return based on a string in format yyyy-mm-dd or yyyymmdd:

search = from c in search
     where c.subcollection.Any(p=>p.theDate.CompareTo(min) >= 0) && c.subcollection.Any(p=>p.theDate.CompareTo(max) <= 0)
     select c;

smartcaveman · Accepted Answer · 2012-08-16T02:33:00.060

The SqlMethods class has some static helper methods that are translatable by LINQ to SQL. Most of these have to do with DateTime comparison. I'm not sure exactly which method you are looking for, but you should be able to find what you want at the documentation page for System.Data.Linq.SqlClient.SqlMethods

Alternatively, you could create a user-defined function and handle the conversion and comparison issue on the SQL side. The integration doesn't require much more than decorating the corresponding Object Model method with a FunctionAttribute, (which indicates how LINQ-to-SQL should process the method. Here is some documentation on this process:

Convert a datetime in a subcollection of collection and use it in LINQ to SQL

2 Answers2

Linked