Linq, filterling result

Question

var result = (from p in productInQuery
  join o in orderInfoQuery on p.refNo equals o.refNo
  join x in productQuery on p.productNo equals x.no
  join t in productOutQuery on p.no equals t.productInNo into productIn
  from t in productIn.DefaultIfEmpty()
  orderby o.processDate descending
  select new
  {
      qty = p.qty,
      dateIn = o.processDate,
      dateOut = (DateTime?)(from m in orderInfoQuery where m.refNo == t.refNo select m.processDate).FirstOrDefault(),
      etaDate = (DateTime?)(from w in orderInfoQuery where w.refNo == t.refNo select w.eta).FirstOrDefault(),  
  })

   if (etaDate != DateTime.MinValue)
   {
      result = result.Where(x => x.etaDate == etaDate);  // Filter result
   }

   return result.ToArray();

I want to filterling linq result like above, but it doesn't work.

Anybody know, how to make it work ?

[Edit]

An error message occur,

Server Error in '/' Application.
Specified method is not supported.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.NotSupportedException: Specified method is not supported.

Source Error:


Line 147:            result = result.Where(s => s.etaDate == null);
Line 148:
Line 149:            return result.ToArray();
Line 150:        }
Line 151:    }


Source File: WebUI\Models\Reports.cs    Line: 149

Stack Trace:


[NotSupportedException: Specified method is not supported.]
   MySql.Data.Entity.SqlGenerator.Visit(DbApplyExpression expression) +54
   System.Data.Common.CommandTrees.DbApplyExpression.Accept(DbExpressionVisitor`1 visitor) +23
   MySql.Data.Entity.SqlGenerator.VisitInputExpression(DbExpression e, String name, TypeUsage type) +65
   MySql.Data.Entity.SelectGenerator.VisitInputExpressionEnsureSelect(DbExpression e, String name, TypeUsage type) +53
   MySql.Data.Entity.SelectGenerator.Visit(DbProjectExpression expression) +132
   System.Data.Common.CommandTrees.DbProjectExpression.Accept(DbExpressionVisitor`1 visitor) +23
   MySql.Data.Entity.SqlGenerator.VisitInputExpression(DbExpression e, String name, TypeUsage type) +65
   MySql.Data.Entity.SelectGenerator.VisitInputExpressionEnsureSelect(DbExpression e, String name, TypeUsage type) +53
   MySql.Data.Entity.SelectGenerator.Visit(DbFilterExpression expression) +131
   System.Data.Common.CommandTrees.DbFilterExpression.Accept(DbExpressionVisitor`1 visitor) +23
   MySql.Data.Entity.SqlGenerator.VisitInputExpression(DbExpression e, String name, TypeUsage type) +65
   MySql.Data.Entity.SelectGenerator.VisitInputExpressionEnsureSelect(DbExpression e, String name, TypeUsage type) +53
   MySql.Data.Entity.SelectGenerator.Visit(DbProjectExpression expression) +132
   System.Data.Common.CommandTrees.DbProjectExpression.Accept(DbExpressionVisitor`1 visitor) +23
   MySql.Data.Entity.SqlGenerator.VisitInputExpression(DbExpression e, String name, TypeUsage type) +65
   MySql.Data.Entity.SelectGenerator.VisitInputExpressionEnsureSelect(DbExpression e, String name, TypeUsage type) +53
   MySql.Data.Entity.SelectGenerator.Visit(DbProjectExpression expression) +132
   System.Data.Common.CommandTrees.DbProjectExpression.Accept(DbExpressionVisitor`1 visitor) +23
   MySql.Data.Entity.SqlGenerator.VisitInputExpression(DbExpression e, String name, TypeUsage type) +65
   MySql.Data.Entity.SelectGenerator.VisitInputExpressionEnsureSelect(DbExpression e, String name, TypeUsage type) +53
   MySql.Data.Entity.SelectGenerator.Visit(DbProjectExpression expression) +132
   System.Data.Common.CommandTrees.DbProjectExpression.Accept(DbExpressionVisitor`1 visitor) +23
   MySql.Data.Entity.SqlGenerator.VisitInputExpression(DbExpression e, String name, TypeUsage type) +65
   MySql.Data.Entity.SelectGenerator.VisitInputExpressionEnsureSelect(DbExpression e, String name, TypeUsage type) +53
   MySql.Data.Entity.SelectGenerator.Visit(DbSortExpression expression) +168
   System.Data.Common.CommandTrees.DbSortExpression.Accept(DbExpressionVisitor`1 visitor) +23
   MySql.Data.Entity.SqlGenerator.VisitInputExpression(DbExpression e, String name, TypeUsage type) +65
   MySql.Data.Entity.SelectGenerator.VisitInputExpressionEnsureSelect(DbExpression e, String name, TypeUsage type) +53
   MySql.Data.Entity.SelectGenerator.Visit(DbProjectExpression expression) +132
   System.Data.Common.CommandTrees.DbProjectExpression.Accept(DbExpressionVisitor`1 visitor) +23
   MySql.Data.Entity.SelectGenerator.GenerateSQL(DbCommandTree tree) +169
   MySql.Data.MySqlClient.MySqlProviderServices.CreateDbCommandDefinition(DbProviderManifest providerManifest, DbCommandTree commandTree) +559
   System.Data.Common.DbProviderServices.CreateCommandDefinition(DbCommandTree commandTree) +147
   System.Data.EntityClient.EntityCommandDefinition..ctor(DbProviderFactory storeProviderFactory, DbCommandTree commandTree) +521

[EntityCommandCompilationException: An error occurred while preparing the command definition. See the inner exception for details.]
   System.Data.EntityClient.EntityCommandDefinition..ctor(DbProviderFactory storeProviderFactory, DbCommandTree commandTree) +1267
   System.Data.EntityClient.EntityProviderServices.CreateCommandDefinition(DbProviderFactory storeProviderFactory, DbCommandTree commandTree) +97
   System.Data.EntityClient.EntityProviderServices.CreateDbCommandDefinition(DbProviderManifest providerManifest, DbCommandTree commandTree) +198
   System.Data.Common.DbProviderServices.CreateCommandDefinition(DbCommandTree commandTree) +147
   System.Data.Objects.Internal.ObjectQueryExecutionPlan.Prepare(ObjectContext context, DbQueryCommandTree tree, Type elementType, MergeOption mergeOption, Span span, ReadOnlyCollection`1 compiledQueryParameters) +371
   System.Data.Objects.ELinq.ELinqQueryState.GetExecutionPlan(Nullable`1 forMergeOption) +642
   System.Data.Objects.ObjectQuery`1.GetResults(Nullable`1 forMergeOption) +149
   System.Data.Objects.ObjectQuery`1.System.Collections.Generic.IEnumerable<T>.GetEnumerator() +44
   System.Data.Entity.Internal.Linq.InternalQuery`1.GetEnumerator() +40
   System.Data.Entity.Infrastructure.DbQuery`1.System.Collections.Generic.IEnumerable<TResult>.GetEnumerator() +40
   System.Linq.Buffer`1..ctor(IEnumerable`1 source) +205
   System.Linq.Enumerable.ToArray(IEnumerable`1 source) +78
   BseWms.WebUI.Models.Reports.GetAllTransaction(TransactionSearchModel searchModel) in C:\Users\mark\Documents\Visual Studio 2010\Projects\BseWms\BseWms.WebUI\Models\Reports.cs:149
   BseWms.WebUI.Controllers.ReportsController.GetAllTransaction(TransactionSearchModel searchModel) in C:\Users\mark\Documents\Visual Studio 2010\Projects\BseWms\BseWms.WebUI\Controllers\ReportsController.cs:187
   lambda_method(Closure , ControllerBase , Object[] ) +108
   System.Web.Mvc.ActionMethodDispatcher.Execute(ControllerBase controller, Object[] parameters) +17
   System.Web.Mvc.ReflectedActionDescriptor.Execute(ControllerContext controllerContext, IDictionary`2 parameters) +208
   System.Web.Mvc.ControllerActionInvoker.InvokeActionMethod(ControllerContext controllerContext, ActionDescriptor actionDescriptor, IDictionary`2 parameters) +27
   System.Web.Mvc.<>c__DisplayClass15.<InvokeActionMethodWithFilters>b__12() +55
   System.Web.Mvc.ControllerActionInvoker.InvokeActionMethodFilter(IActionFilter filter, ActionExecutingContext preContext, Func`1 continuation) +263
   System.Web.Mvc.<>c__DisplayClass17.<InvokeActionMethodWithFilters>b__14() +19
   System.Web.Mvc.ControllerActionInvoker.InvokeActionMethodWithFilters(ControllerContext controllerContext, IList`1 filters, ActionDescriptor actionDescriptor, IDictionary`2 parameters) +191
   System.Web.Mvc.ControllerActionInvoker.InvokeAction(ControllerContext controllerContext, String actionName) +343
   System.Web.Mvc.Controller.ExecuteCore() +116
   System.Web.Mvc.ControllerBase.Execute(RequestContext requestContext) +97
   System.Web.Mvc.ControllerBase.System.Web.Mvc.IController.Execute(RequestContext requestContext) +10
   System.Web.Mvc.<>c__DisplayClassb.<BeginProcessRequest>b__5() +37
   System.Web.Mvc.Async.<>c__DisplayClass1.<MakeVoidDelegate>b__0() +21
   System.Web.Mvc.Async.<>c__DisplayClass8`1.<BeginSynchronous>b__7(IAsyncResult _) +12
   System.Web.Mvc.Async.WrappedAsyncResult`1.End() +62
   System.Web.Mvc.<>c__DisplayClasse.<EndProcessRequest>b__d() +50
   System.Web.Mvc.SecurityUtil.<GetCallInAppTrustThunk>b__0(Action f) +7
   System.Web.Mvc.SecurityUtil.ProcessInApplicationTrust(Action action) +22
   System.Web.Mvc.MvcHandler.EndProcessRequest(IAsyncResult asyncResult) +60
   System.Web.Mvc.MvcHandler.System.Web.IHttpAsyncHandler.EndProcessRequest(IAsyncResult result) +9
   System.Web.CallHandlerExecutionStep.System.Web.HttpApplication.IExecutionStep.Execute() +8836977
   System.Web.HttpApplication.ExecuteStep(IExecutionStep step, Boolean& completedSynchronously) +184

Answer 1

So you do realize that in the code you're showing us etaDate is not shown being created or assigned right? You mentioned it in the comment like it was there (unless I'm just missing it), and I think that is adding to the confusion. Personally I find resuing a variable name for different purposes rather confusing. In your anonymous type you create a field called etaDate and you compare that to what I assume is a variable of the method called etaDate. Maybe renaming the filter one to etaFilterDate, would help. If nothing else it would certainly help for the sake of this question.

Try using the debugger and stopping on your Where() clause and examine the contents of result and etaDate. I would suspect you'll find there is no x.etaDate that matches etaDate EXACTLY. Keep in mind that both the Date AND TIME will have to match. According to your comment your etaDate will have a time of Midnight I believe.

Answer 2

I assume you're getting zero results in line 17? That comparison looks correct to me to select out all of your results with an etaDate that matches etaDate. So I'm guessing your problem is that the previous query isn't turning up any records that match etaDate--can you double-check that this is the case?