Linq to Sql 3 joins with group and min()

Question

I'm trying to convert the SQL below to Linq. I haven't figured out the syntax for the GROUP BY, the MIN() or the extra organization join conditions.

SELECT DISTINCT o.OrganizationHierarchyUnitLevelThreeNm, o.OrganizationHierarchyUnitLevelFourNm, a.LabAssetSerialNbr, MIN(a.SystemCreatedOnDtm) MinCreated
FROM vw_DimLabAsset a
INNER JOIN vw_FactWorker w ON a.LabAssetAssignedToWorkerKey = w.WorkerKey
INNER JOIN vw_DimOrganizationHierarchy o ON w.OrganizationHierarchyKey = o.OrganizationHierarchyKey
    AND o.OrganizationHierarchyUnitLevelThreeNm IS NOT NULL
    AND o.OrganizationHierarchyUnitLevelFourNm IS NOT NULL
GROUP BY o.OrganizationHierarchyUnitLevelThreeNm, o.OrganizationHierarchyUnitLevelFourNm, a.LabAssetSerialNbr

This is what I've managed to get so far:

var pphw = from a in Vw_DimLabAsset
       where a.LabAssetHardwareStatus != "Retired" && (a.LabAssetHardwareSubStatus == null || a.LabAssetHardwareSubStatus != "Archive") && types.Contains(a.LabAssetTypeNm) // (a.LabAssetTypeNm == "u_cmdb_ci_prototype_system" || a.LabAssetTypeNm == "u_cmdb_ci_silicon")
       join w in Vw_FactWorker on a.LabAssetAssignedToWorkerKey equals w.WorkerKey
       join o in Vw_DimOrganizationHierarchy on w.OrganizationHierarchyKey equals o.OrganizationHierarchyKey
       select new { o.OrganizationHierarchyUnitLevelThreeNm, o.OrganizationHierarchyUnitLevelFourNm, a.LabAssetSerialNbr };

Perhaps my [SQL to LINQ recipe](https://stackoverflow.com/questions/49245160/sql-to-linq-with-multiple-join-count-and-left-join/49245786#49245786) would be helpful? — NetMage, Apr 19 '18 at 20:44
The text itself didn't quite solve my issue, but the example you gave did, so thanks! Other than getting type safety, is there any advantage to using Linq to SQL vs. just putting in the raw SQL. The raw sql seems to be much more "supportable" long-term as it's easier for most people to understand. — Gargoyle, Apr 19 '18 at 21:41
LINQ to SQL's main benefit is the type safety and prevention of many common SQL injection type security concerns, as well as isolating the application from database type or engine peculiarities. It also helps familiarize programmers with functional programming concepts they can use in-memory as well as with databases. I also think it is easier for C# programmers to understand and more portable than any SQL since it is part of the language. There are also some enhancements in concepts (e.g. group join, let) and merging with local computation that makes some things easier. — NetMage, Apr 19 '18 at 22:59

NetMage · Accepted Answer · 2018-04-19T23:01:29.607

Here is how I would translate the query:

var ans = (from a in vw_DimLabAsset
           join w in vw_FactWorker on a.LabAssetAssignedToWorkerKey equals w.WorkerKey
           join o in vw_DimOrganizationHierarchy on w.OrganizationHierarchyKey equals o.OrganizationHierarchyKey
           where o.OrganizationHierarchyUnitLevelThreeNm != null && o.OrganizationHierarchyUnitLevelFourNm != null
           group new { o, a } by new { o.OrganizationHierarchyUnitLevelThreeNm, o.OrganizationHierarchyUnitLevelFourNm, a.LabAssetSerialNbr } into oag
           select new {
               oag.Key.OrganizationHierarchyUnitLevelThreeNm,
               oag.Key.OrganizationHierarchyUnitLevelFourNm,
               oag.Key.LabAssetSerialNbr,
               MinCreated = oag.Min(oa => oa.a.SystemCreatedOnDtm)
           }).Distinct();

Yup, that's what I ended up with also. Thanks! – Gargoyle Apr 19 '18 at 22:58 — Gargoyle, Apr 19 '18 at 22:58

Linq to Sql 3 joins with group and min()

1 Answers1