I have a Product table with a 1:n relationship to a ProductLocale table, which stores the product name and description in many different languages. Naturally, I want to get the data from the ProductLocale table in the user's preferred language. However, if there is no matching record in the ProductLocale table for that language, I need to fall back to English, and then to any remaining language. The system was designed so that there will always be at least one record in the ProductLocale table for each product, but it could be in any language.

Right now, I'm simply getting all results from this table and applying the language fallback filtering through code, but this isn't the greatest design, and it hurts performance. I've created a UDF in SQL Server which returns the best matching records from the ProductLocale table based on the given language, so I can write a query such as:

SELECT Product_ID, Product_Price, ProductLocale_Name, ProductLocale_Description
FROM Products INNER JOIN fn_ProductLocale(@LanguageID) ON Product_ID = ProductLocale_ProductID
WHERE Product_CategoryID = @ProductCategoryID

Can someone point me in the right direction on how to make a query like this using the generated code? Does version 2 include some new features that will help in this case?

You may take a look at this post which uses system functions, but will also work for UDFs. http://www.llblgen.com/tinyforum/Messages.aspx?ThreadID=3829

Thanks for the link, but I don't see how it helps in my case. It shows how to create a predicate expression to use in the SELECT or WHERE clause, but I want use a UDF as a table source in the FROM clause.

I looked deeper into the generated code, and I think simplest solution is to override the GetFieldPersistenceInfo methods, and modify the SourceObjectName in the FieldPersistenceInfo object to use my UDF when needed. I also found this post which is very similar to what I'm looking for: http://www.llblgen.com/tinyforum/Messages.aspx?ThreadID=748

That won't work anymore in v2, there you have to create a new FieldPersistenceInfo object, though then it works as you want.