Mateen -ul-haq Mateen -ul-haq - 2 months ago 12
C# Question

Linq to Entity Groupby and concatinate column

Thats my Entity Model

public class Warning
{
public int ID { get; set; }
public string WarningCId { get; set; }
public int WarningYearCounter { get; set; }
public string NavalDepartment { get; set; }
public string MiscellaneousInfo { get; set; }
public EmergencyType EmergencyType { get; set; }
public WarningType WarningType { get; set; }
public DateTime IssuedDate { get; set; }
public DateTime StartDate { get; set; }
public DateTime? EndDate { get; set; }
public string WarningMessage { get; set; }
public string WarningInfo { get; set; }
public bool Active { get; set; }
public string Status { get; set; }
}


Thats My repository

public class WarningRepository :IWarningRepository
{
private ApplicationDbContext _context { get; set; }

public WarningRepository (ApplicationDbContext context)
{
_context = context;
}


}


I want to
groupby
warnings on
startDate.Year
(which are
active == true
)
and concatenate its Column
WarningYearCounter
(something like
group_concat
in MySQL)
Like this

Year Warning
2014 1,5,6,7
2015 6,8,9,0


Query:

_context.Warnings.Where(w => w.Active == true).GroupBy(w => w.StartDate.Year)

Answer

It sounds like you want to do something like this.

var results = (from w in _context.Warnings
               where w.Active
               group w.WarningYearCounter by w.StartDate.Year into grp
               select grp)
              .AsEnumerable()
              .Select(g => new
              {
                  Year = g.Key,
                  Warning = string.Join(",", g)
              });

The string concatenation is best done outside of the DB, thus the use of AsEnumerable. Also I just like to use query syntax for the part that will be translated into SQL and then switch to method syntax for the part that will be done in memory, but you can translate it all to method or query syntax if you like.

Comments