CODEWITHSUNDEEP
c#linq
DeadlyDan
DeadlyDan

Reputation: 699

Merge Rows in List. if one column has different values

I am using the below logic to merge List rows in a list together if they have all have identical columns apart from one (OtherAuditeesUserName). In which case i join the different values of OtherAuditeesUserName with a comma seperater

The original list looks like this:

TrailRemarkId,PreviousAudit,OtherAuditees,StrategicPriority,Observations,DocumentsReviewed

1,"old audit","jane smith", 1,none, doc.docx

1,"old audit","john collins", 1,none, doc.docx

The end result I am looking for is :

TrailRemarkId,PreviousAudit,OtherAuditees,StrategicPriority,Observations,DocumentsReviewed

1,"old audit","jane smaith, john collins", 1,none, doc.docx

see how OtherAuditees is joined using a comma.

Can someone point out a more efficient way to merge List rows ?

 var trailRemarks = (from a in auditData
                                select new
                                {
                                    a.TrailRemarkId,
                                    a.PreviousAudit,
                                    a.OtherAuditees,
                                    a.StrategicPriority,
                                    a.Observations,
                                    a.DocumentsReviewed,
                                }).Distinct();

 List<TrailRemarkEntity> trlist = new List<TrailRemarkEntity>() ;
            int? trId = 0;
            foreach (var tr in trailRemarks)
            {
                if (trId == 0 || (trId != tr.TrailRemarkId))
                {
                    trlist.Add(
                        new TrailRemarkEntity()
                        {
                            TrailRemarkId = tr.TrailRemarkId ?? 0,
                            PreviousAuditName = tr.PreviousAudit,
                            DocumentsReviewed = tr.DocumentsReviewed,
                            StrategicPriorityName = tr.StrategicPriority,
                            OtherAuditeesUserName = tr.OtherAuditees,
                            Observations = tr.Observations
                        }
                        );
                }
                else
                {
                    var existingTR = trlist.Last();
                    existingTR.OtherAuditeesUserName += ", " + tr.OtherAuditees;
                }

                trId = tr.TrailRemarkId;

            }

Upvotes: 0

Views: 564

Answers (2)

DeadlyDan
DeadlyDan

Reputation: 699

Thanks thisiva, your solution worked but I modified it slightly to remove duplicates as follows:

string.Join(",", groupedData.Select(exp => exp.OtherAuditees).Distinct())

Upvotes: 0

Sivaprasath
Sivaprasath

Reputation: 400

You can use group by and string.join for doing it like below,

List<TrailRemarkEntity> trailRemarks = (from a in auditData
                            group a by new {
                                a.TrailRemarkId,
                                a.TrailRemarkId,
                                a.PreviousAudit,
                                a.StrategicPriority,
                                a.Observations,
                                a.DocumentsReviewed
                            } into groupedData

                            select new TrailRemarkEntity()
                            {
                                TrailRemarkId = groupedData.Key.TrailRemarkId ?? 0,
                                PreviousAuditName = groupedData.Key.PreviousAudit,
                                DocumentsReviewed = groupedData.Key.DocumentsReviewed,
                                StrategicPriorityName = groupedData.Key.StrategicPriority,
                                OtherAuditeesUserName = string.join("," , groupedData.Select(exp=>exp.OtherAuditees)),
                                Observations = groupedData.Key.Observations
                            }).ToList();

Hope it helps.

Upvotes: 1

Related Questions