not supported for execution as SQL

not supported for execution as SQL

Postby skibikegolf » 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));
        }
skibikegolf
 
Posts: 2
Joined: Sat 05 Jun 2010 16:58

Postby Shalex » 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.
Shalex
Devart Team
 
Posts: 7473
Joined: Thu 14 Aug 2008 12:44


Return to dotConnect for MySQL