Invalid column name in entity frameworkRSS

• • Reply
  kstMan

  Member

  2 Points

  21 Posts

  Invalid column name in entity framework

  Dec 10, 2018 06:17 PM|kstMan|LINK

  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.

• • Reply
  mgebhard

  All-Star

  39931 Points

  16610 Posts

  Re: Invalid column name in entity framework

  Dec 10, 2018 06:30 PM|mgebhard|LINK

  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.

• • Reply
  kstMan

  Member

  2 Points

  21 Posts

  Re: Invalid column name in entity framework

  Dec 10, 2018 07:03 PM|kstMan|LINK

  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?

• • Reply
  mgebhard

  All-Star

  39931 Points

  16610 Posts

  Re: Invalid column name in entity framework

  Dec 10, 2018 07:18 PM|mgebhard|LINK

  kstMan

  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.

• • Reply
  yogyogi

  Participant

  890 Points

  611 Posts

  Re: Invalid column name in entity framework

  Dec 10, 2018 08:22 PM|yogyogi|LINK

  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

  Helping you always. Don't forget to click "Mark as Answer" on the post that helped you.
  
• • Reply
  Zhi Lv - MSF...

  All-Star

  45459 Points

  7008 Posts

  Microsoft

  Re: Invalid column name in entity framework

  Dec 11, 2018 02:14 AM|Zhi Lv - MSFT|LINK

  Hi kstMan,

  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

  MSDN Community Support
  Please remember to click "Mark as Answer" the responses that resolved your issue.
  If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.
  
• • Reply
  PatriceSc

  All-Star

  44330 Points

  15721 Posts

  Re: Invalid column name in entity framework

  Dec 11, 2018 08:33 AM|PatriceSc|LINK

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

• • Reply
  kstMan

  Member

  2 Points

  21 Posts

  Re: Invalid column name in entity framework

  Dec 11, 2018 06:04 PM|kstMan|LINK

  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. 

• • Reply
  mgebhard

  All-Star

  39931 Points

  16610 Posts

  Re: Invalid column name in entity framework

  Dec 11, 2018 06:19 PM|mgebhard|LINK

  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

• • Reply
  kstMan

  Member

  2 Points

  21 Posts

  Re: Invalid column name in entity framework

  Dec 11, 2018 06:26 PM|kstMan|LINK

  Ok I will try to customize

• • Reply
  mgebhard

  All-Star

  39931 Points

  16610 Posts

  Re: Invalid column name in entity framework

  Dec 11, 2018 06:35 PM|mgebhard|LINK

  kstMan

  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.