How To Convert Linq To Sql?

I want write code for table, I know in SQL Server, but I don’t know Linq or EF.

SQL Server code:

SELECT *
FROM Driver
WHERE id IN (SELECT Driver
             FROM Drive_Car
             WHERE Drive_Car.Finish_Date IS NOT NULL)

​x
 
SELECT *FROM DriverWHERE id IN (SELECT Driver             FROM Drive_Car             WHERE Drive_Car.Finish_Date IS NOT NULL)​

I wrote this code in EF but it does not match the SQL Server results:

var drivers = db.Drivers
                .Where(d => db.Drive_Car
                              .Where(dc => dc.Driver == d.ID && dc.Finish_Date != null)
                              .Select(dc => dc.Driver)
                              .Contains(d.ID));

 
var drivers = db.Drivers                .Where(d => db.Drive_Car                              .Where(dc => dc.Driver == d.ID && dc.Finish_Date != null)                              .Select(dc => dc.Driver)                              .Contains(d.ID));​

Answer

This should be enough:

var drivers = db.Drivers
                .Where(d => db.Drive_Car
                              .Any(dc => dc.Driver == d.ID && dc.Finish_Date != null));

 
var drivers = db.Drivers                .Where(d => db.Drive_Car                              .Any(dc => dc.Driver == d.ID && dc.Finish_Date != null));​

This leads to Exists, which is in the end about the same as the IN approach. Duplicating rows through join and uniquifing them back through distinct is not always optimized away and you may end up with more overhead than necessary, so I would not prefer that way.

Advertisement

Answer