Linq join() - Join two entities and select one

user_2751316 0
Viewed 84

Can someone please help me with the following linq query?

var userList = _context.Employee.AsQueryable();
var id = 1;
userList = userList                
           .Join(_context.EmployeePermission, 
                     ul => ul.EmployeeId,           
                     p => p.EmployeeId,             
               (userlist, perm) => new { Employee = userList, Permisson = perm }) 
            .Where(empAndPerm => empAndPerm.Permisson.Trading >= 1 && empAndPerm.Permisson.EmployeeId == id)
            .Select(x => x.Employee);

I am gettting following error;

Cannot implicitly convert type
'System.Linq.IQueryable<System.Linq.IQueryable<Models.Employee>>' to
'System.Linq.IQueryable<Models.Employee>'. Are you missing a cast?

How to resolve?

join linq c#
2 Answers

It seems you have a Typographical error in your code

(userlist, perm) => new { Employee = userList, Permisson = perm })

userList must be replaced by userlist. userList refers to System.Linq.IQueryable in first line:

var userList = _context.Employee.AsQueryable();

But userlist refer to the parameter of anonymous method which is correct item.

so the seventh line must be changed to:

(userlist, perm) => new { Employee = userlist, Permisson = perm })

Postscript:

your code needs some enhancements which are not in the scope of this question. these enhancements depend on your business requirements. but I mention some potential mistakes:

  1. your employees are not distinct after select so there is a duplicate employee and it's a little strange.
  2. you are using the same context and most of the time you can use navigation properties that are defined in the context instead of using join method manually.

this DotnetFiddle Sample might help you to choose the best approach for your requirement.

user_50679730

You can remove the ambiguity in userlist and userList just by not declaring the original userList variable. Just start off with

var id = 1;
var users = 
    _context.Employee
            .Join(_context.EmployeePermission, 
                  ul => ul.EmployeeId,           
                  p => p.EmployeeId,             
                  (userList, perm) => new { Employee = userList, Permisson = perm }) 
            .Where(empAndPerm => empAndPerm.Permisson.Trading >= 1 && empAndPerm.Permisson.EmployeeId == id)
            .Select(x => x.Employee);

Now there's no confusion with potential variable shadowing or capItalization issues.

user_80318130
Related