Linq query returns 0 count when join with related tables-CodePudding

I need to query a table and join related tables. A single query without joining another table returns the expected result. but once I join another table I get zero result.

The below query returns some results

  var response = from o in context.Orders.Where(p => p.Start_Effective >= startDate && p.Start_Effective < endDate);

But once I join another table

var response = from o in context.Orders.Where(p => p.Start_Effective >= startDate && p.Start_Effective < endDate);
join v in context.Venue on o.Id equals v.Id
                        select new
                           {
                               Id = o.Id,
                               PointId = o.FromPointId,
                               VenueName = v.Name
                            };

I also try the below query and I still get zero result

var response = from o in context.Orders.Where(p => p.Start_Effective >= startDate && p.Start_Effective < endDate) 
                           from v in context.Venue
                           where v.OrderId == o.Id
                           select new
                           {
                               Id = o.Id,
                               PointId = o.FromPointId,
                               VenueName = v.Name
                            };

I cant figure out why this is returning 0 result once I join table

CodePudding user response：

try to use a left join, and add to list

var response = (from o in contextOrders
  join v in context.Venue on o.Id equals v.Id into vj
   from v in vj.DefaultIfEmpty()
 where ( o.Start_Effective >= startDate && o.Start_Effective < endDate) 
  select new
 {
    Id = o.Id,
    PointId = o.FromPointId,
   VenueName = v.Name
 }).ToList();

CodePudding user response：

If there is no record in Venue Table with OrderId, when inner join is used, no data returned and you should insert a record that has OrderId that exist in Order Table.

Another option is using left join. By left join, if there is no OrderId in Venue Table, result was returned