How to add custom columns to a table that LINQ to SQL can translate to SQL

Question

I have a table that contains procedure codes among other data (let's call it "MyData"). I have another table that contains valid procedure codes, their descriptions, and the dates on which those codes are valid. Every time I want to report on MyData and include the procedure description, I have to do a lookup similar to this:

From m in dc.MyDatas _
Join p in dc.Procedures On m.proc_code Equals p.proc_code _
Where p.start_date <= m.event_date _
And If(p.end_date.HasValue, p.end_date.Value, Now) >= m.event_date _
Select m.proc_code, p.proc_desc

Since there are many places where I want to show the procedure description, this gets messy. I'd like to have the lookup defined in one place, so I tried putting this in an extension of MyData:

Partial Public Class MyData
    Public ReadOnly Property ProcedureDescription() As String
        Get
            Dim dc As New MyDataContext

            Return _
                (From p in dc.Procedures _
                 Where p.proc_code = Me.proc_code _
                 And p.start_date <= Me.event_date _
                 And If(p.end_date.HasValue, p.end_date.Value, Now) >= Me.event_date _
                 Select p.proc_desc).SingleOrDefault
        End Get
    End Property
End Class

Which works when displaying data, but you can't use it in a query, because it doesn't know how to turn it into a SQL statement:

Dim test = _
    From x In dc.MyDatas _
    Select x.proc_code _
    Where x.ProcedureDescription.Contains("test")

Error: The member 'MyProject.MyData.ProcedureDescription' has no supported translation to SQL.

Is there a way to turn a complex lookup (i.e. a non-trivial join) like this into something SQL can recognize so that I can define it in one place and just reference the description as if it were a field in MyData? So far the only thing I can think of is to create a SQL view on MyData that does the linking and bring that into my data context, but I'd like to try to avoid that. Any ideas would be welcomed. Thanks.

Answer 1

this is not an extension method, it's a property

VB.net Extension methods: http://msdn.microsoft.com/en-us/library/bb384936.aspx

Consider using this query as a stored procedure if it's really important.

Answer 2

You can insert an AsEnumerable() into your query expression. That will convert everything to that point to a result set so that your custom properties can be included in the remainder of the expression (sorry I don't do VB):

var test = _
    (from x in dc.MyDatas 
    (select x.proc_code).AsEnumerable().
    Where(x => x.ProcedureDescription.Contains("test"));