How to Select a Navigation Property efficiently in LINQ C#

Question

I'm having Three Table

Table #1 : BossEmp

SNo     JobID     BossID      EMPID       StartDt
_____________________________________________________
  1        1           6          1       05-20-2016
  2        1           6          2       05-20-2016
  3        2           7          3       06-20-2016
  4        2           7          4       06-20-2016
  5        2           7          5       06-20-2016

Table #2 : Emplyee

EmpID    EmpName      Gender    DOB           Dep
_________________________________________________________    
    1    Sakthivel     M        12-11-1986    Development
    2    Regina        F        04-03-1989    Development
    3    Samantha      F        12-12-1987    Development
    4    Keerthi       F        08-18-1988    Development
    5    Pranitha      F        11-10-1985    Development
    6    Vijay         M        02-21-1987    Development
    7    Bhavana       F        12-06-1985    Development

Table # 3 : Job

JobID     Title            Description
__________________________________________
    1     RSI              Description RSI
    2     MSI              Description MSI

In Table #1 BossEMP -> JobID is a Foreign Key from the Job Table and BossEmp -> BossID, EmpID are a Foreign Key from the Employee Table.

The EDMX Class Diagram is

BossEmps -> EmpID === Employee -> EmpID
BossEmps1 -> BossID === Employee -> EmpID

BossEmps -> JobID === Job -> JobID

Now I Need to Create an Object of a Model

Class WorkInfo
{
    public List<Employee> EmpList { get; set; }
    public Job JobInfo { get; set; }
}

Now I need to Create List<WorkInfo>, it should contain only Female.

Kindly assist me how to select Navigation Property efficiently in LINQ C# to construct List<WorkInfo>

The Database contains more than 1000K Records.

I tried the following Code:

using (var db = new EmployeeEntities()) {
                db.BossEmps.Where(b => b.Employee.Gender == "F").Select(e => new {
                    Emp = new {
                        Name = e.Employee.EmpName,
                        Id = e.Employee.EmpId
                    },
                    JobInfo = new {
                        Name = e.Job.Title,
                        Id = e.Job.JobID
                    }
                }).GroupBy(x => x.JobInfo).ToList();
            }

Answer 1

If I got this right you need all the female employees who has same job. In this case I think you are looking for somethig like this:

var workInfo = context.BossEmp.Select(b => new
        {
            EmpList = b.Employes.Where(e => b.EmployeId == e.EmployeId && e.Gendar.Equals("F")),
            Job = b.Jobs.FirstOrDefault(j => b.JobId == j.JobId)
        });

This will create collection of objects with properties EmpList and Job. If you need it as List you can use ToList() method.

var employees = workInfo.EmpList.ToList();

and/or

var workInfos = workInfo.toList();

Answer 2

Here is a sample I quickly put together for you based on your classes.

This should select all females, with their corresponding Job info and employee info, from the BossEmp collection.

public class Program
{
    public static void Main(string[] args)
    {
        Program p = new Program();
    }

    public Program()
    {
        var bossEmpCollection = new List<BossEmp>()
        {
            new BossEmp() { SNo = 2, JobID = 1, BossID = 6, EmpID = 2, StartDt = DateTime.Now },
            new BossEmp() { SNo = 1, JobID = 1, BossID = 6, EmpID =  1, StartDt = DateTime.Now }
        };
        var employeeCollection = new List<Employee>()
        {
            new Employee() { EmpID = 1, EmpName = "Sakthivel", Gender = 'M', DOB = DateTime.Now, Dep = "Development" },
            new Employee() { EmpID = 2, EmpName = "Regina", Gender = 'F', DOB = DateTime.Now, Dep = "Development" }
        };
        var jobCollection = new List<Job>()
        {
            new Job() { JobID = 1, Title = "RSI", Description = "RSI" }
        };

        var workInfoCollection = from bEmp in bossEmpCollection
                                 join e in employeeCollection on bEmp.EmpID equals e.EmpID
                                 join j in jobCollection on bEmp.JobID equals j.JobID
                                 where e.Gender.Equals('F')
                                 select new WorkInfo() { EmpObject = e, JobInfo = j };

        foreach (var workInfo in workInfoCollection)
        {
            Console.WriteLine($"Emp Name: {workInfo.EmpObject.EmpName} Work Desc: {workInfo.JobInfo.Description}");
        }

        Console.ReadKey();
    }
}
public class BossEmp
{
    public int SNo { get; set; }
    public int JobID { get; set; }
    public int BossID { get; set; }
    public int EmpID { get; set; }
    public DateTime StartDt { get; set; }
}
public class WorkInfo
{
    public Employee EmpObject { get; set; }
    public Job JobInfo { get; set; }
}

public class Employee
{
    public int EmpID { get; set; }
    public string EmpName { get; set; }
    public char Gender { get; set; }
    public DateTime DOB { get; set; }
    public string Dep { get; set; }

}
public class Job
{
    public int JobID { get; set; }
    public string Title { get; set; }
    public string Description { get; set; }
}

Using this example, will construct the LINQ into one SQL SELECT statement, and only execute, once you loop through the workInfoCollection collection, making, one trip to the DB and back.

Every single time you you access a Navigational Property on a model class, the deferred execution in the background, makes a connection to the DB, and only runs the query for that specific items you are wanting to see, essentially, making more calls to the Database, making this less efficient, in my opinion.

If you run a SQL profiler, you will be able to verify this yourself.