Converting T-SQL string_agg to LINQ C#

发布于 2020-04-05 23:34:14

I am trying to convert my T-SQL query to LINQ.

My query:

SELECT l.Id, s.SystemSerial, v.LicenseVersion, l.CreatedDate, STRING_AGG (sf.[Name], ',') as Features
FROM [system] AS s
LEFT OUTER JOIN SoftwareLicense AS l ON l.SystemId = s.id
LEFT OUTER JOIN SoftwareVersion as v ON l.SoftwareVersionId = v.Id
LEFT OUTER JOIN FeatureLicense as fl ON fl.SoftwareLicenseId = l.Id AND fl.IsActive = 1
LEFT OUTER JOIN SoftwareFeature as sf ON sf.Id = fl.SoftwareFeatureId
GROUP BY l.id, s.SystemSerial, v.LicenseVersion, l.CreatedDate

The query above returns the following:

267     DELL-H99DHM2        1.0     2019-05-06T13:19:59.3081543     Advanced,Internal
270     DESKTOP-SLL5NLC     1.0     2019-05-06T19:22:19.5161704     Standard,Video
271     DESKTOP-T67FIK1     1.0     2019-05-06T19:30:50.6251582     Advanced,Internal,Video
272     DESKTOP-T67FIK1     1.1     2019-05-07T11:30:50.2351512     Advanced

My original LINQ query (before I added STRING_AGG) looked like this:

var allSystemsAndLicenses = (from s in _context.Systems
                            join sl in _context.SoftwareLicenses on s.Id equals sl.SystemId into sll
                            from sl2 in sll.DefaultIfEmpty()
                            join sv in _context.SoftwareVersions on sl2.SoftwareVersionId equals sv.Id into svv
                            from sv2 in svv.DefaultIfEmpty()
                            join fl in _context.FeatureLicenses on sl2.Id equals fl.SoftwareLicenseId into fll
                            from fl2 in fll.DefaultIfEmpty().Where(a => a.IsActive)
                            join sf in _context.SoftwareFeatures on fl2.SoftwareFeatureId equals sf.Id into sff
                            from sf2 in sff.DefaultIfEmpty()
                            select new SystemLicenseResult
                            {
                                LicenseId = sl2.Id,
                                SerialNumber = s.SystemSerial,
                                LicenseVersion = sv2.LicenseVersion + " (" + sv2.Software.Name + ")",
                                LicenseExpiryDate = sl2.CreatedDate,
                                CreatedDate = sl2.CreatedDate
                            });

I'm trying to figure out how to represent the STRING_AGG (sf.[Name], ',') as Features as LINQ in my C# code. I have a feeling I need to either use a GroupBy capability of linq or have some sort of select inside a select?

Any help is appreciated.

Questioner

Metal Wing

Viewed

309

Chinese

Original

var allSystemsAndLicenses = (from s in _context.Systems join sl in _context.SoftwareLicenses on s.Id equals sl.SystemId into sll from sl2 in sll.DefaultIfEmpty() join sv in _context.SoftwareVersions on sl2.SoftwareVersionId equals sv.Id into svv from sv2 in svv.DefaultIfEmpty() join fl in _context.FeatureLicenses on sl2.Id equals fl.SoftwareLicenseId into fll from fl2 in fll.DefaultIfEmpty().Where(a => a.IsActive) join sf in _context.SoftwareFeatures on fl2.SoftwareFeatureId equals sf.Id into sff from sf2 in sff.DefaultIfEmpty() select new SystemLicenseResult { LicenseId = sl2.Id, SerialNumber = s.SystemSerial, LicenseVersion = sv2.LicenseVersion + " (" + sv2.Software.Name + ")", LicenseExpiryDate = sl2.CreatedDate, LicenseFeatures = sf2.Name, CreatedDate = sl2.CreatedDate }); // I have some predicates defined that I am not putting here, but they do exist. var filteredResults = allSystemsAndLicenses.Where(predicates); var groupedResult = filteredResults.GroupBy(a => a.LicenseId); var result = groupedResult.ToList() // Because the ToList(), this select projection is not done in the DB .Select(eg => new SystemLicenseResult { LicenseId = eg.Key, SerialNumber = eg.First().SerialNumber, LicenseFeatures = string.Join(",", eg.Select(i => i.LicenseFeatures)) })

Converting T-SQL string_agg to LINQ C#

Related issues