LINQ to Entities LEFT JOIN 单个聚合结果

Question

低优先级问题，因为我已经有了解决方案，但我认为还有更好的方法。我试图返回一个结果，就像这个 SQL 查询一样：

SELECT COUNT(*) IsAuthenticated, COUNT(UTP.HashedPassword) IsTempPassword
FROM [EnterpriseDB].[dbo].[UniversalCurrentPassword] AS UCP
LEFT JOIN [EnterpriseDB].[dbo].[UniversalTempPassword] AS UTP
ON UCP.UniversalId = UTP.UniversalId
WHERE UCP.UniversalId = 00000
      AND (UCP.HashedPassword = 'some_password'
           OR UTP.HashedPassword = 'some_password')

这是我已经得到的：

//returns null if mismatch 
//returns true if temp password
//returns false if current password
bool? query = 
    (from Password in context.UniversalCurrentPasswords
     join TempPassword in context.UniversalTempPasswords
     on Password.UniversalId equals TempPassword.UniversalId
     where Password.UniversalId == longId
     && (Password.HashedPassword == pwd
         || Password.UniversalMaster.UniversalTempPassword.HashedPassword == pwd)
     select (TempPassword.HashedPassword == null ? true : false)).FirstOrDefault();

你认为怎么样 - 有更好的方法吗？

Answer 1

试试这个：

bool? query =   
     (from Password in context.UniversalCurrentPasswords 
      join TempPassword in context.UniversalTempPasswords 
      on Password.UniversalId equals TempPassword.UniversalId into tp
      from TempPassword in tp.DefaultIfEmpty()
      where Password.UniversalId == longId 
          && (Password.HashedPassword == pwd 
          || Password.UniversalMaster.UniversalTempPassword.HashedPassword == pwd) 
      select (TempPassword == null ? null as bool? : 
              TempPassword.HashedPassword == null ? (bool?)true : (bool?)false)
     ).FirstOrDefault();