LINQ to Entities - Left join not pulling data

Question

My table ClinicalAsset table wont show all the results + any matching results from the clinicalPAT table where the ClinicalAssetID is a match.

I have one result that is in the clinicalPAT table which matches on the ClinicalAsset table, but is only displayed in the MVC View, not the 40 results in the ClinicalAsset table

Here is what i have tried:

   var clinicalAssets = (from PATasset in db.ClinicalPATs
                                  join s in db.ClinicalAssets on PATasset.ClinicalAssetID equals s.ClinicalAssetID into output
                                  from j in output.DefaultIfEmpty()


                                  select new ClinicalASSPATVM
                                  {
                                      ClinicalAssetID = PATasset.ClinicalAssetID,
                                      ProductName = j.ProductName,
                                     InspectionDocumnets = PATasset.InspectionDocumnets,
                                 });

 var clinicalAssets = (from PATasset in db.ClinicalPATs
                              join s in db.ClinicalAssets on PATasset.ClinicalAssetID equals s.ClinicalAssetID into output
                              from j in output.DefaultIfEmpty(new ClinicalAsset())


                              select new ClinicalASSPATVM
                              {
                                  ClinicalAssetID = PATasset.ClinicalAssetID,
                                  ProductName = j.ProductName,
                                 InspectionDocumnets = PATasset.InspectionDocumnets,
                             });

    var clinicalAssets = (from PATasset in db.ClinicalPATs
                                  join s in db.ClinicalAssets on PATasset.ClinicalAssetID equals s.ClinicalAssetID 


                                  select new ClinicalASSPATVM
                                  {
                                      ClinicalAssetID = PATasset.ClinicalAssetID,
                                      ProductName = s.ProductName,
                                     Inspect

     var clinicalAssets = from  PATasset in db.ClinicalPATs
                              join s in db.ClinicalAssets on PATasset.ClinicalAssetID equals s.ClinicalAssetID


                              select new ClinicalASSPATVM
                              {
                                  ClinicalAssetID = PATasset.ClinicalAssetID,
                                  ProductName = s.ProductName,
                                 InspectionDocumnets = PATasset.InspectionDocumnets,
                             };

            var clinicalAssets = from s in db.ClinicalAssets
                                  join PATasset in db.ClinicalPATs on s.ClinicalAssetID equals PATasset.ClinicalAssetID


                                  select new ClinicalASSPATVM
                                  {
                                      ClinicalAssetID = PATasset.ClinicalAssetID,
                                      ProductName = s.ProductName,
                                     InspectionDocumnets = PATasset.InspectionDocumnets,
                                 };

        var clinicalAssets = (from s in db.ClinicalAssets
                              join cp in db.ClinicalPATs on s.ClinicalAssetID equals cp.ClinicalAssetID

                              select new ClinicalASSPATVM
                              {
                                  ClinicalAssetID = cp.ClinicalAssetID,
                                  ProductName = s.ProductName,
                                 InspectionDocumnets = cp.InspectionDocumnets,
                             });

Here is my overall controller:

      public ActionResult DashBoard(string sortOrder, string currentFilter,string searchString, int? page)
        {
            ViewBag.CurrentSort = sortOrder;
            ViewBag.NameSortParm = String.IsNullOrEmpty(sortOrder) ? "name_desc" : "";
            ViewBag.DateSortParm = sortOrder == "Date" ? "date_desc" : "Date";

            if (searchString != null)
            {
                page = 1;
            }
            else
            {
                searchString = currentFilter;
            }

            ViewBag.CurrentFilter = searchString;

            var clinicalAssets = (from s in db.ClinicalAssets
                                  join cp in db.ClinicalPATs on s.ClinicalAssetID equals cp.ClinicalAssetID

                                  select new ClinicalASSPATVM
                                  {
                                      ClinicalAssetID = cp.ClinicalAssetID,
                                      ProductName = s.ProductName,
                                     InspectionDocumnets = cp.InspectionDocumnets,
                                 });


            if (!String.IsNullOrEmpty(searchString))
            {
                clinicalAssets = clinicalAssets.Where(s => s.SerialNo.Contains(searchString)
                                       || s.PoNo.Contains(searchString));
            }

            switch (sortOrder)
            {
                case "name_desc":
                    clinicalAssets = clinicalAssets.OrderByDescending(s => s.PoNo);
                    break;
                case "Date":
                    clinicalAssets = clinicalAssets.OrderBy(s => s.PurchaseDate);
                    break;
                default:
                    clinicalAssets = clinicalAssets.OrderBy(s => s.ClinicalAssetID);
                    break;
            }
            int pageSize = 10;
            int pageNumber = (page ?? 1);
            return View(clinicalAssets.ToPagedList(pageNumber, pageSize));
        }
````

Answer 1

Have you tried a plain old SQL query? You are asking to trust all your joins and without seeing the data. I'd suggest running a query directly on the DB.