Foreign Keys On Base Class in SingleTableInheritance

Hi, I have a base class and two derived classes using SingleTableInheritance. In the database of course, there is one single table. There are ForeignKeys defined on properties that are "common" - that is they are defined in the Base class. In the designer, I set it up so that the One-Many relationships are defined on the Base class. However, when I do a "Update From Source", the designer tells me to create the one-many relationship on both child classes. So, do I need to define the one-many on each of the child classes? Thanks - Imtiaz

No it should be fine to have the relationship defined on the base class if it is common to both. Are you able to post some details about the underlying DB schema for the two tables involved? Just trying to understand how the FK has been defined at the database level as this will be driving the designer behavior when you perform the Update From Source.

Jeremy

Hi Jeremy, Thanks! I made a stupid mistake! I am merging two tables into a single DB table, but using STI to have two separate LS Entities for the original two tables. But I forgot to drop the original two tables :) Thanks for your help! Imtiaz

I have a problem which I think is related to this.

I have recently split a master - child table pair into several inherited classes.

Eg. A table TransactionHeader, became TransactionHeader as an Abstract base class, and GLJournal, ARInvoice and ARPayment etc. as classes inherited from TransactionHeader.

Likewise a table TransactionRow, became TransactionRow as an Abstract base class, and GLJournalRow, ARInvoiceRow and ARPaymentRow etc. as classes inherited from TransactionRow.

Previously we had a single association from TransactionHeader.Id to TransactionRow.TransactionHeaderId in a one-to-many type.

I replaced this with each new class referencing its new row class, and in the Association properties set the Key Property Reference to the name of TransactionHeaderId. In the model all looked fine. However when I updated the database structure based on the model, the TransactionHeaderId field was deleted, but nothing added to replace it. So the model still contains the association, but the database actually doesn't.

I manually add the TransactionHeaderId field back in to the database, but had to delete all my rows of test data as the link tot he header table was gone.

When I updated the model based on the database, it re-added the association between the two base abstract classes (TransactionHeader and TransactionRow). Again all in the model looked fine, and I can add rows and all seems good, master and child records are saved.

However, when I try to do a LINQ query, I receive an error, Cannot find field THDocType for join. THDocType is an Int32 field in both tables which acts as the Discriminator.

The Link I used is

var ops =
                    from th in uow.GLJournals
                    join tr in uow.GLJournalRows on th.Id equals tr.TransactionHeaderId
                    where th.Reference == _reference
                    orderby tr.RowNumber
                    select new TransactionEntry
                               {
                                   BatchId = th.Reference,
                                   TransactionDate = th.TransactionDate,
                                   LineNo = tr.RowNumber,
                                   AccountCode = tr.AccountCode,
                                   AmountLocal = tr.Amount,
                                   AmountOther = tr.AmountOther,
                                   Description = tr.Description,
                               };

So I am not sure why I get this error. Did I do something wrong? Where should the associations be in the base class or in the inherited classes? All should use the same fiends for the association, i.e TH.Id and TR.TransactionHeader.Id

Many thanks for your help

I have been playing around with different options regarding foreign keys when we have multiple classes derived from a single base class, associated with several other classes all derived from a different base class.

First I tried to set the many to one association in the base class, but then I am unable to walk though the details rows in the master/detail relationship. i.e. If I open and invoice, I do not have a property which contains a collection of the invoice rows.

Next I tried to associate the derived classess, i.e. invoice (base:th) with invoicerow (base:tr) and creditnote (base:th) with creditnoterow (base:tr) etc. In the model this appears to be correct, but on updating the database from the model, the problems begin, firstly it creates a new bunch of foreign key fields, one for each association, even though there was already a FK field.

Not happy about this but I could live with it. But then comes the second problem, I am unable to set Cascading deletes, as the SQL Server complains about multiple paths. This I cannot live with, as I could end up with many orphaned detail records.

Next I tried to keep the associations created in try two,i.e. from derived class to derived class, but coercing the associations to all use the same FK, first I tried the Association Column Name field, but this complained the same FK was referenced more than once. So I tried to use the Key Property Reference, this deleted the foreign key fields all together when I updated the database.

This is becoming really frustrating. Can someone tell me what I am doing wrong. I do not think what I am doing is too radical, two tables (Master & Detail), portrayed as different derived classes, i.e. Invoice/InvoiceDetail, CreditNote/CreditNoteDetail etc. with a many to one association at the derived class level, complete with Cascading Updates and Deletes.

Many thanks for any and all help.

Is it possible for you to send through your model? That will help me understand a bit more about whats occurring here so I can try and offer some suggestions. You can email this through to us at support at mindscape.co.nz if needed.

Thanks!
Jeremy

I have sent the model to the support email address as requested.

Thanks

Mark