Navigate across relationships in EF

Question

I'm begginer with EF, so my question is probably basic, but I couldn't find any answer...

I have a SQL Compact DB, from which I generated an entity model via the VS wizard. Everything seems fine, I retrieve all my relationships with the good mapping.

So as I understand from here: http://msdn.microsoft.com/en-us/library/bb386932(v=vs.110).aspx I should be able to do this, "querying across relationships":

IQueryable<Ingredient> IngQuery = from i in db.Ingredient
            where i.Product.ID == ProdID
            select i;

But I get the following error:

'System.Collections.Generic.ICollection' does not contain a definition for 'ID' and no extension method 'ID' accepting a first argument of type 'System.Collections.Generic.ICollection' could be found (are you missing a using directive or an assembly reference?).

This error occurs when you try to call a method or access a class member that does not exist

However, if I go deeper into the auto-generated code, I can see a public property 'ID' is declared for 'Product', and 'Ingredient' return a collection of 'Product':

• Ingredient

  public partial class Ingredient
  {
      public Ingredient()
      {
          this.Product = new HashSet<Product>();
      }
  
      public string Name { get; set; }
      public int ID { get; set; }
  
      public virtual ICollection<Product> Product { get; set; }
  }
  

• Product

  public partial class Products
  {
      public Products()
      {
          this.Ingredient = new HashSet<T_PROPSTHERAP>();
      }  
      public int ID { get; set; }
      public string Name { get; set; }
      public string Usage { get; set; }
      public byte[] Photo { get; set; }
      public int FK_OrganeProduct { get; set; }
      public int FK_Type { get; set; }
      public virtual OrganeProduct OrganeProduct { get; set; }
      public virtual Type Type { get; set; }
      public virtual ICollection<Ingredient> Ingredient { get; set; }
  }
  

But it doesn't work as I expected.

I can use the following as workaround:

List<Ingredient> lstIng = (_oTest.Products
                    .Where(p => p.Name == (string)lsbProducts.SelectedItem)
                    .SelectMany(p => p.T_PROPSTHERAP)).ToList();

But I don't think it's a smart way to do the trick... And I don't understand what I am missing...

Could anyone help?

Answer 1

If I understand you correctly, you are trying to find Ingredients based on Product's ID. As you have known, the Product property is a collection, not a singular object.

What you need is filtering Products based on Product's ID, you can use Any to filter collection.

IQueryable<Ingredient> IngQuery = from i in db.Ingredient
            where i.Product.Any(p => p.ID == ProdID)
            select i;

That means:

Looking for Ingredient if any of its product has ID equals to ProdID.

You can also use All, if what you are looking for is:

Looking for Ingredient if all of its products have ID equals to ProdID.

IQueryable<Ingredient> IngQuery = from i in db.Ingredient
            where i.Product.All(p => p.ID == ProdID)
            select i;

PS

However, based on your workaround, using Any is what you are looking for.