Invalid Column name error in MVC 4

Question

I have created a simple class and a list based on this class. When i try to populte this list and send to view iam getting an error. Please view my class and custom mapper model based on database.

Folloiwng is the class.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;

namespace MvcModal.Models
{
    public class mytransaction
    {
        public int Id { get; set; }
        public int my_trn_id { get; set; }
        public string Description { get; set; }
        public List<mytransaction> Translist { get; set; }
    }
}

Following is the custom database mapper class.

 using System;
    using System.Collections.Generic;
    using System.Linq;
    using System.Web;
    using System.Data.Entity;
    using System.Data.Entity.ModelConfiguration;
    using System.ComponentModel;
    using System.ComponentModel.DataAnnotations;
    using System.ComponentModel.DataAnnotations.Schema;
    using MvcModal.Models;

    namespace MvcModal.Models
    {
        public class PrContext : DbContext
        {
            static string _conString = @"Data Source=.\sqlexpress;Initial Catalog=MyDb;Integrated Security=True";

            public PrContext() : base(_conString) { }


            public DbSet<mytransaction> MyTransactions { get; set; }
            protected override void OnModelCreating(DbModelBuilder modelBuilder)
            {
                Database.SetInitializer<PrContext>(null);
                modelBuilder.Configurations.Add(new NewTransactMapper());
            }

            public class NewTransactMapper : EntityTypeConfiguration<mytransaction>
            {
                public NewTransactMapper()
                {
                    this.ToTable("mytransaction");
                    this.Property(m => m.Id).HasDatabaseGeneratedOption(DatabaseGeneratedOption.Identity);
                    this.HasKey(m => m.my_trn_id);

                }
            }




        }
    }

Following is the error image.

Please view the red circuled error and see the mytransactions_my_trn_id text. mytransaction is my table name and my_trn_id is my column name. Rest of the columns have no issue, but this making me insane.

Please anyone guide what iam missing and how can i make my table name and column isolate and resolve this error. Thanks in advance.

Your model is wrong. Take a look at this similar self referencing example: http://stackoverflow.com/questions/5125052/self-referencing-many-to-many-recursive-relationship-code-first-entity-framework — Steve Greene, Sep 04 '16 at 16:27
Actually I have cretated my own database and the model classes. Now i need to create my own connection string that will point to database using the entity framework apprach. I dont want to create model.ttt using generate from databsae type wizwards. I want to do everything manually which i done it in the past easily. Please guide what to do?, thanks. — Rauf Abid, Sep 04 '16 at 16:50
You should pick a workflow (code first or database first) and stick with it. While you can do both sides on your own it is time consuming and error prone as you are experiencing. You can use EF Tools to reverse engineer your database into classes as a starting point. Then you can stop using them and maintain code models. The error on mytransaction_my_trn_id is because EF can't determine the relationship and adds the key (table_fk). — Steve Greene, Sep 05 '16 at 16:21

score 0 · Answer 1 · answered Sep 05 '16 at 05:45

If you want to create the model you have to know wich columns EF generates to handle relationships and if you don't specify the names (as in this case) you have to know wich name EF will assign to properties.
I suggest you (I do this) to generate the model on an empty database with EF standard migrations then copy the structure from the EF created database.

In your case you only need to add the column mytransaction_my_trn_id of type int (same as id). If you need the same database the EF would generate with migrations, you need also to add an index on that column and a relationship from that column to my_trn_id column (primary key).

score 0 · Answer 2 · answered Sep 06 '16 at 07:48

I have done it using the following code. It may also be help someone. Also thanks to everyone for their nice suggestions.

using System;
using System.Collections.Generic;
using System.Data.Entity;
using System.Linq;
using System.Web;
using System.ComponentModel.DataAnnotations.Schema;
using System.Data.Entity.ModelConfiguration;
using System.Data.Entity.ModelConfiguration.Configuration;
using payorder_draft_printing.Controllers;

namespace payorder_draft_printing.Models
{
    public class context_db : DbContext
    {
        static string _conString = @"Data Source=my datasource";
        public context_db()
            : base(_conString)
        {
            Database.SetInitializer<context_db>(null);
        }


        public IDbSet<sms_description> sms_description { get; set; }
        public IDbSet<sms_imported_trn_code> sms_imported_trn_code { get; set; }

        protected override void OnModelCreating(DbModelBuilder modelBuilder)
        {
            base.OnModelCreating(modelBuilder);
            modelBuilder.Configurations.Add(new sms_description_mapper());
            modelBuilder.Configurations.Add(new sms_imported_trn_code_mapper());


        }
    }

    class sms_description_mapper : EntityTypeConfiguration<sms_description>
    {
        public sms_description_mapper()
        {
            ToTable("dbo.sms_description");

            this.Property(x => x.id).HasDatabaseGeneratedOption(DatabaseGeneratedOption.Identity);
            HasKey(x => x.trn_code);
            Property(x => x.trn_code).HasColumnName("trn_code").IsRequired();
        }
    }

    class sms_imported_trn_code_mapper : EntityTypeConfiguration<sms_imported_trn_code>
    {
        public sms_imported_trn_code_mapper()
        {
            ToTable("dbo.sms_imported_trn_code");
            HasKey(x => x.trn_code);
            this.Property(x => x.id).HasDatabaseGeneratedOption(DatabaseGeneratedOption.Identity);
            Property(x => x.trn_code).HasColumnName("trn_code").IsRequired();
        }
    }

}

Invalid Column name error in MVC 4

2 Answers2