Join tables multiple conditions - compare dates - Linq

Question

I'm trying to do a join on two tables based on multiple conditions, the problem is I'm not able to compare the date fields. The date is stored in datetime format in DB and I want all the records on a particular date, when I do this in as shown in the code below, I get this exception .. Method 'System.String ToShortDateString()' has no supported translation to SQL.

P.S this is part of a big query..

    string dt = "10/14/2009";    


 using (ReportGeneratorDataContext db = new ReportGeneratorDataContext())
        {
            var r = from f in db.f
                    join a in db.a
                    on new { x = f.ID, y = f.date.ToShortDateString() } equals new { x = a.ID, y = dt }
                    select f.Name;
        }

Any workarounds?

Answer 1

I would try to

• either define a view in the DB with its own Linq to SQL mapping
• define a calculated column (either in the table or in a view) of Table a which contains the string converted to a date (on DB side ...)
• do the join outside the database (i.e. get the complete cross join and do a where on this enumeration)

I am aware, that none of these options is really satisfying, but I suppose that's a shorcoming of Linq-To-Sql...