none
Delimited result within LINQ query combined with property results in error RRS feed

  • Question

  • I'm baffled.  Trying to return a concatenated value that consists of a delimited list of string values from a subquery along with another field.  It seems that when I try to return either component on its own, the query runs fine.  However, when I combine them or use the delimited list in an IF statement, I get a "cannot translate to SQL" error.

    I appreciate any advice.  Here is a sample query:

    =====================================

    Return (From WorkOrder In DB.WorkOrders

                 Let RunTickets = String.Join(", ", (From runticket In DB.RunTickets, joborder In DB.JobOrders, workorderruntool In DB.WorkOrderRunTools, runtool In DB.RunTools _

                                                      Where workorderruntool.WorkOrderID = WorkOrder.WorkOrderID _

                                                      And runtool.RunToolID = workorderruntool.RunToolID _

                                                      And runticket.RunTicketID = runtool.RunTicketID _

                                                      And joborder.JobOrderID = runticket.JobOrderID _

                                                      Select runticket.RunNumber).Distinct.ToList)

                Let JobReference = WorkOrder.Reference & " :: " & RunTickets

                Select New With {WorkOrder.WorkOrderID, JobReference})

    Tuesday, October 8, 2013 3:39 AM

Answers

  • Hello,

    Thanks for posting your question to this forum.

    Unfortunately, in Linq lambda expression the query is first translated to an expression tree and then later into actual running SQL code. There is only a small subset of C# code in expression tree form which can be converted to SQL.

    In this case the expression tree is including a call to a method which resides in the current assembly (String.Join()). There is no way for the convert to translate this method into code that can run in the SQL server so it throws an error.

    Thanks &Regards.


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Wednesday, October 9, 2013 1:21 AM
    Moderator