CODEWITHSUNDEEP
c#sql.netentity-framework
Terry Doll
Terry Doll

Reputation: 127

EntityFramework Core Subquery

I am new to Entity Framework Core and not sure how to translate below SQL query to use EF. Below is my query:

SELECT *
FROM Project
WHERE Project_Id IN(

    SELECT Project_Id
    FROM IP
    GROUP BY Project_Id
    HAVING COUNT(Project_Id)<= 2
)

I have tried below script, but returns all the records from the project table.

var projs = _context.Project
    .FromSqlRaw("SELECT * FROM Project WHERE Project_Id IN(SELECT Project_Id FROM IP GROUP BY Project_Id HAVING COUNT(Project_Id)<= 2)")
    .ToList();

Any help is appreciated!

Upvotes: 0

Views: 3604

Answers (2)

Nguyễn Văn Phong
Nguyễn Văn Phong

Reputation: 14208

You can try this way

var groupByCount = _context.IP.GroupBy(p => p.Project_Id)
                              .Select(g => new { Project_Id = g.Key, Count = g.Count() })
                              .ToList();
var Project_Ids = groupByCount.Where(p => p.Count <= 2).Select(p => p.Project_Id)
                               .ToArray(); 

var projs  = (from p in _context.Project
             where Project_Ids.Contains(p.Project_Id)
             select p).ToList();

Upvotes: 1

zsolt
zsolt

Reputation: 1611

If I guess your ER model right this should work:

_context.Project.Where(proj=> proj.IP.Count <= 2);

Upvotes: 1

Related Questions