CODEWITHSUNDEEP
c#entity-frameworkef-code-first
Damian
Damian

Reputation: 2789

EF Code First Foreign Key Same Table

I have an entity which I want to map using EF Code First's Fluent Configuration:

class Transaction
{
    int LineItemId { get; set; }
    string TranNumber { get; set; }
    string TaxLotId { get; set; }
    string TradeLeg { get; set; }

    public virtual Transaction NewDealTransaction { get; set; }
    public virtual ICollection<GvaTransactions> RelatedTransactions { get; set; }
}

Edit There a single table which hold this data (non-relevant columns are omitted for the sake of brevity).

CREATE TABLE [dbo].[Transactions] (
    [LineItemId]               INT           IDENTITY (1, 1) NOT NULL,
    [TranNumber]               VARCHAR (20)  NOT NULL,
    [TaxLotId]                 VARCHAR (20)  NULL,
    [TradeLeg]                 VARCHAR (20)  NULL,
    CONSTRAINT [PK_GVATransactions] PRIMARY KEY CLUSTERED ([LineItemID] ASC)
)

The LineItemId is the primary key.

The RelatedTransactions relation should represent the fact that all Transactions with the same TaxLotId are related.

And finally NewDealTransaction is a transaction with same TaxLotId and `TradeLeg == "NewDeal".

How can I write a fluent mapping to represent these relationships?

NOTE: My preference here is not to change the backend as this is for an existing table design I inherited which has a lot of data already.

Upvotes: 0

Views: 537

Answers (1)

Magnus Johansson
Magnus Johansson

Reputation: 28325

What you are asking for is not something that the foreign key relationships (and thus Entity Framework relationsships/navigation properties) are designed for.
They are meant for keeping data integrity, not for filtering on the data.

To query and work with records that are related (according to your definition), you will simply query in such a manner:
Related records: 

var relatedRecords = context.Transactions.Where(t=>t.TaxLotId == "something");

New deals: 

var newDeals = context.Transactions.Where(t=>t.TaxLotId == "something" && t.TradeLeg == "NewDeal");

For this to be efficient, I would recommend a composite index on the columns TaxLotId and TradeLeg

Upvotes: 1

Related Questions