0

I'm working with EF Core and a third-party Firebird 2.5 database and, for some reason, they decided to, rather than do a simple one-to-one relationship, create a single table with two columns that do that relationship itself, i.e.

STOCK
========
ID_STOCK(int)
more columns (and their datatypes)

STOCK_IDENTIFIER
========
ID_STOCK (int)
ID_IDENTIFIER (int)

STOCK_PRODUCT
========
ID_IDENTIFIER (int)
more columns (and their datatypes)

So, every STOCK has one STOCK_IDENTIFIER, which, in turn, has one STOCK_PRODUCT. Usually, when I'm creating my own DB in MySQL, I just set foreign keys with data annotations (I'm not fluent in Fluent API, pun intended) and let the migration do its job. However, in this case I can't change the DB's schema (on the account of it being third-party), so I need to use the existing structure.

Right now I have the following:

    public class STOCK
    {
        [Key]
        public int ID_STOCK { get; set; }
        [MaxLength(50)]
        public string DESCRIPTION { get; set; }
        [Column(TypeName = "decimal(18, 4)")]
        public decimal SELL_PRICE { get; set; }
        public STOCK_IDENTIFIER STOCK_IDENTIFIER{ get; set; }
    }

    public class STOCK_IDENTIFIER
    {
        [ForeignKey("ID_STOCK")]
        public STOCK ID_STOCK { get; set; }
        public STOCK_PRODUCT ID_PRODUCT { get; set; }
    }

    public class STOCK_PRODUCT
    {
        [ForeignKey("ID_PRODUCT")]
        public STOCK_IDENTIFIER ID_IDENTIFIER{ get; set; }
        [MaxLength(18)]
        public string GTIN{ get; set; }
        [MaxLength(18)]
        public string SKU{ get; set; }
        [Column(TypeName = "decimal(18, 4)")]
        public decimal INSTOCK_AMNT { get; set; }
    }

I read at The property X is of type Y which is not supported by current database provider that Fluent API could fix that, however, that article works flawlessly for one-to-one. As soon as I try to implement Fluent on a cascading relationship like this one, I get

            modelBuilder.Entity<STOCK_IDENTIFIER>()
                .HasOne<STOCK_PRODUCT>(p => p.ID_IDENTIFIER)
                .WithOne();

            modelBuilder.Entity<STOCK>()
                .HasOne<STOCK_IDENTIFIER>(p => p.IDENTIFICADOR)
                .WithOne();

The property or navigation 'ID_IDENTIFIER' cannot be added to the entity 
type 'STOCK_PRODUCT' because a property or navigation with the 
same name already exists on entity type 'STOCK_PRODUCT'.

Any hints on what I've been doing wrong?

Mark Rotteveel
  • 100,966
  • 191
  • 140
  • 197
Artur S.
  • 185
  • 3
  • 15
  • 1
    Are you sure `STOCK_IDENTIFIER` is one to one and always would be so? i feel this to be a forward extension point, one day they can "let loose" the seem, turn this shim into one to many, without remaking other tables. Meanwhile one ugly hack-around can be `alter STOCK add ID_PRODUCT_ID computed by (select si.ID_IDENTIFIER from STOCK_IDENTIFIER si where si.ID_STOCK = ID_STOCK)` or alternatively `create view STOCK_PROD_ID as select s.*, i.ID_IDENTIFIER from STOCK s join STOCK_IDENTIFIER i on i.ID_STOCK = s.ID_STOCK` and then try to use those in your app. Granted, by this simplification you... – Arioch 'The Sep 25 '20 at 08:56
  • 1
    ...you hardcode the one-to-one-to-one requirement and would it be unseemed your app would start failing. First approach is slower, but would one-to-many introduced - it should explicitly fail. Second approach is faster, but in one-to-many case it would produce sets product (multiply STOCK rows) instead of crashign and burning. – Arioch 'The Sep 25 '20 at 08:58
  • We don't need a permanent solution atm, so I think any hacks are good enough for now. That's the issue with working with 3rd party db's, and that's the main reason we are developing our own ERP Suite. I'll try your suggestions and get back to you. Thanks! – Artur S. Sep 25 '20 at 14:32

0 Answers0