How do I map this relationship in EntityFramework and maintain a personal key-value to identify/query entries using DbContext.Find()

I've been rehauling my whole database because I was able to get an enterprise edition for my SQL server and it removed a lot of constraints that I struggled with when using the free version of SQL server. I'm trying to keep my tables clean and tight and it's raising more problems/questions…

1.) My Db stores StockMarket data. MY tables are :

TradingDay (list of all days that market is open) Stock (table for all the stocks in the market) StockDay (daily data for each stock) //has reference to Stock and TradingDay table StockIntraday (1 min interval data from open to close for each StockDay) //has reference to StockDay 

Then I have StockOptionData (Options are derivative contracts on the stock that let you pick a future day to trade the stock at different price levels…the price of the option moves like stock but not to the same degree because it's based on probablity and expectations ). Option data follows the same format. There is an Option table, an OptionDay Table and finally an OptionIntraday table.

Although I created tables in sqlserver first and then created my entity model from that, if I were to do it in simplified code it's something like

 class TradingDay { public int ID {get;set;} //this primary key is basically all numbers from DT field as "yyMMdd" datetime DT{get;set;} } class Stock { int Id {get;} //primary key and identity(1000,1) string Ticker {get;set;} List<Option> StockOptions {get;set;} //Options have expiration dates so this a list of all past, present (maybe even future) options that trade for this Stock } class Option { int Key {get;set;} //primary key that I assign myself It's a combination of Stock.ID+[4 other factors that uniquely identity each option] I string together all factors as numbers and use that as a primary key Each stock has a lot of different Option Contracts trading at any given time. } 

Since every option is a derivative of the underlying stock, option's relationship to Stock is "Stock has Options" and "Option is not Stock". So I'm guessing Option doesn't inherit from Stock.

StockDay : Stock { int key {get;set;} //primary key is a combination of Stock.ID+TradingDay.DT int StockID {get;set;} //refrences Stock int TradingDayId {get;set;} //refrences TradingDay decimal Open, High, Low, Close; int volume; List<OptionDay> OptionChain {get;set;} //Unlike Stock which has all options, this list is only options that traded on this day and haven't expired yet } OptionDay : Option { int key {get;set;} //this primary key uses int StockDayID {get;set;} //refrences StockDay } 

Now my confusion is how to store a reference key. I've been following the logic that a lower class can hold reference to higher class but higher class can't know about a subclass. Is this correct?

There is another class

StockDayDetails : StockDay { int key {get;set;} //a custom key value here would be the exact same as StockDay. So if I want to draw a relationship between this and StockDay I end up having 2 columns in this table that have the same value (1st as the primary key and then against as a foreign key to StockDay) //I'm interested in controlling my Key value so that I can use DbContext.Find() to quickly return entries. //I have to keep this table separate from StockDay becase this has like 15 columns that are null for most days or just missing an entry completely whereas StockDay has entries for each day. So I'm wondering if I should just merge the two tables. } 

My backup plan is to set all table primary keys to identity(1,1) and then create an individual KeyTable for each data table that will have a foreign reference to that table's identity primary key. so something like

StockDayKey { int key{get;set;} //my custom identifier int TableId {get;set;} //references StockDay.Id }

Then I can query this key table to get the main table's primary key.

by pope_says via /r/csharp

Leave a Reply