Maybe this is an nested selects problem I have.

Hi guys, Thanks for solving my previous thread: Work around for LINQ query with join keyword. (http://www.mindscape.co.nz/forums/Thread.aspx?ThreadID=2041) Assume for some reason, I decided Product might not necessary belong to a Category, say we have a CategoryProductLink table to keep track of Products that belongs to Category. To keep track of Product, we added a key pointing to Company. In this scenario, I only want to get all products for a company under "Wine" category. We tried a few options such as var productIds = from cpl in UnitOfWork.Current.CategoryProductLink where cpl.Category.CompanyId == companyId && cpl.Category.Name == "Wine" select cpl.ProductId var product = from p in UnitOfWork.Current.Product where productIds.Contains(p.Id) select p; we get error: The provided type [System.Int32] must inherit from [MindScape.LightSpeed.Entity] Could you please suggest alternatives to solve this problem. Thanks in advance.

Could you clarify your revised model please?  Is it:

Product has a Company
Product has a ProductCategoryLink
ProductCategoryLink has... many Categories?  (So a Product can have many Categories?)

And you want to find all Products which are linked to a particular Company, and include amongst their categories a Category named "Wine"?  Is that right?

Product has a Company

Category has a company

Product may belong to Category by having a CategoryProductLink

Category may contain Products by having 0 or many CategoryProductLink

Entity definition

Product: CompanyId and other properties

Category: CompanyId and other properties

CategoryProductLink contains Id, ProductId, CategoryId

I want to find out all Product under "Wine" Category for a Company Thanks.

 var query = from cpl in uow.CategoryProductLinks
                  where cpl.Category.Name == "Wine" && cpl.Product.CompanyId == companyId
                  select cpl.Product;

 

Hi Ivan, Thanks for the answer and it's what we were looking for. I felt so unworthy now coz it seems so simple. and I wasn't able to come up that! Thanks again. Nathan

 hi ivan,

 I want to do distinct on the query that you posted. Can you tell me how I can do it?

 var query = from cpl in uow.CategoryProductLinks
                  where cpl.Category.Name == "Wine" && cpl.Product.CompanyId == companyId
                  select cpl.Product;

 

Your help is greatly appreciated.

Thanks!

 

Unfortunately, I don't think that's possible at the moment -- the Distinct operator is currently supported only for projections.  So unfortunately you will need to do a full (non-distinct) retrieval then use Distinct on the client side:

var query = ...;
var distinctResults = query.ToList().Distinct(identityComparer);

where identityComparer is an IEqualityComparer that returns two products as equal if they have the same ID.  Note that you must have the ToList() (or AsEnumerable(), etc.) operator in there to force the Distinct to operate on the client-side result set rather than being translated (or, rather, not translated) to SQL.

 Thanks for your response. I am using VB.NET and I don't have ToList() or AsEnumerable() operators avialable on query.Can you suggest some alternative way to do this.

 Thanks!

ToList and AsEnumerable are available in VB.NET.  They are defined as extension methods, which does mean the namespace needs to be imported.  Check that the System.Linq namespace is imported (Project Properties, References tab), or add Imports System.Linq at the top of your source file.