How to Display result Values even null in c# Windows form with LINQ

Question

Hi, I'am using Entity Framwork winform, and I'am trying to display Orders in dataGridView with department name and Roomnr and even those without Roomnr where Roomnr is null, But shows me only those with department and RoomId is not null. How can I display even those departments with RoomId == null ?
Here is my code:

var Customerorders = (from u in db.Orders
                      join s in db.Employee on u.Staffid equals s.EmployeeId
                      join d in db.Department on u.DepartmentId equals d.DepartmentId
                      join r in db.Room on u.RoomId equals r.RoomId
                      where u.OrderNr == 1005 || u.OrderNr == 1005 && u.RoomId == null
                      select new
                      {
                         CheckinoutId = u.CheckInAndOutId,
                         CheckinDate = u.CheckInDate,
                         Checkout = u.CheckOutDate,
                         Department = d.DepartmentName,
                         RoomNr = r.RoomNr,
                         Personal = s.FirstName+" "+s.LastName
                         }).ToList();
                         if(Customerorders != null) // I Tried even with out if condition
                         {

                          dgvOrders.DataSource = Customerorders;
                         }

Isn't possible to display even orders without rooms? Please help

I'd suggest using navigation properties instead of doing all those joins if you have them setup. — juharr, Feb 05 '20 at 21:21
@juharr Thank you for respose , can you please show me how you mean, I'am new for this staffs and this is my best code I'am trying — Anna, Feb 05 '20 at 21:25
@dcg Thank you for your response, but how do you mean Left join, how shall I formulate this code with left join? — Anna, Feb 05 '20 at 21:28
You should be able to do something like `from u in db.Order from s in u.Employees...` but it really depends on the relationships between the various tables. — juharr, Feb 05 '20 at 21:39
@Anna take a look at [this](https://stackoverflow.com/a/3413732/4152153) to get an idea of how to make a `LEFT OUTER JOIN` with LINQ. Given you have many joins it would get a little messy though — dcg, Feb 05 '20 at 22:04

score 1 · Accepted Answer · answered Feb 05 '20 at 21:37

This is LEFT JOIN for DbSet<Room> in LINQ

var Customerorders = (from u in db.Orders
                      join s in db.Employee on u.Staffid equals s.EmployeeId
                      join d in db.Department on u.DepartmentId equals d.DepartmentId
                      join r in db.Room on u.RoomId equals r.RoomId into r2
                      from r3 in r2.DefaultIfEmpty()
                      where u.OrderNr == 1005
                      select new
                      {
                          CheckinoutId = u.CheckInAndOutId,
                          CheckinDate = u.CheckInDate,
                          Checkout = u.CheckOutDate,
                          Department = d.DepartmentName,
                          RoomNr = r3.RoomNr,
                          Personal = s.FirstName + " " + s.LastName
                      }).ToList();

And this is simply solution for your request

var Customerorders = db.Orders.Where(
    item => item.OrderNr == 1005
).Select(
    item => new
    {
        item.CheckInAndOutId,
        item.CheckInDate,
        item.CheckOutDate,
        item.Department.DepartmentName,
        Personal = item.Employee.FirstName + " " + item.Employee.LastName,
        RoomNr = item.RoomId.HasValue ? item.Room.RoomNr : (int?)null
    }
);

Rembecki Wonderful, it's working, Thank you very much my dear! — Anna, Feb 07 '20 at 20:04
Rembecki , Thank you again, working perfectly with "And this is simply solution for your request" code. But I have a question, If I had without nulls, I useally use as you see in my code only JOIN, not LEFT JOIN. What is the benefit of using LEFT JOIN instead JOIN? — Anna, Feb 07 '20 at 20:11
Anna, I'm glad I could help. In simple words you should use `JOIN` in every `1 to many` reference, but `LEFT JOIN` is required when you have `0 or 1 to many` reference. — Łukasz Rembecki, Feb 10 '20 at 06:38

How to Display result Values even null in c# Windows form with LINQ

1 Answers1