Store procedure gives null values to model class properties

Question

User1052662409 posted

Hi All,

I am trying to execute store procedure using EF 6.0.

Here I used 2 approaches. 

1. by putting model generated by EF.

 var basic_setting_of_website = db.Database.SqlQuery<WebSite_Settings>("usp_website_setting_details").ToList()

Here WebSite_Settings is generetaed by EF itself as it was a database first approach.

It's working fine.

2. by putting model created by me.

SqlParameter param  = new SqlParameter("@bigid", 1);
var multi_language_details = db.Database.SqlQuery<LanguageMaster>("usp_language_master_details @bigid", param).ToList();

I have created  LanguageMaster model class as follows.

public class LanguageMaster
    {
        public long bigid { get; set; }
        public string language { get; set; }
        public string language_code { get; set; }
        public string sequence { get; set; }
        public DateTime date_added { get; set; }
    }

But it gives null for values, even it shows the count==1

Even when I run my sp in SQL

exec usp_language_master_details 1

It gives data but in c# code it shows all variables as null why?

Please suggest.

Answer 1

User475983607 posted

You've defined the input parameter twice.

SqlParameter param  = new SqlParameter("@bigid", 1);
var multi_language_details = db.Database.SqlQuery<LanguageMaster>("usp_language_master_details @bigid", param).ToList();

I think you want this.

int param0 = 1;
var multi_language_details = db.Database.SqlQuery<LanguageMaster>("usp_language_master_details @p0", param0).ToList();

EF6 reference docs.

https://docs.microsoft.com/en-us/ef/ef6/querying/raw-sql

Answer 2

User-893317190 posted

Hi demoninside9 ,

How do you map your properties?

I have made a test and successfully got all the properties filled.

Below is my code.

           List<LanguageMaster> languageMasters= context.Database.SqlQuery<LanguageMaster>("  sp_select_Language @id", new SqlParameter("id", SqlDbType.Int) { Value = 1 }).ToList();


public class LanguageMaster
    {
        [Column("id")]
        public long bigid { get; set; }
        public string language { get; set; }
        public string language_code { get; set; }
        public string sequence { get; set; }
        public DateTime date_added { get; set; }
    }


  public partial class MyDbContext : DbContext
    {
        public MyDbContext()
            : base("name=DbContext")
        {
        }

        public virtual DbSet<Student> Students { get; set; }
        public virtual DbSet<Teacher> Teachers { get; set; }
       
        protected override void OnModelCreating(DbModelBuilder modelBuilder)
        {
          
        }
    }

My stored procedure.

create procedure  sp_select_language

@id int

as 

begin 

 select * from LanguageMaster where id= @id
end

exec sp_select_language 1

id      language     language_code     sequence  date_added
1	english	     123	        15	  2018-12-12 00:00:00.000

Please ensure you have mapped your table to your model correctly.

Result.

Best regards,

Ackerly Xu

Answer 3

User1052662409 posted

I think you want this.

int param0 = 1;
var multi_language_details = db.Database.SqlQuery<LanguageMaster>("usp_language_master_details @p0", param0).ToList();

Answer 4

User-893317190 posted

Hi demoninside9,

I'm afraid it is a mapping problem, raw sql query in ef doesn't map property as ef model.

You could refer to the link below to learn how raw sql query in ef map property.

https://social.msdn.microsoft.com/Forums/en-US/519d81af-a1f2-4321-99f1-d4892c3cdeec/sqlquery-does-not-use-the-dataannotation-of-my-poco-class?forum=adodotnetentityframework

If you sp's property doesn't match your model's property , please change your stored procedure use alias.

Best regards,

Ackerly Xu

Answer 5

User1052662409 posted

If you sp's property doesn't match your model's property , please change your stored procedure use alias.

Yes, it works. Thanks a lot.

Is there any other way to do without using alias in store procedure?

Answer 6

User-893317190 posted

Hi demoninside9 ,

I could not figure out another way, maybe you could try https://stackoverflow.com/questions/12573206/getting-entity-framework-raw-query-to-respect-attributes

I'm not sure Why  you must use stored procedure.

Use entity framework's model you could get what you want without stored procedure and this is how ef is normally used.

context.LanguageMasters.Where(e=>e.bigId==1).First()

If you want to add another entity to your dbcontext, you could try ef migrating(if you are using code-first) https://www.entityframeworktutorial.net/code-first/code-based-migration-in-code-first.aspx

But you should pay attention , when you  updatedatabast , it may meet problem , if you already have table in your database.

Try to comment auto generated file's code related to create table.

 public partial class masterlanguage : DbMigration
    {
        public override void Up()
        {
            //CreateTable(
            //    "dbo.LanguageMasters",
            //    c => new
            //        {
            //            id = c.Int(nullable: false, identity: true),
            //            language = c.String(),
            //            language_code = c.String(),
            //            sequence = c.String(),
            //            date_added = c.DateTime(nullable: false),
            //        })
            //    .PrimaryKey(t => t.id);
            
        }
        
        public override void Down()
        {
          //  DropTable("dbo.LanguageMasters");
        }
    }

My file name in Migrations folder is 201905030517589_masterlanguage, yours may differ,but they look similar,masterlanguage is my migration's name

Best regards,

Ackerly Xu