i need to get all rows from table 1 even when there is no info related in second table using Linq C#

Question

I have this code that shows all tickets with its reply info (if any) and it is working fine. The problem is when there is no reply for ticket found in ticket reply, the whole ticket info row from support ticket doesn't appear. So I want to show all rows in (Support_Teckets table) whether or not it has related data in the (Ticket_Reply table)

var query = (from st in Db.Support_Teckets
             join rp in Db.Ticket_Reply.GroupBy(m => m.Support_Tecket_Id)
                                       .Select(m => m.OrderByDescending(x => x.Date).FirstOrDefault()) 
             on st.Support_Tecket_Id equals rp.Support_Tecket_Id 
             into g

             from rp in g.DefaultIfEmpty()
             join tr in Db.trainers on rp.trainer_id equals tr.trainer_id
             join pr in Db.Technical_problem on st.Technical_problem_Id equals pr.Technical_problem_Id

             select new SupportTicketsDetails
             {
                 Support_Tecket_Id = st.Support_Tecket_Id,
                 Created_Date = st.Created_Date,
                 Created_Time = st.Created_Time,
                 Order_by = Db.trainers.Where(b => b.trainer_id == st.trainer_id)
                                       .FirstOrDefault().trainer_name,
                 Technical_problem_name = pr.Technical_problem_name,
                 Created_details = st.Created_details,
                 Location = st.Location,
                 Technician = Db.trainers.Where(b => b.trainer_id == st.Technician_Id)
                                         .FirstOrDefault().trainer_name,
                 Is_Closed = rp.Reply_Text,
                 Closing_Date = st.Closing_Date ,
                 Last_replier = Db.trainers.FirstOrDefault(a => a.trainer_id == rp.trainer_id)
                                           .trainer_name.ToString(),
                 Last_reply_Date = rp.Date,
                 Last_reply_Time = rp.Time,
                 points = st.points
             }).ToList().OrderByDescending(a => a.Created_Date);

        return View(query);

I thank you for your cooperation in advance

You're using `DefaultIfEmpty` but then going on to join on `rp`. What do you think happens when `rp == null`? — Enigmativity, Apr 24 '20 at 10:39
even if you some how get this to bring back what you want the performance is going to be dog slow — Seabizkit, Apr 24 '20 at 11:10
You are looking for a left outer join. Check out this answer https://stackoverflow.com/a/9506274/1401635 — ninja coder, Apr 24 '20 at 11:51

score -1 · Accepted Answer · answered Apr 24 '20 at 10:50

-1

It's really not easy to test, but I suspect you need something like this:

    from st in Db.Support_Teckets
    join pr in Db.Technical_problem on st.Technical_problem_Id equals pr.Technical_problem_Id
    join x in 
            from rp in Db.Ticket_Reply.OrderByDescending(z => z.Date).Take(1)
            join tr in Db.trainers on rp.trainer_id equals tr.trainer_id
            select new { rp, tr }
        on st.Support_Tecket_Id equals x.rp.Support_Tecket_Id into gxs
    from gx in gxs.DefaultIfEmpty()
    select new

answered Apr 24 '20 at 10:50

Enigmativity

113,464
11
89
172

thanks bro this work fine all records in Support_Teckets shown but how get all Last_replier for all rows because i only see Last_replier for first row in Support_Teckets i use this line to get last replier Last_replier = gx.tr.trainer_name, – Ahmed Apr 24 '20 at 11:06
i test code All rows shown in Support_Teckets but why only bring reply info for first row in Ticket Support i appreciate your effort – Ahmed Apr 24 '20 at 11:49
@Ahmed - Because you had `.FirstOrDefault()` in your query. – Enigmativity Apr 25 '20 at 01:14

i need to get all rows from table 1 even when there is no info related in second table using Linq C#

1 Answers1