Joining a Many to Many table

I have a situation where I am providing a method to query for data in various ways. We provide the user with 4 different fitler criteria and they can mix and match as much as they want.

For example:

 public Fruit GetFruit(Boolean isLocal, string storeName, string classificationType, string state);

This is simple when all of the attributes are on the table, but my issue arises as a result of complexity in the data model. Three of my attributes are simple, they are just joins, but i have one table that sits behind a one to many relationship. So in order to query for it I have to do a many to many join.

So lets say i am trying to determine all fruit a store offers. A store has a list of fruits and our classificationType sits behind a many relationship (FruitClassification)

alt text http://tinyurl.com/39q6ruj

The only successful way i have been able to query th开发者_StackOverflow社区is in EF is by selecting all Fruits (by classification), and then selecting all stores that meet the filter criteria and then joining them.

You would think this query in ef would be functional:

var final = (
                from s in Stores
                join fc in FruitClassifications.Where(z=>z.Classifications.Code == classificationType && z.Classifications.Type.Code =="FRT").Select(x=>x.Fruit).Distinct()
                 on s.Fruits.Id equals f.Id
                 where s.Name=name && s.isLocal && s.State==state
                select s
                ).ToList();

But it runs horrible(and looks the same when i profile it), Is there any way i can push this query down to the database? A better way to query?

I think this is what you want:

var final = (from s in Stores
             where s.Name=name && s.isLocal && s.State==state
                   && s.Fruits.Any(f => 
                       f.FruitClassifications.Any(fc => fc.Code == classificationType
                                                           && fc.Type.Code == "FRT"))
             select s).ToList();

http://learnentityframework.com/LearnEntityFramework/tutorials/many-to-many-relationships-in-the-entity-data-model/

this might help you. EF has the possibility to generate those relations with navigation properties from the designer, so you don't have to use the join.