Click here to Skip to main content
15,886,110 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)
                .Select
                    (
                        p => p.Product
                    )
                    .FirstOrDefaultAsync();
Posted
Updated 11-Jul-21 23:07pm
v3
Comments
[no name] 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

Try:
C#
var products = await _ctx.Project
    .Where(p => p.Id == id)
    .SelectMany(p => p.Product)
    .OrderByDescending(p => p.Id)
    .Take(20)
    .ToListAsync();
 
Share this answer
 
v2
Comments
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