Need some LINQ love

[Roshawn]

Hi,

I have three tables in my database that I'm working with for a specific query. The tables
are Merchants, Brands, and Products.

OK. The Products table contains foreign keys from both the Merchants and Brands tables.
What I'm trying to do is get a list of brands by merchant and if the number of products
listed under each brand is greater than zero.

You still there? OK. Here's the LINQ code I wrote to see if I could get it done:

'I created a DataContext object variable named db
Dim brands As List(Of Brand)
Dim myMerchant as Merchant
myMerchant = db.Merchants.Single(Function(m) m.mer_name = "Gucci")
'error happens on the line below
brands = db.Brands.Where(Function(b) b.Products(0).Merchant.name = myMerchant AndAlso
b.Products.Count > 0).ToList


This code totally bombed. A System.NotSupportedException is thrown, stating, "Method
'Product get_Item(Int32)' has no supported translation to SQL"

And that's as close to a workable solution as I've come. Using the iterator works in
other code that I use. Unfortunately for me, it doesn't work in this case.

What am I doing wrong? Can someone point me in the right direction? Your help will be
greatly appreciated.

Thanks,
Roshawn

 

[Roshawn]

I'm thinking that I need another table that maps merchants to brands, something like a
many-to-many table.

Would that work?

 

[Anil Gupte/iCinema.com]

You need to jon those tables, and I think the following code should do it
for you.

http://home.hot.rr.com/graye/Articles/DataTableJoins.htm

 

[Roshawn]

Thanks for the reply, Steve. Sorry I took so long to respond but I've been busy with
other things...

As for the code you provided, it didn't work. When I remove the "(0)" after Products, all
Intellisense gives me are properties and methods associated with EntitySet(Of Product).
There is no mention of the Merchant EntitySet anywhere.

But thanks anyway. I'll keep trying, and when I get it, I'll share it here.

 

[Roshawn]

Hi Anil Gupte/iCinema.com,

I'm not using ADO.NET at all. However, I may need to do just that in order to get things
working correctly.

Thanks.

 

[Roshawn]

Still no luck. I even tried the following:

Dim brands As List(Of Brand)
Dim myMerchant as Merchant
myMerchant = db.Merchants.Single(Function(m) m.mer_name = "Gucci")
Dim products = db.Products.Where(Function(p) p.Merchant.mer_ID = myMerchant.mer_ID)
brands = (from b in products Select p.Brand).ToList

No error is generated but the results are not what I want. The Brand class has an ID
property and a Name property. Why is it that each property is set to "Gucci"? Don't know
why that happens, but that's what I get.

Well, it's back to the drawing board. I could sure use a little love here.

 

[Roshawn]

Can anybody help me out here?

 

[Roshawn]

@Steve

I got it!

Here's what worked:

brands = db.Brands.Where(Function(b) b.Products.Merchant.name = myMerchant.name).ToList
Dim brands As List(Of Brand)
brands = db.Products.Where(Function(b) b.Merchant.name = "Gucci").Select( _
Function(p) p.Brand).Distinct.ToList()

I only modified the code you provided just a little. The real breakthrough came when I
added the Distinct clause to it.

Thanks so much for your help!

Cheers