c# – How to insert values ​​in a relational table when inserting a row and get this ID?

Question:

I'm building a program to insert data from excel files into a database.

My database has this structure:

Tables (and fields):

  • Schedule (Id ( PK ), StartTime, EndTime, DayWeek, RoomId ( FK ), ClassId ( FK ), SubjectId ( FK ), TeacherId ( FK ))
  • Rooms (RoomId ( PK ), RoomName)
  • Classes (ClassId ( PK ), ClassName)
  • Subjects (SubjectId ( PK ), SubjectName)
  • Teachers (TeacherId ( PK ), TeacherName)

The methods are similar to this:

context.Horarios.Add(
    new Horarios
    {
        Cod_Tempo = cod_Tempo,
        Dia_Semana = dia_Semana,
        Cod_Disciplina = ObterCodDisciplina(disciplina),
        Cod_Professor = ObterCodProfessor(nomeProfessor),
        Cod_Sala = ObterCodSala(sala),
        Cod_Turma = ObterCodTurma(turma)
    });

private int? ObterCodDisciplina(string disciplina)
{
    using (var context = new ScheduleDatabaseEntities())
    {
        var dis = context.Disciplinas.FirstOrDefault(a => a.Disciplina == disciplina);
        if (dis == null)
        {
            var disciplinaEntity = new Disciplinas {Disciplina = disciplina};
            context.Disciplinas.Add(disciplinaEntity);
            context.SaveChanges();
            return disciplinaEntity.Cod_Disciplina;
        }
        else
            return dis.Cod_Disciplina;
    }
}

The purpose of this code is to insert it in the relational table if there is no field with that name already and associate it with the row to be filled. This way works, but I wanted a way to improve performance, since this is a little slow to execute the query, any suggestions?

Edit:

Complete Code, to understand the purpose of the program:

        private void btn_addtodb_Click(object sender, EventArgs e)
    {
        try
        {
            stopwatch.Start();

            using (var context = new ScheduleDatabaseEntities())
            {
                foreach (string fullfilePath in ExcelfilesPath)
                {
                    Excel.Workbook theWorkbook = app.Workbooks.Open(fullfilePath);
                    for (int i = 1; i <= theWorkbook.Worksheets.Count; i++)
                    {
                        Excel.Worksheet theWorksheet = theWorkbook.Worksheets[i];
                        Excel.Range excelRange = theWorksheet.UsedRange;
                        object[,] valueArray = (object[,])excelRange.get_Value(Excel.XlRangeValueDataType.xlRangeValueDefault);
                        string nomeProfessor = Convert.ToString(valueArray[9, 2]);

                        for (int k = 4; k <= 12; k = k + 2)
                        {
                            for (int j = 16; j <= 35; j = j + 2)
                            {
                                if (j == 24)
                                    j--;
                                if (valueArray[j, k] != null)
                                {
                                    int cod_Tempo = Convert.ToInt32(valueArray[j, 1]);
                                    string hora_Inicial = Convert.ToString(valueArray[j, 2]);
                                    string hora_Final = Convert.ToString(valueArray[j, 3]);
                                    string sala = Convert.ToString(valueArray[j, k + 1]);
                                    string dia_Semana = Convert.ToString(valueArray[14, k]);
                                    string turma, disciplina;
                                    string str = Convert.ToString(valueArray[j, k]);

                                    if (Char.IsNumber(str[0]) && str.Contains(" "))
                                    {
                                        string[] splistring = str.Split(new[] { " " }, 2, StringSplitOptions.None);
                                        turma = splistring[0];
                                        disciplina = splistring[1];
                                    }
                                    else
                                    {
                                        turma = null;
                                        disciplina = str;
                                    }
                                   context.Horarios.Add(new Horarios { Cod_Tempo = cod_Tempo, Dia_Semana = dia_Semana, Cod_Disciplina = ObterCodDisciplina(disciplina), Cod_Professor = ObterCodProfessor(nomeProfessor), Cod_Sala = ObterCodSala(sala), Cod_Turma = ObterCodTurma(turma) });
                                }
                            }
                        }
                    }
                }
                context.SaveChanges();
            }
            stopwatch.Stop();
            MessageBox.Show("Done! Tempo: "+stopwatch.ElapsedMilliseconds);
        }
        catch (Exception ex)
        {
            MessageBox.Show(ex.ToString());
        }
    }

private int? ObterCodDisciplina(string disciplina)
    {
        using (var context = new ScheduleDatabaseEntities())
        {
            var dis = context.Disciplinas.FirstOrDefault(a => a.Disciplina == disciplina);
            if (dis == null)
            {
                var disciplinaEntity = new Disciplinas { Disciplina = disciplina };
                context.Disciplinas.Add(disciplinaEntity);
                context.SaveChanges();
                return disciplinaEntity.Cod_Disciplina;
            }
            else
                return dis.Cod_Disciplina;
        }
    }
    private int? ObterCodProfessor(string professor)
    {
        using (var context = new ScheduleDatabaseEntities())
        {
            var prof = context.Professores.FirstOrDefault(a => a.Professor == professor);
            if (prof == null)
            {
                var professorEntity = new Professores { Professor = professor };
                context.Professores.Add(professorEntity);
                context.SaveChanges();
                return professorEntity.Cod_Professor;
            }
            else
                return prof.Cod_Professor;
        }
    }
    private int? ObterCodSala(string sala)
    {
        using (var context = new ScheduleDatabaseEntities())
        {
            var sal = context.Salas.FirstOrDefault(a => a.Sala == sala);
            if (sal == null)
            {
                var salaEntity = new Salas { Sala = sala };
                context.Salas.Add(salaEntity);
                context.SaveChanges();
                return salaEntity.Cod_Sala;
            }
            else
                return sal.Cod_Sala;
        }
    }
    private int? ObterCodTurma(string turma)
    {
        using (var context = new ScheduleDatabaseEntities())
        {
            if (turma == null)
                return null;
            var turm= context.Turmas.FirstOrDefault(a => a.Turma == turma);
            if (turm == null)
            {
                var turmaEntity = new Turmas { Turma = turma };
                context.Turmas.Add(turmaEntity);
                context.SaveChanges();
                return turmaEntity.Cod_Turma;
            }
            else
                return turm.Cod_Turma;
        }
    }

Answer:

EDIT: loading lookups before processing excel table

This code loads all lookup information beforehand, and elements will only be added when they do not exist in the lookup dictionaries.

Therefore, all additions that would have already been made will continue to be made. But the data uploads will all be done at once.

private void btn_addtodb_Click(object sender, EventArgs e)
{
    try
    {
        stopwatch.Start();

        using (var context = new ScheduleDatabaseEntities())
        {
            // carregando todas as informações de lookup antecipadamente
            var disciplinas = context.Disciplinas.ToDictionary(a => a.Disciplina, a => a.Cod_Disciplina);
            var professores = context.Professores.ToDictionary(a => a.Professor, a => a.Cod_Professor);
            var salas = context.Salas.ToDictionary(a => a.Sala, a => a.Cod_Sala);
            var turmas = context.Turmas.ToDictionary(a => a.Turma, a => a.Cod_Turma);

            foreach (string fullfilePath in ExcelfilesPath)
            {
                Excel.Workbook theWorkbook = app.Workbooks.Open(fullfilePath);
                for (int i = 1; i <= theWorkbook.Worksheets.Count; i++)
                {
                    Excel.Worksheet theWorksheet = theWorkbook.Worksheets[i];
                    Excel.Range excelRange = theWorksheet.UsedRange;
                    object[,] valueArray = (object[,])excelRange.get_Value(Excel.XlRangeValueDataType.xlRangeValueDefault);
                    string nomeProfessor = Convert.ToString(valueArray[9, 2]);

                    for (int k = 4; k <= 12; k = k + 2)
                    {
                        for (int j = 16; j <= 35; j = j + 2)
                        {
                            if (j == 24)
                                j--;
                            if (valueArray[j, k] != null)
                            {
                                int cod_Tempo = Convert.ToInt32(valueArray[j, 1]);
                                string hora_Inicial = Convert.ToString(valueArray[j, 2]);
                                string hora_Final = Convert.ToString(valueArray[j, 3]);
                                string sala = Convert.ToString(valueArray[j, k + 1]);
                                string dia_Semana = Convert.ToString(valueArray[14, k]);
                                string turma, disciplina;
                                string str = Convert.ToString(valueArray[j, k]);

                                if (Char.IsNumber(str[0]) && str.Contains(" "))
                                {
                                    string[] splistring = str.Split(new[] { " " }, 2, StringSplitOptions.None);
                                    turma = splistring[0];
                                    disciplina = splistring[1];
                                }
                                else
                                {
                                    turma = null;
                                    disciplina = str;
                                }
                                context.Horarios.Add(new Horarios {
                                        Cod_Tempo = cod_Tempo,
                                        Dia_Semana = dia_Semana,
                                        Cod_Disciplina = ObterCodDisciplina(context, disciplina, disciplinas),
                                        Cod_Professor = ObterCodProfessor(context, nomeProfessor, professores),
                                        Cod_Sala = ObterCodSala(context, sala, salas),
                                        Cod_Turma = ObterCodTurma(context, turma, turmas)
                                    });
                            }
                        }
                    }
                }
            }
            context.SaveChanges();
        }
        stopwatch.Stop();
        MessageBox.Show("Done! Tempo: "+stopwatch.ElapsedMilliseconds);
    }
    catch (Exception ex)
    {
        MessageBox.Show(ex.ToString());
    }
}

private int? ObterCodDisciplina(ScheduleDatabaseEntities context, string disciplina, Dictionary<string, int> dic)
{
    int id;
    if (!dic.TryGet(disciplina, out id))
    {
        var entidade = new Disciplinas { Disciplina = disciplina };
        context.Disciplinas.Add(entidade);
        context.SaveChanges();
        id = entidade.Cod_Disciplina;
        dic[disciplina] = id;
    }
    return id;
}

private int? ObterCodProfessor(ScheduleDatabaseEntities context, string professor, Dictionary<string, int> dic)
{
    int id;
    if (!dic.TryGet(professor, out id))
    {
        var entidade = new Professores { Professor = professor };
        context.Professores.Add(entidade);
        context.SaveChanges();
        id = entidade.Cod_Professor;
        dic[professor] = id;
    }
    return id;
}

private int? ObterCodSala(ScheduleDatabaseEntities context, string sala, Dictionary<string, int> dic)
{
    int id;
    if (!dic.TryGet(sala, out id))
    {
        var entidade = new Salas { Sala = sala };
        context.Salas.Add(entidade);
        context.SaveChanges();
        id = entidade.Cod_Sala;
        dic[sala] = id;
    }
    return id;
}

private int? ObterCodTurma(string turma)
{
    if (turma == null)
        return null;
    int id;
    if (!dic.TryGet(turma, out id))
    {
        var entidade = new Turmas { Turma = turma };
        context.Turmas.Add(entidade);
        context.SaveChanges();
        id = entidade.Cod_Turma;
        dic[turma] = id;
    }
    return id;
}

EDIT: old… add or update record one by one

According to EntityFramework legend Ladislav Mrnka (in English) , it is not possible to do this in a single query.

You don't however need to load the entire object, you can make a query that returns something that indicates whether the object exists or not, then attach the object in the entity's context, and finally call SaveChanges :

var idOrNull = context.Disciplinas
    .Where(a => a.Disciplina == disciplina)
    .Select(a => (int?)a.Id)
    .FirstOrDefault();

var disciplinaEntity = new Disciplinas { Disciplina = disciplina };

if (idOrNull != null)
{
    // se idOrNull não for nulo, é porque já existe, então vamos atualizar o objeto
    disciplinaEntity.Id = idOrNull.Value;
    context.Disciplinas.Attach(disciplinaEntity);

    // indicando quais propriedades devem ser salvas
    // para obter o máximo de granularidade no salvamento
    context.Entry(disciplinaEntity).Property(u => u.Disciplina).IsModified = true;

    // se o objetivo é dar um replace em todos os campos do objeto
    // então descomente a linha abaixo
    //context.ObjectStateManager.ChangeObjectState(disciplinaEntity, EntityState.Modified);
}
else
{
    // se idOrNull for nulo, é porque não existe, então vamos adicionar o objeto
    context.Disciplinas.AddObject(disciplinaEntity);
}

About the granularity of saving properties (in English)

Scroll to Top