EF AddRange method does not work with big data

Question

Good morning.

Today I'm facing problem with EF 6 and AddRange method. There is an WPF application which works with ~100000 records. I wrote import functionality, which imports data from .csv files and there is a problem.

It looks like this:

 private void FileImport()
    {
        //Open dialog to choose file
        OpenFileDialog ofd = new OpenFileDialog();

        string fileName = string.Empty;

        if (ofd.ShowDialog() == true)
        {
            fileName = ofd.FileName;
        }

        if (!string.IsNullOrEmpty(fileName))
        {
            //getting all lines
            var lines = File.ReadAllLines(fileName).ToList();

            //File requirements says that there cannot be empty values in first element
            if (lines.Any(line => line.Split(';')[0].Equals("null")))
            {
                MessageBox.Show("BLA BLA BLA");
            }
            else
            {

                List<List<string>> splitLines = new List<List<string>>();

                //split lines into smaller list. For every sublist in list we will do things separatly in separate threads to get it faster.
                for (int i = 0; i < lines.Count; i += 1000)
                {
                    splitLines.Add(lines.GetRange(i, Math.Min(1000, lines.Count - i)));
                }

                var taskList = new List<Task>();

                List<ut_katabcdx_file_filters> filterList = new List<ut_katabcdx_file_filters>();

                foreach (var list in splitLines)
                {
                    //define a Task
                    var t = new Task(() =>
                    {
                        foreach (var line in list)
                        {
                            var filters = line.Split(';');

                            //split line into elements array. It must have 6 elemets
                            if (filters.Count() == 6)
                            {

                                //Temporary declaration for parsing. If element that pretends to be decimals are empty we set its value to -100000.
                                decimal temp;
                                int tempi;
                                decimal? proga = filters[1].Equals("") ? -100000 : (decimal.TryParse(filters[1], out temp) ? (decimal?)temp : null);
                                decimal? progb = filters[2].Equals("") ? -100000 : (decimal.TryParse(filters[2], out temp) ? (decimal?)temp : null);
                                int? plan_sprz_rok = filters[3].Equals("") ? -100000 : (int.TryParse(filters[3], out tempi) ? (int?)tempi : null);

                                ut_katabcdx_file_filters filter = new ut_katabcdx_file_filters()
                                {
                                    indeks = filters[0],
                                    //produkty_iz = ProduktyIzChecked ? (filters[1].Equals("null") ? null : filters[1]) : string.Empty,
                                    proga = ProgaChecked ? proga : -100000,
                                    progb = ProgbChecked ? progb : -100000,
                                    plan_sprz_rok = PlanSprzRokChecked ? plan_sprz_rok : -100000,
                                    kat_tech = KatTechChecked ? (filters[4].Equals("null") ? null : filters[4]) : string.Empty,
                                    kat_handl = KatHandlChecked ? (filters[5].Equals("null") ? null : filters[5]) : string.Empty,
                                };

                                filterList.Add(filter);
                            }
                        }
                    });

                    taskList.Add(t);
                    t.Start();
                }

                //wait for all tasks to end
                Task.WaitAll(taskList.ToArray());

                using (var ctx = new ABCDXContext())
                {
                    ctx.ut_katabcdx_file_filters.AddRange(filterList);
                    ctx.SaveChanges();
                    string param_xml = GetParamXml();
                    Products = new ObservableCollection<ut_katabcdx_wytwor>(ctx.WytworFileUpdate(param_xml));
                }
            }

        }
    }

When I'm debuging code It stops at ctx.ut_katabcdx_file_filters.AddRange(filterList); and does not go further. I've checked filterList.Count and there is an about 60000 rows. I've checked database table also, but It's empty.

Is it because big amount of data or I'm doing something not properly ? I would be very greatful for any advices.

Answer 1

I agree with DavidG's comment about trying to insert 60k records using AddRange. It will be slow. You'll either have to simply wait for it to finish, or find an alternative.

I have found that your Context will be happier when you call SaveChanges periodically. It seems that piling up a large number of items in the change tracker is a huge performance hit.

So you could do something like this:

using (var ctx = new ABCDXContext())
{
    var count = 0;
    foreach (var filter in filterList)
    {
        ctx.ut_katabcdx_file_filters.Add(filter);
        count++;
        if (count > 100)
        {
            ctx.SaveChanges();
            count = 0;
        }
    }
    if (count > 0)
        ctx.SaveChanges();
    string param_xml = GetParamXml();
    Products = new ObservableCollection<ut_katabcdx_wytwor>(ctx.WytworFileUpdate(param_xml));
}

...just to break up the save into blocks. You can adjust count to a value that balances performance for your scenario.

This answer provides additional suggestions you might try. Comments there suggest good results.