CODEWITHSUNDEEP
c#linq-to-sqlexceptionstored-procedures
Jesse
Jesse

Reputation: 23

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

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?

Upvotes: 2

Views: 2353

Answers (1)

Tom Brothers
Tom Brothers

Reputation: 6007

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);

Upvotes: 3

Related Questions