How to do Union all in LINQ?

Asked

Viewed 1,277 times

2

I need to execute the command below using Linq:

SELECT cnes, cmp, cbo, profNome, pa, sum(quant) total FROM bpai
group by pa
union all
select cnes, cmp, cbo, profissional, pa, sum(quant) total from bpac
group by pa

All fields are string except the field quant used to add up.

c# linq
  • 2

    You have to use the Contat which is the Equivalent of UNION ALL. Question by SO-EN How to use Union all in LINQ?

  • 1

    I think it’s gonna look something like this var result =
(SELECT cnes, cmp, cbo, profNome, pa, sum(quant) total FROM bpai
group by pa).CONCAT(
select cnes, cmp, cbo, profissional, pa, sum(quant) total from bpac
group by pa);

1 answer

5


Using the method Concat.

Note that this method requires that the input types are the same. If there is no contract between the classes bpai and bpac it will take that Select declaring all the fields that will be used because, this way, new anonymous types will be created and, as they have exactly the same fields, they will have the same type.

See working on . NET Fiddle.

using System;
using System.Linq;

public class Program
{
    public static void Main()
    {
        var tabela1 = new [] { new bpai { cnes = "A", pa = "1",  quant = 2 }, 
                               new bpai { cnes = "B", pa = "1",  quant = 4 }, 
                               new bpai { cnes = "C", pa = "2",  quant = 80 } };

        var tabela2 = new [] { new bpac { cnes = "A", pa = "1", quant = 2 }, 
                               new bpac { cnes = "B", pa = "1", quant = 4 }, 
                               new bpac { cnes = "C", pa = "2",  quant = 80 } };

        var union = tabela1.Select(a => new { a.cnes, a.pa, a.quant })
                    .Concat(tabela2.Select(b => new { b.cnes, b.pa, b.quant }))
                    .GroupBy(x => x.pa)
                    .Select(gp => new
                    {
                        Pa = gp.Key,
                        Total = gp.Sum(x => x.quant),
                        Itens = gp.ToList()
                    });

        foreach(var u in union)
        {
            Console.WriteLine($"Pa: { u.Pa } - Total: {u.Total}" +
                              $"Qtd Itens: { u.Itens.Count }");

            foreach(var i in u.Itens)
            {
                Console.WriteLine($"\t{i.cnes}");
            }
        }
    }
}

class bpai {
    public string cnes;
    public int quant;
    public string pa;
}

class bpac {
    public string cnes;
    public int quant;
    public string pa;
}

Browser other questions tagged c# linq

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