SQL Query to LINQ syntax using not exist and join

Question

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?

Alper Tunga Arslan · Accepted Answer · 2016-03-31 09:06:26Z

3

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

edited Mar 31, 2016 at 9:06

answered Mar 31, 2016 at 8:58

Alper Tunga Arslan

5793 silver badges10 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

Alex Art. Over a year ago

it should be the opposite logic in the first example FirstOrDefault() == null and

Neo Over a year ago

getting - A query body must end with a select clause or a group clause

Alper Tunga Arslan Over a year ago

yeah my fault i fixed it ty @AlexArt.

Neo Over a year ago

thanks working fine on linqPAD but inside my c# app it is fetching all records :( why ?

Alex Art. Over a year ago

.FirstOrDefault().Any() - You don't need to do .FirstOrDefault() before Any() it needs to be just .Any()

|

Alex Art. · Accepted Answer · 2016-03-31 09:24:32Z

2

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

answered Mar 31, 2016 at 9:24

Alex Art.

8,7813 gold badges32 silver badges50 bronze badges

2 Comments

Neo Over a year ago

I think issue is I'm taking table values inside var variable thats why may be it is wrong? any clue ? I'm getting all localizationKey values as a output

Alex Art. Over a year ago

Then most probably the issue is with your sql query. I assume that it returns the same result

Rahul Tripathi · Accepted Answer · 2016-03-31 07:31:57Z

1

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

answered Mar 31, 2016 at 7:31

Rahul Tripathi

173k33 gold badges292 silver badges341 bronze badges

2 Comments

Neo Over a year ago

Cannot implicitly convert type 'int' to 'bool'

Neo Over a year ago

I think issue is I'm taking table values inside var variable thats why may be it is wrong? any clue ? I'm getting all localizationKey values as a output

Collectives™ on Stack Overflow

SQL Query to LINQ syntax using not exist and join

3 Answers 3

7 Comments

2 Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

7 Comments

2 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related