Adding data to a Model for a nesting web grid

Question

Greetings all I have to create a model from four tables but I'm stumped at append the two list together. The variable result is declared in the Models class as following: "internal MainGridDB result" and when I add the resume table too myRec I lose the data from result. FYI I would appreciate any help on Linq-to-entities. Thanks all.

public ActionResult GridSection3()
        {
            FDR_CorpEntities db = new FDR_CorpEntities();

            var result = (from a in db.M_Employees

                          where a.Employee_Rec_Key == 3633

                          join b in db.M_Offices on a.Office_Rec_Key equals b.Office_Rec_Key
                          join c in db.S_Emp_Education on a.Employee_Rec_Key equals c.Employee_Rec_Key

                          orderby a.Employee_Rec_Key ascending, c.DegreeAwardedYear descending
                          

                          select new MainGridDb
                          {
                              Employee_Rec_Key = a.Employee_Rec_Key,
                              First_Name = a.First_Name,
                              Middle_Name = a.Middle_Name,
                              Last_Name = a.Last_Name,
                              Clearance = a.Clearance,
                              Active = a.Active,
                              CurrentLaborCategory = a.CurrentLaborCategory,
                              Street_1 = b.Street_1,
                              City = b.City,
                              ST = b.ST,
                              DegreeType = c.DegreeType
                          }).ToList();

            result = result.DistinctBy(s => new { s.Employee_Rec_Key }).ToList();

            List<MainGridDb> myRec = new List<MainGridDb>();

            

            using (FDR_CorpEntities dc = new FDR_CorpEntities())
            {
                var o = result.OrderByDescending(v => v.Employee_Rec_Key);                    
                foreach (var i in o)
                {
                    var od = dc.S_Emp_Resume.Where(a => a.Employee_Rec_Key.Equals(i.Employee_Rec_Key)).ToList();
                    myRec.Add(new MainGridDb { result = i, LstS_Emp_Resume = od });
                }
                
            }
            return View(myRec);

        }

Answer 1

This will satisfy the model requirements.

 public ActionResult GridSection3()
    {
        QED_CorpEntities db = new QED_CorpEntities();

        var result = (from a in db.M_Employees

                      join b in db.M_Offices on a.Office_Rec_Key equals b.Office_Rec_Key
                      join c in db.S_Emp_Education on a.Employee_Rec_Key equals c.Employee_Rec_Key

                      //orderby a.Employee_Rec_Key ascending, c.DegreeAwardedYear descending
                      orderby a.Last_Name ascending, c.DegreeAwardedYear descending

                      select new MainGridDb
                      {
                          Employee_Rec_Key = a.Employee_Rec_Key,
                          First_Name = a.First_Name,
                          Middle_Name = a.Middle_Name,
                          Last_Name = a.Last_Name,
                          Clearance = a.Clearance,
                          Active = a.Active,
                          CurrentLaborCategory = a.CurrentLaborCategory,
                          Street_1 = b.Street_1,
                          City = b.City,
                          ST = b.ST,
                          DegreeType = c.DegreeType
                      }).ToList();

        result = result.DistinctBy(s => new { s.Employee_Rec_Key }).ToList();

        result.ToList();


        //List to hold muitl records

        List<MainGridDb> myRec = new List<MainGridDb>();
        //
        using (QED_CorpEntities dc = new QED_CorpEntities())
        {
            var o = result.OrderByDescending(v => v.Employee_Rec_Key); 
            foreach (var i in o)
            {
                var od = dc.S_Emp_Resume.OrderByDescending(a => a.FromDate).Where(a => a.Employee_Rec_Key.Equals(i.Employee_Rec_Key)).ToList();
                myRec.Add(new MainGridDb {  LstS_Emp_Resume = od });
            }
        }


        IEnumerator<MainGridDb> enumerator = result.GetEnumerator();
        int ix = 0;

        while (enumerator.MoveNext())
        {
            var x = enumerator.Current;
            myRec[ix].Employee_Rec_Key = x.Employee_Rec_Key;
            myRec[ix].Last_Name = x.Last_Name;
            myRec[ix].First_Name = x.First_Name;
            myRec[ix].CurrentLaborCategory = x.CurrentLaborCategory;
            myRec[ix].Active = x.Active;
            myRec[ix].DegreeType = x.DegreeType;
            myRec[ix].ST = x.ST;
            myRec[ix].Street_1 = x.City;

            ix++;
        }

        return View(myRec);

    }