Linq: Nested queries are better than joins, but what if you use 2 nested queries?

In her book Entity Framework Julie Lerman recommends using nested queries in preference to joins (scroll back a couple of pages). In her example see populates 1 field this way, but what id you want to populate 2?

I have an example here where I would prefer to populate the Forename and Surname with the same nested query rather than 2 separate ones. I just need to know the correct开发者_开发问答 syntax to do this.

public static List<RequestInfo> GetRequests(int _employeeId)
{
    using (SHPContainerEntities db = new SHPContainerEntities())
    {
        return db.AnnualLeaveBookeds
            .Where(x => x.NextApproverId == _employeeId ||
            (x.ApproverId == _employeeId && x.ApprovalDate.HasValue == false)) 
            .Select(y => new RequestInfo
            {
                AnnualLeaveDate = y.AnnualLeaveDate,
                Forename = (
                    from e in db.Employees 
                    where e.EmployeeId == y.EmployeeId 
                    select e.Forename).FirstOrDefault(),
                Surname = (
                    from e in db.Employees 
                    where e.EmployeeId == y.EmployeeId 
                    select e.Surname).FirstOrDefault(),
                RequestDate = y.RequestDate,
                CancelRequestDate = y.CancelRequestDate,
                ApproveFlag = false,
                RejectFlag = false,
                Reason = string.Empty
            })
            .OrderBy(x => x.AnnualLeaveDate)
            .ToList();
    }
}

There's nothing wrong with your query, but you can write it in a way that is much simpler, without the nested queries:

public static List<RequestInfo> GetRequests(int employeeId)
{
    using (SHPContainerEntities db = new SHPContainerEntities())
    {
        return (
            from x in db.AnnualLeaveBookeds
            where x.NextApproverId == employeeId ||
                (x.ApproverId == employeeId && x.ApprovalDate == null)
            orderby x.AnnualLeaveDate
            select new RequestInfo
            {
                AnnualLeaveDate = x.AnnualLeaveDate,
                Forename = x.Employee.Forename,
                Surname = x.Employee.Surname,
                RequestDate = x.RequestDate,
                CancelRequestDate = x.CancelRequestDate,
                ApproveFlag = false,
                RejectFlag = false,
                Reason = string.Empty
            }).ToList();
    }
}

See how I just removed your from e in db.Employees where ... select e.Forename) and simply replaced it with x.Employee.Forename. When your database contains the correct foreign key relationships, the EF designer will successfully generate a model that contain an Employee property on the AnnualLeaveBooked entity. Writing the query like this makes it much more readable.

I hope this helps.

try this

using (SHPContainerEntities db = new SHPContainerEntities())
{
    return db.AnnualLeaveBookeds
        .Where(x => x.NextApproverId == _employeeId ||
        (x.ApproverId == _employeeId && x.ApprovalDate.HasValue == false))
        .Select(y =>
            {
                var emp = db.Emplyees.Where(e => e.EmployeeId == y.EmployeeId);
                return new RequestInfo
                   {
                        AnnualLeaveDate = y.AnnualLeaveDate,
                        Forename = emp.Forename,
                        Surname = emp.Surname,
                        RequestDate = y.RequestDate,
                        CancelRequestDate = y.CancelRequestDate,
                        ApproveFlag = false,
                        RejectFlag = false,
                        Reason = string.Empty
                   };
            ).OrderBy(x => x.AnnualLeaveDate).ToList();
}