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