linq-to-sql: Stored procedures cannot be used inside queries.

Question

This fails on VS2010 RC LINQ-to-SQL with the InvalidOperationException "Stored procedures cannot be used inside queries.":

var foo = (from a in aTable 
    from b in this.SomeStoredProcedure()
    where a.Id == b.Id
    select b.Id);

SomeStoredProcedure is a SQL procedure which returns a table. 'join' also appears to fail. Any thoughts why?

Answer 1

Because you can't call a stored procedure within a select statement.

Your command would look something like this in tsql... but this is not valid.

select b.Id
    from aTable a
    inner join (exec SomeStoredProcedure) b on a.Id = b.Id

The LINQ statement would work if you were using a udf instead of a stored procedure. Alternatively, you could execute your stored procedure prior to doing the join.

var foo = (from a in aTable 
    from b in this.SomeStoredProcedure().ToList()
    where a.Id == b.Id
    select b.Id);