1

I am trying to generate a LINQ Query Statement in C#

My Sql query is ;

select c."Id", c."Name" ,count(*) as "PolicyCount" from "Customers" c 
join "PolicyDriver" pd on c."Id" = pd."CustomersId" 
join "PolicyOverview" po on pd."PolicyOverviewId" = po."Id" 
inner join "Setup" s on s."Id"=po."SetupId" 
group by c."Id"

This sql query gave me the solution that i want in PostgreSQL. In c# I converted to this query to :

var mergedQuery = await (from cc in context.CustomersEntity
                               join pd in context.PolicyDriver on cc.Id equals pd.CustomersId
                               join po in context.PolicyOverview on pd.PolicyOverviewId equals po.Id
                               join s in context.Setup on po.SetupId equals s.Id
                               group po by new { cc.Id, cc.Ids, cc.Name, po.Status, po.MobilePhone, po.HomePhone, po.WorkPhone } into grp
                               select new CustomerListModel
                               {
                                   Id = grp.Key.Id,
                                   CustomerId2 = grp.Key.Ids,
                                   Name = grp.Key.Name,
                                   Status = grp.Key.Status,
                                   PolicyCount = grp.Count(),
                                   
                               }).ToListAsync();

But It didnt work as I expected. PolicyCount of some rows wasnt correct. It can be caused by mergedQuery. Because i am not sure about it. It is okey for me,if you can help me to convert my sql query to linq

Melihcan Karaca
  • 19
  • 4
  • You need to explain what "didnt work as I expected" actually means. – Aluan Haddad Dec 22 '21 at 23:48
  • @AluanHaddad result of these query wasnt same. Especially for policy count row – Melihcan Karaca Dec 22 '21 at 23:51
  • I'm not sure how you expected to behave the same way since your SQL uses a `having` clause to filter the groups but you haven't tried to do the same. In LINQ you just use `where` after `group..by` to do the same – Aluan Haddad Dec 22 '21 at 23:51
  • Having statement is not important @AluanHaddad – Melihcan Karaca Dec 22 '21 at 23:54
  • Also see this answer: https://stackoverflow.com/questions/18809817/entity-framework-include-command-left-or-inner-join INNER JOIN is a SQL default, but not specifically for EF - so you could be wasting time on looking at a completely "different" query than you might think. You could also inspect the query EF is actually using in debug BTW, perhaps there are answers to be found there. – riffnl Dec 23 '21 at 00:18
  • @MelihcanKaraca it's called `where` in LINQ. Reread my comment – Aluan Haddad Dec 23 '21 at 05:40
  • Don't write LINQ with SQL in mind. More specifically: don't use `join` in LINQ, use navigation properties. Then you won't need grouping either. – Gert Arnold Dec 23 '21 at 08:34

0 Answers0