EF Core Adding Duplicate Entities To Database

asp.net-core c# entity-framework-core

Question

I'm trying to build my controller to check whether an entity (StockData) already exists in the database and, if not, add it. Otherwise, it should not be added. However, the code I built is adding it always and I don't understand why.

I am trying to build an app (ASP.NET Core MVC, using SQL Server and EF Core) that loads stock data from an API and displays it on the page. While that works, I want to avoid saving duplicate entities of StockData, as otherwise my database will continuously fill with already existing data on every call.

Here is my controller and my Product and StockData classes:

    {
        private readonly ApplicationDbContext dbContext;

        public StockController(ApplicationDbContext dbContext)
        {
            this.dbContext = dbContext;
        }
        public IActionResult Index()
        {
            var symbol = "MSFT";
            var name = "Microsoft";
            Product product;

            if(dbContext.Products.Any(p => p.Symbol == symbol))
            {
                product = dbContext.Products.FirstOrDefault(p => p.Symbol == symbol);
            }
            else
            {
                product = new Product
                {
                    Symbol = symbol,
                    Name = name
                };
                dbContext.Add(product);
            }

            dbContext.SaveChanges();

            var apiResponse = $"https://www.alphavantage.co/query?function=TIME_SERIES_INTRADAY&symbol={symbol}&interval=5min&apikey=APIKEY&datatype=csv".GetStringFromUrl();
            var stocks = apiResponse.FromCsv<List<StockData>>().ToList();

            product.Data = stocks;

            foreach (var stock in product.Data)
            {
                if(!dbContext.StockData.Any(s => s.Timestamp.Equals(stock.Timestamp) && s.ProductId.Equals(product.ProductId)))
                {
                    dbContext.Add(stock);
                }
            }

            dbContext.SaveChanges();

            return View(product);
        }
    }



public class Product
    {
        public int ProductId { get; set; }
        public string Name { get; set; }
        public string Symbol { get; set; }
        public string Description { get; set; }
        public List<StockData> Data { get; set; }
    }

public class StockData
    {
        [Ignore]
        public int StockDataId { get; set; }
        [Ignore]
        public int ModelId { get; set; }
        [Ignore]
        public int ProductId { get; set; }
        public string Timestamp { get; set; }
        public string Open { get; set; }
        public string High { get; set; }
        public string Low { get; set; }
        public string Close { get; set; }
        public string Volume { get; set; }
    }
1
1
10/21/2019 1:50:56 PM

Accepted Answer

I believe I found out what caused the issue of duplication.

The foreach method was actually working fine with prohibiting to add new stocks to the database according to the if statement's conditions.

But, for some reason, the product.Data = stocks; was somehow automatically adding that product.Data, that is, the newly fetched stocks collection from the API, to the database, even without any explicit methods to do so.

I am not sure why this is happening, but I refactored my code to use a temporary stock list (tempStocks)instead in order to avoid adding the whole fetched stocks collection to the product.Data, like this:

var apiResponse = $"https://www.alphavantage.co/query?function=TIME_SERIES_INTRADAY&symbol={symbol}&interval=1min&apikey=APIKEY&datatype=csv&outputsize=full".GetStringFromUrl();
var tempStocks = apiResponse.FromCsv<List<StockData>>().ToList();
var newStocks = new List<StockData>();

foreach (var stock in tempStocks)
{
     if (!dbContext.StockData.Any(s => s.Timestamp == stock.Timestamp && s.ProductId == product.ProductId))
     {
          newStocks.Add(stock);
     }
     dbContext.SaveChanges();
}

product.Data = newStocks;

Probably not the most elegant solution, but I hope it helps someone who hits the same issue.

0
10/23/2019 7:15:08 AM

Popular Answer

This should update the record if it finds an Id, else it should add it.

    private readonly ApplicationDbContext dbContext;

    public StockController(ApplicationDbContext dbContext)
    {
        this.dbContext = dbContext;
    }
    public IActionResult Index()
    {
        var symbol = "MSFT";
        var name = "Microsoft";
        Product product;

        product = dbContext.Products.FirstOrDefault(p => p.Symbol.Equals(symbol));


        if (product == null)
        {
            dbContext.Add(new Product() { Symbol = symbol, Name = name });
        }
        else
        {
            dbContext.Entry(product).State = EntityState.Modified;
        }

        dbContext.SaveChanges();


        var apiResponse = $"https://www.alphavantage.co/query?function=TIME_SERIES_INTRADAY&symbol={symbol}&interval=5min&apikey=APIKEY&datatype=csv".GetStringFromUrl();
        var stocks = apiResponse.FromCsv<List<StockData>>().ToList();

        product.Data = stocks;

        foreach (var stock in product.Data)
        {
            var newStock = dbContext.StockData.FirstOrDefault(s => s.ProductId.Equals(product.ProductId));
            if (!dbContext.StockData.Any(s => s.Timestamp.Equals(stock.Timestamp)))
            {
                if (newStock == null)
                {
                    dbContext.Add(stock);
                }
                else
                {
                    dbContext.Entry(stock).State = EntityState.Modified;
                }

                dbContext.SaveChanges();
            }
        }

        return View(product);
    }


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