Long story short, I've been trying to write a LINQ query for the following statement:

DECLARE @id int

SET @id = 3

SELECT slp.LearningProgressID, a.LastUpdatedUTC AS PendingTestDate, a.TimeZoneLookupID 
FROM
     (
     SELECT userid, learningmoduleid, MAX(instancenumber) AS MaxInstance 
     FROM studentlearningprogress
     WHERE learningmoduleid = @id
     GROUP BY userid, learningmoduleid
     ) AS LatestProgress 
 INNER JOIN studentlearningprogress slp 
    ON slp.userid = latestprogress.userid 
    AND slp.learningmoduleid = latestprogress.learningmoduleid 
    AND slp.instancenumber = latestprogress.MaxInstance 
 INNER JOIN studentlearningprogressactivity a
    ON a.studentlearningprogressid = slp.learningprogressid
 WHERE slp.studentlearningprogressstatusid = 4  
 AND a.newstudentlearningprogressstatusid = 4

I've been unsuccessful getting even the subquery of LatestProgress to return anything. I am getting a Security Exception (which is odd). Is this even possible in LINQ or do I have to use a derived table with dynamic relations to get the result set I'm looking for?

Self-Servicing, 2.6, VB.NET

Please post the linq query, the exception text, the stack trace and the LLBLGen runtime library version.

Thanks.

ORMSupportClasses runtime version 2.6.09.0903

The security exception was something to do with the database being remote, I bypassed it and I'm getting the "Grouping on a field in a related entity isn't supported in VB.NET" error.

I was attemping to just get the derived table correct before moving onto the next part:

Public Function TestingOutThisThing(learningModuleId as Long) as IEnumerable(of StudentLearningProgressEntity)
    Dim slp = From s In (New LinqMetaData).StudentLearningProgress _
        Where s.LearningModuleID  = learningModuleId _
        Group s By Key = New With {s.UserID, s.LearningModuleID} Into g = Group _
        Select New With {g, g.Max(Function(i) i.InstanceID)} 
    Return Ctype(slp, ILLBLGenProQuery).Execute(of StudentLearningProgressEntity)
End Function

I don't know if I'll be able to get this correctly if VB.NET group by doesn't work correctly. Maybe the DerivedTable would be better in this case?

Could you perhaps try it in c# to see if that works ?

Also try upgrading the latest version of 2.6 as your is rather old, and there have been a LOT of fixes to the LINQ section over the last year

Matt

Could you please post the exact exception and stacktrace (and please try the latest runtime build first as well).

I ended up just creating a view for now until I can spend more time on this issue