Table Valued Functions in Linq To SQL
Linq to SQL supports both stored procedures and functions that can return result set. However there is a clear distinction in each approach. When you are using store procedures the data is returned as IEnumerable<T>. What this means is in the Linq query where you are calling the stored procedure, if you want to further apply more query operator meaning further filter the result than that portion of the query will get executed as Linq to objects without you being aware of it. This could be great bottle neck if your stored procedure returns thousands of record and your applying filter that gets applied in memory instead of being sent as a part of query that gets executed on SQL server. In contrast if you are using table valued functions, data returned is in the form of IQueryable meaning you can further refine your query based on filters that you require. Those filters would become part of the SQL query that gets sent to the SQL server. To demonstrate this behavior I will start of with a table valued function and a stored procedure that returns customers based on city parameter.
Once I have the stored procedure and function created on SQL server, I am going to drag those objects on the Linq to SQL designer to return me a collection of customers.
After dragging my function and procedure, the designer file for Linq to SQL has generated the code shown above. Looking at the above code you can tell that method calling the function in SQL server returns IQueryable on which you can apply further transformation that would get executed on SQL server. On the contrary, method calling stored procedure returns ISingleResult that inherits from IEnumerable<T>. so any transformations applied would result in queries being executed as Linq to objects which may be an expensive operation in memory with so many records returned from SQL server. Here is an example of a query using the function in Linq to SQL and and further filtering the results by city.
After getting the results from the function we are applying city filter in the Linq query. Looking at the query that gets sent to SQL server, you will notice that city is also applied as the where clause so the entire query is sent as one statement to SQL server. When we change the query to use stored procedures the city filter does not become part of the SQL query. In fact the filter is applied in memory once the data is fetched from the memory. Therefore part of the query gets executed on SQL server and part of it gets executed using Linq to objects.
Here is an example of using Linq query with stored procedures.
