I have been trying to write a linq code to get Manager detail by passing Employee id assigned to that particular Manager. I have 2 tables
//User Table
public UserProfile() //It has both Employee and Manager Records
{
public long UserProfileID{ get; set; } //Identity Column
public string FirstName{ get; set; }
public virtual ICollection<UserOfficialInfo> UserOfficialInfoes { get; set; }
}
//User Official Detail Table
public UserOfficialInfo() //It has Employee to Manager assignment
{
public long UserOfficialInfoID{ get; set; } //Identity Column
public long ReportingTo{ get; set; } //This column will store Manager Id for employee
public long UserProfileID{ get; set; } //Foreign Key to above table
public virtual UserProfile UserProfile { get; set; }
}
Note: As you can see 1st Table has both records and 2nd table has Reporting detail, where User with 1 reporting to User 2. So first i pass Employee 1 id and get reporting Id and then i'll get the details of reporting person from table 1 by taking reporting Id from 2nd table
Here is my code, which i tried to work
public IEnumerable<UserProfile> GetReportingPerson(long UserProfileID)
{
var user1 =
from u in DbContext.UserProfiles
let userofficial = from userss in DbContext.UserProfiles
join useof in DbContext.UserOfficialInfoes
on userss.UserProfileID equals useof.UserProfileID
where useof.UserProfileID == UserProfileID
select useof.ReportingTo
where u.UserProfileID == userofficial.FirstOrDefault()
select u;
return user1.ToList();
}
The above code gives the result but i'm using 2 linq code, is there any efficient way to get Manager detail in single shot.
If you are using entityframework then in your Model structure you must have virtual properties added like below-
//User Table
public class UserDetail //It has both Employee and Manager Records
{
public long UserID { get; set; } //Identity Column having both Emp/Manager
public string Name { get; set; }
public virtual List<UserOfficialDetail> UserOfficialDetails{ get;set; } //virtual property
}
//User Official Detail Table
public class UserOfficialDetail //It has Employee to Manager assignment
{
public long UserOfficialID { get; set; } //Identity Column
public long ReportingTo { get; set; } //This column will store Manager Id
public long UserID { get; set; } //Foreign Key to above table
public virtual List<UserProfiles> UserProfiles { get;set; } //virtual property
}
Then you can simply put in Your LinQ query as -
var managerRecords = DbContext.UserDetails.UserOfficialDetails.FirstOrDefault().UserProfiles.ToList();
You don't even need to match the Id(s). This is benefit of entity framework.