Converting T-SQL string_agg to LINQ C#

Question

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.

There may be a way to do this but personally, I'd return the features as a list in the object model and deal with making a comma separated list when actually required (which may be a view model issue, rather than a data issue). — Neil
– Neil, Commented Jan 31, 2020 at 15:50
@Neil Would you have a guess at how it would fare performance-wise? This query currently returns ~1000 rows. To trim it down to the actual ~200 systems that I have, I would need to loop over the result set I get from the database as I construct the new model? — Metal Wing
– Metal Wing, Commented Jan 31, 2020 at 16:34

Metal Wing · Accepted Answer · 2020-01-31 19:53:16Z

I think I figured it out! My code is as follows:

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))
        })

This appears to return the same result view as what the T-SQL statement does!

brenwebber · Accepted Answer · 2020-03-23 09:43:12Z

Just note that this does not actually convert to the SQL STRING_AGG syntax. EF pulls the entire list and then aggregates those strings in memory.

This means that if you have loads of other fields in your grouped item, and many child strings that need concatenation, then all the "grouped" fields will be repeat for each string being concatenated. I.e. your SerialNumber, LicenseVersion etc. will be duplicated per LicenseFeature (check the generated sql).

This could mean that a lot of extra "duplicated" data is moving between your database server and your EF client.

If this could be an issue (scenario includes few grouped fields, each with a lot of child strings), consider splitting the query into 2 separate queries and concatenating in memory manually.

In scenarios where there will only be a couple of strings per grouped row, then having only 1 round trip will be faster.

Collectives™ on Stack Overflow

Converting T-SQL string_agg to LINQ C#

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related