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

Question

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!

Jamie Ide · Accepted Answer · 2016-03-17 20:22:42Z

1

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;
    }
}

answered Mar 17, 2016 at 20:22

Jamie Ide

49.4k18 gold badges85 silver badges118 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Bohms27 Over a year ago

Thank you. I guess I could have thought of this solution. Is there any down side of doing a SaveChanges on the context each time you add it? I suppose this seems like the only option that I have, making it so I have to add the currencies / languages first, then match them based on name to get the ids. Thanks Jamie

Jamie Ide Over a year ago

The only downside to calling SaveChanges on every insert is that the main import loop has to wait. If you had to insert thousands of "lookup" records then this might be a problem. The important thing is to use an isolated context so that you don't prematurely save the data you're importing. There are other approaches but since the set of languages and currencies is reasonably small this is the approach I use.

Collectives™ on Stack Overflow

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

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related