Multi table join using Entity framework 4.1, should I use lambda or LINQ?

I'm new to the Entity Framework and I'm trying开发者_开发问答 to convert a multi table join to use the Entity framework. The original SQL looked something like this

JOIN tab_NewsCommunities S1 ON S1.News_ID = tab_News.NewsID
inner join tab_communities com on S1.Community_ID = com.Community_ID and com.RecordStatus_ID = 2
JOIN tab_Communities c ON S1.Community_ID = c.Community_ID
JOIN tab_Favorites uf ON S1.Community_ID = uf2.Community_ID
        WHERE uf.[User_ID] = @UserId 

The new Entity model looks something like this, I've simplified it some. Can anyone help me convert the sql to LINQ or lambda? I've seen a lot of samples on-line but they are all single table joins. Thanks

I suggest you read this blog post by CRAIG STUNTZ. The idea is that as long as you have correct relationship in your model, you can simply use navigation link to select all your properties without relying on JOIN operator. And honestly, this is the beauty of LINQ, why doing join yourself if your model can do it.

As long as you have defined the relationship b/w tables in EF designer, you don't have to do the joins again. Just need to eager load associated entities.

var result =  context.News.Include("NewsCommunity.Communities")
                          .Include("NewsCommunity.Favorites")
                          .Where( n => n.NewsCommunity.Favorites.Any(f => f.UserId == userId));       

You can use a lamda expression inside of the Include method with the newest version of Entity Framework. Then you can use a second select to get an extra layer deep into the relationship.

var result = context.News.Include(x=>x.NewsCommunity.select(z=>z.Communities)).Where(...)