EF core code first, loose foreign key contraint after making it nullable

0
votes

I had this:

public class Customer
{
    public int Id {get; set;}
    //some more properties
}

public class Payment
{
    public int Id {get; set;}
    public int CustomerId {get; set;} //foreign key
    public Customer Customer {get; set;}
}

Which lead to a foreign key constraint in the database. Then I made the CustomerId nullable:

public class Payment
{
    public int Id {get; set;}
    public int? CustomerId {get; set;} //foreign key
    public Customer Customer {get; set;}
}

If I generate the migration script, I get something like this:

IF NOT EXISTS(SELECT * FROM [__EFMigrationsHistory] WHERE [MigrationId] = N'MyMigration')
BEGIN

DECLARE @var2 sysname;

    SELECT @var2 = [d].[name]
      FROM [sys].[default_constraints] [d]
INNER JOIN [sys].[columns] [c] ON [d].[parent_column_id] = [c].[column_id] 
       AND [d].[parent_object_id] = [c].[object_id]
     WHERE ([d].[parent_object_id] = OBJECT_ID(N'[Payments]') AND [c].[name] = N'CustomerId');

IF @var2 IS NOT NULL EXEC(N'ALTER TABLE [Payments] 
    DROP CONSTRAINT [' + @var2 + '];');

ALTER TABLE [Payments] ALTER COLUMN [CustomerId] int NULL;
END;

GO

So now column Payments.CustomerId is nullable, but the foreign key constraint is removed. How can I get the foreign key constraint back?

edit: One more thing. Between my code changes, I also added this in protected override void OnModelCreating(ModelBuilder modelBuilder):

new PaymentMap(modelBuilder.Entity<Payment>());
4
c#ef-code-firstef-core-2.1
Have you tried to add Public virtual ICollection<Payment> Payments {get;set;} inside Customer class? - Szymon Tomczyk
The script looks incomplete. My test continues with ALTER TABLE [Payments] ADD CONSTRAINT [FK_Payments_Customers_CustomerId] FOREIGN KEY ([CustomerId]) REFERENCES [Customers] ([Id]) ON DELETE NO ACTION; and then inserting migration history record. - Ivan Stoev
The script is incomplete, but mine lacks the part with "ADD CONSTRAINT [FK_Payments_Customers_CustomerId] FOREIGN KEY...". That's exactly why I asked my question here - Richard
I've tested with EF Core 2.1.4 / SqlServer, what is your environment? - Ivan Stoev

4 Answers

0
votes

You need to annotate it with System.ComponentModel.DataAnnotations.Schema.ForeignKeyAttribute explicitly:

public class Payment
{
    public int Id {get; set;}

    public int? CustomerId {get; set;} //foreign key

    [ForeignKey("CustomerId")]
    public Customer Customer {get; set;}
}
0
votes

In order to make it work you should add public virtual ICollection<Tenant> Payments { get; set;} to Customer class

Blockquote

public class Customer
{
    public int Id { get; set; }
    public virtual ICollection<Payment> Payments { get; set; }
}

public class Payment
{
    public int PaymentId{ get; set; }
    public int? CustomerId { get; set; }
    public Customer Customer { get; set; }
}
0
votes

As EF Core requires manually making the connections between tables, have you tried to write them down in your DataContext class?

Add List<Payment> Payments in your Customer class and in your DataContext class in the OnModelCreate override method add:

builder
     .Entity<Payment>()
     .HasOne(p => p.Customer)
     .WithMany(c => c.Payments)
     .HasForeignKey(p => p.CustomerId);
0
votes

I dropped my local database and re-created it with "update-database". That solved my problem. Thanks everybody for their time and effort!