Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

EF - AND NOT EXISTS (SELECT 1 ...) with Entity Framework

I am trying to run this query using Entity Framework in my ASP.NET MVC project but I am not succeeding. Could anyone help me do this using LINQ?

SELECT p.*
FROM Produtos p
WHERE p.enterpriseID = '00000000000191' and p.productId <> '14' 
AND NOT EXISTS (SELECT 1 FROM SimilarProducts sp WHERE sp.similarId = 
p.productId)

TABLES:

PRODUCT                        SIMILARPRODUCTS
productId|enterpriseId         id|productId|similarId
like image 905
GustavoZafra Avatar asked Nov 05 '15 13:11

GustavoZafra


2 Answers

The direct equivalent LINQ construct of SQL NOT EXISTS (...) is !Any(...).

So

SELECT p.*
FROM Produtos p
WHERE p.enterpriseID = '00000000000191' and p.productId <> '14' 
AND NOT EXISTS (SELECT 1 FROM SimilarProducts sp WHERE sp.similarId = 
p.productId)

translates to

from p in db.Produtos
where p.enterpriseID = "00000000000191" && p.productId != 14 
&& !db.SimilarProducts.Any(sp => sp.similarId == p.productId)
select p;
like image 96
Ivan Stoev Avatar answered Oct 14 '22 10:10

Ivan Stoev


You can use Contains with Any which will work like NOT EXISTS in SQL. Like this:

var restuls = db.Produtos.Where(p => p.enterpriseID == '00000000000191' 
                 && p.productId != 14 
                 && !db.SimilarProducts.Any(sp =>sp.SimilarId == p.productId));
like image 10
Mahmoud Gamal Avatar answered Oct 14 '22 11:10

Mahmoud Gamal