Click here to Skip to main content
14,032,830 members
Rate this:
 
Please Sign up or sign in to vote.
I am unable to convert sql query to lamba expression in entity framework core. below is the sql query -

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


I need to select only few columns from both tables.
please help me on writing an lamba expression.

What I have tried:

I tried as below mentioned code its not working

_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.First_name.Count()
                            })
                           .Where(input => input.company == sb.ToString())
      .GroupBy(c => new { c.companyID, c.industryType, c.company, c.location });



I am having entity(Search) which has companyID,industryType,company,location,contacts as members of entity. I am return the value of the linq query to IQuerayable<search>. I am getting error as cannot convert to IQuerayable<search> from IQueryable<Igrouping< anonymous>>>
Posted
Updated 6-Feb-19 22:46pm

1 solution

Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 1

I'd do that this way:

string[] companies = {"'15 Minutes' Inc.", "!mpactMEDIA", "'Stuffed' Cajun Meat Market & Specialty Food Store"};
var result = _entities.Company
    .Where(com=>companies.Contains(com.Company_name))
    .Select(com=> new SearchCompany
    {
        companyID = com.Company_id,
        industryType = com.industry_id,
        company = com.Company_name,
        location = com.City,
        contacts = _entities.Employee.Where(e=>e.Company_id==com.Company_id).Count()
    });


[EDIT]Thanks to Richard Deeming[^] for valuable suggestion of code improvement.[/EDIT]
   
v2
Comments
Richard Deeming 7-Feb-19 17:23pm
   
Based on the original query, I don't think you need the y=>y.Contains part. The OP's query is looking for an exact match on the company name, so:
.Where(com => companies.Contains(com.Company_name))
Maciej Los 8-Feb-19 1:59am
   
Nice improvement!
Thanks, Richard.

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

  Print Answers RSS
Top Experts
Last 24hrsThis month


Advertise | Privacy | Cookies | Terms of Service
Web01 | 2.8.190423.1 | Last Updated 8 Feb 2019
Copyright © CodeProject, 1999-2019
All Rights Reserved.
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100