asked on

Convert T-SQL to LINQ - Need Help With Syntax

Happy Independent Day Experts!

I need your help with LINQ syntax. Basically, trying to convert the following T-SQL into LINQ:

SELECT DISTINCT 
       e.PersonId, 
       StatusDate = e.StatusDate, 
       NextDate = LEAD(e.StatusDate) OVER( ORDER BY e.PersonId,  e.StatusDate), 
       parstatus = RTRIM(LTRIM(e.STATUS))
FROM dbo.Enrollment e
ORDER BY 1, 2, 3;

Open in new window

What it should do is look at the current record and try to get the 'NextDate' by
looking at the StatusDate of the next record (order by PersonId, and StatusDate).
If there does not exists a next record then the NextDate is NULL.
It should return Distinct records

Here are some examples.

If we have a single enrollment record:

PersonId  StatusDate   Status   
--------  --------    --------  
602       01/01/2012  Active    

Record should return is:

PersonId  StatusDate   Status   NextDate
--------  --------    --------  --------
602       01/01/2012  Active    NULL

If we have these records:

PersonId  StatusDate   Status
--------  --------    --------
1000      01/01/2012  Active
1000      01/01/2012  Active
1000      01/01/2012  Active

Records should return are:

PersonId  StatusDate   Status   NextDate
--------  --------    --------  --------
1000      01/01/2012  Active    01/01/2012
1000      01/01/2012  Active    NULL

If we have these records:

PersonId	StatusDate	Status   
--------    --------    -------  
602911      01/01/2011	Active
602911      01/01/2011	Active
602911      01/01/2011	Active
602911      06/05/2015	Termed
602911      06/05/2015	Termed
602911      06/05/2015	Termed
602911      03/14/2016	Active
602911      03/14/2016	Active
602911      03/14/2016	Active
602911      03/14/2016	Active
602911      03/14/2016	Active
602911      03/14/2016	Active     

It should return these records:

PersonId	StatusDate	Status      NextDate
--------    --------    -------     --------
602911	    01/01/2011	01/01/2011 	Active
602911	    01/01/2011	06/05/2015 	Active
602911	    06/05/2015	06/05/2015 	Termed
602911	    06/05/2015	03/14/2016 	Termed
602911	    03/14/2016	NULL	    Active
602911	    03/14/2016	03/14/2016 	Active

Open in new window

Here's the unit tests, and need your help with the method CompactEnrollments.

    [TestClass]
    public class UnitTest4
    {

        [TestMethod]
        public void Enrollment_SingleEnrollment_OneRecordReturned()
        {
            //Arrange
            List<Enrollment> enrollments = new List<Enrollment>() {
                new Enrollment() {PersonId = 602, StatusDate = new DateTime(2012,01,01), EnrollmentStatus = "Active"}
            };
            //Act
            List<EnrollmentWithNextDate> result = CompactEnrollments(enrollments);
            //Assert
            Assert.IsTrue(result.Count() == 1);
            Assert.IsTrue(result[0].StatusDate == new DateTime(2012, 01, 01) && result[0].NextDate == null);
        }

        [TestMethod]
        public void Enrollment_ThreeSameEnrollments_TwoRecordsReturned()
        {
            //Arrange
            List<Enrollment> enrollments = new List<Enrollment>() { 
                new Enrollment() {PersonId = 100, StatusDate = new DateTime(2002,02,01), EnrollmentStatus = "Termed"},
                new Enrollment() {PersonId = 100, StatusDate = new DateTime(2002,02,01), EnrollmentStatus = "Termed"},
                new Enrollment() {PersonId = 100, StatusDate = new DateTime(2002,02,01), EnrollmentStatus = "Termed"}
            };
            //Act
            List<EnrollmentWithNextDate> result = CompactEnrollments(enrollments);
            //Assert
            Assert.IsTrue(result.Count() == 2);
            Assert.IsTrue(result[0].StatusDate == new DateTime(2002, 02, 01) && result[0].NextDate == new DateTime(2002, 02, 01));
            Assert.IsTrue(result[1].StatusDate == new DateTime(2002, 02, 01) && result[1].NextDate == null);
        }

        [TestMethod]
        public void Enrollment_MultiplyEnrollments_CorrectRecordsReturned()
        {
            //Arrange
            List<Enrollment> enrollments = new List<Enrollment>() {
                new Enrollment() {PersonId = 602911, StatusDate = new DateTime(2011,01,01), EnrollmentStatus = "Active"},
                new Enrollment() {PersonId = 602911, StatusDate = new DateTime(2011,01,01), EnrollmentStatus = "Active"},
                new Enrollment() {PersonId = 602911, StatusDate = new DateTime(2011,01,01), EnrollmentStatus = "Active"},
                new Enrollment() {PersonId = 602911, StatusDate = new DateTime(2015,06,05), EnrollmentStatus = "Term"},
                new Enrollment() {PersonId = 602911, StatusDate = new DateTime(2015,06,05), EnrollmentStatus = "Term"},
                new Enrollment() {PersonId = 602911, StatusDate = new DateTime(2015,06,05), EnrollmentStatus = "Term"},
                new Enrollment() {PersonId = 602911, StatusDate = new DateTime(2016,03,14), EnrollmentStatus = "Active"},
                new Enrollment() {PersonId = 602911, StatusDate = new DateTime(2016,03,14), EnrollmentStatus = "Active"},
                new Enrollment() {PersonId = 602911, StatusDate = new DateTime(2016,03,14), EnrollmentStatus = "Active"},
                new Enrollment() {PersonId = 602911, StatusDate = new DateTime(2016,03,14), EnrollmentStatus = "Active"},
                new Enrollment() {PersonId = 602911, StatusDate = new DateTime(2016,03,14), EnrollmentStatus = "Active"}
            };
            //Act
            List<EnrollmentWithNextDate> result = CompactEnrollments(enrollments);
            //Assert
            Assert.IsTrue(result.Count() == 6);
            Assert.IsTrue(result[0].StatusDate == new DateTime(2011, 01, 01) && result[0].NextDate == new DateTime(2011, 01, 01));
            Assert.IsTrue(result[1].StatusDate == new DateTime(2011, 01, 01) && result[1].NextDate == new DateTime(2015, 06, 05));
            Assert.IsTrue(result[2].StatusDate == new DateTime(2015, 06, 05) && result[2].NextDate == new DateTime(2015, 06, 05));
            Assert.IsTrue(result[3].StatusDate == new DateTime(2015, 06, 05) && result[3].NextDate == new DateTime(2016, 03, 14));
            Assert.IsTrue(result[4].StatusDate == new DateTime(2016, 03, 14) && result[4].NextDate == null);
            Assert.IsTrue(result[5].StatusDate == new DateTime(2016, 03, 14) && result[5].NextDate == new DateTime(2016, 03, 14));
        }

        private List<EnrollmentWithNextDate> CompactEnrollments(List<Enrollment> enrollments)
        {
            List<EnrollmentWithNextDate> results = (from e in enrollments
                           select new EnrollmentWithNextDate()
                           {
                               PersonId = e.PersonId,
                               StatusDate = e.StatusDate,
                               EnrollmentStatus = e.EnrollmentStatus,
                               NextDate = ????
                           }
                           ).Distinct().OrderBy(n => n.PersonId).ThenBy(n => n.StatusDate).ThenBy(n => n.NextDate).ToList();
            return results;
        }
    }

    public class Enrollment
    {
        public int PersonId { get; set; }
        public DateTime StatusDate { get; set; }
        public string EnrollmentStatus { get; set; }
    }

    public class EnrollmentWithNextDate : Enrollment
    {
        public Nullable<System.DateTime> NextDate { get; set; }
    }

Open in new window

TIA!!

ste5an

What have you so far to get this data?

Just for curiosity: Why?

SQL Server is really good in doing this kind of data operations. Cause the last time I have looked into this kind of problem, this kind of operation was better done in an explicit loop to do the forward lookup (LEAD) using your own iterator.

Otherwise you may look into ElementAt(+/-1).

ASKER CERTIFIED SOLUTION

louisfr

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

louisfr

@ste5an

Just for curiosity: Why?

SQL Server is really good in doing this kind of data operations

I agree with you. But I somehow wanted to accept the challenge.

Allan

ASKER

Thank you for accepting the challenge, louisfr. I will try out the solution now.
@ste5an

Just for curiosity: Why?

This is an extract that runs at night, so performance is not an issue. Doing the work in C# we get to improve our skills, and hopefully a more testable and maintainable extract.

ste5an

Already took a look at SSDT or tSQLt? They allow T-SQL testing..

Allan

ASKER

Yes, we looked at SSDT unit testing using checksums. The concern is not sure how much longer MS will support it. For example, MS will drop their UI testing framework after VS 2019.

As for tSQLt, we took a peek at it a few years back. I forgot if it's able to mock up test data?

The biggest problem for us, enterprise shops, is our test data. Our data is huge, so it would take a lot of effort to try and create test data for every scenarios. The best options we could think of is to 'freeze', 'mask', and trim down production data and use it as a source for our test data.

Allan

ASKER

BTW, we're using SSDT to create auto build and deploy for SQL; I hope MS will continue to support this feature.

Allan

ASKER

@louisfr. The solution is beautiful and it works. Created more unit tests and it all passed.
Thank you for accepting this challenge; I have a lot to learn.