SQL query to LINQ syntax using does not exist and appends

Question

My SQL query, as below, works fine in SQL I need to convert this to LINQ syntax

SQL -

SELECT [Key], Id FROM LocalizationKeys AS lk WHERE NOT EXISTS (SELECT 1 FROM Languages AS l JOIN LocalizationValues AS lv ON l.Id = lv.LanguageId WHERE l.Title = 'en-US' AND lv.LocalizationKeyId = lk.Id) 

LINQ Syntax I tried

 var result = (from lk in localizationKey where !(from l in lang join lv in localizationValue on l.Id equals lv.LanguageId where l.Title == "en-US" && lv.LocalizationKeyId == lk.Id select 1).FirstOrDefault() select lk).ToList(); 

Getting error:

The operator '!' cannot be applied to operand of type 'int'

Any clue where I made a mistake?

Answer 1

You can try the following:

 (from lk in localizationKey where (from l in lang join lv in localizationValue on l.Id equals lv.LanguageId where (l.Title == "en-US" && lv.LocalizationKeyId == lk.Id) select l).FirstOrDefault() == null select lk).ToList(); 

or

 (from lk in localizationKey where !(from l in lang join lv in localizationValue on l.Id equals lv.LanguageId where !(l.Title == "en-US" && lv.LocalizationKeyId == lk.Id) select l).FirstOrDefault().Any() select lk).ToList(); 

Answer 2

Try the following:

 (from lk in localizationKey where (from l in lang join lv in localizationValue on l.Id equals lv.LanguageId where !(l.Title == "en-US" && lv.LocalizationKeyId == lk.Id) select 1).FirstOrDefault() select lk).ToList(); 

Answer 3

I think your original request is fine, you just need to add another pair of brackets to the where clause:

 (from lk in localizationKey where !((from l in lang join lv in localizationValue on l.Id equals lv.LanguageId where l.Title == "en-US" && lv.LocalizationKeyId == lk.Id select 1).Any()) select lk).ToList();