Simple way of converting a flat table from a stored procedure into a hierarchical structure in ASP.NET

Question

I am rather new to programming, < 2 years. I am trying to take a flat table that is currently a stored procedure in MS-SQL and turn it into a complex data structure. What I'm trying to accomplish is returning all the changes for the various release versions of a project.

These are the model classes I currently have:

public class ReleaseNote
    {
        public string ReleaseVersion { get; set; }
        public DateTime ReleaseDate { get; set; }
        public List<ReleaseNoteItems> ReleaseNoteItems { get; set; }
    }

    public class ReleaseNoteItems
    {
        public string ChangeType { get; set; }
        public List<string> Changes { get; set; }
    }

And this is the business logic in the DAL class I have:

public IEnumerable<ReleaseNote> GetAllReleaseNotes()
        {
            string cmdText = ConfigurationManager.AppSettings["ReleaseNotesAll"];
            Func<DataTable, List<ReleaseNote>> transform = releaseNoteTransform;
            return getRecords<ReleaseNote>(cmdText, transform);
        }

        public List<ReleaseNote> releaseNoteTransform(DataTable data)
        {
            //DISTINCT LIST OF ALL VERSIONS (PARENT RECORDS)
            var versions = data.AsEnumerable().Select(row => new ReleaseNote
            {
                ReleaseVersion = row["ReleaseVersion"].ToString(),
                ReleaseDate = DateTime.Parse(row["ReleaseDate"].ToString())
            }).Distinct().ToList();

            //ENUMERATE VERSIONS AND BUILD OUT RELEASENOTEITEMS
            versions.ForEach(version =>
            {
                //GET LIST OF ROWS THAT BELONG TO THIS VERSION NUMBER
                var rows = data.AsEnumerable().Where(row => row["ReleaseVersion"].ToString() == version.ReleaseVersion).ToList();
                //GET DISTINCT LIST OF CHANGE TYPES IN THIS VERSION
                var changeTypes = rows.Select(row => row["ChangeType"].ToString()).Distinct().ToList();

                //INSTANTIATE LIST FOR RELEASENOTE ITEMS
                version.ReleaseNoteItems = new List<ReleaseNoteItems>();

                //ENUMERATE CHANGE TYPES AND CREATE THEM
                changeTypes.ForEach(changeType =>
                {
                    //FILTER FOR CHANGES FOR THIS SPECIFIC CHANGE TYPE AND PROJECT TO LIST<STRING>
                    var changes = rows.Where(row => row["ChangeType"].ToString() == changeType)
                                      .Select(row => row["ReleaseNote"].ToString()).ToList();

                    //CREATE THE ITEM AND POPULATE IT
                    var releaseNoteDetail = new ReleaseNoteItems();
                    releaseNoteDetail.ChangeType = changeType;
                    releaseNoteDetail.Changes = changes;
                    version.ReleaseNoteItems.Add(releaseNoteDetail);
                });
            });

            return versions;
        }

I'm presently using Postman to return a JSON object and the issue I'm presently having is that it is not returning unique objects or release versions, it is still giving me duplicates.

These are some links I've looked at. None I've found provide solutions for the specific implementation I'm using. I've tried different implementations, but it seems they fall outside the framework of what I'm trying to accomplish.

Please let me know if you need more information. I'm trying to follow the question protocol, but I'm sure there is something I've left out.

Thanks in advance!

Nice & universal way to convert List of items to Tree Is there a way to easily convert a flat DataTable to a nested .NET object?Recursive method turning flat structure to recursive

Are there duplicates in the underlying data? E.g can there be two records with the same value for ReleaseVersion but different values for ReleaseDate? — John Wu, Jun 20 '17 at 17:43
Hey John, yes, that is correct. The flat table from the stored procedure has duplicate data. So I'm attempting in the DAL class to turn them into unique JSON objects. I've tried calling the Distinct() method where it seemed necessary but I'm not calling it in the right place apparently. — kbest429, Jun 20 '17 at 17:50

John Wu · Answer 1 · 2017-06-20T21:14:47.883

Sounds like your data has duplicates. A given ReleaseVersion may have more than one record. When you take DISTINCT in your example, you are enforcing uniqueness over {ReleaseVersion, ReleaseDate}, which apparently is not good enough.

If you want to have rows that are unique with respect to ReleaseVersion, you need to figure out how to populate ReleaseDate when there is more than one possible value. I would suggest that it should be populated with the latest release date associated with that version. You can enforce that logic with LINQ GroupBy and Max, like this:

var uniqueRows = dt.AsEnumerable()
                   .GroupBy(row   => row["ReleaseVersion"])
                   .Select (group => new ReleaseNote 
                                     { 
                                         ReleaseVersion = group.Key as string, 
                                         ReleaseDate    = group.Max(row => (DateTime)row["ReleaseDate"]) 
                                     }
                           );

This LINQ will create one row per release version. The release date will be populated with the latest (max) release date, given the release version.

Thanks for the help, John. I was able to implement a GroupBy function and have it return distinct values. In the Select's lambda expression, instead of returning a new ReleaseNote, I returned `group => group.First()).ToList()` which gave me the distinct values. Much appreciated. :) — kbest429, Jun 21 '17 at 15:21

Simple way of converting a flat table from a stored procedure into a hierarchical structure in ASP.NET

1 Answers1