Return custom table from Stored Procedure In Ef Code First

Question

Is it possible to return custom table from SP . I'm using EF code first approach.

This is my SP

CREATE PROCEDURE [dbo].[GetUserNotification]  
    @ToUserId INT
AS
BEGIN
select  TU.FullName as ToUserName,
        FU.FullName as FromUserName, 
        N.NotificationClickType,
        N.TemplateId,
        J.Title as JobTitle,
        J.Identifier as JobIdentifier,
        B.Identifier as BidIdentifier
        from Notification N
        inner Join  AppUser TU on TU.Identifier = N.ToUserId
        inner Join  AppUser FU on FU.Identifier = N.FromuserId
        inner Join Bid B on B.Identifier = N.NotificationBidId
        inner Join Job J on J.Identifier = B.JobId
        where N.ToUserId=@ToUserId
        Order By N.Identifier DESC
END

my Custom View Model

public class NotificationModel
    {
        public string ToUserName { get; set; }

        public string FromUserName { get; set; }

        public NotificationClickType NotificationClickType { get; set; }

        public int TemplateId { get; set; }

        public string JobTitle { get; set; }

        public int JobIdentifier { get; set; }

        public int BidIdentifier { get; set; }
    }

I have created same ViewModel. But every where I have seen Using SP i can return Only single table data which I have added in my DbContext class.

Answer 1

When you add your stored procedure to the EF Model, it will automatically create a complex type. (At least it did for me, using Model First).

The name of the complex type is the SP name with “_Result” added, e.g. GetUserNotification_Result.

You can use the complex type like:

using (Entities dbContext = new Entities())
{
    List<GetUserNotification_Result > data = dbContext.GetUserNotification(userId).ToList();
}

Alternatively, https://msdn.microsoft.com/en-us/data/jj691402.aspx shows how to use Code first. How to call Stored Procedure in Entity Framework 6 (Code-First)? may also be helpful.