我想使用dotnetcore web api控制器中的linq方法从具有entityframework的多个表中获取数据。

Question

public class Mission
    {
        public int Id { get; set; }        
        public string Name { get; set; }        
        public long Duration { get; set; }// time stored in second        
        public string Aircraft { get; set; }
        public string Syllabus { get; set; }        
        public int MissionTypeId { get; set; }
        public int PhaseId { get; set; }        
        public MissionType Type { get; set; }        
        public  Phase Phase { get; set; }
    }

public class Phase
    {        
        public int PhaseId { get; set; }
        public string PhaseName { get; set; }
        public  ICollection<Mission> Missions { get; set; }

    }
public class MissionType
    {
        public int MissionTypeId { get; set; }
        public string MissionTypeName { get; set; }
        
        public  ICollection<Mission> Missions { get; set; }


    }

//当我在控制器中使用await context.Missions.ToListAsync()时，它给出了类似以下格式的项目列表。

    "id": 1,
    "name": "ID-1",
    "duration": 3600,
    "aircraft": "PT-6",
    "syllabus": "BASIC CONV",
    "missionTypeId": 1,
    "phaseId": 1,
    "type": null,
    "phase": null
}

//我需要此格式的列表。我可以使用哪种linq查询方法从数据库中获得结果，如下所示

    {
        "id": 1,
        "name": "ID-1",
        "duration": 3600,
        "aircraft": "PT-6",
        "syllabus": "BASIC CONV",
        "missionTypeId": 1,
        "phaseId": 1,
        "type": {
            "missionTypeId": 1,
            "missionTypeName": "ID - 1 "
        },
        "phase": {
            "phaseId": 1,
            "phaseName": "ID"
        }
    }

Answer 1

你需要明确地告诉EF Core你想要加载哪些相关的实体。

试试这个：

await context.Missions
    .Include(m => m.Type)
    .Include(m => m.Phase)
    .ToListAsync();

Answer 2

试试下面的代码：

var mission = (from m in _context.Missions
                join t in _context.MissionTypes on m.MissionTypeId equals t.MissionTypeId
                join p in _context.Phases on m.PhaseId equals p.PhaseId
                select new Mission
                {
                    Id = m.Id,
                    Name = m.Name,
                    Duration = m.Duration,
                    Aircraft = m.Aircraft,
                    Syllabus = m.Syllabus,
                    MissionTypeId = m.MissionTypeId,
                    PhaseId = m.PhaseId,
                    Type = m.Type,
                    Phase = m.Phase
                }).FirstOrDefault();