locked
Invalid column name in entity framework RRS feed

  • Question

  • User-562039579 posted

    Hello, I have an ASP application.I performed the tutorial on code first migrations. When I executed the commands, the following code was generated for the user table:

    CreateTable(
                "dbo.AspNetUsers",
                c => new
                    {
                        Id = c.String(nullable: false, maxLength: 128),
                        ApplicationId = c.Guid(nullable: false),    
                        Email = c.String(maxLength: 256),
                        EmailConfirmed = c.Boolean(nullable: false),
                        PasswordHash = c.String(),
                        SecurityStamp = c.String(),
                        PhoneNumber = c.String(),
                        PhoneNumberConfirmed = c.Boolean(nullable: false),
                        TwoFactorEnabled = c.Boolean(nullable: false),
                        LockoutEndDateUtc = c.DateTime(),
                        LockoutEnabled = c.Boolean(nullable: false),
                        AccessFailedCount = c.Int(nullable: true),
                        UserName = c.String(nullable: false, maxLength: 256),
                        Discriminator = c.String(),
                        LegacyPasswordHash = c.String(),
                })
                .PrimaryKey(t => t.Id)
                .Index(t => t.UserName, unique: true, name: "UserNameIndex");

    My problem is that I don't need some columns. So, I removed the code from those columns and updated my DB. But, when I try to authenticate a user, I get this error:

    Invalid column name 'EmailConfirmed'.
    Invalid column name 'PhoneNumberConfirmed'.
    Invalid column name 'TwoFactorEnabled'.
    Invalid column name 'LockoutEndDateUtc'.
    Invalid column name 'LockoutEnabled'.
    Invalid column name 'AccessFailedCount'.

    But these columns no longer exist because I deleted them. Or, is it possible to put it back and assign them default values ​​for example:

    EmailConfirmed = false
    PhoneNumberConfirmed = false

    to avoid making an insertion when creating a user. I use EF 6.2.0

    Need help please.

    Monday, December 10, 2018 6:17 PM

All replies

  • User475983607 posted

    You removed the columns from the script that builds the table but did not remove the model properties that generated the script.

    I would start over and ignore the columns you are not planning to use.

    Monday, December 10, 2018 6:30 PM
  • User-562039579 posted

    Please Mgebhard, what do yo mean by "the model properties that generated the script". Does that mean that after I execute the command that generates the script, I have to modify the script before continuing?

    Monday, December 10, 2018 7:03 PM
  • User475983607 posted

    Please Mgebhard, what do yo mean by "the model properties that generated the script". Does that mean that after I execute the command that generates the script, I have to modify the script before continuing?

    Migration scripts are generated from Entity Models.  The script was updated but not the model.  

    Again, simply start over and ignore the columns you do not plan to use.

    Monday, December 10, 2018 7:18 PM
  • User-2054057000 posted

    If you don't need a database table's column to be created for a property then give [NotMapped] attribute to that property. See this tutorial- Configurations in Entity Framework Core

    Monday, December 10, 2018 8:22 PM
  • User-271186128 posted

    Hi kstMan,

    My problem is that I don't need some columns. So, I removed the code from those columns and updated my DB. But, when I try to authenticate a user, I get this error:

    As yogyogi said, you can apply the [NotMapped] attribute on one or more properties for which you do NOT want to create a corresponding column in a database table. This attribute applies to EF 6 and EF core.

    Code as below:

    using System.ComponentModel.DataAnnotations.Schema;
    
    public class Student
    {
        public int StudentId { get; set; }
        public string StudentName { get; set; }
            
        [NotMapped]
        public int Age { get; set; }
    }

    More details about the [NotMapped] attribute, please check this article.

    Best regards,
    Dillion

    Tuesday, December 11, 2018 2:14 AM
  • User753101303 posted

    Hi,

    https://docs.microsoft.com/en-us/aspnet/identity/overview/extensibility/overview-of-custom-storage-providers-for-aspnet-identity covers custom storage but is also interesting to understand the overall architecture. Rather than using the default implementation that covers all features you could also implement just those features you need.

    It could be a bit cleaner than using the full implementation and trying to ignore/delete some columns...

    Tuesday, December 11, 2018 8:33 AM
  • User-562039579 posted

    Thank you for the replies. As suggested, I have tried :

    public class ApplicationUser : IdentityUser{
    [NotMapped]
    bool PhoneNumberConfirmed { get; set; }
    
    [NotMapped]
    public bool TwoFactorEnabled { get; set; }
    
    [NotMapped]
    public Nullable<DateTime> LockoutEndDateUtc { get; set; }
    
    [NotMapped]
    public bool LockoutEnabled { get; set; }
    
    [NotMapped]
    public int AccessFailedCount { get; set; }
    
    [NotMapped]
    public bool EmailConfirmed { get; set; }
    }

    And also:

     public class ApplicationUser: IdentityUser{
    [NotMapped]
    public override bool PhoneNumberConfirmed { get; set; }
    
    [NotMapped]
    public override bool TwoFactorEnabled { get; set; }
    
    [NotMapped]
    public override Nullable<DateTime> LockoutEndDateUtc { get; set; }
    
    [NotMapped]
    public override bool LockoutEnabled { get; set; }
    
    [NotMapped]
    public override int AccessFailedCount { get; set; }
    
    [NotMapped]
    public override bool EmailConfirmed { get; set; }
    }

    because the columns are inherited from identityUser, but it doesn't work. 

    Tuesday, December 11, 2018 6:04 PM
  • User475983607 posted

    And the expected results. 

    Either ignore the ASP Identity feature you are not interested in used or customize Identity.  Keep in mind fields you are trying exclude are used in other parts of the Identity API.  These are features like two-factor authentication,  Phone number store, and user lockout.  So simply excluding a few properties does not really remove the features.

    I recommend customizing Identity to suite your needs and only implement the stores that you need.

    https://docs.microsoft.com/en-us/aspnet/identity/overview/extensibility/overview-of-custom-storage-providers-for-aspnet-identity

    Tuesday, December 11, 2018 6:19 PM
  • User-562039579 posted

    Ok I will try to customize

    Tuesday, December 11, 2018 6:26 PM
  • User475983607 posted

    Ok I will try to customize

    It's far easier to take advantage of the features you are interested in implementing and simply do not use or configure the features that you are not interested in.  In the end it is up to you but crafting a custom Identity implementation has a steep learning curve depending on your programming experience.

    Tuesday, December 11, 2018 6:35 PM