locked
Help with Linq query RRS feed

  • Question

  • User657329123 posted

    Hello,

    I have two tables  - Files and Claims. I want to select all rows from Files and Claims tables where Claim status is "Pending" and records that are more than 7 days old.  To find more than 7 days old, I use two fields SubmittedOn and UpdatedOn. If UpdatedOn is Null then use SubmittedOn date for comparison. If UpdatedOn is NOT NULL, then use UpdatedOn date for comparison.

    Here is my query but it is not returning anything but I see records in database that satisfy these conditions. What am I doing wrong?

                DateTime DateCompareTo = DateTime.Now.AddDays(-7);
                var ExpiredPending = (from A in db.Files
                                         join B in db.Claims
                                         on A.Claim_id equals B.Id
                                         where B.Status == "Pending" &&
                                         ((B.UpdatedOn == null && B.SubmittedOn < DateCompareTo) ||
                                         (B.UpdatedOn != null && B.UpdatedOn < DateCompareTo))                          
                                         select new
                                         {
                                             A.FileId,
                                             A.Filename,
                                             A.Claim_id
                                         }).ToList();
    

    I noticed that not all claims have files associated with them. So I need to do Full Outer Join. Not sure how to do it.

    Joe

    Monday, March 15, 2021 12:54 PM

All replies

  • User475983607 posted

    The community cannot see your data, table schema, or entity design.  There is not way for us to verify what's wrong.  Although you are returning an anonymous type which probably contributes to the problem.

    IMHO, a much easier approach is always setting the UpdatedOn date rather than messing around with null and populating a strong type rather than an anonymous type.

    Monday, March 15, 2021 1:12 PM
  • User475983607 posted

    I noticed that not all claims have files associated with them. So I need to do Full Outer Join. Not sure how to do it.

    You updated the post... Left outer joins are covered in the standard documentation for the EF version you are using.

    https://docs.microsoft.com/en-us/ef/core/querying/complex-query-operators#left-join

    https://docs.microsoft.com/en-us/dotnet/csharp/linq/perform-left-outer-joins

    Monday, March 15, 2021 1:41 PM
  • User1312693872 posted

    Hi,joegreen2005

    (B.UpdatedOn == null && B.SubmittedOn < DateCompareTo) 
    || (B.UpdatedOn != null && B.UpdatedOn < DateCompareTo)

    Here if the B.UpdatedOn==null is false, then the whole code will also be false, that means only one can be executed,change the code to this can

    solve the problem:

    var ExpiredPending = (from A in db.Files
                                      join B in db.Claims
                                      on A.Claim_id equals B.Id
                                      where (B.Status == "Pending") &&
                                      ((B.SubmittedOn < DateCompareTo)||
                                      (B.UpdatedOn < DateCompareTo))
                                      select new
                                      {
                                          A.FileId,
                                          A.Filename,
                                          A.Claim_id
                                      }).ToList();

    Best Regards,

    Jerry Cai

    Tuesday, March 16, 2021 5:17 AM