Devart Forums

Posted: **Fri 18 Jun 2010 16:02**

How do I implement my own boolean-expression method in the where clause?

I am attempting to use my own boolean-expression method in the and I get the following exception:

Method ‘Boolean IsInLookback(Int32, System.String, System.String)’ is not supported for execution as SQL.

Here is the offending linq query:

Code: Select all

var races = from rd in myContext.TblRacedetail
   	     where IsInLookback(8,"2011-1",rd.TblEventdetail.EventId.ToString())
            group rd by rd.TblEventdetail.TblNaasfmem.NaasfmemLastname;

foreach (var grp in races)
{
    Console.WriteLine("Group Key: {0}", grp.Key);
    foreach (var prt in grp)
    {
        Console.WriteLine(" - {0} {1}", prt.TblRace.EventId, prt.TblRace.RaceRace);
    }
}
Console.ReadKey();

If I remove the IsInLookback() method and use a standard Boolean-expression in the where clause, the code executes normally.

Additionally, IsInLookback has been tested with the following code:

Code: Select all

public void Linq11()
{
  string CurEvent = "2011-2";
  string RefEvent = "2009-2";
  Console.WriteLine("Event: {0} is in the lookback period - {1}", RefEvent,    IsInLookback(8, CurEvent, RefEvent));
  Console.ReadKey();

}

private bool IsInLookback(int lookback, string curEvent, string refEvent)
{
   int week = Convert.ToInt32(curEvent.Substring(5, 1));
            int year = Convert.ToInt32(curEvent.Substring(0, 4));

            List lookBackEvents = new List();
            string eventToAdd = " ";

            int wk = (week - 1) % 3;
            for (int i = lookback; i > 0; i--)
            {
                if (wk == 0) wk = 3;
                if (wk== 3) --year;
                eventToAdd = year.ToString() + "-" + wk.ToString();
                lookBackEvents.Add(eventToAdd);
                wk--;
            }
            return (bool)(lookBackEvents.Contains(refEvent));
        }

Posted: **Mon 21 Jun 2010 14:19**

It is not allowed to use your own methods as functions of LINQ to SQL. Functions used in LINQ queries should be supported by LinqConnect, because they are translated by it to SQL that is executed by our provider. Here is a reference on LINQ to SQL: http://msdn.microsoft.com/en-us/library/bb425822.aspx.