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.

Answer 1

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

Answer 2

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()

Answer 3

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

Answer 4

 

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)

Answer 5

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