LINQToSQL doesn't like my sproc. It says that the sproc has return type "none", which is probably, because I am using an sp_ExecuteSQL statement to produce the results.
The SQL Server Sproc Code
I have a stored procedure similar to the following
CREATE PROCEDURE Foo
@BarName varchar(50)
AS
BEGIN
DECLARE @SQL NVARCHAR(1024)
SET @SQL = 'SELECT tbFoo.FooID, tbFoo.Name FROM tbFOO ';
IF @BarName IS NOT NULL
BEGIN;
SET @SQL = @SQL
+ ' JOIN tbBar '
+ ' ON tbFoo.FooID = tbBar.FooID '
+ ' AND tbBar.BarName = ''' + @BarName + ''''
END;
EXEC sp_executeSQL @SQL
END
Returns
This sproc returns a set of FooID | FooName tuples.
- 12345 | Tango
- 98765 | Cash
Goal
This stored procedure is going to be used to return search results on a search page. This is a fairly common search pattern. I want to find Foos that meet a condition, however the condition is being applied to a separate table. I could have chosen to write this query directly without using sp_executeSQL, however what this approach does is to create SQL that will only include the tables actually being queried. In a real world scenario, I could have 12 joins, instead of 1 and this methodology allows me to only string together joins that will actually be used as criteria.
The problem
LINQ to SQL doesn't like it. It says that this query returns type "none" and doesn't allow me to specify a return type. I'm not sure if other ORMs, such as NHibernate, Entity Framework or LLBLGen would be able to handle this or not. LINQToSQL has worked fine thus far on the project and I'm 95% finished with the project and don't want to use a different ORM for a single method. It might be something to refactor if I make further changes, but for now, I'm not ready to do a switch to a different ORM just for this.
I really want to find a way to make this work in LinqToSql! I'm not sure if it can or not. I haven't found any official documentation on this apparent limitation.
Alternatives that I'm considering so far
I've come up with a few alternatives so far. I don't like any of them so I really hope that someone has a good "hack" to solve this problem. These are what I've got thus far:
- Re-write the sproc. Get rid of sp_executeSQL. Do LEFT JOINs on all the tables.
- Use ADO.Net and hand roll the method.
- Don't use a sproc, but instead try to do all the filtering in LINQ.