how to write SQl Query to LINQ to SQL MVC entity framework

Question

This is my sql Query: SELECT ParkingPlaceName, NoOfParkingPlaces, COUNT(Place.ParkingAreaID) AS NoOfCarsParked, NoOfParkingPlaces-COUNT(Place.ParkingAreaID) FROM ParkingArea LEFT JOIN Place ON ParkingArea.ParkingAreaID = Place.ParkingAreaID LEFT JOIN Car ON Car.CarID = Place.CarID GROUP BY ParkingPlaceName, NoOfParkingPlaces, Place.ParkingAreaID how to write in LINQ to SQL Query

Answer 1

I think you can use DefaultIfEmpty method to perform left outer join between 3 tables with let statements to deal with group results, hence the LINQ version from your SQL query should be similar (or same) like below:

var query = from pa in ParkingArea
            from pl in Place.Where(x => pa.ParkingAreaID == x.ParkingAreaID).DefaultIfEmpty()
            from ca in Car.Where(x => x.CarID == pl.CarID).DefaultIfEmpty()
            group new { pa, pl.ParkingAreaID, ca.CarID } by new { pa.ParkingPlaceName, pa.NoOfParkingPlaces, pl.ParkingAreaID } into grp
            let ParkingPlaceName = grp.Select(x => x.ParkingPlaceName)
            let NoOfParkingPlaces = grp.Select(x => x.NoOfParkingPlaces)
            let NoOfCarsParked = grp.Select(x => x.ParkingAreaID).Count()
            select new
            {
                ParkingPlaceName,
                NoOfParkingPlaces,
                NoOfCarsParked,
                AvailableParkingPlaces = NoOfParkingPlaces - NoOfCarsParked // new alias for holding substraction
            };

I decided not to use join ... in ... on ... equals ... directly after first and third from clauses, since it may lead into confusion about which table instance should be handled when creating group elements after performing join.