Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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?

like image 858
Neo Avatar asked Mar 31 '16 07:03

Neo


People also ask

Can we use joins in LINQ?

In a LINQ query expression, join operations are performed on object collections. Object collections cannot be "joined" in exactly the same way as two relational tables.

Can we use left join in LINQ?

You can use LINQ to perform a left outer join by calling the DefaultIfEmpty method on the results of a group join.

What is group join in LINQ?

The group join is useful for producing hierarchical data structures. It pairs each element from the first collection with a set of correlated elements from the second collection. For example, a class or a relational database table named Student might contain two fields: Id and Name .


1 Answers

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();
like image 180
Alper Tunga Arslan Avatar answered Sep 21 '22 04:09

Alper Tunga Arslan