Can LINQToSQL be used with sproc that uses sp_executeSQL? If not, how do you handle?

Question

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.

Answer 1

Is there some reason you can't handle the query using the object properties in Linq?

I'd need to see the table scheme including the foreign key linkage in order to give a good example, but it'd be something like:

dbContextObject.Foos.Where(foo=> foo.Bars.Where(bar=> bar.BarName == searchString))

You'd then have an IEnumerable of Foos matching the condition you could do whatever you wanted with.

If searchString is null, then you'd just use dbContextObject.Foos.All()

Answer 2

You can use Linq2SQL to call your sproc, but the Entity modeler probably can't generate the call wrapper for you because it can't figure out what the sproc is returning, so you'll have to create the call wrapper yourself.

To do this create an Entity Model "non-designer" module with a partial class definition matching your Entity Model data Context (and Entities if necessary) and define the call wrapper like this.

namespace bar.Context
{
    public partial class EntityModelDataContext
    {
        /// <summary>
        /// LINQ to SQL class mapper for Foo StoredProcedure
        /// </summary>
        /// <remarks>
        /// This one is too tough for the LINQ to SQL modeler tool to auto-generate
        /// </remarks>
        /// <returns></returns>
        [Function(Name = "dbo.Foo")]
        [ResultType(typeof(bar.Entity.tbFoo))]
        public ISingleresult<bar.Entity.tbFoo> Foo([Parameter(Name = "BarName", DbType = "varchar")] string barname)
        {
            IExecuteResult result = this.ExecuteMethodCall(this, ((MethodInfo)(MethodInfo.GetCurrentMethod())), barname);
            return ((ISingleResult<bar.Entity.tbFoo>)(result.ReturnValue));
        }
    }
}

namespace bar.Entity
{
    /// <summary>
    /// Data Modeler couldn't figure out how to generate this from the sproc
    /// hopefully your entity model generated this and you don't need to replicate it
    /// </summary>
    [Table(Name = "dbo.tbFoo")]
    public partial class tbFoo        {
       ....
    }
}