none
Entity Framework 5 invalid column name? RRS feed

  • Question

  • I am trying to test WPF, C# and EF 5 on existing database. When I execute context.SaveChanges, i have the following exception message  : Invalid column name simulation_SimulationID. I tried severale tips, but i was unable to solve this problem, everything seems to be correct. below entities:

    //Entity Echeance
     public partial class Echeance : INotifyPropertyChanged
    {
        public long EheanceId { get; set; }
        public byte EchNumber { get; set; }
        public System.DateTime date { get; set; }
        public Double principal { get; set; }
        public Double interet { get; set; }
        public byte isLoy1 { get; set; }
        public long SimulationId { get; set; }
        public virtual simulation simulation { get; set; }
    
        //Not mapped Attributs 
        public Double tva { get; set; }
        public Double txtva { get; set; }
        public Double horstaxe { get; set; }
        public Double ttc { get; set; }
        public Double amtfin { get; set; }
        public Double encfin { get; set; }
    
    /*
    ....
    .... Here Properties for WPF databinding and 
    */
     public Echeance()
    {
    /*
    ...
    */
    }
    }
    
    
    public partial class simulation : NotifyPropertyChanged
    {
     public long SimulationId { get; set; }
     public Double baseLocative { get; set; }
     
     public virtual IList<Echeance> echeancier { get; set;}
    
    /*
    ...
    ...Not mapped properties
    */
    .... Here Properties for WPF databinding 
    //Constructor
    public simulation()
    {
    /*
    ....
    */
      echeancier = new List<Echeance>();
     }

    And i used the folowing dbcontext

     public class MyContext : DbContext
    {
    
        public DbSet<simulation> Simulations { get; set; }
        public DbSet<Echeance> Echeances { get; set; }
    
    
        public MyContext(string cnxString)
            : base(cnxString)
        {
            Database.SetInitializer<MyContext>(null);
        }
    
    
    protected override void OnModelCreating(DbModelBuilder modelBuilder)
        {
            try
            {
                modelBuilder.Configurations.Add(new SimulationMapping());
                modelBuilder.Configurations.Add(new EcheanceMapping());
    
    //************************************************************************************************************************
    //Not mapped Properies for simulation entity
    modelBuilder.Entity<simulation>().Ignore(t => t.TotalPri);
                modelBuilder.Entity<simulation>().Ignore(t => t.TotalInt);
    //etc.
    
    //Not mapped Properies for Echeance entity 
    modelBuilder.Entity<Echeance>().Ignore(t => t.NumLoy);
    modelBuilder.Entity<Echeance>().Ignore(t => t.DateEcheance);
    
    //etc.
    
    base.OnModelCreating(modelBuilder);
    
        }

    Fluent code mapping :

    public class EcheanceMapping : EntityTypeConfiguration<Echeance>
    {
        public EcheanceMapping()
        {
            this.HasKey(t => t.EheanceId);
            this.ToTable("Echeance");
            this.Property(t => t.EheanceId).HasColumnName("EheanceId").IsRequired()
                .HasDatabaseGeneratedOption(DatabaseGeneratedOption.Identity);
            this.Property(t => t.EchNumber).HasColumnName("EchNumber").IsRequired();
            this.Property(t => t.date).HasColumnName("date").IsRequired();
            this.Property(t => t.principal).HasColumnName("principal");
            this.Property(t => t.interet).HasColumnName("interet");
            this.Property(t => t.isLoy1).HasColumnName("isloy1").IsRequired();
            this.Property(t => t.SimulationId).HasColumnName("simulationId").IsRequired();
            this.HasRequired(t => t.simulation).WithMany(t => t.echeancier).HasForeignKey(d => new { d.SimulationId });
    
    
    
        }
    }
    
     public class SimulationMapping : EntityTypeConfiguration<simulation>
    {
     public SimulationMapping()
        {
            this.HasKey(t => t.SimulationId);
            this.ToTable("simulation");
            this.Property(t => t.SimulationId)
                .HasColumnName("SimulationId")
                .IsRequired()
                .HasDatabaseGeneratedOption(DatabaseGeneratedOption.Identity);
                //.HasDatabaseGeneratedOption(new Nullable<DatabaseGeneratedOption>(DatabaseGeneratedOption.Identity));
    
            this.Property(t => t.baseLocative).HasColumnName("Baselocative").IsRequired();
            this.Property(t => t.txInt).HasColumnName("txInt").IsRequired();
    
               this.Property(t => t.txVr).HasColumnName("txvr");
               this.Property(t => t.Autofin).HasColumnName("Autofin");
               this.Property(t => t.echjour).HasColumnName("echjour").IsRequired();
               this.Property(t => t.duree).HasColumnName("duree").IsRequired();
               this.Property(t => t.echBegin).HasColumnName("echBegin").IsRequired();
               this.Property(t => t.loy1).HasColumnName("loy1");
               this.Property(t => t.loy1_freq).HasColumnName("loy1_freq");
               this.Property(t => t.encfin).HasColumnName("encfin");
               this.Property(t => t.loyer).HasColumnName("loyer");
               this.Property(t => t.txtva).HasColumnName("txtva");
               this.Property(t => t.perio).HasColumnName("perio").IsRequired();
               this.Property(t => t.ratio).HasColumnName("ratio");
        }
    }
    
    

    I logged the mapping and it works fine.

    and finally i use the following unit of work for database access :

    public class UnitOfWork : MyContext, IUnitOfWork, IQueryableUnitOfWork { private ILogger _logger; public UnitOfWork(string cnxString, ILogger logger) : base(cnxString, logger) { } public UnitOfWork(ILogger logger) : base(logger) { _logger = logger; } public void RegisterNew<T>(T item) where T : class { try { if (item != null) { base.Entry<T>(item).State = EntityState.Added; _logger.LogInfoMessage("[" + item.GetType().Name + "] As Added."); } else _logger.LogInfoMessage("<Add> : Nul Entity."); } catch (Exception ex) { _logger.LogException(ex); throw ex; } }

     public void Commit()
        {
            try
            {
                base.SaveChanges();
            }
    
            catch (DbUpdateException e)
            {
                var innerEx = e.InnerException;
                while (innerEx.InnerException != null)
                    innerEx = innerEx.InnerException;
                throw new Exception(innerEx.Message);
            }
    
            catch (DbEntityValidationException e)
            {
                var sb = new StringBuilder();
    
                foreach (var entry in e.EntityValidationErrors)
                {
                    foreach (var error in entry.ValidationErrors)
                    {
                        sb.AppendLine(string.Format("{0}-{1}-{2}",
                        entry.Entry.Entity,
                        error.PropertyName,
                        error.ErrorMessage
                    ));
                    }
                }
                throw new Exception(sb.ToString());
            }
        }
    //etc. }

    As you can see, it is a One-to-many Relationship : one "simulation" must correspond to one or n "Echeance", and "Echeance" cannot exist without simulation. SimulationId Is a primary key in table "simulation" and a foreing key in table "echeance" and it is an identity column (sql server 2008). I used IList instead of ICollection, because i want to edit list of "echeance" in WPF datagrid, it is impossible with ICollection.

    Now When i execute :

      uow.RegisterNew()

        uow.Commit()

    i get "Invalid colmun name simulation_SimulationID".


    Your help would be welcome...Thanks.






    Monday, October 7, 2013 9:20 AM

Answers

All replies

  • Please show in your code where you have defined the variable simulation_SimulationID?

    Fernando (MCSD)

    If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".

    NOTE: If I ask for code, please provide something that I can drop directly into a project and run (including XAML), or an actual application project. I'm trying to help a lot of people, so I don't have time to figure out weird snippets with undefined objects and unknown namespaces.

    Monday, October 7, 2013 1:22 PM
  • Actually, i don't have a variable named simulation_SimulationID. The exception mentioned in my initial thread is from EF 5, and i don't understand from where it extract this variable. for xaml code, there are many custom  themes and custom controls, how can i send them to you ?






    Monday, October 7, 2013 1:42 PM
  • Hi intibnin;

    Have you opened the Database up to see if that column name exist in the database in one of the two tables it has created?

      

     

    Fernando (MCSD)

    If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".

    NOTE: If I ask for code, please provide something that I can drop directly into a project and run (including XAML), or an actual application project. I'm trying to help a lot of people, so I don't have time to figure out weird snippets with undefined objects and unknown namespaces.

    Monday, October 7, 2013 1:48 PM
  • Hi Fernando,

    Yes I have. here sql seerver 2008 script for the two tables Echeance and Simulation :

    USE [dblease]
    GO

    /****** Object:  Table [dbo].[Echeance]    Script Date: 10/07/2013 14:54:56 ******/
    SET ANSI_NULLS ON
    GO

    SET QUOTED_IDENTIFIER ON
    GO

    CREATE TABLE [dbo].[Echeance](
        [EheanceId] [bigint] IDENTITY(1,1) NOT NULL,
        [EchNumber] [tinyint] NOT NULL,
        [date] [date] NOT NULL,
        [principal] [float] NULL,
        [interet] [float] NULL,
        [isLoy1] [tinyint] NOT NULL,
        [SimulationId] [bigint] NOT NULL,
     CONSTRAINT [PK_Echeance] PRIMARY KEY CLUSTERED
    (
        [EheanceId] ASC
    )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
    ) ON [PRIMARY]

    GO

    ALTER TABLE [dbo].[Echeance]  WITH CHECK ADD  CONSTRAINT [FK_Echeance_simulation] FOREIGN KEY([SimulationId])
    REFERENCES [dbo].[simulation] ([SimulationId])
    ON DELETE CASCADE
    GO

    ALTER TABLE [dbo].[Echeance] CHECK CONSTRAINT [FK_Echeance_simulation]
    GO

    ***************************************************************************

    USE [dblease]
    GO

    /****** Object:  Table [dbo].[simulation]    Script Date: 10/07/2013 14:56:32 ******/
    SET ANSI_NULLS ON
    GO

    SET QUOTED_IDENTIFIER ON
    GO

    CREATE TABLE [dbo].[simulation](
        [SimulationId] [bigint] IDENTITY(1,1) NOT NULL,
        [baseLocative] [float] NULL,
        [txInt] [float] NULL,
        [txVr] [float] NULL,
        [Autofin] [float] NULL,
        [echjour] [tinyint] NOT NULL,
        [duree] [tinyint] NOT NULL,
        [echBegin] [date] NOT NULL,
        [loy1] [float] NULL,
        [loy1_freq] [tinyint] NULL,
        [encfin] [float] NULL,
        [loyer] [float] NULL,
        [txtva] [float] NULL,
        [perio] [tinyint] NOT NULL,
        [ratio] [numeric](9, 3) NULL,
     CONSTRAINT [PK_simulation] PRIMARY KEY CLUSTERED
    (
        [SimulationId] ASC
    )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
    ) ON [PRIMARY]

    GO

    Monday, October 7, 2013 1:56 PM
  • Hi intibnin;

    Can you run the application and when it give the exception in the dialog box that pops up click on Show Data or something to that effect. A new dialog box opens, open the tree object and post here the exception message and inner exception message as it appears in the dialog box. Also post the code at where it occurred.

      


    Fernando (MCSD)

    If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".

    NOTE: If I ask for code, please provide something that I can drop directly into a project and run (including XAML), or an actual application project. I'm trying to help a lot of people, so I don't have time to figure out weird snippets with undefined objects and unknown namespaces.

    Monday, October 7, 2013 2:06 PM
  • Hi Fernando,

    I have a customized MessageBox, but here a log (it was in french, i translated it) :

    2013-10-07 15:15:44,023 [10] INFO  Winlease.CrossCutting.Logging.Logger: [Méthode : SimulationManagementService.Add()] : Erreur --> View following exception:
    2013-10-07 15:15:44,026 [10] ERROR Winlease.CrossCutting.Logging.Logger: invalid column name : 'simulation_SimulationId'.
     Stacktrace :    à Winlease.Infrastructure.Data.Core.UnitOfWork.Commit() in d:\Winlease-ddd\Winlease-ddd\Winlease.Infrastructure.Data.MainModule\UnitOfWork\UnitOfWork.cs:line 120
       à Winlease.Infrastructure.Data.Core.Repository`1.Add(T item) dans d:\Winlease-ddd\Winlease-ddd\Winlease.Infrastructure.Data.Core\Repository.cs:line 38
       à Winlease.Infrastructure.Data.MainModule.SimulationRepository.Add(simulation item) in d:\Winlease-ddd\Winlease-ddd\Winlease.Infrastructure.Data.MainModule\Repositories\SimulationRepository.cs:line 63
       à WinLease.Application.MainModule.SimulationManagementService.Add(simulation ss) in d:\Winlease-ddd\Winlease-ddd\WinLease.Application\SimulationManagementService.cs:line 39

    base on this log, you can ask me for the portion of code you want. if we don't succeed, i will try reproduce error in a simple application without styles and custom controls, just data access.

    Monday, October 7, 2013 2:31 PM
  • Hi intibnin;

    I was really hoping to see the inner exception on the problem. I have never seen a situation where in Code First you defined a column name one way and that being assigned in the database table the same way but an error message saying that the column name is something different. The only time I have seen Entity Framework change names like this was in the navigation properties where the navigation property has the same name so it had added a 1 to the end of the name as in a reference from the entity to itself as in a self reference.

    What is the code associated with this logging message:

    2013-10-07 15:15:44,026 [10] ERROR Winlease.CrossCutting.Logging.Logger: invalid column name : 'simulation_SimulationId'. 

      


    Fernando (MCSD)

    If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".

    NOTE: If I ask for code, please provide something that I can drop directly into a project and run (including XAML), or an actual application project. I'm trying to help a lot of people, so I don't have time to figure out weird snippets with undefined objects and unknown namespaces.

    Monday, October 7, 2013 2:48 PM
  • Hi Fernando,

    Sorry, I tiered you.

    Actually, in my application, i followed DDD approach, so the application is layered and tracing nested exception is difficult.

    The code associated with the message above is :

     public class SimulationManagementService
        {
           
            ILogger _logger;
            ISimulationRepository _simulationRepository;
    
            public SimulationManagementService(ISimulationRepository sr, ILogger logger)
            {
                
                _logger = logger;
                _simulationRepository = sr;
            }
    
    
    //********************The code causing the exception
           //Add a new simulation
      public Boolean Add(simulation ss)
            {
                Boolean code = true;
    
                try
                {
               
                    _simulationRepository.Add(ss);
                    _logger.LogInfoMessage("[Méthode : SimulationManagementService.Add()] : effectué avec succès.");
                }
                catch (Exception ex)
                {
                    code=false;
                    _logger.LogInfoMessage("[Méthode : SimulationManagementService.Add()] : Erreur --> View following exception");
                    _logger.LogException(ex);
                    throw ex;
                }
    
                return code;
            }

    And in simulationform :

     //Saving current simulation
    private void SimulationSave()
    {
              
    string msgModify = "Modification de la simulation effectuée avec succès.";
    string  msgNew = "Enregistrement effectué avec succès.";
    string msgError = "Erreur d'enregistrement de la simulation";
    //string errorTitle = "Erreur d'enregistrement";
    string saveTitle = "Enregistrement";
    GetSMSInstance();
    try
     {
        if (_currentSimulation.SimulationId == 0)
             {
       txDescription.Text = "Saving  new simulation...";
       if (sms.Add(_currentSimulation))
       MessageBox.Show(msgNew, saveTitle, MessageBoxButton.OK);
                    else
    UICommon.ShowErrorMessage(msgError);
                    }
                    else
                        if (sms.Modify(_currentSimulation))
                            MessageBox.Show(msgModify, saveTitle, MessageBoxButton.OK);
                        else
    UICommon.ShowErrorMessage(msgError);
                }
    
                catch (Exception ex)
                {
                    UICommon.ShowErrorMessage(ex);
                    
                }
            }
    

    here the code for instancing global variables

    private void GetSMSInstance()
          {
                if (_SimulationRepository == null)
                   _SimulationRepository = new SimulationRepository(_currentUoW);
                if (sms == null)
                sms = new SimulationManagementService(_SimulationRepository, _currentLog);
                
    }
    
    //Gestionnaire de l'évènement LOADED de la fenêtre
    private void Window_Loaded_1(object sender, RoutedEventArgs e)
            {
                _currentSimulation = new simulation();
                masterGrid.DataContext = _currentSimulation;
                if (_currentLog != null)
                    _currentLog.LogInfoMessage("Starting  <Simulationform.xaml>.");
                InitParametersFields();
                InitDateFormat();
              
            }



    • Edited by intibnin Monday, October 7, 2013 3:15 PM
    Monday, October 7, 2013 3:14 PM
  •   

    Sorry but as I said I have not seen this issue before and could not tell where to look next. Hopefully someone else will look into this question who may have seen this before. Good luck.

      


    Fernando (MCSD)

    If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".

    NOTE: If I ask for code, please provide something that I can drop directly into a project and run (including XAML), or an actual application project. I'm trying to help a lot of people, so I don't have time to figure out weird snippets with undefined objects and unknown namespaces.

    Monday, October 7, 2013 3:23 PM
  • I am trying to reproduce this in a simple wpf form, if it still happens, i will post it again. So, If you can take a look later.

    Thank you very much...

    Monday, October 7, 2013 3:35 PM
  • Hi intibnin,

    With your simple sample, we test it and have a reply for it.

    The post link:

    http://social.msdn.microsoft.com/Forums/en-US/84dc7e64-f9ae-4a5a-93e9-ef8902c98ca6/entity-framework-5-invalid-column-name?forum=adodotnetentityframework

    Regards.


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Wednesday, October 9, 2013 9:02 AM
    Moderator