Is there any way I can execute this SPROC and get the result with Entity Framework?

Question

This SPROC "returns" a table with two columns: Id, Score.

Is it possible to execute this SPROC and include the custom data-type as parameters from within Entity Framework?

ALTER PROCEDURE [presenta].[SearchLpi] 
    // The presenta.IdTableType is a table with just one column "Id"
    @selectedLpis presenta.IdTableType READONLY
AS
BEGIN
    SET NOCOUNT ON;

    WITH Scores AS(
        SELECT
            ItemId, SUM(Score) AS Score

        FROM [Presenta].[presenta].[LpivScores]

        WHERE
            ListPropertyItemId IN (
                SELECT Id
                FROM @selectedLpis
            )

        GROUP BY
            ItemId
    )
    SELECT
        i.Id,
        s.Score
    FROM
        Scores  s,
        Items   i
    WHERE
        s.ItemId = i.Id

END

If not, is there any other way to get the results of the SPROC and being able to join this result with another LINQ-query?

Answer 1

Here you are better of writing a EF linq query directly against the table. See http://msdn.microsoft.com/en-us/library/bb896341.aspx for an example.

If you must use stored procedures, then there is a way using a table as a return type. You need to create a temporary table with the fields that you return. See: http://blogs.msdn.com/bindeshv/archive/2008/11/20/using-stored-procedures-in-entity-framework.aspx

Answer 2

Create DefiningQuery in the Store part of the model with properties corresponding to the result of your stored procedure.
You can make it using XML Editor you like. Devart Entity Developer has design-time support for DefiningQueries.
Then create a function import for the procedure and set the return type to the hand-made entity.
Devart Team
http://www.devart.com/dotconnect
ADO.NET data providers for Oracle, MySQL, PostgreSQL, SQLite with Entity Framework and LINQ to SQL support

Answer 3

This is one of the areas where EF 4 will bring a substantial improvement. With EF4, you'll be able to pull in a stored procedure, and if the return value of that sproc doesn't map to a given table, you can easily create a so-called complex type (basically a class) that will hold the stored proc return values.

See some blog posts for samples and more info on that:

• Automatic Generation of Stored Procedure Return Types
• Using a Stored Procedure in Entity Framework 4
• A big step for Stored Procedures in EF4
• Getting Started with Entity Framework 4 – Complex Types and Entities

Yet another great new feature to look forward to!