Dailycode.info

Short solution for short problems

LINQ: Left outer join (non relational DB)

If you want to get data en detail data, from for example Routes. You will need to select the data and the details and then select only the distinct records. If your model framework support relations (Foreing key) then it easier to accomplish with LINQ. But in my case the relations were not set in the object model.

So this is how I get all routes with a detail that have a certain status, or routes that have no details:

var routes = from r in Ents.Routes

                from rd in Ents.RouteDetails.Where(s => s.RouteID == r.RouteID).DefaultIfEmpty()

                where (rd.Status == 10 || rd.Status == null)

select r;

 

var distinctRoutes = routes.Distinct<Routes>();

I cannot use the join with LINQ because there are no relations between the objects, so I select all routes with details that have status 10 or routes that have no details. The .DefaultIfEmpty will add an empty RouteDetail object to the list if it has no detail. So in the were I can check for null, then it includes the Routes that have no details. Now there can be duplicates because some routes have more detials. If we only select the Routes (select r) then the object with different details have the same Route object, so the distinct at the end will clear the duplicates.