我正在尝试将 T-SQL 查询转换为 LINQ。
我的查询:
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
上面的查询返回以下内容:
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
我原来的 LINQ 查询(在我添加之前STRING_AGG
)看起来像这样:
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
});
我正在尝试找出如何表示STRING_AGG (sf.[Name], ',') as Features
就像我的 C# 代码中的 LINQ 一样。我有一种感觉,我需要使用 linq 的 GroupBy 功能,或者在选择中进行某种选择?
任何帮助表示赞赏。