Join the Many to Many table

Question

I have a situation where I provide a method for querying data in different ways. We provide the user with 4 different selection criteria, and they can mix and match as much as they want.

For instance:

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

It is simple when all the attributes are in the table, but my problem arises due to the complexity of the data model. Three of my attributes are simple, they just join, but I have one table that sits at the same relation to each other. Therefore, to request this, I need to do a lot with many.

So let's say I'm trying to identify all the fruits offered by the store. There is a list of fruits in the store, and our Type classification is behind many relationships (FruitClassification)

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

The only successful way that I was able to request this from EF was to select all Fruits (by classification), and then select all the stores that match the filter criteria, and then join them.

You might think that this request 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 works terribly (and looks the same when I profile it). Is there any way I can pass this query to the database? Best way to request?

Answer 1

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();

Answer 2

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

. EF , .