locked
LINQ subquery

    Question

  • How do I write this in LINQ?

    SELECT     ClassStudentID, ClassID, StudentID, CompletionDate
    FROM         TrainingClassStudents
    WHERE     (CompletionDate IN
                              (SELECT     MAX(CompletionDate) AS Expr1
                                FROM          TrainingClassStudents AS TrainingClassStudents_1
                                GROUP BY StudentID))

    Thanx.
    Friday, April 10, 2009 5:06 PM

All replies


  • Try this,

    var results = from maxCD in 
                       ( from tc in TrainingClassStudents 
                                group tc.CompletionDate by tc.StudentID into tcs
                                select tcs.Max())
                       from traCS in TrainingClassStudents
                       where traCS.CompletionDate = maxCD
                       select new {
                       traCS.ClassStudentID, traCS.ClassID, traCS.StudentID, traCS.CompletionDate };

    Thanks,
    Vijay Pandurangan
    Wednesday, April 15, 2009 6:15 AM
  • I've tried this but I think I've run into an entity frame work issue:

    qStudent =

    From maxCD In (From tc In TrainingService.TrainingClassStudents Group tc.CompletionDate By tc.Employees.EMP Into tcs = Group Select tcs.Max()) From traCS In TrainingService.TrainingClassStudents Where traCS.CompletionDate = maxCD Select traCS

    Here is the resulting error:
    System.Linq.Expressions.Expression.Lambda(System.Linq.Expressions.Expression, System.Collections.Generic.IEnumerable`1<System.Linq.Expressions.ParameterExpression>)
       at System.Reflection.MethodBase.PerformSecurityCheck(Object obj, RuntimeMethodHandle method, IntPtr parent, UInt32 invocationFlags)
       at System.Reflection.RuntimeMethodInfo.Invoke(Object obj, BindingFlags invokeAttr, Binder binder, Object[] parameters, CultureInfo culture, Boolean skipVisibilityChecks)
       at System.Reflection.RuntimeMethodInfo.Invoke(Object obj, BindingFlags invokeAttr, Binder binder, Object[] parameters, CultureInfo culture)
       at System.Reflection.MethodBase.Invoke(Object obj, Object[] parameters)
       at System.Linq.Expressions.Expression.Lambda(Type delegateType, Expression body, IEnumerable`1 parameters)
       at System.Data.Services.Client.ExpressionNormalizer.TryMatchCoalescePattern(Expression expression, Expression& normalized)
       at System.Data.Services.Client.ExpressionNormalizer.TryMatchCoalescePattern(Expression expression, Expression& normalized)
       at System.Data.Services.Client.ExpressionNormalizer.NormalizePredicateArgument(MethodCallExpression callExpression)
       at System.Data.Services.Client.ExpressionNormalizer.VisitMethodCall(MethodCallExpression m)
       at System.Data.Services.Client.ExpressionVisitor.Visit(Expression exp)
       at System.Data.Services.Client.DataServiceExpressionVisitor.Visit(Expression exp)
       at System.Data.Services.Client.ExpressionVisitor.VisitExpressionList(ReadOnlyCollection`1 original)
       at System.Data.Services.Client.ExpressionVisitor.VisitMethodCall(MethodCallExpression m)
       at System.Data.Services.Client.ExpressionNormalizer.VisitMethodCall(MethodCallExpression m)
       at System.Data.Services.Client.ExpressionVisitor.Visit(Expression exp)
       at System.Data.Services.Client.DataServiceExpressionVisitor.Visit(Expression exp)
       at System.Data.Services.Client.DataServiceQueryProvider.Translate(Expression e)
       at System.Data.Services.Client.DataServiceQuery`1.get_RequestUri()
       at System.Data.Services.Client.DataServiceContext.SaveAsyncResult.GenerateBatchRequest(Boolean replaceOnUpdate)
       at System.Data.Services.Client.DataServiceContext.SaveAsyncResult.BatchBeginRequest(Boolean replaceOnUpdate)
       at System.Data.Services.Client.DataServiceContext.BeginExecuteBatch(AsyncCallback callback, Object state, DataServiceRequest[] queries)
       at TrainingMatrix.Page.StartDataRetrieval()

    Wednesday, April 15, 2009 6:13 PM
  • Hi,

    In VB, the parameterless aggregate operators is not supported over projection.

    Try the following for VB

    From maxCD In (From tc In TrainingService.TrainingClassStudents Group tc.CompletionDate By tc.Employees.EMP Into tcs = Group Select tcs.Max(Function(t) t) From traCS In TrainingService.TrainingClassStudents Where traCS.CompletionDate = maxCD Select traCS


    Thanks,
    Vijay Pandurangan
    Thursday, April 16, 2009 5:46 AM
  •  

    This works on a local in memory query on an observable collection
    Dim
    qStudent = From maxCD In (From tc In localStudents Group tc.CompletionDate By tc.Employees.EMP Into tcs = Group Select tcs.Max(Function(t As DateTime) t)) From traCS In localStudents Where traCS.CompletionDate = maxCD Select traCS

    but using

     

    Dim qStudent = From maxCD In (From tc In TrainingService.TrainingClassStudents.Expand("Employees,TrainingClass/TrainingClassTopics/TrainingTopic") Group tc.CompletionDate By tc.Employees.EMP Into tcs = Group Select tcs.Max(Function(t As DateTime) t)) From traCS In TrainingService.TrainingClassStudents.Expand("Employees,TrainingClass/TrainingClassTopics/TrainingTopic") Where traCS.CompletionDate = maxCD Select traCS

     



    I get the error



    I get the error


    The operands for operator 'Equal' do not match the parameters of method 'op_Equality'.   at System.Linq.Expressions.Expression.GetMethodBasedBinaryOperator(ExpressionType binaryType, Expression left, Expression right, MethodInfo method, Boolean liftToNull)
       at System.Linq.Expressions.Expression.Equal(Expression left, Expression right, Boolean liftToNull, MethodInfo method)
       at System.Linq.Expressions.Expression.MakeBinary(ExpressionType binaryType, Expression left, Expression right, Boolean liftToNull, MethodInfo method, LambdaExpression conversion)
       at System.Linq.Expressions.Expression.MakeBinary(ExpressionType binaryType, Expression left, Expression right, Boolean liftToNull, MethodInfo method)
       at System.Data.Services.Client.ExpressionVisitor.VisitBinary(BinaryExpression b)
       at System.Data.Services.Client.ExpressionNormalizer.VisitBinary(BinaryExpression b)
       at System.Data.Services.Client.ExpressionVisitor.Visit(Expression exp)
       at System.Data.Services.Client.DataServiceExpressionVisitor.Visit(Expression exp)
       at System.Data.Services.Client.ExpressionVisitor.VisitBinary(BinaryExpression b)
       at System.Data.Services.Client.ExpressionNormalizer.VisitBinary(BinaryExpression b)
       at System.Data.Services.Client.ExpressionVisitor.Visit(Expression exp)
       at System.Data.Services.Client.DataServiceExpressionVisitor.Visit(Expression exp)
       at System.Data.Services.Client.ExpressionVisitor.VisitLambda(LambdaExpression lambda)
       at System.Data.Services.Client.ExpressionVisitor.Visit(Expression exp)
       at System.Data.Services.Client.DataServiceExpressionVisitor.Visit(Expression exp)
       at System.Data.Services.Client.ExpressionVisitor.VisitUnary(UnaryExpression u)
       at System.Data.Services.Client.ExpressionNormalizer.VisitUnary(UnaryExpression u)
       at System.Data.Services.Client.ExpressionVisitor.Visit(Expression exp)
       at System.Data.Services.Client.DataServiceExpressionVisitor.Visit(Expression exp)
       at System.Data.Services.Client.ExpressionVisitor.VisitExpressionList(ReadOnlyCollection`1 original)
       at System.Data.Services.Client.ExpressionVisitor.VisitMethodCall(MethodCallExpression m)
       at System.Data.Services.Client.ExpressionNormalizer.VisitMethodCall(MethodCallExpression m)
       at System.Data.Services.Client.ExpressionVisitor.Visit(Expression exp)
       at System.Data.Services.Client.DataServiceExpressionVisitor.Visit(Expression exp)
       at System.Data.Services.Client.ExpressionVisitor.VisitExpressionList(ReadOnlyCollection`1 original)
       at System.Data.Services.Client.ExpressionVisitor.VisitMethodCall(MethodCallExpression m)
       at System.Data.Services.Client.ExpressionNormalizer.VisitMethodCall(MethodCallExpression m)
       at System.Data.Services.Client.ExpressionVisitor.Visit(Expression exp)
       at System.Data.Services.Client.DataServiceExpressionVisitor.Visit(Expression exp)
       at System.Data.Services.Client.DataServiceQueryProvider.Translate(Expression e)
       at System.Data.Services.Client.DataServiceQuery`1.get_RequestUri()
       at System.Data.Services.Client.DataServiceContext.SaveAsyncResult.GenerateBatchRequest(Boolean replaceOnUpdate)
       at System.Data.Services.Client.DataServiceContext.SaveAsyncResult.BatchBeginRequest(Boolean replaceOnUpdate)
       at System.Data.Services.Client.DataServiceContext.BeginExecuteBatch(AsyncCallback callback, Object state, DataServiceRequest[] queries)
       at TrainingMatrix.Page.trvNavigate_SelectedItemChanged(Object sender, RoutedPropertyChangedEventArgs`1 e)

    Thursday, April 16, 2009 3:50 PM
  • Hi,

    Try the same query without mentioning the data type in the lamda funtion (Funtion(t as DateTime) t)) like Funtion(t) t as give below

    Dim qStudent = From maxCD In (From tc In TrainingService.TrainingClassStudents.Expand("Employees,TrainingClass/TrainingClassTopics/TrainingTopic") Group tc.CompletionDate By tc.Employees.EMP Into tcs = Group Select tcs.Max(Function(t) t)) From traCS In TrainingService.TrainingClassStudents.Expand("Employees,TrainingClass/TrainingClassTopics/TrainingTopic") Where traCS.CompletionDate = maxCD Select traCS

    Thanks,
    Vijay Pandurangan
    Friday, April 17, 2009 7:07 AM