c# - Linq，如何使用子查询？

Question

下面的linq查询不起作用，因为子查询行。

如果注释子查询行并运行，它工作正常。

我真的不知道如何运行该子查询。

有知道的请指教~

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.

谢谢!

注意，

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

可能会返回 null。 (无记录)

[编辑]

我试过了，

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

和

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

但是这两种方式都行不通...

关于错误信息，

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

您可以使用 let 关键字来限定包含子查询结果的变量的范围。但是，这取决于 LINQ 提供程序是否可以理解该命令(我开始怀疑可能是这种情况)。

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
      });