0

I'm trying to perform a left join with LINQ and I'm not finding the correct structure (my original query came from a query built in Access).

I'm trying to think with the method available in LINQ but I'm not getting any useful result. I really appreciate any suggestion related LINQ architecture and how to think complex queries that the ones below.

Original Query in Access:
(reformatted)

SELECT [Check People].ID, [Check People].MMS_USER__C, [Check People].EMPLOYMENT_STATUS__C, [Check People].LASTLOGINDATE, [Check People].ENTERPRISE_ID__C, [Check People].ISACTIVE, [ExtractPrivateReports].NAME, [ExtractPrivateReports].LASTRUNDATE, [ExtractPrivateReports].ID
FROM ExtractPrivateReports
    LEFT JOIN [Check People] ON [ExtractPrivateReports].OWNERID = [Check People].MMS_USER__C
WHERE
(
    (([Check People].ISACTIVE) = "true")
    And
    (([ExtractPrivateReports].LASTRUNDATE) < Date() - 180)
)
Or
(
    (([Check People].ISACTIVE)="true")
    And
    (
        (([ExtractPrivateReports].LASTRUNDATE) Is Null)
        And
        (([ExtractPrivateReports].CREATEDDATE) < Date() - 180)
    )
);

Note: the tables are [Check People] and [ExtractPrivateReports]

Query that I made with LINQ (doesn't retrieve the same number of records that retrieves the query above) 

DateTime daysCalculation = today.AddDays(-166);

var query_3 = from privateReports in privateReportList
              join checkPeople in this.getListaCheckPeople(p, u)
                  on privateReports.OWNERID equals checkPeople.Mms_user_c
                  into jn
              from j in jn.DefaultIfEmpty()
              where ((j.IsActve.Equals("true")) && (privateReports.LASTRUNDATE.CompareTo(daysCalculation) > 0))
                  || ((j.IsActve.Equals("true")) && (privateReports.LASTRUNDATE == null))
                      && (privateReports.CREATEDDATE.CompareTo(daysCalculation) > 0)
                      && (!string.IsNullOrEmpty(privateReports.OWNERID))
              select new ActivePeopleWithPrivateReportsNotRunInMoreThan180Days
              { 
                  EMPLOYMENT_STATUS__C = j.EmploymentStatus == null ? "no value" : j.EmploymentStatus,
                  LASTRUNDATE = privateReports.LASTRUNDATE,
                  CheckPeople_ID = j.PeopleKey_c == null ? "no value" : j.PeopleKey_c,
                  ENTERPRISE_ID__C = j.EnterpriseID_c == null ? "no value" : j.EnterpriseID_c,
                  ISACTIVE = j.IsActve == null ? "no value" : j.IsActve,
                  NAME = privateReports.NAME,
                  LASTLOGINDATE = j.LastLoginDate == null ? DateTime.ParseExact("0001-01-01", "yyyy-MM-dd", System.Globalization.CultureInfo.InvariantCulture) : j.LastLoginDate,
                  ExtractPrivateReports_ID = privateReports.ID,
                  MMS_USER__C = j.Mms_user_c == null ? "no value" : j.Mms_user_c
              };
Pᴇʜ
  • 56,719
  • 10
  • 49
  • 73
Cecilia Quintana
  • 11
  • 1
  • 4
    You should take a few extra minutes to format your code. This isn't very readable... [How do I format my code blocks?](https://meta.stackexchange.com/questions/22186/how-do-i-format-my-code-blocks) – Broots Waymb Sep 24 '19 at 20:30
  • When you remove superfluous parentheses, you'll notice that you have an overall syntax of `A or B and C and D` which, because OR has lower precedence, is evaluated as `A or (B and C and D)`. You have an extra condition in the LINQ (the D condition): `!string.IsNullOrEmpty(privateReports.OWNERID)` . The original `A or B and C`, evaluated as `A or (B and C)`, may have been what you originally wanted, but adding `D` threw things off. I'm guessing you wanted `(A or (B and C)) and D` . – madreflection Sep 24 '19 at 21:18
  • Perhaps you would find my [SQL to LINQ Recipe](https://stackoverflow.com/questions/49245160/sql-to-linq-with-multiple-join-count-and-left-join/49245786#49245786) useful. – NetMage Sep 24 '19 at 22:49

0 Answers0