group by and order by with linq

Question

There are 2 tables: Rents and People

I'm new in linq statements programming. Here is my sql query which i want to transform into linq:

select top 20 r.partnerid, max(p.PartnerName )
from Rents r join People p on p.Partnerid = r.Partnerid
where r.partnerid > 0 
group by  r.partnerid
order by  count(r.rentid) desc

I tried something like this, but I do not know how to get the People.PartnerName into the select statement:

var linqQuery = from r in meta.Rents 
             join p in meta.People on r.PartnerId equals p.PartnerId
             where r.PartnerId > 0 
             group r by r.PartnerId into pp
             select new
             {
                 PartnerId = pp.Key,
                 PartnerName = ??? //  PartnerName is a field/column from People
             } 
             // order by ?

Does PartnerName depend on PartnerId? Meaning, all partner objects with the same id, will they have the same name (company)? — Lasse V. Karlsen, Sep 27 '18 at 07:27
Will all people that is put into the same group based on partnerid have the same company? — Lasse V. Karlsen, Sep 27 '18 at 07:29
And company is depending on partner id? Meaning that all people related to the same partnerid will have the same company? Your question is confusing since you have referred to a Company in the SQL but call it PartnerName in the linq. — Lasse V. Karlsen, Sep 27 '18 at 07:30
It was my mistake... Company = PartnerName. I edited the question — Florin M., Sep 27 '18 at 07:30
And still, **are all people related to the same partnerid having the same company/PartnerName**? — Lasse V. Karlsen, Sep 27 '18 at 07:31
Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/180852/discussion-between-florin-m-and-lasse-vagsaether-karlsen). — Florin M., Sep 27 '18 at 07:43
Florin, consider to edit your question so that all comment questions are answered. Try to provide the information that you think people need to answer your question. Don't wait until they ask for this information — Harald Coppoolse, Sep 27 '18 at 09:14

score 0 · Answer 1 · answered Sep 27 '18 at 07:31

0

You might want to get the first one from the grouped list

PartnerName = pp.First().PartnerName

Or, you could also use max like your original query

PartnerName = pp.Max(x => x.PartnerName)

answered Sep 27 '18 at 07:31

Jamiec

133,658
13
134
193

Thanks! And also how can I order the list descending by r.RentId ? – Florin M. Sep 27 '18 at 07:33
@FlorinM. Its a bit counterproductive to ask followup questions in comments. Perhaps update your original question or ask a new one. – Jamiec Sep 27 '18 at 07:34
I specified in my sql query that I'm using order by descending. I specified also that I want to transform it into linq – Florin M. Sep 27 '18 at 07:34
@FlorinM. `orderby [table].[property] [descending],` between groupby and select – Drag and Drop Sep 27 '18 at 07:37
@DragandDrop Yes, if I order by a simple property. But I want the order to be descending based on Count – Florin M. Sep 27 '18 at 07:38

score 0 · Accepted Answer · answered Sep 27 '18 at 07:40

0

You can do something like

PartnerName = pp.Select(p => p.PartnerName).FirstOrDefault()

You will get some further explanation and a running fiddle from here: Group by in LINQ

answered Sep 27 '18 at 07:40

Cedric Mendelin

172
1
2
9

And how can I order descending by Count(r.RentId)? – Florin M. Sep 27 '18 at 07:41

group by and order by with linq

2 Answers2