LINQ to SQL get grouped MIN with JOIN

Question

I'm having trouble with a LINQ to SQL query getting the min value using Visual Basic. Here's the SQL:

SELECT RC.AssetID, MIN(RC.RecCode) AS RecCode, JA.EngineerNote from JobAssetRecCode RC
JOIN JobAssets JA ON JA.AssetID = RC.AssetID AND JA.JobID = RC.JobID
WHERE RC.InspState = 2 AND RC.RecCode > 0
AND RC.JobID = @JobID
GROUP BY RC.AssetID, JA.EngineerNote;

I seem to be going around in circles here with grouping etc not managing to get it working. Any help would be greatly appreciated.

EDIT: Steven, thanks for the help. Converted to VB:

Dim jobResult = From asset In m_dc.JobAssets _
    From recCode In asset.JobAssetRecCodes _
    Where recCode.InspState = 2 And recCode.RecCode > 0 _
    And recCode.JobID = JobID _
    Group recCode By recCode.AssetID Into g = Group _
    Select New With {g.First().JobAssets.AssetID, _
    g.First().JobAssets.EngineerNote, _
    g.Select(Function(rec) rec.RecCode).Min()}

Answer 1

I had to reverse engineer your data model from the query, so this is a bit a guess, but I think this query might do the trick. Note that it is C#, but converting it to VB should be pretty easy:

int jobId = [some value];

var minRecCodes =
    from asset in db.JobAssets
    from recCode in asset.JobAssetRecCodes
    where recCode.InspState == 2 && recCode.RecCode > 0 &&
        recCode.JobID == jobId
    group recCode by recCode.AssetID into g
    select new
    {
        AssetID = g.Key, 
        EngineerNote = g.First().JobAsset.EngineerNote,
        g.Select(rec => rec.RecCode).Min()
    };