Changing field that as Foreign key throws error on update DB : The Official Microsoft ASP.NET Forums

Reply

GorillaMann

Member

117 Points

332 Posts

Changing field that as Foreign key throws error on update DB

Nov 28, 2012 01:12 PM|LINK

Hi, whats the best way to go about fixing errors like this, I tried to run update db

Column 'dbo.CustomProfiles.UserId' is not the same data type as referencing column 'Companies.CustomProfile_UserId' in foreign key 'FK_dbo.Companies_dbo.CustomProfiles_CustomProfile_UserId'.
Could not create constraint. See previous errors.

public class Company
    {
        [Key]
        public int Id { get; set; }
        
        [Required]
        public Guid UserId { get; set; }

        public DateTime DateCreated { get; set; }
        public string Description { get; set; }
        public decimal InvestmentSought { get; set; }
        public decimal InvestmentFound { get; set; }
        public string LogoFileName { get; set; }
        [Required]
        [DisplayName("Company name")]
        [StringLength(100, ErrorMessage = "{0} cannot exceed {1} characters")]
        public string CompanyName { get; set; }

        public string FundingCompleted()
        {
            return ((InvestmentFound/InvestmentSought)*100).ToString(CultureInfo.InvariantCulture) + '%';
        }
        public Company(Guid userId)
        {
            this.UserId = userId;
            DateCreated = DateTime.Now;
        }

 public class CustomProfile
    {

        [Key]
        [Required]
        public Guid UserId { get; set; }

        public string FullName { get; set; }
        [DataType(DataType.MultilineText)]
        public string MiniAbout { get; set; }
        public Address HomeAddress { get; set; }

        public Company Company { get; set; }
        public Investor Investor { get; set; }
        public List<Company> CompaniesSupported { get; set; }
        public byte[] ProfileImage { get; set; }

      

        public CustomProfile(Guid UserId)
        {
            this.UserId = UserId;
        }

migration:

    public partial class cptoguid : DbMigration
    {
        public override void Up()
        {
            DropForeignKey("dbo.Companies", "CustomProfile_Username", "dbo.CustomProfiles");
            DropForeignKey("dbo.Companies", "CustomProfile_Username1", "dbo.CustomProfiles");
            DropIndex("dbo.Companies", new[] { "CustomProfile_Username" });
            DropIndex("dbo.Companies", new[] { "CustomProfile_Username1" });
            RenameColumn(table: "dbo.Companies", name: "CustomProfile_Username", newName: "CustomProfile_UserId");
            AddColumn("dbo.Companies", "UserId", c => c.Guid(nullable: false));
            AddColumn("dbo.CustomProfiles", "UserId", c => c.Guid(nullable: false));
            DropPrimaryKey("dbo.CustomProfiles", new[] { "Username" });
            AddPrimaryKey("dbo.CustomProfiles", "UserId");
            AddForeignKey("dbo.Companies", "CustomProfile_UserId", "dbo.CustomProfiles", "UserId");
            CreateIndex("dbo.Companies", "CustomProfile_UserId");
            DropColumn("dbo.Companies", "CustomProfile_Username1");
            DropColumn("dbo.CustomProfiles", "Username");
        }
        
        public override void Down()
        {
            AddColumn("dbo.CustomProfiles", "Username", c => c.String(nullable: false, maxLength: 128));
            AddColumn("dbo.Companies", "CustomProfile_Username1", c => c.String(maxLength: 128));
            DropIndex("dbo.Companies", new[] { "CustomProfile_UserId" });
            DropForeignKey("dbo.Companies", "CustomProfile_UserId", "dbo.CustomProfiles");
            DropPrimaryKey("dbo.CustomProfiles", new[] { "UserId" });
            AddPrimaryKey("dbo.CustomProfiles", "Username");
            DropColumn("dbo.CustomProfiles", "UserId");
            DropColumn("dbo.Companies", "UserId");
            RenameColumn(table: "dbo.Companies", name: "CustomProfile_UserId", newName: "CustomProfile_Username");
            CreateIndex("dbo.Companies", "CustomProfile_Username1");
            CreateIndex("dbo.Companies", "CustomProfile_Username");
            AddForeignKey("dbo.Companies", "CustomProfile_Username1", "dbo.CustomProfiles", "Username");
            AddForeignKey("dbo.Companies", "CustomProfile_Username", "dbo.CustomProfiles", "Username");
        }
    }

Edited by GorillaMann on Nov 28, 2012 05:36 PM

Reply

Decker Dong ...

All-Star

118619 Points

18779 Posts

Re: Changing field that as Foreign key throws error on update DB

Nov 29, 2012 12:48 AM|LINK

GorillaMann
'dbo.CustomProfiles.UserId'

Hi,

This error means that your real table's UserId isn't Guid or something related to that. So please change to a correct type such as Int or anything else.

Regurds & Thanks!

Edited by GorillaMann on Nov 29, 2012 08:47 AM
Edited by decker dong - msft on Dec 04, 2012 03:55 AM
Marked as answer by decker dong - msft on Dec 04, 2012 03:55 AM

Reply

GorillaMann

Member

117 Points

332 Posts

Re: Changing field that as Foreign key throws error on update DB

Nov 29, 2012 08:49 AM|LINK

yo!

atm the DB schema looks like this: so I 'm not sure what to do to fix, I usually delete the DB and start again when having issues like this in EF :(

CREATE TABLE [dbo].[CustomProfiles] (
    [Username]       NVARCHAR (128)  NOT NULL,
    [FullName]       NVARCHAR (MAX)  NULL,
    [ProfileImage]   VARBINARY (MAX) NULL,
    [Company_Id]     INT             NULL,
    [Investor_Id]    INT             NULL,
    [HomeAddress_Id] INT             NULL,
    [MiniAbout]      NVARCHAR (MAX)  NULL,
    CONSTRAINT [PK_dbo.CustomProfiles] PRIMARY KEY CLUSTERED ([Username] ASC),
    CONSTRAINT [FK_dbo.CustomProfiles_dbo.Companies_Company_Id] FOREIGN KEY ([Company_Id]) REFERENCES [dbo].[Companies] ([Id]),
    CONSTRAINT [FK_dbo.CustomProfiles_dbo.Investors_Investor_Id] FOREIGN KEY ([Investor_Id]) REFERENCES [dbo].[Investors] ([Id]),
    CONSTRAINT [FK_dbo.CustomProfiles_dbo.Addresses_HomeAddress_Id] FOREIGN KEY ([HomeAddress_Id]) REFERENCES [dbo].[Addresses] ([Id])
);


GO
CREATE NONCLUSTERED INDEX [IX_Company_Id]
    ON [dbo].[CustomProfiles]([Company_Id] ASC);


GO
CREATE NONCLUSTERED INDEX [IX_Investor_Id]
    ON [dbo].[CustomProfiles]([Investor_Id] ASC);


GO
CREATE NONCLUSTERED INDEX [IX_HomeAddress_Id]
    ON [dbo].[CustomProfiles]([HomeAddress_Id] ASC);

Edited by GorillaMann on Nov 29, 2012 08:50 AM

Reply

GorillaMann

Member

117 Points

332 Posts

Re: Changing field that as Foreign key throws error on update DB

Nov 29, 2012 09:38 AM|LINK

I think this is the issue, I don't want Companies to reference CustomProfile anymore.

RenameColumn(table: "dbo.Companies", name: "CustomProfile_Username", newName: "CustomProfile_UserId");

I take it I need to manually edit the fluent migrations file?

Edited by GorillaMann on Nov 29, 2012 09:38 AM

Reply

GorillaMann

Member

117 Points

332 Posts

Re: Changing field that as Foreign key throws error on update DB

Nov 29, 2012 04:53 PM|LINK

I made my own fluent changes to remove the bad stuff and it now works

public override void Up()
        {
            DropForeignKey("dbo.Companies", "CustomProfile_Username", "dbo.CustomProfiles");
            DropForeignKey("dbo.Companies", "CustomProfile_Username1", "dbo.CustomProfiles");
            DropIndex("dbo.Companies", new[] { "CustomProfile_Username" });
            DropIndex("dbo.Companies", new[] { "CustomProfile_Username1" });
            RenameColumn(table: "dbo.Companies", name: "CustomProfile_Username", newName: "CustomProfile_UserId");
            AddColumn("dbo.Companies", "UserId", c => c.Guid(nullable: false));
            AddColumn("dbo.CustomProfiles", "UserId", c => c.Guid(nullable: false));
            DropPrimaryKey("dbo.CustomProfiles", new[] { "Username" });
            AddPrimaryKey("dbo.CustomProfiles", "UserId");
          //  AddForeignKey("dbo.Companies", "CustomProfile_UserId", "dbo.CustomProfiles", "UserId");
            CreateIndex("dbo.Companies", "CustomProfile_UserId");
            DropColumn("dbo.Companies", "CustomProfile_Username1");
            DropColumn("dbo.CustomProfiles", "Username");
        }
        


and

    public partial class t : DbMigration
    {
        public override void Up()
        {
            DropIndex("dbo.Companies", new[] { "CustomProfile_UserId" });
            DropColumn("dbo.Companies", "CustomProfile_UserId");
        }

        public override void Down()
        {
            AddColumn("dbo.Companies", "CustomProfile_UserId", c => c.String(maxLength: 128));
            CreateIndex("dbo.Companies", "CustomProfile_UserId");
          
        }
    }

Marked as answer by GorillaMann on Nov 29, 2012 04:54 PM

Reply

GorillaMann

Member

117 Points

332 Posts

Re: Changing field that as Foreign key throws error on update DB

Nov 29, 2012 05:49 PM|LINK

rats now I'm getting this error:

Invalid column name 'CustomProfile_UserId'.

Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.Data.SqlClient.SqlException: Invalid column name 'CustomProfile_UserId'.

Source Error:


Line 36:         {
Line 37:             var c = context.Companies.Add(company);
Line 38:             context.SaveChanges();
Line 39:             return c;

Some how EF is still using the model incorrectly:

    public class Company
    {
        [Key]
        public int Id { get; set; }
        
        [Required]
        public Guid UserId { get; set; }

        public DateTime DateCreated { get; set; }
        public string Description { get; set; }
        public decimal InvestmentSought { get; set; }
        public decimal InvestmentFound { get; set; }
        public string LogoFileName { get; set; }
        [Required]
        [DisplayName("Company name")]
        [StringLength(100, ErrorMessage = "{0} cannot exceed {1} characters")]
        public string CompanyName { get; set; }

        public string FundingCompleted()
        {
            return ((InvestmentFound/InvestmentSought)*100).ToString(CultureInfo.InvariantCulture) + '%';
        }
        public Company(Guid userId)
        {
            this.UserId = userId;
            DateCreated = DateTime.Now;
        }
        public Company()
        {
            
            DateCreated = DateTime.Now;
        }

    }

Reply

GorillaMann

Member

117 Points

332 Posts

Re: Changing field that as Foreign key throws error on update DB

Nov 29, 2012 08:01 PM|LINK

I ran a text search accross the solution with text crawler and CustomProfile_UserId only appears in 2 migrations files

Reply

GorillaMann

Member

117 Points

332 Posts

Re: Changing field that as Foreign key throws error on update DB

Nov 30, 2012 04:58 PM|LINK

I figured it out, EF was looking for CustomProfile_UserId

because of this, Solved!

public List<Company> CompaniesSupported { get; set; }

public class CustomProfile
    {

        [Key]
        [Required]
        public Guid UserId { get; set; }

        public string FullName { get; set; }
        [DataType(DataType.MultilineText)]
        public string MiniAbout { get; set; }
        public Address HomeAddress { get; set; }

        public Company Company { get; set; }
        public Investor Investor { get; set; }
        public List<Company> CompaniesSupported { get; set; }
        public byte[] ProfileImage { get; set; }

      

        public CustomProfile(Guid UserId)
        {
            this.UserId = UserId;
        }
        public CustomProfile()
        {
        }

    }

Marked as answer by decker dong - msft on Dec 01, 2012 02:05 AM

Changing field that as Foreign key throws error on update DB RSS

7 replies

GorillaMann

Changing field that as Foreign key throws error on update DB

Decker Dong ...

Re: Changing field that as Foreign key throws error on update DB

GorillaMann

GorillaMann

Re: Changing field that as Foreign key throws error on update DB

GorillaMann

Re: Changing field that as Foreign key throws error on update DB

GorillaMann

Re: Changing field that as Foreign key throws error on update DB

GorillaMann

Re: Changing field that as Foreign key throws error on update DB

GorillaMann

Re: Changing field that as Foreign key throws error on update DB

GorillaMann

Re: Changing field that as Foreign key throws error on update DB

This site is managed for Microsoft by Neudesic, LLC. | © 2013 Microsoft. All rights reserved.

Follow Us On:

Microsoft