locked
Linq query not bringing latest record on two table RRS feed

  • Question

  • User-1590246810 posted

    Dear All,

       The following query has not to return the latest records based on the record updated time. it brings the records wrongly. 

    List<int> resp = new List<int>() { 2, 3};
    var list = from a in _context.Vip_Event_Resp
    join c in _context.PA_Event_Resp on a.Vip_Id equals c.Vip_Id into qry
    let r = qry.Where(x => x.Vip_Id == vipId && x.Event_Id == model.Events.Event_Id && resp.Contains(x.Response)).OrderByDescending(x => x.Updated_On).DefaultIfEmpty().
    FirstOrDefault() select new { r.Vip_Id, r.PA_Id, r.Response, r.Updated_On };

    TABLE: Vip_Event_Resp

    Id Event_Id Vip_Id Response Response_Text Created_On Updated_On

    7 F43FCC27-2726-4775-939C-23E3B322BB63 03C72650-D05F-4E7B-B491-4A0DF05B689A 2 NULL 2020-01-07 14:29:21.093 2020-01-07 14:29:21.093

    TABLE: PA_Event_Resp 

    Id Event_Id Vip_Id PA_Id Response Response_Text Created_On Updated_On
    4 F43FCC27-2726-4775-939C-23E3B322BB63 03C72650-D05F-4E7B-B491-4A0DF05B689A F1FA81C7-E1A9-464E-B3B9-D67ED97BB833 3 Dont know 2020-01-07 13:14:11.857 2020-01-07 13:14:11.857
    Please advise me on this issue.
    Awaiting your reply at earliest.

     

    Tuesday, January 7, 2020 12:22 PM

Answers

  • User665608656 posted

    Hi al_muba,

    According to the two table structures you provided, I found that their column names are exactly the same, except that the PA_Event_Resp table has a new column PA_Id.

    In your linq statement, when you join two tables into qry ,then filtered and sorted, but it does not indicate which column of the table to operate on, you need to tell us which table you need to filter and sort the fields?

    Best Regards,

    YongQing.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, January 8, 2020 7:45 AM

All replies

  • User1120430333 posted

    You can always use Linqpad or other such tool and verify that the Linq you are trying to create is correct when querying the database..

    https://www.linqpad.net/

    Tuesday, January 7, 2020 1:02 PM
  • User665608656 posted

    Hi al_muba,

    According to the two table structures you provided, I found that their column names are exactly the same, except that the PA_Event_Resp table has a new column PA_Id.

    In your linq statement, when you join two tables into qry ,then filtered and sorted, but it does not indicate which column of the table to operate on, you need to tell us which table you need to filter and sort the fields?

    Best Regards,

    YongQing.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, January 8, 2020 7:45 AM
  • User-1590246810 posted

    Hi, Thanks for your reply. and I already fixed that issue.

    Sorry for the delay

    Wednesday, January 29, 2020 12:08 PM