How To Convert Linq To Sql?

Question

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)

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));

If you want to convert **Sql to Linq**, then why does your title say *exactly the opposite*? — Peter B, Jan 05 '19 at 19:26
`it does not match the SQL Server result` in which way? What is the generated SQL? — Antonín Lejsek, Jan 05 '19 at 21:30

score 2 · Accepted Answer · answered Jan 05 '19 at 22:19

2

This should be enough:

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.

answered Jan 05 '19 at 22:19

Antonín Lejsek

6,003
2
16
18

I agree with that join with distinct it is not best practice. Also i provided another method by splitting the command into 2 parts. But i think the syntax you provided is better +1 – Hadi Jan 06 '19 at 10:12

Hadi · Answer 2 · 2019-01-06T10:09:27.927

You can get the same result using a Join operation. Or to split the command into 2 parts.

Linq SQL-Like syntax:

var query =
   (from d in db.Driver
   join dc in dc.Drive_Car on dc.Driver equals d.ID
   where dc.Finish_Date != null
   select d).Distinct();

Other method (Split into 2 parts)

var lst =
   (from dc in dc.Drive_Car 
   where dc.Finish_Date != null
   select dc.Driver).Distinct().ToList();

var query =
   (from d in db.Driver
    where lst.Contains(d.ID)
   select d);

References:

How To Convert Linq To Sql?

2 Answers2