在MVC中以视图模型的形式从LINQ的多连接返回单个记录

Question

我试图通过MVC中的LINQ从多连接返回一条记录，我使用我的人事数据库中的一个模型，该模型具有名为personnel的主表，有些字段只是一个id，依赖于其他表。因此，我需要一种方法来连接这些表以检索所有数据，通过id参数过滤它们，并将LINQ结果放入视图模型并将其返回给视图。这是我的数据库模型，我使用了下面的代码，但它不起作用。

public ActionResult Details(int id)
    {
        var q = from personnel in db.Personels
                join Genders in db.Genders on personnel.GenderId equals Genders.Id
                join nationality in db.Nationalities on personnel.NationalityId equals nationality.Id
                join religion in db.Religions on personnel.ReligionId equals religion.Id
                join course in db.Courses on personnel.CourseId equals course.Id
                join accBank in db.AccBanks on personnel.AccBankId equals accBank.Id
                join advanceStatus in db.AdvanceStatus on personnel.AdvanceStatusId equals advanceStatus.Id
                join chart in db.Charts on personnel.ChartId equals chart.Id
                join contract in db.Contracts on personnel.ContractId equals contract.Id
                join costCenter in db.CostCenters on personnel.CostCenterId equals costCenter.Id
                join job in db.Jobs on personnel.JobId equals job.Id
                join level in db.Levels on personnel.LevelId equals level.Id
                join military in db.Militaries on personnel.MilitaryId equals military.Id
                join subReligion in db.SubReligions on personnel.SubReligionId equals subReligion.Id
                join taxTable in db.TaxTables on personnel.TaxTableId equals taxTable.Id
                join unit in db.Units on personnel.UnitId equals unit.Id
                join workHouse in db.WorkHouses on personnel.WorkHouseId equals workHouse.Id
                join licence in db.Licences on personnel.LicenceId equals licence.Id
                join payPlace in db.PayPlaces on personnel.PayPlaceId equals payPlace.Id
                join married in db.MarriedStatus on personnel.MarriedId equals married.Id
                join jobStatus in db.JobStatus on personnel.JobStatusId equals jobStatus.Id
                where (personnel.Id == id)
                select new PersonnelViewModel()
                {
                    PersonelNo = personnel.PersonelNo,
                    PersonelFName = personnel.PersonelFName,
                    PersonelLName = personnel.PersonelLName,
                    FatherName = personnel.FatherName,
                    NId = personnel.NId,
                    RecognizeNo = personnel.RecognizeNo,
                    BirthDate = personnel.BirthDate,
                    BirthPlace = personnel.BirthPlace,
                    RecordCity = personnel.RecordCity,
                    WorkHouse = workHouse.Name,
                    CostCenter = costCenter.Name,
                    Course = course.Name,
                    Nationality = nationality.Title,
                    Licence = licence.Title,
                    Religion = religion.Title,
                    SubReligion = subReligion.Title,
                    Military = military.Title,
                    Level = level.Title,
                    Chart = chart.Name,
                    Job = job.Name,
                    PayPlace = payPlace.Title,
                    Unit = unit.Name,
                    Contract = contract.Title,
                    Gender = Genders.Title,
                    MarriedStatus = married.Title,
                    ChildQty = personnel.ChildQty,
                    InsNo = personnel.InsNo,
                    Address = personnel.Address,
                    PhoneNumber = personnel.PhoneNumber,
                    MobileNumber = personnel.MobileNumber,
                    Status = personnel.Status,
                    ProcessStatus = personnel.ProcessStatus,
                    JobStatus = jobStatus.title,
                    TaxPerFree = personnel.TaxPerFree,
                    TaxPriceFree = personnel.TaxPriceFree,
                    StartWorkDate = personnel.StartWorkDate,
                    StartRuleDate = personnel.StartRuleDate,
                    EndRuleDate = personnel.EndRuleDate,
                    StopWorkDate = personnel.StopWorkDate,
                    TimeYearlyPrice = personnel.TimeYearlyPrice,
                    DateYearlyPrice = personnel.DateYearlyPrice,
                    TaxMonth = personnel.TaxMonth,
                    AccBankNo = personnel.AccBankNo,
                    AccBank = accBank.Title,
                    SumTimeWork = personnel.SumTimeWork,
                    SumTaxPrice = personnel.SumTaxPrice,
                    SumPay = personnel.SumPay,
                    PrepareStatus = personnel.PrepareStatus,
                    TotalStandWork = personnel.TotalStandWork,
                    InsPerFree = personnel.InsPerFree,
                    InsPriceFree = personnel.InsPriceFree,
                    SumPerWork = personnel.SumPerWork,
                    SumPerWorkTemp = personnel.SumPerWorkTemp,
                    AccCode = personnel.AccCode,
                    PrnFish = personnel.PrnFish
                };
        return View(q);
    }

有办法吗?

Answer 1

这里有两个选择。可以使用Single()方法。这将检查整个集合中满足您可以提供的谓词或您可以使用SingleOrDefault()的单个实例。如果集合没有满足谓词要求的实例，则将返回默认值。

Single()和SingleOrDefault()方法将抛出一个异常，如果发现多个记录符合谓词要求。如果是这种情况，那么您可以使用First()方法或FirstOrDefault()方法。这些方法往往执行得更快，因为一旦在集合中找到一个实例，就不需要执行进一步的工作来测试是否存在更多的实例。