How to create a simple left outer join in lambda expression in EF ASP.NET MVC5

Question

I searched hours and hours for this without any luck. I'm trying to create a lambda expression to fetch data from two tables Schedule and Request. But i'm outputting a bool here. How can i do a proper left outer join to fix this? this is the best i could come up with

ViewBag.RequestList = db.Requests
    .Include(r => r.Department)
    .Select(r => db.Schedules.Any(s => s.RequestId == r.RequestId));

but its a bool not a list.

Assume my table models are as follows

public class Request{
 public virtual int RequestId { get; set; }
 public virtual string Remarks { get; set; }
}

public class Schedule{
 public virtual int ScheduleId{ get; set; }
 public virtual string Name{ get; set; }
 public virtual Request Request { get; set; }
}

I'm trying to see if each and every request has one or more schedules associated with it or not. so if i could attach schedule object to request and output it as a list then thats all i need. But I want to do it using LINQ and lambda expressions and I've seen queries as below;

var leftList = (from emp in db.Requests
                join d in db.Schedules
                on emp.RequestId equals d.RequestId into output
                from j in output.DefaultIfEmpty()
                select new { RequestId = emp.RequestId, 
                             name = emp.Department.Name, 
                             route = emp.Route.Name });

But that's not what i want, because i have to specify every field i need in new { RequestId = emp.RequestId, name = emp.Department.Name, route = emp.Route.Name }

Thanks a lot!

yes ScheduleId=j==null?0:j.ScheduleId will do but, see my comment on your answer. — guitarlass
– guitarlass, Commented Aug 22, 2014 at 7:46

Tim.Tang · Accepted Answer · 2014-08-22 07:40:27Z

1

just list what you want like this:

          var leftList =    from emp in db.Requests
                            join d in db.Schedules
                            on emp.RequestId equals d.RequestId into output
                            from j in output.DefaultIfEmpty()
                            select new 
                            { 
                               RequestId = emp.RequestId,
                               name = emp.Department.Name, 
                               route = emp.Route.Name,
                               ScheduleId=j==null?0:j.ScheduleId,
                               SName=j==null?""j.Name,
                            };

answered Aug 22, 2014 at 7:40

Tim.Tang

3,1881 gold badge17 silver badges18 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

guitarlass Over a year ago

As i clearly stated in my question i don't want to specify each and every property inside select new{} because my original model class Request has many number of properties. is there a way i could fetch all fields like we use * in sql?

Tim.Tang Over a year ago

I'm afraid there is no simple way to use select * for LINQ, you can try this select new { emp,j}

slava Over a year ago

The only way is select emp or select j or select {emp,j}

Collectives™ on Stack Overflow

How to create a simple left outer join in lambda expression in EF ASP.NET MVC5

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related