locked
Exception: "has no supported translation to SQL"

    Question

  • Hello,

     

    I am using LINQ to get my products out of my database. The products table has a column called "offerCount" of type int.

     

     

    I have extended the product class, which was generated by Linq to SQL, with a method to check its validity:

     

    Code Block

    partial class product

    {

    public bool IsValid()

    {

    return offerCount > 0;

    }

    }

     

     

    But when I try to access the data via Linq expression:

     

    Code Block

    var v = from a in dbContext.products

    where a.IsValid() == true

    select a;

     

     

     

    I am getting this Exception

     

    Code Block
    Method 'Boolean IsValid()' has no supported translation to SQL.

     

     

     

    I do not understand what happens here. I thought I could extend the classes with own methods and use them in the linq expressions. I also get the method "IsValid()" proposed by Intellisense when typing the "where a."

     

    Am I on a completly wrong way? How would you solve the problem?

     

    Thanks

    Hannes.

    Monday, December 17, 2007 4:50 PM

Answers

  •  

    LINQ to SQL does not know how to translate a call your method 'IsValid' into SQL in order to execute the where clause on the server.  Your method does not exist on the server.

     

    If you wrote the following it would probably succeed.

     

    Code Block

    var v = from a in dbContext.products

    where a.offsetCount > 0

    select a;

     

     

    Tuesday, December 18, 2007 1:15 AM

All replies

  • According to this article: http://msdn2.microsoft.com/en-us/bb386989(VS.90).aspx

     

    "Method" in the entity class cooresponds to a "Stored Procedure or Function" on the database side.

     

     

    The first step to turning this around is to make IsValid into a property.

     

    Monday, December 17, 2007 5:19 PM
  • HI David,

    thanks for your awnser.

    I changed "IsValid()" into a property "IsValid", but it didn't help. Also with a property, I get the Exception "IsValid has no supported translation to SQL".
    Monday, December 17, 2007 8:28 PM

  • You are not adding any attributes around your property or method that map back to SQL?

    Petar
    Tuesday, December 18, 2007 12:21 AM
  •  

    LINQ to SQL does not know how to translate a call your method 'IsValid' into SQL in order to execute the where clause on the server.  Your method does not exist on the server.

     

    If you wrote the following it would probably succeed.

     

    Code Block

    var v = from a in dbContext.products

    where a.offsetCount > 0

    select a;

     

     

    Tuesday, December 18, 2007 1:15 AM
  • Now that I have access to my LINQ environment, I have figured out how to accomplish your goal.

     

    LinqToSql won't translate just any old method (or property, which is just a fancy method).

    LinqToSql won't even translate any Func.

     

    LinqToSql translates expressions.  Specifically:  System.Linq.Expressions.Expression<Func<T, bool>>

     

     

    Code Block

     

    public partial class TestTable

    {

    //not gonna work

      public static Func<TestTable, bool> IsValid(int i)

      {

        return new Func<TestTable, bool>(myTable => myTable.TheKey > i);

      }

    //works!!!

      public static System.Linq.Expressions.Expression<Func<TestTable, bool>> IsValidExpr(int i)

      {

        return (myTable => myTable.TheKey > i);

      }

    }

     

     

    Here's how-to call it (along with 2 examples of how-not-to call it:

     

    Code Block

     

    public static void LinqToSQLPropertyQuery()

    {

      DataClasses1DataContext myDC = new DataClasses1DataContext();

    //Method 'System.Object DynamicInvoke(System.Object[])' has no supported translation to SQL.

    //var q =

    // from t in myDC.TestTables

    // where TestTable.IsValid(1)(t)

    // select t;

     

    //Compile error: MethodName expected

    //var q = from t in myDC.TestTables

    // where TestTable.IsValidExpr(1)(t)

    // select t;

     

    IQueryable<TestTable> q = myDC.TestTables

      .AsQueryable<TestTable>()

      .Where<TestTable>(TestTable.IsValidExpr(1));

     

    Console.WriteLine(myDC.GetCommand(q).CommandText);

    foreach (var item in q)

    {

      Console.WriteLine(item.Name);

    }

    }

     

     

    Note the need to call AsQueryable, if you don't, you'll invoke the Where method in System.Linq.Enumerable... when you really want the Where method in System.Linq.Queryable .

     

     

     

     

     

    Tuesday, December 18, 2007 4:00 AM
  • Thank you, thank you, thank you for this post.  There's so much to learn about LINQ. 

    Thursday, May 15, 2008 7:18 PM
  • No problem.  Glad I could help.

    Friday, May 16, 2008 3:41 PM
  • Using your method I get the following run time error "System.NotSupportedException: Unsupported overload used for query operator 'Where'."

    Does your code actually execute?
    Thursday, September 25, 2008 10:11 PM
  • Thanks for the clarification, Dave! I wanted to print it, but MSDN forums are extremely hard to print.
    IE7 print one good page, but 7 blanks. Is there a special technique developers use to print any posting that I am not aware of?
    Fakher Halim
    Friday, March 13, 2009 4:41 PM
  • David, Awesome reply!!

    it can be further simplified as follows

    public class TestTableExt {

      public static System.Linq.Expressions.Expression<Func<TestTable, bool>> IsValid(int i)   {

    return (a => a.Count > i);

      }

    }

    void Main()

    {

    var q = TestTable.Where(TestTableExt.IsValid(1));

    }

     

    Tuesday, July 26, 2011 9:32 PM