Double filtering in LINQ

Question

I need to combine all the data according to the GroupIDs to which these GroupIDs belong to EmpNo

public IEnumerable<EmployeeWithEmail> GetAllEmployeesWithEmail(int EmpNo)
{
    using (var context = new SQL_TA_SCOREBOARDEntities1())
    {
        return (from ea in context.View_SystemAdminMembers
                join vh in context.View_HCM on (Int16)ea.EmpNo equals vh.EmpNo
                join rl in context.EmployeeAccessLevels on ea.RoleID equals rl.id into outer_join
                from subjoin in outer_join 

              //need code to join all data according to EmpNo GroupIDs

                group new
                {
                    ea.EmpNo,
                    subjoin.Role,
                    vh.EmailAddress,
                    vh.LNameByFName,
                    ea.Active

                } by vh.LNameByFName into grp
                let item = grp.FirstOrDefault()
                orderby item.Role ascending
                select new EmployeeWithEmail
                {
                    EmpNum = item.EmpNo ?? 0,
                    Role = item.Role,
                    EmailAddress = item.EmailAddress,
                    LNameByFname = item.LNameByFName,
                    Active2 = item.Active ?? false

                }).ToList();

    }
}

I think I'm trying to filter and combine common data twice, but there are actually two filters that I don’t know how to manage.

So my conclusion will look like this:

EmpNo --->  __  01 |  01 |  01 | 01

GroupID --->  __10 |  10 |  20 | 20

Data ---> _________Apple | Apple | Orange | Orange

I can filter EmpNo 01 and GroupID 10, but What if EmpNo belongs to two groups? Sorry for not finding the right terms.

Thanks in advance.

Answer 1

Based on your comment, the SQL you are trying to create should be (I simplified a bit)

SELECT EmployeeAccess.EmpNo, View_SystemAdminMembers.LNameByFName, View_SystemAdminMembers.GroupName,
        View_SystemAdminMembers.Role, View_SystemAdminMembers.Active, View_SystemAdminMembers.EmpNo, 
        View_SystemAdminMembers.RoleID 
FROM EmployeeAccess 
    INNER JOIN View_SystemAdminMembers ON EmployeeAccess.GroupID = View_SystemAdminMembers.GroupID 
WHERE (EmployeeAccess.EmpNo = '01')

, :

from ea in context.View_SystemAdminMembers
     join vh in context.View_HCM on (Int16)ea.EmpNo equals vh.EmpNo
     join rl in context.EmployeeAccessLevels on ea.RoleID equals rl.id into outer_join
     from subjoin in outer_join

, , SQL-, , :

var query =
from ea in context.EmployeeAccess
join vsam in context.View_SystemAdminMembers on ea.GroupID equals vsam.GroupID
where ea.EmpNo == "01"
select new
{
    ea.EmpNo, vsam.LNameByFName, vsam.GroupName, vsam.Role, vsam.Active, vsam.EmpNo, vsam.RoleID
};

( ), :

var query =
    context.EmployeeAccess
    .Join(context.View_SystemAdminMembers, allEA => allEA.GroupID, allVSAM => allVSAM.GroupID, (ea, vsam) => new {ea, vsam})
    .Where(combined => combined.ea.EmpNo == "01")
    .Select(combined => combined.ea.EmpNo, combined.vsam.LNameByFName, combined.vsam.GroupName, combined.vsam.Role, combined.vsam.Active, combined.vsam.EmpNo, combined.vsam.RoleID);

( - , .Select(combined => combined.ea) - , 100% ...)

, "var query" IQueryable, , ToList , . , , , (, GroupBy - ). , IQueryable , ; query , , -

query = query.Where(c => c.LNameByFName.Contains("A"))

- .

: View_SystemAdminMembers, EmployeeAccess.EmpNo, , , . View_SystemAdminMember, . .

Answer 2

, . , -?

, , , .

1 - , . , , EmployeeWithEmail. , . IEnumerable<Employee> IEnumerable<IEmployeeEmailView>

2 - . ( , , , ). . , - . :

from ea in context.View_SystemAdminMembers
join vh in context.View_HCM on (Int16)ea.EmpNo equals vh.EmpNo
join rl in context.EmployeeAccessLevels on ea.RoleID equals rl.id into outer_join
from subjoin in outer_join 

- IEnumerable<Employee>

public class Employee {
     public int Id { get; set; }
     public string Role { get; set; }
     public string EmailAddress { get; set; }
     public string Name { get; set; }
     public bool IsActive { get; set; }
}

. LINQ-to-SQL. ...

IEnumerable<Employee> employees = <your renamed function>();
var employeesGroupedByName = employees.GroupBy(e => e.Name);

, . , , / .

, , - :

public class EmployeeRepository {
    public IEnumerable<Employee> GetAll() {
        // This function won't compile. I don't know the syntax for this type of LINQ
        using (var context = new SQL_TA_SCOREBOARDEntities1()) {
            return (from ea in context.View_SystemAdminMembers
            join vh in context.View_HCM on (Int16)ea.EmpNo
            join rl in context.EmployeeAccessLevels on ea.RoleID equals rl.id into outer_join
        }
    }

    public IEnumerable<Employee> GetAllEmployeesWithEmployeeId(int employeeId) {
        return GetAll().Where(e => e.Id == employeeId).ToList();
    }

    public IEnumerable<Employee> SomeOtherFunctionThatDoesWhatYouWantToDoFromThisPost() {
        // You could also create a class that extends IEnumerable<Employee> to
        // encapsulate the logic so that the repository doesn't have a million functions
    }
}

, . , , , , .