What is the best way to search for many variables in the database?

Question

I am developing a C# MVC application and am having problems presenting the records. I have a list of over 250 variables and the user chooses which one he wants to see on the chart. Inside the controller I make a switch case for the LINQ of each variable in the list. The problem is that it always gives System.Stackoverflowexception error after that step of 200 variables. Is there any smarter way to do this search in SQL?

switch (tipoDados) {
case "Temperatura Ar de Combustão":
                    var pffeifer = db.PffeiferPRODs.Where(p => DbFunctions.TruncateTime(p.Data_coleta) >= data1 && DbFunctions.TruncateTime(p.Data_coleta) <= data2).OrderBy(p => p.Data_coleta).ToArray();
                    var dataTabela = pffeifer.Select(p => p.Data_coleta).Distinct().ToArray();
                    max = new double[dataTabela.Length]; min = new double[dataTabela.Length]; mediana = new double[dataTabela.Length]; quartil1 = new double[dataTabela.Length]; quartil3 = new double[dataTabela.Length];
                    for (int a = 0; a < dataTabela.Length; a++)
                    {
                        DateTime data = dataTabela[a];
                        int q1 = 0, q3 = 0;
                        double cont1 = 0, cont2 = 0;
                        var varDia = pffeifer.Where(p => p.Data_coleta == data).OrderBy(p => p.Temp_arcombustao_camcombustao).ToArray();

                        //pega o máximo e o minimo
                        try
                        {
                            max[a] = varDia.OrderBy(p => p.Temp_arcombustao_camcombustao).Max(p => p.Temp_arcombustao_camcombustao);
                            min[a] = varDia.OrderBy(p => p.Temp_arcombustao_camcombustao).Min(p => p.Temp_arcombustao_camcombustao);
                        }
                        catch
                        {
                            max[a] = 0;
                            min[a] = 0;
                        }

                        if (varDia.Length % 2 != 0) //se o tamanho do array for impar
                        {
                            int pos = (varDia.Length + 1) / 2;
                            mediana[a] = varDia[pos - 1].Temp_arcombustao_camcombustao;

                            if (((pos - 1) % 2) != 0) // se o tamanho dos quartis for ímpar
                            {
                                q1 = Convert.ToInt32((varDia.Length + 1) * 0.25);
                                quartil1[a] = varDia[q1 - 1].Temp_arcombustao_camcombustao;

                                q3 = Convert.ToInt32((varDia.Length + 1) * 0.75);
                                quartil3[a] = varDia[q3 - 1].Temp_arcombustao_camcombustao;
                            }
                            else //se o tamanho dos quartis for par
                            {
                                q1 = Convert.ToInt32((varDia.Length - 1) * 0.25);
                                try
                                {
                                    cont1 = varDia[q1 - 1].Temp_arcombustao_camcombustao;
                                }
                                catch (IndexOutOfRangeException)
                                {
                                    cont1 = varDia[q1].Temp_arcombustao_camcombustao;
                                }
                                cont2 = varDia[q1].Temp_arcombustao_camcombustao;
                                quartil1[a] = (cont1 + cont2) / 2;

                                q3 = Convert.ToInt32((varDia.Length) * 0.75);
                                cont1 = varDia[q3 - 1].Temp_arcombustao_camcombustao;
                                try
                                {
                                    cont2 = varDia[q3].Temp_arcombustao_camcombustao;
                                }
                                catch (IndexOutOfRangeException)
                                {
                                    if (q3 < 0)
                                    {
                                        q3 = 0;
                                    }
                                    else
                                    {
                                        q3 = q3 - 1;
                                    }
                                    cont2 = varDia[q3].Temp_arcombustao_camcombustao;
                                }
                                quartil3[a] = (cont1 + cont2) / 2;
                            }
                        }
                        else // se o tamanho for par
                        {
                            //se 10
                            int pos1 = varDia.Length / 2;
                            int pos2 = (varDia.Length / 2) - 1;
                            if (pos1 < 0)
                            {
                                pos1 = 0;
                            }
                            if (pos2 < 0)
                            {
                                pos2 = 0;
                            }
                            try
                            {
                                mediana[a] = (varDia[pos1].Temp_arcombustao_camcombustao + varDia[pos2].Temp_arcombustao_camcombustao) / 2;
                            }
                            catch (IndexOutOfRangeException)
                            {
                                mediana[a] = 0;
                            }

                            if ((pos1 % 2) != 0) // se o tamanho dos quartis for ímpar
                            {
                                q1 = Convert.ToInt32(pos1 / 2) - 1;
                                if (q1 < 0)
                                {
                                    q1 = 0;
                                }
                                quartil1[a] = varDia[q1].Temp_arcombustao_camcombustao;

                                q3 = Convert.ToInt32((pos1 / 2) + (varDia.Length / 2)) - 1;
                                if (q3 < 0)
                                {
                                    q3 = 0;
                                }
                                quartil3[a] = varDia[q3].Temp_arcombustao_camcombustao;
                            }
                            else //se o tamanho dos quartis for par
                            {
                                q1 = Convert.ToInt32(pos1 / 2);
                                try
                                {
                                    cont1 = varDia[q1 - 1].Temp_arcombustao_camcombustao;
                                }
                                catch (IndexOutOfRangeException)
                                {
                                    cont1 = 0;
                                }
                                try
                                {
                                    cont2 = varDia[q1].Temp_arcombustao_camcombustao;
                                }
                                catch (IndexOutOfRangeException)
                                {
                                    cont2 = 0;
                                }

                                quartil1[a] = (cont1 + cont2) / 2;

                                q3 = Convert.ToInt32((pos1 / 2) + (varDia.Length / 2));
                                try
                                {
                                    cont1 = varDia[q3 - 1].Temp_arcombustao_camcombustao;
                                }
                                catch (IndexOutOfRangeException)
                                {
                                    cont1 = 0;
                                }

                                try
                                {
                                    cont2 = varDia[q3].Temp_arcombustao_camcombustao;
                                }
                                catch (IndexOutOfRangeException)
                                {
                                    if (q3 < 0)
                                    {
                                        q3 = 0;
                                    }
                                    else
                                    {
                                        q3 = q3 - 1;
                                    }
                                    try
                                    {
                                        cont2 = varDia[q3].Temp_arcombustao_camcombustao;
                                    }
                                    catch (IndexOutOfRangeException)
                                    {
                                        cont2 = 0;
                                    }
                                }
                                quartil3[a] = (cont1 + cont2) / 2;
                            }
                        }
                        mdia = varDia.Average(p => p.Temp_arcombustao_camcombustao);
                        listaDia.Add(Convert.ToString(dataTabela[a].ToShortDateString()));
                        listaDados.Add(new BoxplotSeriesData { Y = Math.Round(mdia, 2), Q1 = Math.Round(quartil1[a], 2), Q3 = Math.Round(quartil3[a], 2), Median = Math.Round(mediana[a], 2), Low = Math.Round(min[a], 2), High = Math.Round(max[a], 2) });
                    }

                    media = pffeifer.Average(p => p.Temp_arcombustao_camcombustao);
                    break;
}

Answer 1

If you have the exception System.StackOverflowException is because you are having a lot of calls on the runstack. This should have nothing to do with your switch (which is nothing more than a sequence of IFs). The error most likely occurs due to a loop (for/foreach/while).