Multiple-field GroupBy Linq query is giving same count() value for all records

Here is my SQL query:

select sp.TitleEN as ServiceProvider, spt.TitleEN as ServiceProviderType, t.TicketNo, count(t.TicketNo) as TicketsCount 
from tickets as t
join branches as b
on t.branchid = b.id
join ServiceProviders as sp 
on b.ServiceProviderId = sp.id 
join ServiceProviderTypes as spt
on sp.ServiceProviderTypeid = spt.id
group by t.TicketNo, sp.TitleEN, spt.TitleEN

Result:

Simple and straight forward.

Now when I coded the same in LINQ, I wrote:

 _context.Tickets
                .Include(m => m.Branch)
                .ThenInclude(m => m.ServiceProvider)
                .ThenInclude(m => m.ServiceProviderType)
                .AsEnumerable()
                .GroupBy(m => new { m.TicketNo, ServiceProvider = m.Branch.ServiceProvider.TitleEn, ServiceProviderType = m.Branch.ServiceProvider.ServiceProviderType.TitleEn })
                .Select(m => new SPsAndSPTypes
                {
                    ServiceProvider = m.Key.ServiceProvider,
                    ServiceProviderType = m.Key.ServiceProviderType,
                    TicketNo = m.Key.TicketNo,
                    TicketCount = m.Key.TicketNo.Count()
                }).ToList();

This is what the LINQ query returns:

Why am I getting 11 only in the TicketsCount column of LINQ Query result? What am I doing wrong? My application is using ASP.NET Core 3.1.

Answers:

Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

Method 1

I wonder why developers use Include without knowing for which purpose it is introduced. If you have fully custom projection or grouping, Include just complicates query translation.

This is correct query:

var query = 
   from m in _context.Tickets
   group m by new 
      { 
         m.TicketNo, 
         ServiceProvider = m.Branch.ServiceProvider.TitleEn, 
         ServiceProviderType = m.Branch.ServiceProvider.ServiceProviderType.TitleEn       
      } into g
   select new 
   {
      g.Key.TicketNo,
      g.Key.ServiceProvider,
      g.Key.ServiceProviderType,
      TicketsCount = g.Sum(x => x.TicketNo == null ? 0 : 1)
   }

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0