Linq to SQL check if data exists and Update/Insert depending

Question

EDIT: CODE UPDATED

I have the following code but it isn't working, it makes an infinite loop and add thousand of rows I want to check if there is any row with the values I specify, if not, insert it, if yes, just update two columns.

You will se an IF Statement for Servicio.Value == "PAS" that's because, if the service is "PAS", it has a column called modulo_id on table Umbrales (where is supossed to insert everything) and also, the query to return values changes to PAS table instead of Banksphere Table... If you know a better method to handle this, will be appreciated too becasuse I think this is so messy..

        checkcenterDBDataContext dc = new checkcenterDBDataContext();
        int intOkMas = Convert.ToInt32(txtUmbralOkMas.Text);
        int intOkMenos = Convert.ToInt32(txtUmbralOkMenos.Text);
        var days = GetDaysLikeMe(DateTime.Today).Take(50).Where(d => d.Date.Day < 28 && d.Date.Day > 1).Take(4);

        XDocument xDoc = XDocument.Load("Settings.xml");
        var Entidades =
            from ent in xDoc.Root.Elements("Entidad")
            select ent;
        foreach (var entidad in Entidades)
        {
            var Servicios =
                from serv in entidad.Element("Servicios").Elements("ServName")
                select serv;
            foreach (var servicio in Servicios)
            {
                if (servicio.Value == "PAS")
                {
                    Umbrales newUmbral = new Umbrales();
                    var Modulos =
                        from mod in entidad.Element("Servicios").Elements("ModName")
                        select mod;
                    foreach (var modulo in Modulos)
                    {
                        for (int i = 0; i <= 2; i++)
                        {
                            var valores =
                                from b in dc.PAS
                                where
                                b.Modulos.nombre == modulo.Value &&
                                b.Entidades.nombre == entidad.Element("Nombre").Value &&
                                b.peticion_id == i
                                && days.Contains(Convert.ToDateTime(b.fecha))
                                orderby b.id descending
                                select b;

                            var medias =
                                from b in valores
                                group b.valor by new { b.hora_id, b.dia_id, b.entidad_id, b.modulo_id, b.peticion_id } into hg
                                orderby hg.Key.hora_id descending
                                select new
                                {
                                    Hora = hg.Key.hora_id,
                                    Dia = hg.Key.dia_id,
                                    Entidad = hg.Key.entidad_id,
                                    Modulo = hg.Key.modulo_id,
                                    Peticion = hg.Key.peticion_id,
                                    Maximo = Math.Round(System.Convert.ToDouble(hg.Average() + ((hg.Average() * intOkMas) / 100)), 3, MidpointRounding.AwayFromZero),
                                    Minimo = Math.Round(System.Convert.ToDouble(hg.Average() - ((hg.Average() * intOkMenos) / 100)), 3, MidpointRounding.AwayFromZero)
                                };

                            foreach (var med in medias)
                            {
                                var update_medias =
                                    (from um in dc.Umbrales
                                     where
                                     um.hora_id == med.Hora
                                     && um.dia_id == med.Dia
                                     && um.entidad_id == med.Entidad
                                     && um.servicio_id == 3
                                     && um.peticion_id == med.Peticion
                                     && um.modulo_id == med.Modulo
                                     select um).ToList();
                                if (update_medias.Any())
                                {
                                    foreach (var ume in update_medias)
                                    {
                                        ume.maximo = Convert.ToDecimal(med.Maximo);
                                        ume.minimo = Convert.ToDecimal(med.Minimo);
                                        dc.SubmitChanges();
                                    }
                                }
                                else
                                {
                                    newUmbral.hora_id = med.Hora;
                                    newUmbral.dia_id = med.Dia;
                                    newUmbral.entidad_id = med.Entidad;
                                    newUmbral.servicio_id = 3;
                                    newUmbral.peticion_id = med.Peticion;
                                    newUmbral.modulo_id = med.Modulo;
                                    newUmbral.maximo = Convert.ToDecimal(med.Maximo);
                                    newUmbral.minimo = Convert.ToDecimal(med.Minimo);
                                    dc.Umbrales.InsertOnSubmit(newUmbral);
                                    dc.SubmitChanges();
                                }
                            }

                        }
                    }
                }
                else
                {
                    Umbrales newUmbral = new Umbrales();
                    for (int i = 0; i <= 2; i++)
                    {
                        var valores =
                            from b in dc.Banksphere
                            where
                            b.Servicios.nombre == servicio.Value &&
                            b.Entidades.nombre == entidad.Element("Nombre").Value &&
                            b.peticion_id == i
                            && days.Contains(Convert.ToDateTime(b.fecha))
                            orderby b.id descending
                            select b;

                        var medias =
                            from b in valores
                            group b.valor by new { b.hora_id, b.dia_id, b.entidad_id, b.servicio_id, b.peticion_id } into hg
                            orderby hg.Key.hora_id descending
                            select new
                            {
                                Hora = hg.Key.hora_id,
                                Dia = hg.Key.dia_id,
                                Entidad = hg.Key.entidad_id,
                                Servicio = hg.Key.servicio_id,
                                Peticion = hg.Key.peticion_id,
                                Maximo = Math.Round(System.Convert.ToDouble(hg.Average() + ((hg.Average() * intOkMas) / 100)), 3, MidpointRounding.AwayFromZero),
                                Minimo = Math.Round(System.Convert.ToDouble(hg.Average() - ((hg.Average() * intOkMenos) / 100)), 3, MidpointRounding.AwayFromZero)
                            };

                        foreach (var med in medias)
                        {
                            var update_medias =
                                (from um in dc.Umbrales
                                 where
                                 um.hora_id == med.Hora
                                 && um.dia_id == med.Dia
                                 && um.entidad_id == med.Entidad
                                 && um.servicio_id == med.Servicio
                                 && um.peticion_id == med.Peticion
                                 select um).ToList();
                            if (update_medias.Any())
                            {
                                foreach (var ume in update_medias)
                                {
                                    ume.maximo = Convert.ToDecimal(med.Maximo);
                                    ume.minimo = Convert.ToDecimal(med.Minimo);
                                    dc.SubmitChanges();
                                }
                            }
                            else
                            {
                                newUmbral.hora_id = med.Hora;
                                newUmbral.dia_id = med.Dia;
                                newUmbral.entidad_id = med.Entidad;
                                newUmbral.servicio_id = med.Servicio;
                                newUmbral.peticion_id = med.Peticion;
                                newUmbral.maximo = Convert.ToDecimal(med.Maximo);
                                newUmbral.minimo = Convert.ToDecimal(med.Minimo);
                                dc.Umbrales.InsertOnSubmit(newUmbral);
                                dc.SubmitChanges();
                            }
                        }

                    }
                }
            }
        }

In what way is it not working? The Insert / Update / Exists check? — DaveShaw
– DaveShaw, Commented Nov 13, 2012 at 22:48
If your sumbitChanges() doesn't work it can be that your table doesn't have a primary key. — RAS
– RAS, Commented Nov 13, 2012 at 22:50

Diego · Accepted Answer · 2012-11-13 22:49:35Z

2

update_medias will never be null. Try this instead:

var update_medias = (from um in dc.Umbrales
where
um.hora_id == med.Hora
&& um.dia_id == med.Dia
&& um.entidad_id == med.Entidad
&& um.servicio_id == med.Servicio
&& um.peticion_id == med.Peticion
select um).FirstOrDefault();

answered Nov 13, 2012 at 22:49

Diego

18.5k5 gold badges67 silver badges68 bronze badges

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

1 Comment

AHM Over a year ago

but that will execute the query twice, when the foreach loop executes over it, if there is a match

AHM · Accepted Answer · 2012-11-13 22:49:43Z

1

update_medias will never be null - it is always a query that hasn't been executed. Change it to:

var update_medias =
    (from um in dc.Umbrales
    where
    um.hora_id == med.Hora
    && um.dia_id == med.Dia
    && um.entidad_id == med.Entidad
    && um.servicio_id == med.Servicio
    && um.peticion_id == med.Peticion
    select um).ToList();

and then check it with this instead of the null check

if (update_medias.Any())

answered Nov 13, 2012 at 22:49

AHM

5,23536 silver badges37 bronze badges

3 Comments

Robert W. Hunter Over a year ago

I have the same problem, my form freezes, :S or it takes tooooooooooooo long to complete... 2 mins for now and still freeeze :S

Robert W. Hunter Over a year ago

sorry it doesn't freeze, just does an infinite loop and execute thousands of querys :S

AHM Over a year ago

There is nothing in the code you posted here that should cause that - unless the query actually matches thousands of elements in the Umbrales table. The problem must be somewhere outside the code you posted here.

Collectives™ on Stack Overflow

Linq to SQL check if data exists and Update/Insert depending

2 Answers 2

1 Comment

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related