There is a table in which there can be cells in the row empty. They also need to withdraw. LINQ skips these lines and does not display them.

IEnumerable<AllOrderView> select = (from order in db.RetailOrders join status in db.RetailStatus on order.StatusId equals status.Id join customer in db.RetailCustomers on order.RetailCustomerId equals customer.Id orderby order.Id descending select new AllOrderView { Id = order.Id, FirstName = customer.FirstName, LastName = customer.LastName, Phone = customer.Phone, StatusName = status.StatusName, Sum = (int)order.Sum, DateTimes = order.Data, Comment = order.Comment }).ToList();
  • 6
    so you have to join and you need left join - Bald
  • @Bald, thank you. Helped - WebMorda
  • 2
    @Bald: Well, make a reply? - VladD
  • @VladD tried to post a response, criticism is welcome - Bald
  • @Bald: I can not say anything against it. </ criticism> - VladD

1 answer 1

You have an internal connection, while you need a left connection.

correct the linq query as follows:

 join status in db.RetailStatus on order.StatusId equals status.Id into rs from status in rs.DefaultIfEmpty() join customer in db.RetailCustomers on order.RetailCustomerId equals customer.Id into rc from customer in rc.DefaultIfEmpty()

In this case, the Select will be the same, except for the need to handle the null situation in the left table, I would do it like this:

  Status = rs?.Status ?? string.Empty

In this example, I used the innovations of version 6 of the language ?. - the so-called elvis operator

Types of connections in SQL

You can also use the navigation properties to get related entities, then the query will look like this:

 from o in Orders select new { Status = o.Status?.Name ?? string.Empty }

The way to create a navigation property depends on the linq provider used. For example, when using the EF code first approach, the declaration of the navigation properties looks like this:

 public class Order { public int Id {get;set;} public int OrderStatusId {get;set;} //Навигационное свойство public virtual OrderStatus Status {get;set;} } public class OrderStatus { public int Id {get;set;} public string Name {get;set;} }

more on getting related entities using EF

  • It would be worth mentioning the navigation properties, which also solve this problem. - Pavel Mayorov
  • @PavelMayorov thanks for the hint that I didn’t even think about it. I add an answer a bit later - Bald
  • @PavelMayorov supplemented the method of obtaining with the help of navigation properties - Bald
  • one
    Now there is another problem - you implicitly assume the use of EF Code First. And the same linq2sql can be used. - Pavel Mayorov
  • @PavelMayorov I understand correctly that when approaching linq2sql navigation property is populated manually in the linq query? - Bald