Harambe Attack Helicopter Harambe Attack Helicopter - 1 month ago 14
C# Question

Convert LINQ with multiple columns to Array (to be used by the 2nd LINQ)

USR_MSTR
- List of users who can use the application

PERSONNEL_MSTR
- Repository for all users and their details (first name, last name, etc.).

My goal is to show the list of users who can use the application along with its details. So I made a LINQ to get the
USR_MSTR
and compare it with the 2nd LINQ that gets the
PERSONNEL_MSTR
. My problem is I'm not sure yet how to pass the other details aside from
EMP_ID
using array and use it for the 2nd LINQ.

Note: I can't use anonymous type as I'm having errors with that.

//Get list of login users
var users = from u in _odb.USR_MSTR
select new UserInformationModel
{
empid = u.EMP_ID,
startdate = u.BGN_DT,
enddate = u.END_DT,
usegroupid = u.USR_GRP_ID
};

string[] userList = users.Select(u => u.empid).ToArray();

//Get user information of login users
var userPersonnel = from p in _odb.PERSONNEL_MSTR
where userList.Contains(p.EMP_ID)
select new UserInformationModel
{
empid = p.EMP_ID,
firstname = p.FNAME,
middlename = p.LNAME,
lastname = p.LNAME,
emailadd = p.EMAIL,
//Need to get the startdate, enddate and usergroupid from var users.
//How do I pass that to string[] userList and use it here?
};

Answer

It looks like a simple left join. One of the simplest ways to achieve this is by creating a view in your database. That is a good direction if you will access the joined data often and from multiple (application) views. However, overusing that approach can lead to your database having a glut of views which can become hard to maintain. "Let the database do what it's good at"

The Linq statement is something like...

IEnumerable<UserInformationModel> collection = from u in in _odb.USR_MSTR
    join p in _odb.PERSONNEL_MSTR on u.EMP_ID equals p.EMP_ID
    select new UserInformationModel()
    {
        empid = u.EMP_ID,
        startdate = u.BGN_DT,
        ...
        firstname = p.FNAME,
        ...
    };

It's worth looking at 101 Linq samples for more info. There are versions of this for lambdas as well, if you prefer.

Comments