Entity Framework returns null value of associated tables when using stored procedure

Question

I am using code-first approach in EF, I have created a stored procedure to select, and when I run it, I get the value from User table but the FK attributes are null.

This is my stored procedure:

ALTER PROCEDURE rsm_Select
    @Id [int]
AS
BEGIN
    SET NOCOUNT ON;

    SELECT 
        u.*, p.*, ut.*
    FROM
        Users u
    LEFT JOIN
        UserProfiles p ON u.userProfile_Id = p.Id
    LEFT JOIN
        UserTypes ut ON u.UserType_Id = ut.Id
    WHERE
        u.Id = @Id;
END

I have three classes (User, UserProfile, UserType):

Public class User
{
    public int Id { set; get; }
    public string U_UserName { set; get; }
    public string U_UserPass { set; get; }
    public string U_Accountstat { set; get; }

    public virtual UserProfile UserProfile_Id { set; get; }
    public virtual UserType Usertype_Id { set; get; }
}

public class Admin : User
{
    //...
}

public class SuperAdmin : Admin
{
    //...
}

public Class UserType
{
    public int Id { set; get; }
    public string TypeName { set; get; }
}

public class UserProfile
{
    public int Id { set; get; }
    public string UFirstName { set; get; }
    public string ULastName { set; get; }
    public string UContact { set; get; }

    public virtual User U_Id { set; get; }
}

These are the classes I used and according to the them the tables will be created in DB.

In User table, ProfileID and UserTypeId are foreign keys.

NOW

When I try to execute the stored procedure, I will get null in UserProfile_ID and UserType_ID.

Here's how I run it:

void Select(int userId)
{
    using (var context = new myCodeFirstApproach())
    {
        var Result = context.Database
                            .SqlQuery<User>("EXEC rsm_Select @Id", new SqlParameter("@Id", userId))
                            .ToList();
    }
}

When I debug the result, I get null on UserProfile_Id and UserType_Id, but I do get values in other fields like U_UserName, U_UserPass, U_Accountstat.

When I run the stored procedure in SQL Server Management Studio, it returns correct result but not in my code.

What is wrong here?

PS: all other stored procedures for Update, Delete and Create are working fine.

I try changing the stored procedure but still the same result

Answer 1

I have change the code a little bit .what I did is that I have separate my model classes as before I have declare some functions in my User class and also I inherits this class in Admin and then admin class was inherits in SuperAdmin.

Now I have only User class which has properties in it and these are my domain classes. and I have create a controllers

Class SimpleUser 
Class Admin : SimpleUser
Class SuperAdmin : Admin

and all the logic are in them.

Also I have new stored procedures:

CREATE PROCEDURE rsm.ReadData 
    @Id [int] 
AS 
BEGIN
    SELECT * 
    FROM Users
    WHERE Id = @Id; 
END;

As this returns me the data of selected user by matching the ID.

NOW

void Select(int userId)
{
    using (var context = new myCodeFirstApproach())
    {
        var Result = context.Database.SqlQuery<User>("ReadeData @Id", new 
        SqlParameter("@Id", userId)).ToList();
    }
}

And my DOMAIN CLASSES or MODEL classes on which the table is created are

public class User
{
    public int Id { set; get; }
    public string U_UserName { set; get; }
    public string U_UserPass { set; get; }
    public string U_Accountstat { set; get; }

    public virtual UserProfile UserProfile_Id { set; get; }
    public virtual UserType Usertype_Id { set; get; }
}

public class UserType
{
    public int Id { set; get; }
    public string TypeName { set; get; }
}

public class UserProfile
{
    public int Id { set; get; }
    public string UFirstName { set; get; }
    public string ULastName { set; get; }
    public string UContact { set; get; }

    public virtual User U_Id { set; get; }
}

Note: By just removing the inheritance the issue is solved

This was the alternative way to solve this problem and by using this approach my code is clean and easy to handle now. Use MVC approach ;)