I need some help with an optimisation of a lambda expression.
Entities
public class ProductGroup
{
[DatabaseGenerated(DatabaseGeneratedOption.Identity)]
[Key]
public int Id { get; set; }
[Required]
[Index(IsUnique = true)]
[StringLength(50)]
public string Code { get; set; }
[Required]
[StringLength(50)]
public string Name { get; set; }
public virtual ICollection<GroupProduct> GroupProducts { get; set; }
}
public class GroupProduct
{
[DatabaseGenerated(DatabaseGeneratedOption.Identity)]
[Key]
public int Id { get; set; }
// Foreign Key
public int ProductGroupId { get; set; }
public int ProductId { get; set; }
// Navigation Properties
[Required]
[ForeignKey("ProductGroupId")]
public virtual ProductGroup ProductGroup { get; set; }
[Required]
[ForeignKey("ProductId")]
public virtual Product Product { get; set; }
}
public class Product
{
[DatabaseGenerated(DatabaseGeneratedOption.Identity)]
[Key]
public int Id { get; set; }
[Required]
[Index(IsUnique = true)]
[StringLength(50)]
public string Code { get; set; }
[Required]
[StringLength(100)]
public string Name{ get; set; }
}
Expression Currently Used
var q = context.ProductGroups.Where(s => s.Code == code)
.Include(s => s.GroupProduct.Select(p => p.Product));
There are a lot more columns in the Product Entity. What I'm trying to do optimise the expression so when the SQL is generated on the Product.Code column is selected and not the entire Product table, along with the Related GroupProduct, and the owning ProductGroup.
When I try and "Filter" the Product entity I get a error:
The Include path expression must refer to a navigation property defined on the type. Use dotted paths for reference navigation properties and the Select operator for collection navigation properties. Parameter name: path
Any ideas?