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

Question

LINQToSQL does not like my sproc. It says that sproc has a return type of "none", which is probably because I use the sp_ExecuteSQL statement to get the results.

Sproc SQL Server 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


Return

This sproc returns a set of FooID | Recycle Bins FooName.

• 12345 | Tango
• 98765 | Cash

purpose

. . Foos, , . , sp_executeSQL, , , SQL, , . 12 , 1, , .

LINQ to SQL . , "none" . , ORM, NHibernate, Entity Framework LLBLGen, . LINQToSQL , 95% ORM . - , , ORM .

LinqToSql! , . .

,

. , , - "", . , :

• sproc. sp_executeSQL. .
• ADO.Net .
• sproc, LINQ.

Answer 1

Linq2SQL sproc, - Entity, , , , sproc, .

"-" Entity Model , Entity Model Context ( Entities), , .

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        {
       ....
    }
}

Answer 2

, , Linq?

, , , - :

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

IEnumerable of Foo, , , .

searchString - null, dbContextObject.Foos.All()