Q: IQueryable with subquery filter returning wrong results I have 2 IQueryables, one with items of type dossier (called innerIq) with a foreign key carId. A second IQueryable with all of the car types.
I want the car IQueryable to contain only cars I that match the dossier IQueryable.
First I filter out all the unwanted dossiers from innerIq, then I want to grab all the cars for those dossiers.
Normally I do something like this:
iqCar = iqCar.Where(c => innerIq.Any(d => d.CarId == c.Id));

When I look at this in the profiler, I get a nice subquery (which is wrong) with cars that do not belong to those dossiers.
The subquery on dossier does not match the query when I do innerIq.toList() on the line before I add my where filter to the carIq. The where clause matches, but the select statement where he fetches dossiers does not match, if I manually add more properties to the select it will match.
When I rewrite my code to
 List<int?> dossierIds = dossiers.Select(d => d.Id).ToList();
 //GetIq returns iq with dossiers 
 innerIq = GetIq().Where(d => dossierIds.Contains(d.Id));

And use this innerIq in filter as such:
iqCar = iqCar.Where(c => innerIq.Any(d => d.CarId == c.Id));

This will give the right results. How is this possible, I'm not manipulating innerIq. Why does the subquery not match the main query where I do innerIq.ToList().
When I use the any method and i compare the results i can see that the difference is 41 cars (out of 50)
 List<decimal> carIds = cars.Select(x => x.Id).ToList(); // 49 results
 List<decimal?> carIdsInDossier = dossiers.Select(x => x.CarId).ToList(); // 50results
 List<decimal> diffrence = carIds.Where(carId => !carIdsInDossier.Contains(carId)).ToList();

 //diffrence is 41. 

This is something we do often and have never had issues with, or at least none we picked up. It's a bit worrying that we get different results then what we expect.
This is the subquery that gets created when you do iqCar.Where(c => innerIq.Any(d => d.CarId == c.Id));
SELECT TOP (50) *
FROM (
     SELECT [t0dossier].[dossier_id] AS [value], [t0dossier].[dossier_status], [t0dossier].[car_id]
     FROM [dbo].[sd_dossier] AS [t0dossier]
     ) AS dossiers
LEFT OUTER JOIN [dbo].[sd_order_supplier] AS orderSupplier ON dossiers.[value] = orderSupplier.[dossier_id]
WHERE dossiers.[dossier_status] IN ('ACT')
ORDER BY orderSupplier.[order_freeId] DESC

This is the query that you get when you do innerIq.ToList(); on the line BEFORE you do the any. Everything looks the same but the select is diffrent, resulting in diffrent results...
SELECT TOP (50) *
FROM (
    SELECT [t0dossier].[dossier_id] AS [value], [t0dossier].[dossier_freeId], [t0dossier].[company_code], [t0dossier].[dossier_type], [t0dossier].[dossier_status], [t0dossier].[dossier_isNew], [t0dossier].[dossier_destination], [t0dossier].[s_credat], [t0dossier].[s_moddat], [t0dossier].[s_creby_winuser], [t0dossier].[s_modby_winuser], [t0dossier].[car_id]
    FROM [dbo].[sd_dossier] AS [t0dossier]
    ) AS dossiers
LEFT OUTER JOIN [dbo].[sd_order_supplier] AS orderSupplier ON dossiers.[value] = orderSupplier.[dossier_id]
WHERE dossiers.[dossier_status] IN ('ACT')
ORDER BY orderSupplier.[order_freeId] DESC

