Problem in populating list object in a certain way

Question

I have a class like below :

public class UserChangeLogs
    {
        public int Id { get; set; }
        public int UserId { get; set; }
        public int Version { get; set; }
        public string Column { get; set; }
        public string OriginalValue { get; set; }
        public string UpdatedValue { get; set; }
    }

This table has data like below:

Id      UserId      Version     Column      OriginalValue       UpdatedValue
1       100         1.0         FirstName   Jon                 John
2       100         1.0         LastName    Kid                 Kidhar
3       100         1.0         Address     abc                 Pqr
4       100         1.0         Comments                        Updated basic info
5       100         2.0         Phone       1111                222
6       100         2.0         Age         20                  21
7       100         2.0         Comments                        Update phone no and age

Comments for Version 1 User changes:

4       100         1.0         Comments                            Updated basic info

Comments for Version 2 User changes:

7       100         2.0         Comments                            Update phone no and age

Below is my DTO class for the output:

public class UserChangeLogsPOCO
    {
        public int Id { get; set; }
        public int Version { get; set; }
        public string Column { get; set; }
        public string OriginalValue { get; set; }
        public string UpdatedValue { get; set; }
        public string Comments { get; set; }
    }

Now I want to get the list of User change logs for particular user 100 and have Comments only in the first record of each version. Like below :

Output:

 Id         Version     Column      OriginalValue       UpdatedValue   Comments
 5          2.0         Phone       1111                222            Update phone no and age
 6          2.0         Age         20                  21             null
 1          1.0         FirstName   Jon                 John           Updated basic info
 2          1.0         LastName    Kid                 Kidhar         null
 3          1.0         Address     abc                 Pqr            null
 .
 .

I just want Comments to be there in the first record of each new version.

Code:

var query = context.UserChangeLogs
                   .Where(u => u.UserId == 100)
                   .OrderByDescending(p => p.Version); //latest version data first
                    .ToList();
                    
var output = query
         .Select(a => new UserChangeLogsPOCO
        {
            Id = a.Id,
            Version = a.Version,
            Column = a.Column,
            OriginalValue = a.OriginalValue,
            UpdatedValue = a.UpdatedValue,
            Comments = context.UserChangeLogs
                    .Where(u =>u.Column == "Comments" &&
                    a.UserId == u.UserId &&
                    u.Version == a.Version).
                    Select(u => u.UpdatedValue).FirstOrDefault(),
        }).ToList();

But the problem is I a getting "Comments" with each record.

Can someone please help me with this?

user_17213526 · Answer

You could create a dictionary for the version comments and use it to simplify the population of the output's Comments property.

The suggestion below is based on the assumption that for each Version's collection of records associated with the target UserId, there is:

exactly one record for a change in the "Comments" column
at least one record for a change in any other column

I have marked all changes made to the original code with comments.

var query = context.UserChangeLogs
    .Where(u => u.UserId == 100)
    .OrderByDescending(p => p.Version); //latest version data first
    .ToList();

// ADDED
const string CommentsColumn = "Comments";

// ADDED
// Dictionary associating the version comment with the ID of the first record for the version
var commentForRecordWithId = query
    .GroupBy(entry => entry.Version)
    .ToDictionary(
        gr => gr.First(entry => entry.Column != CommentsColumn).Id, 
        gr => gr.First(entry => entry.Column == CommentsColumn).UpdatedValue);

var output = query
    .Where(a => a.Column != CommentsColumn) // ADDED - we don't want the Comments column change records in output
    .Select(a => new UserChangeLogsPOCO
    {
        Id = a.Id,
        Version = a.Version,
        Column = a.Column,
        OriginalValue = a.OriginalValue,
        UpdatedValue = a.UpdatedValue,
        // CHANGED:
        Comments = commentForRecordWithId.ContainsKey(a.Id)
            ? commentForRecordWithId[a.Id]
            : null
    }).ToList();

EDIT: The code above also assumes that the context.UserChangeLogs is in chronological order, i.e. ordered by Id, at the time the query variable is assigned.

Problem in populating list object in a certain way

1 Answers