LINQ: Call Stored Procedure and Join its results with IQueryable

Question

I have:

• a stored procedure which retrieves zip codes in a radius around another zip code

• a table with contacts

I'm getting the contacts through an IQueryable interface which I've extended with various extension methods to filter results by age etc.

What I have trouble with is adding an IQueryable extension method which calls the stored procedure described above (zip codes in radius) and then joins those zip codes with my IQueryable list of contacts.

Any idea how to do this?

Answer 1

To do that, you would need to swap the sp for a udf. This is usually possible without impacting any existing code (that calls the SP) by simply forwarding the (existing) sp to the (new) udf, i.e.

CREATE PROC foo @blah int
AS
BEGIN
    SELECT * FROM dbo.foo_udf(@blah)
END

LINQ-to-SQL supports composability against udfs, but not SPs, so you should then be able to join to the UDF (via yourContext.UdfName(...)) in your query.