Return final result from stored procedure in Entity Framework

Question

I have a WebAPI service with a SQL Server backend which is managed using Entity Framework (code-first). I have used Linq queries to retrieve data from the database. Since a few of my API endpoints do multiple joins to get data, I observed some delay when deployed in production. I read on using ORM mapping vs direct SQL queries (stored procedures) and came to a conclusion that for complex queries, it's optimal to use stored procedures.

I came across few articles explaining how to return value from stored procedure using code like:

context.Database.SqlQuery<T>(...)

However, all the examples deals with simple SELECT query and yes, I also came across returning multiple SELECT query results too. But in that case, it's 2 SELECT queries.

In real scenario, it can be more than 2 select queries since it may involves joins and other codes. And, at the end of the query only we'll be deducing the final SELECT query which returns the expected data. Below is a sample mock-up code to explain the scenario:

// Inside my stored procedure
Declare @XId uniqueidentifier
Declare @YId uniqueidentifier

// Some mockup queries. This can have JOINS and other complex codes in real code.
// Below code is made as simple as possible to explain the scenario
Select @XId=Id From Table1 Where Name = 'Name1'
Select @YId=Id From Table2 Where Code = 'Code1'

// This is the data, I am interested
Select * From Table3 Where Col1Id=@XId And Col2Id=@YId

How can I get rows from last SELECT query achieve this?

I did some research and assume that it's not possible to do with EF since stored procedure support is minimal. Is that possible to do within EF limits or do I need to fall back to ADO.NET for this?

Any help will be appreciated.

Thanks.

Answer 1

I somehow managed to deduce a solution after reading the MSDN article - https://msdn.microsoft.com/en-us/data/jj691402.aspx. Here is what I had done.

I executed stored procedure using DbCommand.ExecuteReader() which return DbDataReader. Since, my point-of-interest is the last SELECT statement. I managed to skip to the last resultset of DbCommandReader and casted that to my DTO using ObjectContext.Translate<T>().

Not sure whether there is any better optional available. For time being it works.

Update: I finally switched to Dapper which is a micro ORM from StackExchange. It gives significant performance boost when compared with EF. The API call that took 3 sec with EF is now returning data in less than 500ms.

Thanks.

Answer 2

Since you didn't disclose much of your code, I can only provide you a working sample.

Suppose you have the following CodeFirst schema

class DbC : DbContext
{
    public DbC()
    {
    }
    public DbSet<Blog> Blogs { get; set; }
}

class Blog
{
    public int Id { get; set; }
    public string Name { get; set; }
    public List<Post> Posts { get; set; }
}

class Post
{
    public int Id { get; set; }
    public string Text { get; set; }
}

A few data that I assume to reside within the database:

var tmp = new List<Blog>()
{
    new Blog
    {
        Name = "Blog1",
        Posts = new List<Post>()
        {
            new Post { Text = "Post1" },
            new Post { Text = "Post2" },
            new Post { Text = "Post3" },
        }
    },
    new Blog
    {
        Name = "Blog2",
        Posts = new List<Post>()
        {
            new Post { Text = "Post4" },
            new Post { Text = "Post5" },
            new Post { Text = "Post6" },
        }
    },
};

And a stored procedure to retrieve Posts by Blog.Name

CREATE PROCEDURE [dbo].[PostsRetrieverProc]
    @BlogName NVARCHAR (MAX)
AS
    Declare @BlogId int = 0

    Select @BlogId=Id From [Blogs] Where Name = @BlogName

    Select
        b.Id [BlogId],
        p.Id [PostId],
        p.[Text] [PostText]
    From [Blogs] b join [Posts] p On b.Id = p.Blog_Id
    Where p.Blog_Id =  @BlogId
GO

Then you can create an arbitrary class with properties whose names and types match your procedure result

public class MyCustomReturnType
{
    public int BlogId { get; set; }
    public int PostId { get; set; }
    public string PostText { get; set; }
}

And call the stored procedure to return some values

using (var db = new DbC())
{
    var result = db.Database.SqlQuery<MyCustomReturnType>("dbo.PostsRetrieverProc @param1", new SqlParameter("param1", "Blog2"));
    foreach (var item in result)
    {
        Console.WriteLine(item.PostText);
    }
}

My result output as expected:

Post4
Post5
Post6

Any problems with the approach?

Edit:

Since you only want the last result from your stored procedure, it might be better to redesign the SP instead of skipping the intermediate results. This can be done with the With clause. Note that this example code is a bit bloated, since I only use the blog Id and still join with [Blogs] just to show that its possible

CREATE PROCEDURE [dbo].[PostsRetrieverProc]
    @BlogName NVARCHAR (MAX)
AS
    With
        BlogEntries (BlogId)
    As
        (Select Id BlogId From [Blogs] Where Name = @BlogName)
    Select
        b.Id [BlogId],
        p.Id [PostId],
        p.[Text] [PostText]
    From BlogEntries e
    join [Blogs] b On e.BlogId = b.Id
    join [Posts] p On b.Id = p.Blog_Id

Also interesting to allow multiple pre-selects: Can I use multiple "with"?

Answer 3

do not use just execute.

use this sql:

Insert into #tempTable 
 execute  sp_executesql @SELECT