CODEWITHSUNDEEP
c#asp.net-mvc
Bassam Aklan
Bassam Aklan

Reputation: 15

Can't perform Linq query from multiple tables mvc 4

I am beginner in linq and I am trying to write linq query to get data from multiple tables 

IQueryable<ESSRequest> ESSRequest = from essrequest in db.ESSRequest
                                    join emps in db.HrEmpS on essrequest.HrEmp_no equals emps.HrEmp_no
                                    join emp in db.HrEmp on emps.Emp_no equals emp.Emp_no
                                    where essrequest.Next_Approval == user_id
                                    select new { essrequest, emp_name=emp.Emp_Name};

I got the following error

Error 71 Cannot implicitly convert type 'System.Linq.IQueryable<AnonymousType#2>' to 'System.Linq.IQueryable<ESSCS.Models.ESSRequest>'. An explicit conversion exists (are you missing a cast?) E:\MVC\ESSCS\ESSCS\Controllers\RequestController.cs 138 53 ESSCS

Upvotes: 0

Views: 313

Answers (2)

Dawood Awan
Dawood Awan

Reputation: 7328

The type of your Class/Object on both sides of you equation on not same.

On Left Hand side you have: IQueryable<ESSRequest>

On Right Hand side you have an anonymous type, you are generating using LINQ: select new { essrequest, emp_name=emp.Emp_Name};

The both have to be same type.

IQueryable<ESSRequest> ESSRequest = from essrequest in db.ESSRequest
                                    join emps in db.HrEmpS on essrequest.HrEmp_no equals emps.HrEmp_no
                                    join emp in db.HrEmp on emps.Emp_no equals emp.Emp_no
                                    where essrequest.Next_Approval == user_id
                                    select essrequest;

Then if you need the Anonymous type you can do this, after above linq use this:

var aType = ESSRequest.Select(s => new { Employee = (s), emp_name = s.Emp_Name });

Then you can Access it like this:

foreach( var employee in aType)
{
var empNamefromObject = employee.Employee.Emp_Name;
var empName = employee.Emp_Name;
}

Upvotes: 2

DavidG
DavidG

Reputation: 118947

You are creating a query of an anonymous type rather than the actual type specified in the variable. so instead of creating a IQueryable<ESSRequest> you are creating a IQueryable<AnonymousTypeX>. Instead, when you do your select, specify the type there or in your case, it looks like you can simply select essrequest. If you also want to include the Emp_name value, you will either have to use the anonymous type or create another model class to use.

IQueryable<ESSRequest> ESSRequest = from essrequest in db.ESSRequest
                                    join emps in db.HrEmpS on essrequest.HrEmp_no equals emps.HrEmp_no
                                    join emp in db.HrEmp on emps.Emp_no equals emp.Emp_no
                                    where essrequest.Next_Approval == user_id
                                    select essrequest;

Or if you need the value of Emp_Name, create a class like this:

private class RequestAndName
{
    public ESSRequest ESSRequest { get; set; }
    public string Emp_Name { get; set; }
}

And populate it like this:

IQueryable<RequestAndName> ESSRequest = from essrequest in db.ESSRequest
                                        join emps in db.HrEmpS on essrequest.HrEmp_no equals emps.HrEmp_no
                                        join emp in db.HrEmp on emps.Emp_no equals emp.Emp_no
                                        where essrequest.Next_Approval == user_id
                                        select new RequestAndName
                                        {
                                            ESSRequest = essrequest,
                                            Emp_Name = emp.Emp_Name
                                        };

Upvotes: 0

Related Questions