Click here to Skip to main content
15,569,696 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
I have 2 classes with joint that has been created by EF5
how can I do a query to get the list of products for specific project

in good old days it was simple I just needed to do this

select top(20) p.Id, p.Name from Product p
	  inner join ProjectProduct j
	  on p.Id=j.ProductId and j.ProjectId ='1'
	  order by p.Id desc

Now in EF world I am confused
seems I can't find a solid way to do that

public class Project
    public int Id { get; set; }
    public string Name { get; set; }
    public List<Product> Product { get; set; }

public class Product
    public int Id { get; set; }
    public string Name { get; set; }
    public List<Project> Project { get; set; }

public class ProjectProduct
    public int ProjectId { get; set; }
    public int ProductId { get; set; }

What I have tried:

var products = await _ctx.Project
                .Where(p => p.Id == id)
                        p => p.Product
Updated 12-Jul-21 0:07am
Gerry Schmitz 11-Jul-21 13:37pm    
You said you wanted id and name, yet you select only product.

You didn't "join" to get "Name" (which Name?)

"Top 20" would be a .Take( 20 ) in LINQ.
Member 11298447 11-Jul-21 16:42pm    
What I meant is get the Id and Name from product table based on selected project and possibly get the count of products in that project and show top 20

in SQL it's very easy as I provided on top
so I am looking to get the equivalent of that in linq

1 solution

var products = await _ctx.Project
    .Where(p => p.Id == id)
    .SelectMany(p => p.Product)
    .OrderByDescending(p => p.Id)
Share this answer
Member 11298447 12-Jul-21 10:39am    
Thank you Richard
that was exactly what I was looking for
that SelectMany was the trick

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

CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900