0

I'm working on this conference app where I have to return certain values from the different table using join queries using LINQ

  from a in db.ApplySchedule
  join b in db.userdetails on a.UserID equals b.UserId
  join c in db.roomdetails on a.RoomID equals c.RoomId 
  join d in db.VideoFiles on a.RoomID equals d.RoomId where d.IsActive == true || d.IsActive==false 
  join e in db.ImageFiles on a.RoomID equals e.RoomId where e.IsActive == true|| e.IsActive==false
  where EntityFunctions.TruncateTime(a.MDate) == EntityFunctions.TruncateTime(DateTime.Now) && a.RoomID == id
  && a.Status == true
  select new ShedulerViewModel
  {
      Id = a.BookingID, 
      UserId = a.UserID,
      Uname = b.UserName,
      RoomId = a.RoomID,
      Rname = c.RoomName.ToUpper(),
      Organizer = a.SubjectDetail,
      Date = a.MDate,
      FromTime = a.start_date,
      ToTime = a.end_date,
      //Accesseries = a.Accesseries,
      attend = a.Attend,
      VideoID = d.RoomId,
      VideoPath = d.FilePath,
      videoIsActive= d.IsActive,
      ImageId = e.RoomId,
      ImagePath = e.FilePath,
      ImageIsActive = e.IsActive 
   

      
      

  };

Here is the problem, I'm able to get the values if the last two tables db.imagefiles and db.videofiles has matching values but incase it doesn't I'm getting 0 value from all the other tables even though there are rows with matching id, how do I overcome this? I want to pass value even if the video and image tables have no rows.

Ashwin
  • 109
  • 2
  • 9
  • 1
    Don't use `join` but navigation properties. And post details of your class model + mapping code (if present). + the exact EF version . – Gert Arnold Sep 12 '21 at 09:19
  • okay I will post the model asap and I'm new to this company, the guy who previously worked on this application asked me not to edit the tables so I'm stuck. can't I achieve it with joins? – Ashwin Sep 12 '21 at 09:27
  • 1
    Navigation properties don't change the tables, only the class model. https://learn.microsoft.com/en-us/ef/core/modeling/relationships – Gert Arnold Sep 12 '21 at 09:30
  • You need [LEFT JOIN](https://stackoverflow.com/a/3413732/10646316) – Svyatoslav Danyliv Sep 12 '21 at 11:47
  • IMO people should be encouraged to add navigation properties to their models and avoid using the error-prone join statement in LINQ. – Gert Arnold Sep 12 '21 at 14:54
  • `from d in db.VideoFiles.Where(a.RoomID == d.RoomId && (d.IsActive || !d.IsActive)).DefaultIfEmpty()` do the same thing for `db.ImageFiles` – iSR5 Sep 12 '21 at 16:35

0 Answers0