3 Table LINQ query with a where clause

This simple yet evil LINQ query is giving me problems at runtime (note that any appropriate where clause works as long as I'm not using JOINs:

var query = from iDay in db.DateTimeSlot
   j开发者_如何学Gooin tsk in db.Tasks on iDay.FkTask equals tsk.PkTask
   join dte in db.Mdate on iDay.FkDate equals dte.PkDate
   where dte.Mdate1 == day.ToString(dtForm)
   select new {
      tsk.PkTask,
      tsk.Task,
      iDay.FkTask,
      iDay.TimeSlot,
      iDay.Mdate,
      dte.Mdate1
};

I can get the where clause to work at runtime but only if it's applies to a db.DateTimeSlot column. Otherwise, the query works if I remove the where clause. If I try to use the proper where cause, the one that is listed here, I receive a 'Unhandled Exception: System.ArgumentException: Value does not fall within the expected range' error when I try to foreach through the var query result. Note that when I strip out the JOIN clauses, the where clause does indeed work, when I query the proper table.

The schema of the database is:

tasks -∞ dateTimeSlot ∞- mdate

I am trying to get a list of tasks related a certain mdate.date, so the where clause tests mdate.date.

Thanks

EDIT: here is the Sqlite DB schema for this portion:

CREATE TABLE mdate (
  pkDate       INTEGER PRIMARY KEY AUTOINCREMENT,
  mdate        TEXT,
  nDay         TEXT);
CREATE TABLE dateTimeSlot (
  pkDTS        INTEGER PRIMARY KEY AUTOINCREMENT,
  timeSlot     INTEGER,
  fkDate       INTEGER,
  fkTask       INTEGER,

  FOREIGN KEY(fkDate) REFERENCES mdate(pkDate)
  FOREIGN KEY(fkTask) REFERENCES tasks(pkTask));
CREATE TABLE mdate (
  pkDate       INTEGER PRIMARY KEY AUTOINCREMENT,
  mdate        TEXT,
  nDay         TEXT);

EDIT: Here is the SQL statement that works:

sqlite> SELECT tasks.task, mdate.mdate FROM dateTimeSlot
   ...>   INNER JOIN tasks ON dateTimeSlot.fkTask=tasks.pkTask
   ...>   INNER JOIN mdate ON dateTimeSlot.fkDate=mdate.pkDate
   ...>   where mdate.mdate = '2011-07-21';
task|mdate
laundry|2011-07-21
laundry|2011-07-21

EDIT: Here is the output of Db.Log = Console.Out. Note that I don't get this SQL spam if the where clause is left in, I am only getting the normal exception debug spam:

SELECT tsk$.[pkTask], tsk$.[task], iDay$.[fkTask], iDay$.[timeSlot], t1$.[mdate], t1$.[nDay], t1$.[pkDate], dte$.[mdate]
FROM [main].[dateTimeSlot] AS iDay$
 LEFT JOIN [main].[mdate] AS t1$ ON t1$.[pkDate] = iDay$.[fkDate]
 INNER JOIN [main].[mdate] AS dte$ ON iDay$.[fkDate] = dte$.[pkDate]
 INNER JOIN [main].[tasks] AS tsk$ ON iDay$.[fkTask] = tsk$.[pkTask]
-- Context: SqlServer Model: AttributedMetaModel Build: 4.0.0.0

I posted the full error at: here

Solved! I replaced day.ToString(dtForm) with: tDate tDate is just a local string = day.ToString(dtForm)

---

Your exception output highlights the problem as the L2S provider you are using cannot convert day.ToString(dtForm) into any comprehensible form for SQLite.

The nice thing is this is basically a fixed string for the query, and does not depend on anything. You'll have to remove it from the query, but simply lift it to a local variable:

var mdate1 = day.ToString(dtForm);
var query = from iDay in db.DateTimeSlot
   join tsk in db.Tasks on iDay.FkTask equals tsk.PkTask
   join dte in db.Mdate on iDay.FkDate equals dte.PkDate
   where dte.Mdate1 == mdate1
   select new
   {
      tsk.PkTask,
      tsk.Task,
      iDay.FkTask,
      iDay.TimeSlot,
      iDay.Mdate,
      dte.Mdate1
   };

The relevant part of the exception is the AnalyzeToString bit which points you in this direction:

Unhandled Exception: System.ArgumentException: Value does not fall within the expected range.
  at DbLinq.Data.Linq.Sugar.Implementation.ExpressionDispatcher.AnalyzeToString (System.Reflection.MethodInfo method, IList`1 parameters, DbLinq.Data.Linq.Sugar.BuilderContext builderContext) [0x00151] in /var/tmp/portage/dev-lang/mono-2.10.2-r1/work/mono-2.10.2/mcs/class/System.Data.Linq/src/DbLinq/Data/Linq/Sugar/Implementation/ExpressionDispatcher.Analyzer.cs:466 
  at DbLinq.Data.Linq.Sugar.Implementation.ExpressionDispatcher.AnalyzeUnknownCall (System.Linq.Expressions.MethodCallExpression expression, IList`1 parameters, DbLinq.Data.Linq.Sugar.BuilderContext builderContext) [0x0008d] in /var/tmp/portage/dev-lang/mono-2.10.2-r1/work/mono-2.10.2/mcs/class/System.Data.Linq/src/DbLinq/Data/Linq/Sugar/Implementation/ExpressionDispatcher.Analyzer.cs:345 
  at DbLinq.Data.Linq.Sugar.Implementation.ExpressionDispatcher.AnalyzeCall (System.Linq.Expressions.MethodCallExpression expression, IList`1 parameters, DbLinq.Data.Linq.Sugar.BuilderContext builderContext) [0x00040] in /var/tmp/portage/dev-lang/mono-2.10.2-r1/work/mono-2.10.2/mcs/class/System.Data.Linq/src/DbLinq/Data/Linq/Sugar/Implementation/ExpressionDispatcher.Analyzer.cs:178

---

Evan:

I wonder what the value that day.ToString(dtForm) takes on? Is it a date before January 1, 1753? If so, SQL Server won't accept that.