LinqToSql - Bit of a strange behavior

Question

Hi,

I am trying to run the following code. But the code breaks

    Dim complaints = From comp In Me.Db.Complaints _
    Let varX = GetVariations().WithVariationId(If(comp.ItemPropertyXVariationId, 0)) _
    Let varY = GetVariations().WithVariationId(If(comp.ItemPropertyYVariationId, 0)) _
    Select New Hogia.Retail.POS.Data.Complaint() With _
    {.ItemXVariation = If(varX Is Nothing, DirectCast(String.Empty, String), varX.Name)}

.ItemXVariation is a property of string type. Now i tested this line of code outside the LINQ statement in the following manner and it worked fine, returned the proper result as i expected

    Dim varXX = GetVariations().WithVariationId(0)
    Dim varYY = GetVariations().WithVariationId(0)
    Dim temp As New Complaint() With {.ItemXVariation = If(varXX Is Nothing, DirectCast(String.Empty, String), varXX.Name)}

Could someone please help me understand why the code in the first block breaks. what is wrong in there.

Here is the message that comes up (help me make sense out of this)

Could not translate expression 'Table(Complaint).Select(comp => new VB$AnonymousType_22 (comp = comp, varX = Invoke(value(System.Func1[System.Linq.IQueryable1[ Data.ItemPropertyVariation]])).WithVariationId((comp.ItemPropertyXVariationId ?? 0)))).Select($VB$It1 => new VB$AnonymousType_32($VB$It1 = $VB$It1, varY = Invoke(value(System.Func1[System.Linq.IQueryable1[Data.ItemPropertyVariation]])).WithVariationId(($VB$It1.comp.ItemPropertyYVariationId ?? 0)))). Select($VB$It => new Complaint() {ItemXVariation = IIF((Convert($VB$It.$VB$It1.varX) = null), null, $VB$It.$VB$It1.varX.Name)})' into SQL and could not treat it as a local expression.

Answer 1

Is GetVariations() a method of your own. linq to sql will be trying to call it as a stored procedure.

You'd need to either write a stored procedure, or get the full entity set then use linq (to objects) with your "let" statements. That would call your GetVariations()

Answer 2

It's a common misunderstanding that you would be able to use anything against IQueryable, and that it simply gets 'magically translated'. That is not the case.

The problem is that you use code that the Linq2SQL Linq provider does not support.

Simply put, Linq works by interpreting the expression tree defined in your query into it's target language (in the case of Linq2Sql, T-SQL). It cannot simply translate any method call into the appropriate sql...

So when using linq2sql (or any other linq provider for that matter), you should be aware of which query operators are supported, and how.

For a more in-depth explanation about the translation to Sql, check out this blog post

Answer 3

IQueryable in Linq to SQL query http://w3vb.net/vb-net-ado-net/linq/linq-to-sql/iqueryable-in-linq-to-sql-query