Dynamic LINQ DateTime Comparison String Building - Linq To Entities
Solution 1
It seems what I was trying to do is not possible with the current DynamicLINQ library. The reason it didn't work was well outlined below by Tilak.
My solution was to modify the DynamicLINQ library to allow the query to be written as a string and passed to the where clause for Date/Time datatypes. The modification was found here by Paul Hatcher: LINQ TO SQL, Dynamic query with DATE type fields
Solution 2
I was able to get it working with a slightly different string format using the information here.
Doing this worked fine for me:
ContactList.Where("DateAdded >= DateTime(2013, 06, 18)")
Note this does not work at all with DateTimeOffset
columns.
Solution 3
ObjectQuery.Where overload accepts 2 parameters.
string predicate
params ObjectParameter[] parameters
In your first example, Where
builds the query (where clause) using ObjectParameter
parameters (using Name, Type and Value of ObjectParameter
)
In your second example, whatever is passed is treated as final where clause (no internal conversion based on datatype of passed parameters done).
Solution 4
Based on Richard Rout's option, with a slight modification:
ContactList.Where("DateAdded >= DateTime(2013, 06, 18)")
This works in my Linq2Entities solution. Note the DateTime
instead of Date
.
Hope this saves someone the headache this problem gave me.
Ricketts
Experienced in Microsoft .NET development as well as many other fields. Expert in standard web coding of xHtml and CSS. Also very experienced in ASP.NET, C#, Javascript, JQuery, Database Development and Graphic Design. I have designed and developed hundreds of websites for many different industries. I own and operate Ricketts Web Design: http://www.RickettsWebDesign.com, where we develop custom promotional website and custom web applications. I also write an online blog: http://www.WebDeveloperPost.com, where I give tips and tricks on how to do different things.
Updated on September 18, 2022Comments
-
Ricketts about 1 year
I'm using the dynamic LINQ library by Scott Guthrie together with Entity Framework and C#.
I have to build my where string into a variable based on several factors and then pass the string variable to the where clause. For some reason, this will work:
ContactList = ContactList.Where("DateAdded >= @0", DateTime.Parse("12/1/2012"));
But this will not work
string WhereClause = string.Format("DateAdded >= {0}", DateTime.Parse("12/1/2012")); ContactList = ContactList.Where(WhereClause);
As mentioned, I need to use it in the version of passing the variable. Anyone know why the second doesn't work?
Thanks in advance!
-
Ricketts almost 11 yearsI've tried it with quotes, when you set string.Format("DateAdded >= \"{0}\"", DateTime.Parse("12/1/2012")) you end up with the error "Operator '>=' incompatible with operand types 'DateTime' and 'String'"
-
Ricketts almost 11 yearsYes, I see where it is building the query when passing params, however, I can't seem to find a way to replicate what it is building. As I step through, it shows the final output as "Param_0 => (Param_0.DateAdded >= 12/1/2012 12:00:00 AM)". When I pass the string to be like that, it fails. Do you have any ideas on how I can get this to work by passing just the string variable without using params?
-
Tilak almost 11 yearsExplore
System.Data.Entity.dll!ObjectQuery<T>.Where, EntitySqlQueryBuilder.Where
andSystem.Data.Entity.dll!System.Data.Objects.Internal.EntitySqlQueryBuilder
in your favorite dissasembler. -
Aage over 9 yearsShould be:
DateTime(2013, 06, 18)
...Date
isn't a supported function in Linq2Entities. -
Makla almost 6 yearsAnd be careful not to name column DateTime because it will not work.
-
St3ve almost 2 yearsjust to add to @Makla's point - if any column is called DateTime, then no DateTime compression will work, no matter what are your other columns called..