How do I pass a parameter from the select list into a function for joining a linq query?

Question

I have a query that can be summarised in SQL as follows;

Select
    S.StockCode
From
    StockToCheck As S
Inner Join
    GetPOSStock(S.StockCode) As C
On  S.StockCode = C.StockCode;

I'm trying to do the same in Linq but it seems I cannot pass a parameter to the function I am joining on as it has not been parsed by Linq.

I imagine it would look like this (vb);

Dim x = From S In StockToCheck
    Join C In GetPOSStock(S) On S Equals C.ProductCode

Where the S var is a list of strings. This gives the error 'S' is not declared and points to the S in the function call / join (GetPOSStock). So it does not seem possible to do this in Linq, can anyone confirm?

Thanks in advance

Ryan

Answer 1

The following worked for me:

Using dc As New TestDC
    Dim x = From s In dc.Stocks
    From c In dc.GetPOSStock(s.Code)
    Where s.Code = c.Code
    Select s.Code
End Using

I assumed that GetPOSStock is a table-valued function in your DB, so I created one accordingly. I also created separate classes for Stock and for POSStock and assigned POSStock as the return value for GetPOSStock using Linq-to-SQL designer.