1

I converted sql query to linq query without any error.

Now, my question is that I get the data properly in sql query, while in linq query showing the whole data without filtering product null.

Here is my code:

SQL Query

SELECT Name
FROM ProductMaster product
LEFT JOIN TouchWastageGroup touchWastageGroup ON touchWastageGroup.Product = product.Name and touchWastageGroup.GroupNameId = 2 and touchWastageGroup.CaratId = 6
WHERE touchWastageGroup.Product IS NULL 

From this query data showing properly.

Linq Query

var productSelected = (from product in _productMasterRepository.Table
from touchWastageGroup in _touchWastageGroupRepository.Table
.Where(touchWastageGroup => touchWastageGroup.Product == product.Name && touchWastageGroup.GroupNameId == 2 && touchWastageGroup.CaratId == 6)                                   
.DefaultIfEmpty().Where(x => x.Product == null)
select new
{
   Result = product.Name
}).ToList();

Same query of linq showing whole data without filtering this (Where(x => x.Product == null)).

Is there a problem in linq syntax or in query?

Dokksen
  • 372
  • 1
  • 4
  • 17
s.k.Soni
  • 1,139
  • 1
  • 19
  • 37

2 Answers2

2

Check with following query to return which has no product

from product in _productMasterRepository.Table
join touchWastageGroup in _touchWastageGroupRepository.Table on new { Product = product.Product, GroupNameId = 2, CaratId = 6 } equals new { touchWastageGroup.Product, touchWastageGroup.GroupNameId, touchWastageGroup.CaratId } into joinedResult
from touchWastageGroup in joinedResult.DefaultIfEmpty()
where touchWastageGroup == null
select new { Result = product.Name }
user1672994
  • 10,509
  • 1
  • 19
  • 32
0

Try to use this query

var productSelected = from product in _productMasterRepository.Table
join from touchWastageGroup in _touchWastageGroupRepository.Table on
product.Name equals touchWastageGroup.Product into temp
from t in temp.DefaultIfEmpty()
where t.GroupNameId == 2 && t.CaratId == 6
select new
{
   Result = product.Name
}).ToList();
Wowo Ot
  • 1,362
  • 13
  • 21