not supported for execution as SQL

Discussion of open issues, suggestions and bugs regarding ADO.NET provider for MySQL
Post Reply
skibikegolf
Posts: 2
Joined: Sat 05 Jun 2010 16:58

not supported for execution as SQL

Post by 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));
        }

Shalex
Devart Team
Posts: 8088
Joined: Thu 14 Aug 2008 12:44

Post by 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.

Post Reply