Using lambda expression unable to do join, group by and where condition at a time in entity framework core

Question

0.00/5 (No votes)

See more:

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 -

_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.

What I have tried:

I have tried in google but I am unable to find correct solution to return the query to IQueryable with join,group by and where conditions combination with lambda expression.

Posted 11-Jul-18 2:42am

sri4dotnet

Updated 11-Jul-18 2:46am

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

F-ES Sitecore · Answer 1 · 2018-07-11T02:46:00

Solution 1

If you want it IQueryable then don't do the "ToListAsync" at the end.

Posted 11-Jul-18 2:46am

F-ES Sitecore

Comments

sri4dotnet 11-Jul-18 8:56am

Then its giving error as cannot convert IQueryabale<igrouping> to IQuerayable<searchcompany>. SearchCompany is my custom class

F-ES Sitecore 11-Jul-18 9:24am

Think about the data being returned by your group. If your data is

1, a
2, b
2, c
3, d
3, e
3, f

then doing a GroupBy on the first item gives you a nested structure like

1
-- a
2
-- b
-- c
3
-- d
-- e
-- f

Do your group is three "Key" items (1, 2, 3) and each of those Keys is an IEnumerable of your actual data (SearchCompany), so you effectively have a list of lists.

So how do you want that nested structure to converted to a single list of SearchCompany objects? Rather than ToList you could do

.SelectMany(x => x.ToList())

That will give you just the SearchCompany objects but there are now no longer nested, so you've effectively discarded the groupby and you could have duplicate data. If you want a single list of SearchComapny items then you need to decide if you want the groupby and don't want a Distinct instead. Or you can do as above and return just the grouped data but that probably won't give you want you want either.