CODEWITHSUNDEEP
c#linqentity-framework
kat1330
kat1330

Reputation: 5332

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

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.

Upvotes: 6

Views: 3994

Answers (1)

Yuliam Chandra
Yuliam Chandra

Reputation: 14640

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

Upvotes: 9

Related Questions