Transforming SQL procedure to Linq

Question

In Sql, let's say I have the following procedure

GetCars
(
   @Ids nvarchar(MAX) = NULL       // represent a list of Id (with comma)
   @TypeIds nvarchar(MAX) = NULL   // represent a list of TypeId (with comma)
)

// After transforming my Ids and TypeIds into CSVTABLE (comma seperated values table)  

SELECT * FROM CARS 
WHERE ((@Id IS NULL) OR ( CARS.Id IN (SELECT Id FROM CSVTABLE)) AND 
      ((@TypeId IS NULL) OR (CARS.TypeId IN (SELECT ID FROM CSVTABLE)))

Is is possible to transform this sql queries into a Linq?

Answer 1

Well, a direct translation is pretty easy:

var query = from car in dataContext.Cars
   where car.Id == null || dataContext.CsvTable.Select(x => x.Id)
                                               .Contains(car.Id)
   where car.TypeId == null || dataContext.CsvTable.Select(x => x.TypeId)
                                                   .Contains(car.TypeId)
   select car;

You'll have to try it to see if it actually creates the right SQL though.

You can simplify it somewhat:

var csvIds = dataContext.CsvTable.Select(x => x.Id);
var query = from car in dataContext.Cars
            where car.Id == null || csvIds.Contains(car.Id)
            where car.TypeId == null || csvIds.Contains(car.TypeId)
            select car;

(That may produce different SQL - I don't know, but it's worth a try.)

Answer 2

Just because is possible doesn't mean you should. Using @variable IS NULL OR @ variable = field (or even worse, using IN operator) is pretty much the sure shot way to trash performance.

Use explicit IF statements and separate SELECTs in T-SQL, and equivalent separate query expressions in LINQ:

IF (@ID IS NULL and @TypeID IS NO NULL)
  SELECT ... FROM CARS WHERE ...
ELSE IF (@ID IS NOT NULL AND @TypeID IS NULL)
  SELECT ... FROM CARS WHERE ...
ELSE IF (@ID IS NOT NULL AND @TypeID IS NOT NULL)
 SELECT ...

The problem with trying to build one single SELECT that satisfies different conditions depending on @variable parameters is that SQL stil has to build one query plan to cover all possible combination. Most times this plan will be the worst possible plan that does a full table scan. Even when the plan is somehow optimized, it will be optimized for the @variable values at the moment the plan was generated (due to parameter sniffing) and this plan will perform even worse than a trivial plan when @variables have different values on subsequent execution.

Answer 3

I've found a solution.

that would be something like this :

List<Car> Find(List<int> carIds, List<int> carTypeIds)
{
     var carQuery = from c in ctx.Cars
                    select c;

     if(carIds!=null && carIds.Count>0)
        carQuery = carQuery.Where(c=> carIds.Contains(c.id) );


     if(carTypeIds!=null && carTypeIds.Count>0)
        carQuery = carQuery.Where(c=> carTypeIds.Contains(c.typeId) );

     return carQuery.ToList();
}