How to insert values into a relational table when inserting a Row and get this ID?

Asked

Viewed 380 times

3

I am 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 code is to insert it into the relational table if there is no field with that name already and associate it with Row to be filled in. This way it works, but I wanted a way to improve the performance, since this is a little slow to execute the query, some suggestion?

Edit:

Complete Code, to understand the objective 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;
        }
    }
c# sql-server entity-framework linq

  • It’s probably slow because you’re giving a SaveChanges() in the middle of the operation. In your current scenario, can you leave all this in one block? Both the insertion of schedules and disciplines?

  • I have to do Savechanges() in the method so that next time you see that he make the same query already be in the comic book, but I tested without Savechanges(), even though I knew it would not work as I wanted and still takes a long time.

1 answer

1


EDIT: loading the lookups before processing the excel table

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

Therefore, all the additions that would already be 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 to one

According to the legend of the Entityframework Ladislav Mrnka(in English), cannot do this in a single query.

You however do not need to load the whole object, you can do a query that returns something that indicates whether the object exists or not, then attach the object in the context of Entity, 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);
}

On the rescue granularity of properties(in English)

  • Thanks for the help, there is difference between Add() and Addobject() is that I don’t have access to Addobject(). I’m updating all the code, he’s making an exception for Foreign Keys. I think you are misinterpreting the code, I have a schedule schedule, and when doing Insert in this table, I need to relate with disciplines, classrooms, teachers and classes (for that I need to check if it already exists, otherwise I add).

  • You are using EF6?

  • Yes, the latest version

  • I think I mixed codes of different versions of EF... so the method does not exist. Probably no problem to exchange for Add... I’ll have to test.

  • Thank you very much for the help ;). If you have the patience to analyze a little the code and the intention of it, but as I said it works but the performance is slow. The goal is just to insert a Row in the schedule table (this is the real purpose), but for this I need to enter values in the relational tables and then fill in with relational id’s.

  • Now I understand. Well, the problem is that you are lookup in the database, for each time being inserted. Instead of doing this you could load all the ID’s of all the related objects to use as lookup.

  • I edited the answer with an alternative on how to process this information... could you please test and tell me if it gets faster this way?

  • I don’t know how to thank you, thank you for helping a novice understand the code :). I’ve worked on some projects with Entity Framework, but nothing like inserting with static values. The time has decreased, now to 10700 ms (10 seconds), that is half of what it was, but a little slower than a single table (however it is not compared to the organization with relational tables).

  • To know the real time of the operations in the bank, you will have to measure the time that is spent only to read the excel table. You can comment on the code you read from the comic book, and also the Savechanges you write in the comic book. Then you can get the time that is spent exclusively on the comic, with the difference of this time to the time that it takes if nothing is commented.

  • I really don’t think it will be possible to leave the answer better than that using Entityframework. Maybe just using pure SQL.

  • If you think this question is closed, you can mark the answer as accepted. Otherwise, if you want more help in this matter, just say.

  • Thank you, when I finish the project I will consider it finished, don’t take it personally, so I don’t need to open another topic?

  • It depends... here at SOPT there are not exactly topics, but questions. In this issue the subject is about the performance of the Entityframework. Whether or not you accept an answer will depend on whether or not you are satisfied with the answers that have been given. However, if you need help on another issue, even if it is about Entityframewok, then the ideal is to create another question. So you create questions and solve them one by one.

  • There is a guide on how to ask. The issues must be focused and objective. That way if you still want to explore the performance question further, you can leave this question unanswered for now, maybe even come up with better answers than mine. But always in that context, I say without changing the focus of the question.

  • So I will just check, and I will accept your reply ;). Obligatory.

Show 10 more comments

Browser other questions tagged c# sql-server entity-framework linq

You are not signed in. Login or sign up in order to post.