Query with join, group by and count to linq

Question

im doing this query on sql and it works, but how do i make it to Linq ?

select b.Name,a.idempresa,count(1) as cuenta from Descarga_XML_recepcion_V2 as a inner join EnterpriseSGE as b on a.idempresa = b.EnterpriseSGEId group by idempresa,b.name

this is what it should bring

name1 | 5041 |  583
name2 | 4031 |  1730
name3 | 5042 |  640
name4 | 4034 |  300
name5 | 6047 |  861
name6 | 5043 |  187
name7 | 4033 |  318

please edit your question with what you have tried so far in `c#` — SWilko, Sep 15 '22 at 13:00
What do you mean with "it works"? What are you trying to achieve? What data type are you trying to store the data in? When you say "Linq" are you actually referring to entity framework? — JHBonarius, Sep 15 '22 at 13:37
Updated, yes im referring to EF, i need to bring it and show it in a gridview — Roberto González Moreno, Sep 15 '22 at 13:41
Perhaps my [SQL to LINQ Recipe](https://stackoverflow.com/questions/49245160/sql-to-linq-with-multiple-join-count-and-left-join/49245786#49245786) might help you. — NetMage, Sep 15 '22 at 14:38

score 2 · Accepted Answer · answered Sep 15 '22 at 14:46

2

A straight forward translation of the SQL into LINQ yields:

var ans = from a in Descarga_XML_recepcion_V2
          join b in EnterpriseSGE on a.idempresa equals b.EnterpriseSGEId
          group 1 by new { a.idempresa, b.name } into ingroup
          select new {
            ingroup.Key.idempresa,
            ingroup.Key.name,
            cuenta = ingroup.Count()
          };

answered Sep 15 '22 at 14:46

NetMage

26,163
3
34
55

Thanks, this work, just have to wrap into () and add .ToList(), but it works, thank you very much – Roberto González Moreno Sep 15 '22 at 14:52
1

@RobertoGonzálezMoreno Yes, I don't instantiate queries in case you want to do something else with them (or use `await`, etc.) – NetMage Sep 15 '22 at 15:01

score 1 · Answer 2 · answered Sep 15 '22 at 14:14

1

Try :

var results = (from a in Descarga_XML_recepcion_V2
   join b in EnterpriseSGE on a.idempresa equal b.EnterpriseSGEId
   select new { a = a, b = b})
   .GroupBy(x => new { idempresa = x.a.idempresa, name = x.b.name})
   .Select(x => new {name = x.Key.name, idempresa = x.Key.idempressa, count = x.Count()})
   .ToList();

answered Sep 15 '22 at 14:14

jdweng

33,250
2
15
20

Error on "Key" doesnt exists in the actual context, and on "count=x.Count" says it cannot assign a group of methods – Roberto González Moreno Sep 15 '22 at 14:46
I updated two hours ago to x.Key – jdweng Sep 15 '22 at 16:38
I updated two hours ago to x.Key. Count may not need parenthesis . – jdweng Sep 15 '22 at 16:46

Query with join, group by and count to linq

2 Answers2