Entity Framework adds duplicate foreign keys

c# entity-framework-6 sql-server

Question

I have two entities

public class CandlestickData
{
    [Key]
    public int Id { get; set; }
    public virtual Symbol Symbol { get; set; }
    [Column(TypeName = "datetime2")]
    public DateTime Time { get; set; }
    public decimal Open { get; set; }
    public decimal High { get; set; }
    public decimal Low { get; set; }
    public decimal Close { get; set; }
}

and

public class Symbol
{
    [Key]
    public int Id { get; set; }
    public string Name { get; set; }
}

Because I add lots of 'CandlestickData' in the db per minute and the Symbols are usually the same and the don't get changed, I want to avoid unuseful calls to the DB so I made an extension method that keeps the symbols that were already retrieved from the db in a List so I re-use them.

public static class Extension
    {
        static List<Symbol> ExistingSymbols = new List<Symbol>();
        public static Symbol GetSymbolIfExistsOrCreateItInTheDb(this string name, Repository repository)
        {
            if (ExistingSymbols.Any(x => x.Name == name))
            {
                return ExistingSymbols.First(x => x.Name == name);
            }
            if (repository.SymbolExists(name))
            {
                var symbol = repository.GetSymbol(name);
                ExistingSymbols.Add(symbol);
                return symbol;
            }
            else
            {
                Symbol symbol = new Symbol { Name = name };
                repository.AddSymbol(symbol);
                symbol = repository.GetSymbol(name);
                ExistingSymbols.Add(symbol);
                return symbol;
            }
        }
    }

This is code code that adds multiple 'CandlestickData' in the db

using (var repository = new Repository())
            {
                var candlesticks = new List<CandlestickData>();
                foreach (var symbol in AllSymbolsTradeData[GetNotUsedIndex()])
                {
                    candlesticks.Add(new CandlestickData
                    {
                        Close = symbol.Value.Close,
                        Symbol = symbol.Key.GetSymbolIfExistsOrCreateItInTheDb(repository),
                        High = symbol.Value.High,
                        Low = symbol.Value.Low,
                        Open = symbol.Value.Open,
                        Time = symbol.Value.Time
                    });
                }
                repository.AddCandlesticksData(candlesticks);
                repository.CommitChanges();
            }

Also if you want to see how the Repository class looks:

public class Repository : IDisposable
    {
        private Db context;
        public Repository()
        {
            context = new Db();
        }

        public bool SymbolExists(string name)
        {
            return context.Symbols.Where(x => x.Name == name).Any();
        }
        public Symbol GetSymbol(string name)
        {
            return context.Symbols.First(x => x.Name == name);
        }
        public void AddSymbol(Symbol symbol)
        {
            context.Symbols.Add(symbol);
            context.SaveChanges();
        }
        public void AddCandlestickData(CandlestickData candlestickData)
        {
            context.Candlesticks.Add(candlestickData);
        }
        public void AddCandlesticksData(List<CandlestickData> candlesticks)
        {
            context.Candlesticks.AddRange(candlesticks);
        }

        public void CommitChanges()
        {
            context.SaveChanges();
        }

        public void Dispose()
        {
            context.Dispose();
        }
    }

Now my problem is that every time my Extension method gets the symbol from the memory list(with ID and Name) after the CommitChanges method is called the reference Symbol that was used in from my list gets another ID and in the database a new Symbol is inserted with the same name but with a new ID.

My code worked correctly(without creating duplicates in the Db) when I was getting the symbols from the db every time and I can't find what I'm doing wrong since the Symbols stored in the list are the ones from the db with the correct ID...

1
0
4/12/2020 7:32:14 AM

Accepted Answer

The problem is ExistingSymbols was loaded in a different DbContext than is the context that saves the candlestick.

That is because the ExistingSymbols is static it is used for multiple operations saving candlesticks.

You might solve it by changing thr model of CandlestickData to add a foreign key:

public class CandlestickData
{

    public int SymbolId { get; set; }

    [ForeignKey(nameof(SymbolId))]
    public virtual Symbol Symbol { get; set; }

and then use just the foreign key when creating a new object using the symbol:

candlesticks.Add(new CandlestickData
{
    Close = symbol.Value.Close,
    SymbolId = symbol.Key.GetSymbolIfExistsOrCreateItInTheDb(repository).Id,
    High = symbol.Value.High,
    Low = symbol.Value.Low,
    Open = symbol.Value.Open,
    Time = symbol.Value.Time
});
1
4/12/2020 7:55:32 AM


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow