Selecting a left join default key with Linq (to SQL)

I have a minimal case as follows:-

Table Posts

PostID - PK
PostDateTime

Table LocalisedPosts

PostID - JointPK
Culture - JointPK
LocalisedTitle
LocalisedBody

The table LocalisedPosts will always have a matching entry for a PostID that exists in table Posts with a Culture "en". It may have entries for other cultures, such as "es", "fr" etc.

How do I construct th开发者_如何学运维e simplest query that will return all the posts with either the current culture if available, or if not the default "en" culture.

I was thinking along the lines of:-

string lang = "fr" // lang is supposed to be the current culture (i.e. Thread.CurrentThread.CurrentUICulture.TwoLetterISOLanguageName)

var LocalisedPosts = 
    from p in Posts
    join pl in LocalisedPosts on p.PostID == pl.PostID
    where pl.culture == lang || pl.culture == "en"
    select new {p.PostID, p.PostDateTime, pl.LocalisedTitle, pl.LocalisedBody};

But that will produce records for "fr" and "en" on some lines. I guess I need to use orderby/distinct/first and or something but I can't quite figure it out. I am using "Or Else" not "Or" though.

---

Something like this?

var restult = (
    from p in Posts
    let localizedPost = (
        from pl in LocalisedPosts
        where p.PostID == pl.PostID && (pl.culture == lang || pl.culture == "en")
        let culturePriority = pl.culture == lang ? 0 : 1
        orderby culturePriority
        select pl)
        .First()
    select new { p.PostID, p.PostDateTime, localizedPost.LocalisedTitle, localizedPost.LocalisedBody };

Assumed that for each post at least one localized post exists either en or lang.

---

String lang = "fr"

var LocalisedPosts = 
    from p in Posts
    let anyWithCurrentLang = (Posts.Any(ps=>ps.LocalisedPost.Culture == lang))
    join pl in LocalisedPosts on p.PostID == pl.PostID
    where pl.culture == lang || (pl.culture == "en" && !anyWithCurrentLang )
    select new {p.PostID, p.PostDateTime, pl.LocalisedTitle, pl.LocalisedBody};

Edited: I think I understand you now, edited the query to check if you have any post with current lang, if not then it'll search for english ones. You might need to write a full blown query with from where join in the let statement, I assumed you have proper relationships set up already.