I need to run 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 quant field used to add.
Using the Concat method.
Note that this method forces the input types to be the same. If there is no agreement between the bpai and bpac classes, that Select is required, declaring all the fields that will be used because, in this way, new anonymous types will be created and, as they have exactly the same fields, they will have the same type.
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;
}