Menu
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.
272
For example, user-defined functions can be used in the following: The select list of a SELECT statement. The condition of a WHERE clause. CONNECT BY , START WITH , ORDER BY , and GROUP BY clauses.
EF Core allows for using user-defined SQL functions in queries. To do that, the functions need to be mapped to a CLR method during model configuration. When translating the LINQ query to SQL, the user-defined function is called instead of the CLR function it has been mapped to.
Step 1 − Select the Console Application from the middle pane and enter TableValuedFunctionDemo in the name field. Step 2 − In Server explorer right-click on your database. Step 3 − Select New Query and enter the following code in T-SQL editor to add a new table in your database.
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.
45
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.
173
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
