Linq - I am trying to get list of items in which every item is assigned to a specific supplier and this supplier can have more than 1 Item type he can supply so a many to many table was created including a supplierId and supplierItemTypeId

The point is that I need to get List of items for each supplierItemType

view in the visual studio

   var TotalAssignedItems =  _context.SupplierAssignedOrderItems.Include(x=>x.Item)
        .Include(x=>x.Supplier)
        .ThenInclude(x=>x.TypeSuppliers).ToList();
    
      var FirstTypeItems = TotalAssignedItems.Where(x => 
         x.Supplier.TypeSuppliers.supplierItemTypdId == 1).ToList();

The above code is what I wanted to do but I am not able to access the attributes of the many to many table(TypeSuppliers)

CodePudding user response：

x.Supplier.TypeSuppliers.supplierItemTypdId == 1

TypeSuppliers is a collection. It doesn't have a single supplierItemTypdId property. It has many TypeSupplier objects, each of which has a supplierItemTypdId property

Let's make the problem simpler to think about:

class Person{
  string Name;
}

var people = new []{
  new Person { Name = "John" },
  new Person { Name = "Jane" }
};

We want to know "in our list of people, does any person have a name of John?" Note, we do not want "people where the name is John", we want to know "is one of them called John or not?"

We would not do this:

people.Name == "John"

We should do this:

people.Any(person => person.Name == "John" )

Going back to the original problem.. We want to know "our assigned item Supplier has a list of TypeSuppliers; does any one typeSupplier in the list have a supplierItemTypdId that is equal to 1?"

x.Supplier.TypeSuppliers.Any(ts => ts.supplierItemTypdId == 1)

May I recommend you stop using `x‘ for everything and start using variable names that reflect the type of object you're dealing with- it will help keep things straight in your mind as to what is what at various levels of nesting in your query..

TotalAssignedItems.Where(ai => 
  ai.Supplier.TypeSuppliers.Any(
    ts => ts.supplierItemTypdId == 1
  )
).ToList();

Also, please follow c# naming conventions - local variables are camelCase (you should call it totalAssignedItems) and properties are PascalCase (you should call it SupplierItemTypdId)

ps; supplierItemTypdId seems to have a typo

CodePudding user response：

Use join

var Result = (from s in _context.Suppliers
             join t in _context.TypeSuppliers on s.typeId equals t.supplierItemTypdId
             where t.supplierItemTypdId == 1
             select s
             ).ToList();