LINQ to Entities无法识别方法'System.Collections.Generic.Dictionary`2[System.Int32,System.String] ToDictionary'

Question

我正在尝试从数据库检索存储在员工表中的EmployeeDTO列表。每个员工可以有一个或多个专业。专业存储在OrganizationSpecialtyType中。Employee和OrganizationSpecialtyType通过EmployeeSpecialty表进行"多对多"关联。

我使用以下查询，但遇到了标题中的异常:

var q = _context.Employee.Where(p => employeeEMIIDs.Contains(p.EmployeeID))
            .Select(p => new EmployeeDTO
                                    {
                                        EmployeeID = p.EmployeeID,
                                        GenderTypeID = p.GenderTypeID,
                                        FirstName = p.FirstName,
                                        LastName = p.LastName,
                                        Name = p.Name,
                                        MiddleName = p.MiddleName,
                                        DOB = p.DOB,
                                        Suffix = p.Suffix,
                                        Title = p.Title,
                                        Specialty = p.EmployeeSpecialty
                                                            .ToDictionary(d => d.OrganizationSpecialtyType.SpecialtyTypeID, d => d.OrganizationSpecialtyType.Name)
                                    }
                        );

在EmployeeDTO类中，属性Specialty的类型是public Dictionary<int, string>。

如果我执行此查询，一切正常：

var spec = _context.Employee.Where(p => p.EmployeeID == -9070).FirstOrDefault()
    .EmployeeSpecialty.ToDictionary(d =>
         d.OrganizationSpecialtyType.SpecialtyTypeID,
         d => d.OrganizationSpecialtyType.Name);

我该如何解决我第一个查询，以获取具有特殊技能的EmployeeDTO对象？

谢谢。

- kat1330

Answer 1

您可以先选择匿名类型，然后稍后设置字典。

var q = _context.Employee.Where(p => employeeEMIIDs.Contains(p.EmployeeID))
    .Select(p => new
    {
        Employee = new EmployeeDTO
        {
             EmployeeID = p.EmployeeID,
             GenderTypeID = p.GenderTypeID,
             FirstName = p.FirstName,
             LastName = p.LastName,
             Name = p.Name,
             MiddleName = p.MiddleName,
             DOB = p.DOB,
             Suffix = p.Suffix,
             Title = p.Title
         },
         Specialty = p.EmployeeSpecialty
             .Select(d => new 
             {
                 d.OrganizationSpecialtyType.SpecialtyTypeID,
                 d.OrganizationSpecialtyType.Name
             })
    })                    
    .AsEnumerable()
    .Select(a => 
    {
        a.Employee.Specialty = a.Specialty
            .ToDictionary(d => d.SpecialtyTypeID, d => d.Name);
        return a.Employee;
    });

- Yuliam Chandra

---

如何从这里获取IQueryable？ - Prithvi

IQueryable只能在AsEnumerable之前访问，因为这是实际从数据库获取对象的方法。 - Matheus Simon