locked
Store procedure gives null values to model class properties RRS feed

  • 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.

    Thursday, May 2, 2019 11:23 AM

Answers

All replies

  • 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

    Thursday, May 2, 2019 12:41 PM
  • 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

    Friday, May 3, 2019 3:03 AM
  • 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();

    Friday, May 3, 2019 3:10 AM
  • 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

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, May 3, 2019 3:55 AM
  • 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?

    Friday, May 3, 2019 4:36 AM
  • 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

    Friday, May 3, 2019 5:40 AM