We use dynamic LINQ at work, and I’ll admit that there are some nice things about it. It certainly fixes some of the fundamental issues with LINQ that should have prevented it rolling out the door in the first place (a DAL that doesn’t interact with Webforms?? Seriously?)
So we’ve been swimming in spaghetti strings which seems silly, but at least you can do what you need to do. I don’t like writing first class objects manually as string data, but if . I like the type safety (how could you not) even if it’s only implemented at compile time by the LINQ parser, and the lack of intellisense support seems like a glaring wart that tells you no one has really thought LINQ all the way through yet. I guess that’s why they’ve primarily moved on to EF. However, we’ve run into a really strange problem…
Using spaghetti strings and feeding them into the dynamic LINQ functions, e.g. .Where(), .Orderby(), etc., has worked fine for strings, ints, and bools. However, this week we tried to build our first Where against a DateTime field in the database. Big problems. We can’t incorporate the datetime as a native comparison because the .Where() function only accepts strings. However, when we pass in something like…
string whereClause = “dbDateField > MM/DD/YYYY”
string whereClause = “dbDateField > ‘MM/DD/YYYY’”
or…
string whereClause = “dbDateField > \“MM/DD/YYYY\””
the LINQ parser throws type safety errors because it recognizes the date as either an int32 or a string. After a couple days of reading and Googling anything related to dynamic SQL, LINQ, and T-SQL, I finally ran across one limited solution on a guy’s blog. It works like…
DateTime myDateTime = getMyDateTime();
.
blahblahblah LINQ stuff blahblah
.
.Where(“dbDateField > @0”, myDateTime)
The problem here of course is that the datetime is now tightly coupled to the creation of the LINQ IQueryable<>. You can’t encapsulate the entire filtering process, so in a complex set of filtering requirements like we have, we’re building up the Where clause one piece at a time in an encapsulated function, but the actual Date value is set in an entirely different file where the LINQ query is created. Anyone familiar with a way to create a string that the LINQ parser and SQL Server 2005 can identify as a DateTime? Or is LINQ just not suitable for a medium scale production application like this?