There is a SQl query (example) that returns the result I need:

select * from visitedpoints left join [dbo].[Routes] r ON visitedpoints.code = r.tocode and r.Shift_Id = visitedpoints.Shift_Id where visitedpoints.Shift_Id = '184' order by visitedpoints.serialnumber

Here is Linq, which also returns what I need as the top SQL.

 var result = from v in _shift.VisitedPoints join rs in _shift.Routes on v.Code equals rs.ToCode into rr from r in rr.DefaultIfEmpty(new Route()) orderby v.SerialNumber select new VisitedPointRow { Code = v.Code, CityName = r.ToCityName, Name = r.ToName, DetourKMs = v.SerialNumber == 1 ? null : r.DetourKMs, KGsTransported = v.SerialNumber == 1 ? null : r.KGsTransported, PalletsTransported = v.SerialNumber == 1 ? null : r.PalletsTransported, SerialNumber = v.SerialNumber, ArrivalTime = v.ArrivalTime, DepartureTime = v.DepartureTime, Distance = v.SerialNumber == 1 ? null : r.Distance };

How can I do the same thing of this kind:

 List<VisitedPointRow> visitedPoints = _shift.VisitedPoints .OrderBy(v => v.SerialNumber) .Join(_shift.Routes, v => v.Code, r => r.ToCode, (v, r) => new VisitedPointRow { Code = v.Code, CityName = r.ToCityName, Name = r.ToName, DetourKMs = v.SerialNumber == 1 ? null : r.DetourKMs, KGsTransported = v.SerialNumber == 1 ? null : r.KGsTransported, PalletsTransported = v.SerialNumber == 1 ? null : r.PalletsTransported, SerialNumber = v.SerialNumber, ArrivalTime = v.ArrivalTime, DepartureTime = v.DepartureTime, Distance = v.SerialNumber == 1 ? null : r.Distance }) .ToList();

The result of the left part and the right part of the first two examples:

 10023 NULL 398 398 532 532 377 377 7058 7058 15155 15155

Result of the last:

 398 398 532 532 377 377 7058 7058 15155 15155

    0