LINQ to Entities does not recognize the method 'System.Collections.Generic.Dictionary`2[System.Int32,System.String] ToDictionary

Question

I am trying to retrieve list of EmployeeDTO from DB which are stored in Employee table. Every employee can have one or more specialty. Specialty are stored in OrganizationSpecialtyType. Employee and OrganizationSpecialtyType are related with "many-to-many" via EmployeeSpecialty table.

I'm using the following query for that, and got an exception like in title:

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)
                                    }
                        );

In the EmployeeDTO class the property Specialty is type public Dictionary<int, string>.

If I execute this query, everything works normally:

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

How I can solve my first query to obtain EmployeeDTO with specialties?

Thanks.

Answer 1

You can select to anonymous type first, then set the dictionary later.

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;
    });