CODEWITHSUNDEEP
entity-framework
Bohms27
Bohms27

Reputation: 407

How can I insert records using the Entity Framework without them duplicating?

I have been searching for a solution to my issue without any success so far.

Here's the problem I have to solve. I have a CSV file that I am reading in through a StreamReader. I have one field in the CSV that represents multiple new columns based on substring position of the text.

My entity is Country which has a Currency property and a Language property which there is a relationship between. There is a one to many relationship between Country & Currency and also Country & Language.

I need to read this CSV and insert the currencies and languages into their respective tables and use that inserted id which will populate my LanguageId or CurrencyId field in my Country entity.

The problem I am currently having is that it is inserting a record per line of the StreamReader. So it will enter English a few times, each with different Ids into the language table. 

using (var ctx = new AddressLoader())
{
    if(!ctx.Currencies.Any())
    {
         string line = "";
         var counter = 0;

         Country country = new Country();

         using (StreamReader sr = new StreamReader("Country.csv"))
         {
             while (!sr.EndOfStream)
             {
                 line = sr.ReadLine();

                 TextFieldParser parser = new TextFieldParser(new StringReader(line));
                 parser.HasFieldsEnclosedInQuotes = true;
                 parser.SetDelimiters(",");

                 if (counter > 0)
                 {
                     country = GetCountryFromCSV(parser);
                     ctx.Countries.Add(country);
                 }

                 counter++;
             }
         }
     }

     ctx.SaveChanges();
}

I am very new to Entity Framework and I am unsure how to write this so that it will insert a Country record, check if a language or currency already exists (without knowing it's ID) and use that ID for the relationship. If it doesn't already exist, it will insert the language or currency into the table and use the newly created Id for the relationship.

I hope this makes sense. Thanks!

Upvotes: 2

Views: 55

Answers (1)

Jamie Ide
Jamie Ide

Reputation: 49251

Using currency for the example and assuming you're matching on name, before processing the file load the set of existing currencies into a dictionary:

private IDictionary<string, Currency> _currencies;

using (var context = new DbContext)
{
    _currencies = context.Currencies.ToDictionary(k => k.Name.ToLower());
}

Then get the ID using a method that will retrieve the existing currency from the dictionary or add a new one.

private int GetOrCreateCurrencyId(string currencyName)
{
    if (string.IsNullOrWhiteSpace(currencyName))
    {
        throw new ArgumentException();
    }

    var key = currencyName.ToLower();
    Currency currency;
    if (_currencies.TryGetValue(key, out currency))
    {
        return currency.CurrencyId;
    }

    using (var context = new DbContext())
    {
        currency = new Currency() { Name = currencyName };
        context.Currencies.Add(currency);
        context.SaveChanges();
        _currencies.Add(key, currency);
        return currency.CurrencyId;
    }
}

Upvotes: 1

Related Questions