Entity Framework - code first, call complex stored procedure

Question

I am new to ASP.NET MVC and Entity Framework code first approach. I want to implement a complex [with INNER JOIN, FUNCTION etc.] stored procedure using this approach. I have gone through many question on SO but I get more confused after reading these questions.

What I have done

I first successfully created database using code first approach. As per my research on google, I created a stored procedure with an INNER JOIN in my database.

Then I created class matching with resultant columns from the stored procedure. Then I added this class to my ApplicationDbContext class like this

public DbSet<AllInfo> allInfos { get; set; }

Then in my APIController I call stored procedure like this

var registerUser = db.allInfos.FromSql($"SP_GetInfo").ToList();

but I get error saying AllInfo has no key defined. Also AllInfo table is added to database because I have added reference in ApplicationDbContext.

My questions

1. Why does AllInfo class needs a key when I want this class just to show information retrieved from the stored procedure?

2. If I have to create class for each stored procedure, then there will be so many classes in my project. Is my understanding correct ?

3. This link says DO NOT create SP when using stored procedures. Then what is the method to call complex queries ?

Kindly help with my confusion.

Thanks!

EDIT

Here is my query. it is not as much complex though but further queries might be complex

SELECT 
    a.UserId,
    a.FullUserName,
    a.BirthDate,
    a.BloodGroup,
    a.ContactNo,
    a.Email,
    a.Gender,
    ISNULL(dbo.Fn_FetchEducationInfo(a.UserId), '-NA-') AS Education
FROM 
    RegisterUsers AS a

Function :

ALTER FUNCTION [dbo].[Fn_FetchEducationInfo]
    (@Id VARCHAR(100))
RETURNS NVARCHAR(MAX)
AS
BEGIN
    DECLARE @Education AS NVARCHAR(MAX)

    SELECT
        @Education = STUFF((SELECT ' ' + Education +',' 
                            FROM Tbl_Education as edu 
                            INNER JOIN OPersonMEducations opme ON edu.EduId = opme.EduId 
                            INNER JOIN RegisterUsers RU ON RU.UserId = opme.Id
                            WHERE RU.UserId = @Id
                            FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 1, '')
    FROM 
        RegisterUsers AS a

    --print @Education
--'UPB005' 
    RETURN @Education
END

Answer 1

I would need to understand what your final intention is (not sure what you need the xml for), but from the stored procedure you are showing us, I would say that you do not need a stored procedure to do this selection. Create an 'Education' entity and add a List of 'Education' to your 'RegisterUsers' entity.

public class RegisterUser
{
    [Key]
    public int UserId { get; set; }
    public string FullUserName { get; set; }

    public DateTime BirthDate { get; set; }

    //Extra properties

    public IList<Education> Educations { get; set; }
}

public class Education
{
    [Key]
    public int EduId { get; set; }

    //Extra properties
} 



public class ApplicationDbContext : DbContext
{
public DbSet<RegisterUser> RegisterUsers { get; set; }
public DbSet<Education> Educations { get; set; }
}

// Add the needed conditions here
var registerUser = ctx.RegisterUsers.Include(x => x.Educations);

Answer 2

I am not sure why you adding the class to ApplicationDbContext. If you add a class to ApplicationDbContext, it would need a key so that the corresponding table have a key. Also, since you already have the class, remove it from the ApplicationDbContext and just call the stored procedure.

var variableName= context.Database.SqlQuery<ClassName>("EXEC storedProcedureName @ParameterName_1, @ParameterName_n",
                   new System.Data.SqlClient.SqlParameter("ParameterName_1", ParameterValue_1),
                   new System.Data.SqlClient.SqlParameter("ParameterName_n", ParameterValue_n)
                   ).ToList();

Then you can use the object however you want. Remember it returns a list so don't forget to add index when you want to access the value. If you are expecting just one row from the stored procedure, you can use the SingleOrDefault() extension instead of the ToList() so you won't need to use the indexing.

UPDATE:

Lets say for example you have a stored procedure that return a row with three fields Field_1, Field_2, Field_3. You also have 2 parameters you pass into the stored procedure Parameter_1, Parameter_2 and their values is ParameterValue_1, ParameterValue_2

You can create a class without adding it to the DbContext. They are called ViewModels. So you have

public class WhateverNameYouWant
{
    public string Field_1{ get; set;}
    public string Field_2{ get; set;}
    public string Field_3{ get; set;}
}

Then run the code

var variableName= context.Database.SqlQuery<WhateverNameYouWant>("EXEC storedProcedureName @Parameter_1, @Parameter_2",
                   new System.Data.SqlClient.SqlParameter("ParameterName_1", ParameterValue_1),
                   new System.Data.SqlClient.SqlParameter("ParameterName_2", ParameterValue_2)
                   ).ToList();

The join is done in the database, return an object you can display however you want. Also, if you know for sure the record is going to return just one result, you can use the SingleOrDefault() extension so you don't need to worry about indexes.

To answer your questions, 1. Yes you would need to create a class for objects returned from stored procedures to be able to use them. It is the same thing with model cos if you think about it, you create a model class for all tables and when you query data from the database, the result is injected into the model.

2. Well technically true but you should look to redesigning your model if you are having to get a lot of your data away from EF. If the model is designed well, there shouldn't be too much need to do complex joins away from EF. Using the Include extension gets a lot of joins done but then again, you have to properly design the models with correct mappings.

3. Again, the reason for EF is so we don't have to manually retrieve data from the database by writing SQL. However, this goes back to point number 2, you can design complex statements manually but if you are having to do it all the time, you should look at the model design again.