将 SQL 转换为 LINQ

Question

我有以下 SQL。我需要将其转换为 LINQ。

ALTER VIEW [dbo].[vwRptBorrowerAccount]  
AS  
SELECT dbo.tblAccount.[Creditor Registry ID], dbo.tblAccount.[Account No], dbo.tblAccount.[Date Opened], dbo.tblAccount.[Account Status ID],   
               dbo.tblAccount.[Date First Reported], dbo.tblAccount.[Credit Limit], dbo.tblAccount.Balance, dbo.tblAccount.[Minimum Installment], dbo.tblAccount.[Account Type],   
               dbo.tblAccount.Term, dbo.tblAccount.Purpose, dbo.tblAccount.[Account Owner Notes], dbo.tblAccount.[Creditor Notes], dbo.tblAccount.Collateral,   
               dbo.tblAccount.[Collateral Value], dbo.tblAccount.[Legal Status ID], dbo.tblAccount.[Legal Status Date], dbo.tblAccount.LastUpdatedBy,   
               dbo.tblAccount.LastUpdated, dbo.tblAccount.[Unique ID], dbo.tblAccount.[Account Status Date], dbo.tblAccount.Payment, dbo.tblAccount.[Payment Date],   
               dbo.tblAccount.[Balance Date], dbo.tblAccount.[Term Frequency], dbo.tblAccount.[State Change Date],   
               dbo.fn_GetAccountTypeDescription(dbo.tblAccount.[Account Type]) AS [Account Type Description], dbo.tblBusiness.[Business Name] AS CreditorName,   
               dbo.tblBusiness.Address AS CreditorAddress, dbo.tblBusiness.City AS CreditorCity, dbo.tblBusiness.State AS CreditorState,   
               dbo.tblLegalStatus.[Legal Status Description] AS [Legal Status], dbo.tblAccountStatus.[Account Status Description] AS [Account Status],   
               dbo.tblAccountOwner.[Account Owner Registry ID]  
FROM  dbo.tblAccount INNER JOIN  
               dbo.tblAccountOwner ON dbo.tblAccount.[Creditor Registry ID] = dbo.tblAccountOwner.[Creditor Registry ID] AND   
               dbo.tblAccount.[Account No] = dbo.tblAccountOwner.[Account No] INNER JOIN  
               dbo.tblBusiness ON dbo.tblAccount.[Creditor Registry ID] = dbo.tblBusiness.[Registry ID] INNER JOIN  
               dbo.tblAccountStatus ON dbo.tblAccount.[Account Status ID] = dbo.tblAccountStatus.[Account Status ID] INNER JOIN  
               dbo.tblLegalStatus ON dbo.tblAccount.[Legal Status ID] = dbo.tblLegalStatus.[Legal Status ID]  
WHERE (dbo.tblAccount.[Account Type] NOT IN ('CA00', 'CA01', 'CA03', 'CA04', 'CA02', 'PA00', 'PA01', 'PA02', 'PA03', 'PA04')) 

[已编辑] 功能详细信息是：

CREATE FUNCTION [fn_GetAccountTypeDescription]  
(  
 -- Add the parameters for the function here  
 @accountType varchar(max)  
)  
RETURNS varchar(max)  
with schemabinding  
AS  
BEGIN  
 -- Declare the return variable here  
 DECLARE @Result varchar(max)  

 -- Add the T-SQL statements to compute the return value here  
 IF EXISTS(SELECT Abbreviation FROM dbo.tblAccountType WHERE [Account Type Code] = @accountType)  
 BEGIN  
  SELECT @Result = Abbreviation FROM dbo.tblAccountType WHERE [Account Type Code] = @accountType  
 END  
 ELSE  
 BEGIN  
  SELECT @Result = @accountType  
 END  

 -- Return the result of the function  
 RETURN @Result  

END

您能建议如何将其转换为 LINQ 吗？我不想使用联接。

Answer 1

我认为无论是否使用 LINQ，您都无法在没有连接的情况下执行此操作。

此外，这似乎是徒劳的练习。您期望从您投入的时间中获得什么收益？

此外，您还没有指定您打算使用哪个 LINQ 提供程序，因此您的问题完全无法回答（每个提供程序在语法上都有显着差异）。

Answer 2

好的，请确保将 tblAccountType 添加到模型中，并且它与 tblAccount 关联，然后执行如下操作。

我比你更无法测试这一点，但我建议你有一个具有相同架构的测试数据库，并用一些虚拟数据填充它。

String[] excludedCodes = new String[]
    {
        "CA00",
        "CA01",
        "CA03",
        "CA04",
        "CA02",
        "PA00",
        "PA01",
        "PA02",
        "PA03",
        "PA04"
    };

var data = context.tblAccount.Where(a => !excludedCodes.Contains(a.AccountType))
    .Select(a => new{
            a.Creditor_Registry_ID,
            a.Account_No,
            a.Date_Opened,
            ...
            Account_Type_Description = a.tblAccountType.Where
                (t => t.Account_Type_Code = a.Account_Type).SingleOrDefault() 
                   ??  a.Account_Type),
            Creditor_Name = a.tblBusiness.Business_Name,
            CreditorAddress = a.tblBusiness.Address,
            ...
            Legal_Status = a.tblLegalStatus.Legal_Status_Description,
            ... etc.
        });