Nested Linq to get Manager detail based on Employee Id

c# entity-framework-6 linq mysql

Question

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

//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; }
}

enter image description here

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.

1
2
1/19/2016 11:11:14 AM

Popular Answer

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.

2
5/26/2017 4:47:28 AM


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow