Linq, How to use subquery?

Question

Below linq query does not work, because the subquery line.

if comment the subquery line and run, it works fine.

I really no idea, how to run that subquery.

anybody knows, please advice me~

var result = (from p in productInQuery
      join o in orderInfoQuery on p.refNo equals o.refNo
      join t in productOutQuery on p.no equals t.productInNo into productIn
      from t in productIn.DefaultIfEmpty()
      orderby o.processDate descending
      select new
      {
          modelNo = x.modelNo,
          qty = p.qty,
          dateIn = o.processDate,
          dateOut = (DateTime?) (from m in orderInfoQuery where m.refNo == t.refNo select m.processDate).FirstOrDefault(), //without this line, it works fine.
      });

return Json(result.ToArray()); // error : Input string was not in a correct format.

Thank you!

Note,

from m in orderInfoQuery where m.refNo == t.refNo select m.processDate)

it might be return null. (no record)

[Edit]

I tried ,

let dateOut = (Nullable<DateTime>)(from m in orderInfoQuery where m.refNo == t.refNo select m.processDate).FirstOrDefault()

and

dateOut = (DateTime?) (from m in orderInfoQuery where m.refNo == t.refNo select m.processDate).FirstOrDefault()

but both way does not works...

The error message on,

Line 206:            return Json(result.ToArray());

.

Stack Trace:

[FormatException: Input string was not in a correct format.]
   System.Number.StringToNumber(String str, NumberStyles options, NumberBuffer& number, NumberFormatInfo info, Boolean parseDecimal) +9594411
   System.Number.ParseInt32(String s, NumberStyles style, NumberFormatInfo info) +119
   System.Int32.Parse(String s) +23
   MySql.Data.Types.MySqlDateTime.ParseMySql(String s) +476
   MySql.Data.Types.MySqlDateTime.Parse(String s) +89
   MySql.Data.MySqlClient.MySqlDataReader.GetDateTime(Int32 i) +186
   MySql.Data.Entity.EFMySqlDataReader.GetDateTime(Int32 ordinal) +53

[TargetInvocationException: Exception has been thrown by the target of an invocation.]
   System.RuntimeMethodHandle._InvokeMethodFast(IRuntimeMethodInfo method, Object target, Object[] arguments, SignatureStruct& sig, MethodAttributes methodAttributes, RuntimeType typeOwner) +0
   System.RuntimeMethodHandle.InvokeMethodFast(IRuntimeMethodInfo method, Object target, Object[] arguments, Signature sig, MethodAttributes methodAttributes, RuntimeType typeOwner) +72
   System.Reflection.RuntimeMethodInfo.Invoke(Object obj, BindingFlags invokeAttr, Binder binder, Object[] parameters, CultureInfo culture, Boolean skipVisibilityChecks) +251
   System.Reflection.RuntimeMethodInfo.Invoke(Object obj, BindingFlags invokeAttr, Binder binder, Object[] parameters, CultureInfo culture) +28
   System.Data.Common.Internal.Materialization.ErrorHandlingValueReader`1.GetValue(DbDataReader reader, Int32 ordinal) +284
   lambda_method(Closure , Shaper ) +652
   System.Data.Common.Internal.Materialization.Coordinator`1.ReadNextElement(Shaper shaper) +170
   System.Data.Common.Internal.Materialization.SimpleEnumerator.MoveNext() +84
   System.Linq.Buffer`1..ctor(IEnumerable`1 source) +217
   System.Linq.Enumerable.ToArray(IEnumerable`1 source) +78
   BseWms.WebUI.Controllers.ReportsController.GetAllTransaction(Int32 selectedCompanyCode, String modelNo, Nullable`1 transactionTo, Nullable`1 transactionFrom) in C:\Users\mark\Documents\Visual Studio 2010\Projects\BseWms\BseWms.WebUI\Controllers\ReportsController.cs:206
   lambda_method(Closure , ControllerBase , Object[] ) +259
   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

You can use the let keyword to scope a variable containing the result of your subquery. However, it depends on the LINQ provider as to whether or not it can understand the command (I'm starting to suspect that may be the case).

var result = (from p in productInQuery
      join o in orderInfoQuery on p.refNo equals o.refNo
      join t in productOutQuery on p.no equals t.productInNo into productIn
      from t in productIn.DefaultIfEmpty()
      let dateOut = (from m in orderInfoQuery where m.refNo == t.refNo select m.processData).FirstOrDefault()
      orderby o.processDate descending
      select new
      {
          modelNo = x.modelNo,
          qty = p.qty,
          dateIn = o.processDate,
          dateOut = dateOut
      });