wrong entry is shown in the list instead of null records

Question

User456628452 posted

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();

Answer 1

User-719153870 posted

Hi 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

Answer 2

User456628452 posted

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)

Answer 3

User-719153870 posted

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.

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