EF Core: Optional one-to-one foreign key relation from content entity

Question

I've implemented some database context for my .net core blazor application. The database context has access to an external database (no database migration etc.)

Now my problem is that I am not sure how to define a foreign key using fluent api or data attributes when the parent table contains the foreign key for the table.

As simplified example: I've got a transactions entity with data like this:

[Table("transactions")]
public class Transaction
{
  [Key]
  [Column("id")]
  public int Id { get; set; }

  [Column("trans_num")]
  public string TransNum { get; set; }

  [Column("shop_id")]
  public int? ShopId { get; set; }

  [Column("total_amount")]
  public decimal TotalAmount { get; set; }

  public Shop Shop { get; set;}
}

And some shop entity with data like this:

[Table("shops")]
public class Shop
{
  [Key]
  [Column("id")]
  public int Id { get; set; }

  [Column("shop_name")]
  public string ShopName{ get; set; }

  public Transaction Transaction { get; set;}
}

As the models indicate, the "shop_id" is the foreign key.

So ... I've got no transaction reference within my shop entity. Also, in my productive scenario I've got some optional relations like this, meaning for example shop_id would be null.

How would I indicate the optional relation to my model builder?

Best regards

One-to-one relatioship. But it's optional, so one-to-none-or-one — epanalepsis, Jan 11 '20 at 19:06
this will help you to set one-one or one-to-one-or-none relationship https://www.entityframeworktutorial.net/efcore/configure-one-to-one-relationship-using-fluent-api-in-ef-core.aspx — divyang4481, Jan 11 '20 at 19:14
Unfortunately this is the other way round ... which is why I am asking here. In the example you provided the StudentAddress entity contains the foreign key. Which would be normal ... but in this case I've got my transaction entity which I want to fetch and have the optional Shop information if the foreign key field within transactions is set. I don't have a foreign key within my shop entity. — epanalepsis, Jan 11 '20 at 19:18
in that case, you relationship is maybe one-to-none-or-one-or-many. you can refer this link https://stackoverflow.com/questions/40291466/entity-framework-core-nullable-foreign-key — divyang4481, Jan 11 '20 at 19:24
It's clear, that my ShopId Property within my Transaction Model must be nullable. But still I don't know how to setup this relation within my model builder. :/ — epanalepsis, Jan 11 '20 at 19:31

divyang4481 · Accepted Answer · 2020-01-11T19:55:32.153

1

set optional FK in model builder

[Table("shops")]
public class Shop
{
  [Key]
  [Column("id")]
  public int Id { get; set; }

  [Column("shop_name")]
  public string ShopName{ get; set; }

  public virual ICollection<Transaction> Transactions { get; set;}
}

     modelBuilder.Entity<Shop>()
            .HasMany(c => c.Transactions)
            .WithOptional(c => c.Shop)
            .HasForeignKey(c => c.ShopId)
            .WillCascadeOnDelete(false);

if you are looking only for EF core then you can refer this link :

WithOptional with Entity Framework Core

edited Jan 11 '20 at 19:55

answered Jan 11 '20 at 19:34

divyang4481

1,584
16
32

I am able to receive the transaction using your solution but my navigational property on the transaction for the corresponding shop is null even though my foreign key is set to id "1". – epanalepsis Jan 11 '20 at 20:06
Oh, nevermind, forgot to add the lazy loading proxies. It's working like a charm now. Thank you very much! – epanalepsis Jan 11 '20 at 20:17

EF Core: Optional one-to-one foreign key relation from content entity

1 Answers1