0

I am trying to display tables connected by the foreign key DriverID. It generally works and displays data but the problem is that some Vehicles have 0 as DriverID as it is nullable and Vehicles with the FK of DriverID 0 does not appear in the list. I want to know if there's a way to implement something like this in order to show vehicles with any DriverID.

Example :

  Vehicle vehicle = db.Vehicles.Find(id);
        var VVM = new VehicleViewModel();
        var existingVehicle = (from a in db.Vehicles
                               where a.VehicleID == id
                               select new
                               {
                                   a.VehicleID,
                                   a.VehicleMake,
                                   a.VehicleType,
                                   a.PlateNumber,
                                   a.CodingDay,
                                   a.Location,
                                   a.DriverID
                                   //b.DriverFirstName,
                                   //b.DriverLastName
                               }).FirstOrDefault();

        var existingVehicleOffice = (from a in db.Vehicles
                                     join b in db.Drivers
                                     on a.DriverID equals b.DriverID
                                     where a.VehicleID == id
                                     select new
                                     {
                                         a.DriverID,
                                         b.DriverFirstName,
                                         b.DriverLastName
                                     }).FirstOrDefault();

        VVM.VehicleID = existingVehicle.VehicleID;
        VVM.VehicleMake = existingVehicle.VehicleMake;
        VVM.VehicleType = existingVehicle.VehicleType;
        VVM.PlateNumber = existingVehicle.PlateNumber;
        VVM.CodingDay = existingVehicle.CodingDay;
        VVM.Location = existingVehicle.Location;
        VVM.DriverID = existingVehicle.DriverID;

        if (VVM.CodingDay == "1")
        {
            VVM.CodingDayText = "Monday";
        }

        if (VVM.CodingDay == "2")
        {
            VVM.CodingDayText = "Tuesday";
        }

        if (VVM.CodingDay == "3")
        {
            VVM.CodingDayText = "Wednesday";
        }

        if (VVM.CodingDay == "4")
        {
            VVM.CodingDayText = "Thursday";
        }

        if (VVM.CodingDay == "5")
        {
            VVM.CodingDayText = "Friday";
        }

        if (VVM.DriverID == 0)
        {
            VVM.FullName = "N/A";

        }

        if (VVM.DriverID > 0 )
        {

            VVM.FullName = existingVehicleOffice.DriverLastName + ", " + existingVehicleOffice.DriverFirstName;

        }

Here is my current code for the list:

        public ActionResult List()
    {
        var repo = new ManagementRepository();
        var model = repo.ListVehicle();

        VehicleListVM VLVM = new VehicleListVM();
        VLVM.Vehicles = new List<VehicleViewModel>();


        VLVM.Vehicles = (from data in model
                         join b in db.Drivers
                         on data.DriverID equals b.DriverID
                         select new VehicleViewModel()
                         {
                             VehicleID = data.VehicleID,
                             VehicleType = data.VehicleType,
                             VehicleMake = data.VehicleMake,
                             PlateNumber = data.PlateNumber,
                             CodingDay = data.CodingDay,
                             Location = data.Location,
                             DriverID = data.DriverID,
                             DateRegistered = data.DateRegistered,
                             FullName = b.DriverLastName + ", " + b.DriverFirstName,
                             //DriverFirstName = b.DriverFirstName,
                             DriverLastName = b.DriverLastName,
                             IsReserved = data.IsReserved,
                             IsActive = data.IsActive,
                         }).ToList();




        return View(VLVM);
Joshua
  • 15
  • 7
  • You need to use something called 'left join'. Just look for "Entity Framework left join" and you will for sure find what you need. Some example here: https://stackoverflow.com/questions/5537995/entity-framework-left-join – PJDev Aug 18 '18 at 16:54
  • @PJDev Thanks! I followed the example and changed it up a bit and it actually worked. – Joshua Aug 22 '18 at 11:02

0 Answers0