CODEWITHSUNDEEP
c#sql-serverlinq
Neo
Neo

Reputation: 16239

SQL Query to LINQ syntax using not exist and join

My SQL query is like below working 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:

Operator '!' cannot be applied to operand of type 'int'

Any clue where I made mistake?

Upvotes: 10

Views: 2792

Answers (3)

Alex Art.
Alex Art.

Reputation: 8781

I think your original query is fine you just need to add another pair of brackets in 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();

Upvotes: 2

Alper Tunga Arslan
Alper Tunga Arslan

Reputation: 579

You can try like this: 

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

Upvotes: 3

Rahul Tripathi
Rahul Tripathi

Reputation: 172528

Try this:

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

Upvotes: 1

Related Questions