wrong entry is shown in the list instead of null recordsRSS

• • Reply
  marya

  Member

  9 Points

  11 Posts

  wrong entry is shown in the list instead of null records

  Dec 26, 2019 12:06 PM|marya|LINK

  i  want to show a list of terminated and active employees in a specific range,  but if an employee is reappointed, i don't want to show that employee record in the list

  from the query i have written there shouldn't  be null record since the latest expiry date doesn't fall in those range, but it shows the previous record  instead of null

  EmployeeId	EmpName	Salary	RegisrationId
  1	John Doe	2000	5
  2	John Doe	2000	6

  RegistrationId	RegistrationStatus	RegisteredDate	CancelledDate	ExpiryDate
  5	Terminated	1/1/2019	12/31/2019	12/31/2019
  6	Active	1/1/2020		12/31/2020

  here is what i have done so far

       //The specific date range i want 
   var expiryFromtoDate = DateTime.Today.AddDays(-90);
   var expiryUptoDate = DateTime.Today.AddDays(90
  
  
              model.Employees = (from e in Employees
                                 where
                                 (e.EmploymentTypeID == ModelConfig.EmploymentTypes.CONTRACT) &&
                                 (
                                     (e.Registration.States == ModelConfig.RegistrationStates.ACTIVE && e.Registration.ExpiryDate.HasValue && (e.Registration.ExpiryDate >= DateTime.Today) && e.Registration.ExpiryDate <= expiryUptoDate) ||
                                     (e.Registration.RegistrationStateID == ModelConfig.RegistrationStates.TERMINATED && e.Registration.CancelledDate.HasValue && e.Registration.CancelledDate >= expiryFromtoDate)
                                 )
                                 select e).ToList();
  
  
  

• • Reply
  Yang Shen

  Contributor

  2050 Points

  655 Posts

  Re: wrong entry is shown in the list instead of null records

  Dec 27, 2019 02:28 AM|Yang Shen|LINK

  Hi marya,

  marya

  from the query i have written there shouldn't  be null record since the latest expiry date doesn't fall in those range, but it shows the previous record  instead of null

  This is really confusing, you said 'there shouldn't be null record' but also said 'but it shows previous record instead of null', do you want null or not? And which record do you mean, CancelledDate? ExpiryDate?

  As we can see from your code, you are selecting two kinds of employee:

  1. Is contract and Registration.States is "Active" and has ExpiryDate value and this value is after today and will expire in 90 days from today.
  2. Is contract and Registration.RegistrationStateID(?) is "Terminated" and has CancelledDate value and has been canceld after 90 days before today.

  All emloyee who met any one of above two conditions will be selected. If the second table you provided is the query result then both two records meet the above conditions.

  Would you please provide a clearer description of your problem? That would be much more helpful.

  Best Regard,

  Yang Shen

• • Reply
  marya

  Member

  9 Points

  11 Posts

  Re: wrong entry is shown in the list instead of null records

  Dec 28, 2019 07:42 AM|marya|LINK

  thanks for ur reply, here is what i want to acheive, if the employee is reappointed it update Reappointment  - new employmentId colum is added.

  right now dont know how to write this query   in linq using left outer join for reappointment table and also to use reappointment table in where clause,

  -- Region Parameters
  DECLARE @p0 Int = 2 -- contract
  DECLARE @p1 Int = 1 --active
  DECLARE @p2 DateTime = '2019-12-28 00:00:00.000' -- GetDate()
  DECLARE @p3 DateTime = '2020-03-27 00:00:00.000' -- GetDate + 90 Days
  DECLARE @p4 Int = 9 --terminate
  DECLARE @p5 DateTime = '2019-09-29 00:00:00.000'--GetDate - 90 Days
  -- EndRegion
  SELECT [t0].[EmploymentID], [t0].[RegistrationID], [t1].ExpiryDate, [t1].CancelledDate
  FROM [Employments] AS [t0]
  
  INNER JOIN [Registrations] AS [t1] ON [t1].[RegistrationID] = [t0].[RegistrationID]
  LEFT OUTER JOIN
  Reappointment ON [t0].EmploymentID = Reappointment .EmploymentID
  WHERE ([t0].[EmploymentTypeID] = @p0) AND ((([t1].[RegistrationStateID] = @p1) AND ([t1].[ExpiryDate] IS NOT NULL)
  AND ([t1].[ExpiryDate] >= @p2) 
  AND ([t1].[ExpiryDate] <= @p3)) OR (([t1].[RegistrationStateID] = @p4)
  AND ([t1].[CancelledDate] IS NOT NULL) AND ([t1].[CancelledDate] >= @p5)))
  and (t0.OfficeID = 1511) and (Reappointment .NewEmploymentID = null)

• • Reply
  Yang Shen

  Contributor

  2050 Points

  655 Posts

  Re: wrong entry is shown in the list instead of null records

  Dec 30, 2019 07:24 AM|Yang Shen|LINK

  Hi marya,

  Sorry but the requirement is still confusing.

  Now we know the two tables provided in the first post are not the results but the Employments table and Registrations table in your database, but the Reappointment table is not provided.

  marya

  if the employee is reappointed it update Reappointment  - new employmentId colum is added.

  By this, you mean a new record with employmentId will be inserted into your Reappointment table right? Do you make sure the new employmentId added is as same as the corresponding one in your Employments table?

  Besides, we don't know what's the relationship between the Employees list in your current linq and these three tables in your database, please provide related code as much as possible including the definition of Employees see if we can reproduce the problem and solve it.

  In addition, please check LEFT OUTER JOIN in LINQ for more information.

  Best Regard,

  Yang Shen