luckyluke luckyluke - 6 months ago 17
SQL Question

Linq to EF query with criteria filter

I have two tables - below as seen in database

| Country | ID int | Name varchar |

| Car |ID int | Name varchar | CountryID int FK to Country


  1. I need to select all the Cars in country with ID 1

  2. I also need all the cars in countries WITH ID 2 and 3 whos ids(car) are in (4,5)



Using EF i have the below query.

List<int> listOfCountries = new List<int> { 1,2,3 };

var query = (
from country in context.Countries.AsNoTracking()
join car in context.Cars.AsNoTracking() on new { CountryID = country.ID}
equals new { CountryID = cars .CountryID }
where listOfCountries.Contains(prv.CountryID)
select car);


Is there any other way to do this instead of using a union? Do i need a case statement e.g when country id not equal to (1) then filter where car id in (4,5), how is this achieved? Thanks.

Answer

If i understand your question correctly, this could be it:

List<int> listOfCountries = new List<int> { 2,3 };
List<int> listOfCarIds = new List<int> { 4,5 };

var query = from car in context.Cars.AsNoTracking()
where car.Country.Id = 1 || (listOfCountries.Contains(car.Country.Id) && listOfCarIds.Contains(car.Id))