LINQ - 如果表不包含任何数据，则外部联接查询返回异常

Question

我有一个查询，它与linq中的子查询执行左联接。但是子查询没有任何值。在这种情况下，linq抛出一个异常。这是我的代码

IEnumerable<MemberDetailsViewModel> query = await (from account in _context.Account
join kym in _context.KYMMaster on account.ClientId equals kym.Id
join address in _context.KYMAddress on kym.Id equals address.MasterId
join branch in _context.Branches on kym.BranchId equals branch.Branch_Id
join deposit in (
//this subquery doesnot have any value
from d in _context.Deposit
where d.AccountNo == accountNo
group d by d.AccountNo into grp
select new
{
AccountNo = grp.Key,
Balance = grp.Sum(x => x.Amount)
}
) on account.AccountNo equals deposit.AccountNo into temp
from deposit in temp.DefaultIfEmpty()
where account.AccountNo == accountNo
select new MemberDetailsViewModel
{
ClientId = kym.ClientId,
ClientName = kym.ClientName,
Mobile = kym.MobileNo,
Address = AddressHelper.GetAddress(address),
Branch = branch.Branch_Name,
AvailableBalance = deposit == null ? 0.00M : deposit.Balance
//AvailableBalance = temp == null ? 0.00M : temp.
})
.AsNoTracking()
.ToListAsync();

正如代码中提到的，在我执行左联接的地方，子查询没有任何值。我该如何解决这个问题？？

以下是的实际异常

System.InvalidOperationException：可为null的对象必须有一个值
在lambda_method108(闭包、QueryContext、DbDataReader、，ResultContext，SingleQueryResultCoordinator(Microsoft.EntityFrameworkCore.Query.Internal.SingleQuerying可枚举1.AsyncEnumerator.MoveNextAsync() at Microsoft.EntityFrameworkCore.EntityFrameworkQueryableExtensions.ToListAsync[TSource](IQueryable1source，CancellationToken CancellationToken(位于的Microsoft.EntityFrameworkCore.EntityFrameworkQueryableExtensions.ToListAsync[TSource](IQueryable1 source, CancellationToken cancellationToken) at TechnoSys.Coop.Repository.DepositRepository.GetMemberDetailsByAccountAsync(String accountNo) in D:ProjectInventorynetcoreappRepositoryImplementationTechnoSys.Coop.RepositoryDepositRepository.cs:line 53 at TechnoSys.Coop.Service.DepositService.GetMemberDetailsByAccountAsync(String accountNo) in D:ProjectInventorynetcoreappServiceImplementationTechnoSys.Coop.ServiceDepositService.cs:line 75 at TechnoSys.Coop.Controller.DepositController.GetAccountForDeposit(String accountNo) in D:ProjectInventorynetcoreappControllerTechnoSys.Coop.ControllerDepositController.cs:line 23 at lambda_method101(Closure , Object ) at Microsoft.AspNetCore.Mvc.Infrastructure.ActionMethodExecutor.AwaitableObjectResultExecutor.Execute(IActionResultTypeMapper mapper, ObjectMethodExecutor executor, Object controller, Object[] arguments) at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.<InvokeActionMethodAsync>g__Awaited|12_0(ControllerActionInvoker invoker, ValueTask1 actionResultValueTask(Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.g_Await|10_0(ControllerActionInvokerinvoker，Task lastTask，State next，Scope Scope，Object State，BooleanisCompleted(Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.Rethrow(ActionExecutedContextSealed上下文(Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.Next(State&下一步，Scope&作用域，对象&state，布尔&isCompleted(Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.g_Await|13_0(ControllerActionInvokerinvoker，Task lastTask，State next，Scope Scope，Object State，BooleanisCompleted(Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.g_Await|19_0(资源调用程序invoker，Task lastTask，State next，Scope Scope，Object State，BooleanisCompleted(Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.g_Logged|17_1(资源调用程序invoker(Microsoft.AspNetCore.Routing.EndpointMiddleware.g_AwaitRequestTask|6_0(端点端点，任务请求任务，ILogger记录器(Microsoft.AspNetCore.Authorization.Policy.AAuthorizationMiddlewareResultHandler.HandleAsync(RequestDelegate接下来，HttpContext上下文，AuthorizationPolicy策略，PolicyAuthorizationResult authorizeResult(Microsoft.AspNetCore.Authorization.AuthenticationMiddleware.IInvoke(HttpContext上下文(Microsoft.AspNetCore.Authentication.AuthenticationMiddleware.IInvoke(HttpContext上下文(Microsoft.AspNetCore.Diagnostics.DeveloperExceptionPageMiddleware.IInvoke(HttpContext上下文(

Answer 1

LINQ翻译器的简化查询，它应该可以工作。

var depositQuery = 
from d in _context.Deposit
where d.AccountNo == accountNo
group d by d.AccountNo into grp
select new
{
AccountNo = grp.Key,
Balance = grp.Sum(x => x.Amount)
};
var query = 
from account in _context.Account
join kym in _context.KYMMaster on account.ClientId equals kym.Id
join address in _context.KYMAddress on kym.Id equals address.MasterId
join branch in _context.Branches on kym.BranchId equals branch.Branch_Id
join deposit in depositQuery on account.AccountNo equals deposit.AccountNo into temp
from deposit in temp.DefaultIfEmpty()
where account.AccountNo == accountNo
select new MemberDetailsViewModel
{
ClientId = kym.ClientId,
ClientName = kym.ClientName,
Mobile = kym.MobileNo,
Address = AddressHelper.GetAddress(address),
Branch = branch.Branch_Name,
AvailableBalance = (decimal?) deposit.Balance ?? 0.00M
};
var result = await query
.AsNoTracking()
.ToListAsync();