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

// 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)
        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 =,
        version = a.version,
        column = a.column,
        originalvalue = a.originalvalue,
        updatedvalue = a.updatedvalue,
        // changed:
        comments = commentforrecordwithid.containskey(
            ? commentforrecordwithid[]
            : null

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.

Related Query

More Query from same tag