linq to one to many relationship Entity framework

Question

I'm trying to learn entity framework, and have built a simple application that lists and search's products, all going ok so far, the only issue that i seem to be having is filtering products on a one to many table.

ie

A product could have multiple types

table structure is :-

Product

productid

colourid

productname

ProductType

productTypeID

productID

typeID

Type

typeID

typeName

Our linq so far looks like :- (ColourID and ProductTypeID are variables passed into the procedure)

var qry = ctx.Products.AsQueryable();

qry = qry.Where(b => b.Product.ColourID == *ColorId*);

qry = qry.Where(b => b.Product.ProductTypes.Any(l => l.ProductTypeID == *ProductTypeID*));

qry.Select(c => new ProductDTO
                {
                    ProductID = c.ProductID,
                    ProductName = c.Product.ProductName
                }).ToList();

This is returning no results when actually it should ( this has been checked and tested using SQL against the DB )

Answer 1

You can use join to get data:

var result = (from p in ctx.Products
             join pt in ctx.ProductTypes on p.ProductTypeId equals pt.ProductTypeID
             where p.ColourId == ColorId && pt.ProducrtTypeID ==ProductTypeID
             select new ProductDTO
                     {
                       ProductID = p.ProductID,
                       ProductName = p.ProductName
                     ]).ToList();

using lambda syntax:

var result = ctx.Products
            .Join(ctx.ProductTypes,
                 product=> product.ProductTypeID,
                 producttype => producttype.ProductTypeID,
                (product, producttype) => new 
                                         { 
                                          Product = product,
                                          ProductType = producttype 
                                         })
           .Where(productAndType => productAndType.Product.ColourId == ColorId
               && productAndType.ProductType.ProductTypeID == productTypeID)
           .Select(x => new ProductDTO
                        {
                          ProductID = x,Product.ProductID,
                           ProductName = x.Product.ProductName
                        }).ToList();