CODEWITHSUNDEEP
c#entity-frameworklinqentity-framework-core
Shaggs
Shaggs

Reputation: 111

Entity Framework - retrieve only if records that match in other table

The title is a little confusing, but I couldn't think of another way to word this.

So, as the title says, I'm using Entity Framework Core in my ASP.NET application. I have a few tables/models that are relevant for this question (I'll leave out irrelevant fields/properties):

The Customer table:

public class Customer
{
    public int ID { get; set; }
    [MaxLength(100)]
    public string Name { get; set; }
}

The Menu table:

public class Menu
{
    public int ID { get; set; }
    public int? ParentMenuID { get; set; }
    [MaxLength(50)]
    public string Name { get; set; }
}

The CustomerMenu table:

public class CustomerMenu
{
    public int ID { get; set; }
    public int MenuID { get; set; }
    public int? CustomerID { get; set; }
}

My goal is to have standard menu structure (menu records are linked to each other in a child/parent relationship), but customers will only see parts they are linked to, via the CustomerMenu table.

So, my questions is, what is the best way to getting the children menu records that are associated with a given customer?

Getting the child menus of an menu is easy enough - I can do that with this code:

var childMenus = _context.Menu.Where(m => m.ParentMenuID == id);

But I only want to return the menu records that are linked to a given customer via the CustomerMenu record/model.

So, my question is this: is there a way I can do this in a single call (retrieve child menu records that are associated with a customer), without having to manually cycle through each menu record and check if it is linked to a customer?

Thanks.

Upvotes: 0

Views: 768

Answers (1)

charithsuminda
charithsuminda

Reputation: 341

Try This 

(from a in context.Menu
               join c in context.CustomerMenu on a.ParentMenuID equals c.ID

               select a,c).ToList();

Upvotes: 1

Related Questions