How to call a UDF in a linq to sql query?

Question

How would the following sql statement translate to a linq query?

select ID, 
       Price, 
       dbo.fGetText(DescriptionID, defaultLanguage, currentUserLanguage) 
from Products

The UDF fGetText is quite substantial and used throughout the code base, so it needs to be encapsulated (as a UDF or otherwise, perhaps a Linq Expression).

Extra round trips to the database server are not a option. There should only be one query, retrieving 3 fields.

Many thanks in advance for your help. It is greatly appreciated.

Answer 1

Here is the MSDN article:

How to: Call User-Defined Functions Inline (LINQ to SQL)

A note from the same page:

Although you can call user-defined functions inline, functions that are included in a query whose execution is deferred are not executed until the query is executed. For more information, see Introduction to LINQ Queries.

When you call the same function outside a query, LINQ to SQL creates a simple query from the method call expression

Also, take a look at this 13 min screencast.

Answer 2

You can add UDF's to a LINQ to SQL DBML file just like you add tables and sprocs.

They then become executable methods on the DataContext.

Google has lots of articles, like this - http://dotnet.org.za/hiltong/archive/2008/05/21/using-user-defined-functions-udfs-with-linq-to-sql-and-in-linq-to-sql-where-clauses.aspx

Answer 3

Cool! Since the UDF in the datacontext returns a string (in my case) I never realised this was possible.