Linq to Entities : relationship * - *

Question

Hello,

I have on my EF Schema a relationship * - * (designed by a middle table with 2 keys). When I want filter on each (ie : I want all market segment filtered by one function), I execute this query :

 var requete = from mkt in ent.MARKETSEGMENT_MKT
 where mkt.FUNCTION2_FUN.Where(fu => fu.FUN_ID == FunId).FirstOrDefault().FUN_ID == FunId

It's working, but it's ugly. How to craft a good query for filtered relationship * - * ?

Thank you

Answer 1

use the Join method.

this tiny example from nerddinner.com might help:

 var dinners = from dinner in FindUpcomingDinners()
        join i in db.NearestDinners(latitude, longitude)
        on dinner.DinnerID equals i.DinnerID
        select dinner;

Answer 2

It's almost never correct to use join in LINQ to Entities (or LINQ to SQL).

Since you don't specify your entity set names, I have to guess. I hope this makes sense:

var requete = from fun in ent.FUNCTION
              where fun.FUN_ID == FunId
              from mkt in fun.MARKETSEGMENT_MKT
              select mkt;

The important point here is that you have a two way association between FUNCTION and MARKETSEGMENT. Use it!