After being an ADO.NET disciple for many (, many, many) years, I'm finally trying to figure out Linq. In Linq, table data returned from an SQL Server function is type as IQueryable, whereas data returned from a stored procedure is an ISingleResult.

Since IQueryable is somewhat easier to deal with, I'd like to use that. My question: is an SQL function that returns table data as efficient as an equivalent stored procedure?

3 Answers
3

My question: is an SQL function that returns table data as efficient as an equivalent stored procedure?

Yes the underlying code will eventuate in TSQL that goes through the Query Optimizer the same way, so for "efficiency equivalence", it would have to be a solid yes. However, some things are in the table valued function's favour, especially if the function can be inlined (outer filters taken into the query inside the function).

Whereas an SP that generates a result set (or multiple) can only take input from parameters, an inline table function can bring in additional filters from the outside query. Consider this:

The answers from Cyberwiki and Kirk both helped me out, but using stored procedures in Linq just seems very awkward. I did discover that Linq will treat views just like tables, so I can use a view to shape my results, so I can still do much of that processing on the server, instead of joining the tables inside my application's code.

I think that using views, along with Linq compiled queries for greater performance (as Kirk suggested), will give me the results I want.