Linq join result to List

Question

Here is a linq inner join which returns what I want

 var innerJoinQuery =
                from employee in DbSet
                join department in DbContext.Set<Departments>() on employee.DepartmentID equals department.ID
                select new { ID = employee.ID, FirstName = employee.FirstName, LastName = employee.LastName, DepartmentID = employee.DepartmentID, DepartmentName = department.Name };


            List<Employees> innerjoinresult_as_employees_list = innerJoinQuery.AsEnumerable().Cast<Employees>().ToList();

Model of Employees is:

public class Employees
    {
        public int ID { get; set; }   

        [Required]
        public string FirstName { get; set; }
        [Required]
        public string LastName { get; set; }

        public int DepartmentID { get; set; }

        [NotMapped]
        public string DepartmentName { get; set; }
    }

My problem is that I cannot have var innerJoinQuery as List<Employees> so I can return it.

Does anyone knows how to do this?

Shyju · Accepted Answer · 2016-07-18T19:58:27.733

If i understood you correctly, you are looking for a way to transform your result to a list of Employees instances.

You can do that by updating the projection part of your LINQ statement. Currently it projects to an antonymous object. you want to project to Employees type

var innerjoinresult_as_employees_list =(from employee in DbSet
                                        join department in DbContext.Set<Departments>() 
                                        on employee.DepartmentID equals department.ID
                                        select new Employees { 
                                                   ID = employee.ID, 
                                                   FirstName =  employee.FirstName, 
                                                   LastName = employee.LastName,
                                                   DepartmentID = employee.DepartmentID, 
                                                   DepartmentName = department.Name }
                                        ).ToList();

The result (variable innerjoinresult_as_employees_list's value will be a list of Employees class)

I get this error ... -> The entity or complex type 'Ferrero.Model.Employees' cannot be constructed in a LINQ to Entities query. — Give IT, Jul 18 '16 at 20:08
my error is explained in this question and answer http://stackoverflow.com/questions/5325797/the-entity-cannot-be-constructed-in-a-linq-to-entities-query — Give IT, Jul 18 '16 at 20:14

score 1 · Answer 2 · answered Jul 18 '16 at 19:55

1

You are projecting to an anonymous type, not Employee objects. Change your query to:

var innerJoinQuery =
    from employee in DbSet
    join department in DbContext.Set<Departments>()
        on employee.DepartmentID equals department.ID
    select new Employee { ID = employee.ID, 
                          FirstName = employee.FirstName, 
                          LastName = employee.LastName, 
                          DepartmentID = employee.DepartmentID, 
                          DepartmentName = department.Name 
                        };

List<Employees> innerjoinresult_as_employees_list = innerJoinQuery.ToList();

answered Jul 18 '16 at 19:55

D Stanley

139,271
11
154
219

I get this error ... -> The entity or complex type 'Ferrero.Model.Employees' cannot be constructed in a LINQ to Entities query. – Give IT Jul 18 '16 at 20:09
1

Is `Employee` an entity type in your model? You can;t project to entity types - only anonymous types on non-entity (DTO) types. Why are you trying to project to an entity type instead of using them directly? – D Stanley Jul 18 '16 at 20:30
Yea yea.. Now I know that.. Thank you for your answer you were right :) – Give IT Jul 18 '16 at 20:31

score 1 · Answer 3 · answered Jul 18 '16 at 19:58

Haven't tested this, but try it out; you need to project to the correct type:

     var innerJoinQuery =
                (from employee in DbSet
                join department in DbContext.Set<Departments>() on employee.DepartmentID equals department.ID
                select new Employees{ ID = employee.ID, FirstName = employee.FirstName, LastName = employee.LastName, DepartmentID = employee.DepartmentID, DepartmentName = department.Name }).ToList();

List<Employees> employees = new List<Employees>(innerJoinQuery);

Linq join result to List

3 Answers3