Using lambda unable to do join, groupby and where in EF Core

Question

Hi,

I am trying to do join, group by and where condition using lambda expression in entity frame core but unable to return the query as IQuerayable. Below is the code -

Hide Copy Code

await _entities.Company.Join(_entities.Employee,

                        com => com.Company_id,

                        emp => emp.Company_id,

                        (com, emp) => new SearchCompany

                          companyID = com.Company_id,

                          industryType = com.industry_id,

                          company = com.Company_name,

                          location = com.City,

                          contacts = emp.Employee_phone

})

                      .Where(input => input.company == companyName)

                    .GroupBy(c => new { c.companyID, c.industryType, c.company, c.location })

                     .ToListAsync();

SearchCompany is a class with companyID,industryType,company,location,contacts as string properties and I need to return these values.

Please help me in getting it solved. Thanks in advance.

srinath · Answer

Hi, Thank you for giving response but I am looking out for group by, join and where conditions in the same statement and select some columns by joining from 2 tables below is the sql query which is I am trying to convert - select c.company_id,industry_id,company_name,city,count(first_name) "Count" from company c join employee e on c.Company_id = e.Company_id where c.Company_name in ('"15 Minutes" Inc.', '!mpactMEDIA','"Stuffed" Cajun Meat Market & Specialty Food Store' ) group by c.company_id,industry_id,Company_name,city

Hemant Jindal · Answer

Hi Srinath hope this link might help you https://stackoverflow.com/questions/2767709/join-where-with-linq-and-lambda thanks Hemant Jindal

Vincent Maverick Durano · Answer

What's the current output of your code? What is the output that you are expecting?