从SQL查询到LINQ，该怎么做? [英] How to do this from SQL Query to LINQ?

问题描述

我有一个SQL查询:

I have an SQL query :

SELECT Sum(ABS([Minimum Installment])) AS SumOfMonthlyPayments FROM tblAccount
        INNER JOIN tblAccountOwner ON tblAccount.[Creditor Registry ID] = tblAccountOwner.
        [Creditor Registry ID] AND tblAccount.[Account No] = tblAccountOwner.[Account No]
        WHERE (tblAccountOwner.[Account Owner Registry ID] = 731752693037116688)
        AND (tblAccount.[Account Type] NOT IN ('CA00', 'CA01', 'CA03', 'CA04', 'CA02', 'PA00', 'PA01', 'PA02', 'PA03', 'PA04'))
        AND (DATEDIFF(mm, tblAccount.[State Change Date], GETDATE()) <=
        6 OR tblAccount.[State Change Date] IS NULL)
        AND (tblAccount.[Account Status ID] <> 999)
        AND ((tblAccount.[Account Type] NOT IN ('CL01','PL01','CL10','
        CL11','PL10','PL11','OD','CL00','PL00','CL03','CL20','CL30','CL31','CL32',
        'CL33','CL34','CL35','CL69','CL90','ML00','PL03','PL20','PL30','PL31','PL33',
        'CA00', 'CA01', 'CA03', 'CA04', 'CA02', 'PA00', 'PA01', 'PA02', 'PA03', 'PA04',
        'PL34','PL35','PL40','PL90'))
        AND NOT CONTAINS(tblAccount.[Account Type], 'Overdra')
        OR NOT CONTAINS(tblAccount.[Account Type], 'Mortgage')
        OR NOT CONTAINS(tblAccount.[Account Type],'Revolv')
        OR NOT CONTAINS(tblAccount.[Account Type],'*Credit*Card*'))

我已将其翻译为LINQ:

I have translated it to LINQ:

var excludeTypes = new[]
               {
                  "CA00", "CA01", "CA03", "CA04", "CA02",
                    "PA00", "PA01", "PA02", "PA03", "PA04"      
               };
            var maxStateChangeMonth = 4;
            var excludeStatusId = 999;
            var includOtherPayments = new[] {
                        "CL01","PL01","CL10",
                        "CL11","PL10","PL11","OD","CL00","PL00","CL03","CL20","CL30",
                        "CL31","CL32,CL33","CL34","CL35","CL69","CL90","ML00","PL03",
                        "PL20","PL30","PL31","PL33,CA00", "CA01", "CA03", "CA04","CA02",
                        "PA00", "PA01", "PA02", "PA03", "PA04,PL34","PL35","PL40","PL90" 
                    };
            var sum = (
               from account in context.Accounts
               from owner in account.AccountOwners
               where owner.AccountOwnerRegistryId == ownerRegistryId
               where !excludeTypes.Contains(account.AccountType)
               where account.StateChangeDate == null
               ||
                   EntityFunctions.DiffMonths(account.StateChangeDate, DateTime.Now)
                       <= maxStateChangeMonth
               where includOtherPayments.Contains(account.AccountType) ||
                       !account.AccountType.Contains("Overdra") || !account.AccountType.Contains("Mortgage")
                       || !account.AccountType.Contains("Revolv") || !account.AccountType.Contains("*Credit*Card*")
               where account.AccountStatusId != excludeStatusId
               select (decimal?)account.MinimumInstallment).ToList()
               .Sum(minimumInstallment => Math.Abs((decimal)(minimumInstallment)));
            return sum;

但是SQL返回0，而LINq返回23456.我知道问题在于LINQ中的括号异常或where语句的顺序.请给我建议解决方案.

but SQL is returning 0 where as LINq is returning 23456. I know the issue is with paranthesis in LINQ or order of where statements. Please suggest me solution.

推荐答案

尝试这种方式

try that way

var excludeTypes = new[]
               {
                  "CA00", "CA01", "CA03", "CA04", "CA02",
                    "PA00", "PA01", "PA02", "PA03", "PA04"      
               };
            var maxStateChangeMonth = 4;
            var excludeStatusId = 999;
            var includOtherPayments = new[] {
                        "CL01","PL01","CL10",
                        "CL11","PL10","PL11","OD","CL00","PL00","CL03","CL20","CL30",
                        "CL31","CL32,CL33","CL34","CL35","CL69","CL90","ML00","PL03",
                        "PL20","PL30","PL31","PL33,CA00", "CA01", "CA03", "CA04","CA02",
                        "PA00", "PA01", "PA02", "PA03", "PA04,PL34","PL35","PL40","PL90" 
                    };
            var sum = (
               from account in context.Accounts
               from owner in account.AccountOwners
               where owner.AccountOwnerRegistryId == ownerRegistryId
               where !excludeTypes.Contains(account.AccountType)
               where account.StateChangeDate == null || EntityFunctions.DiffMonths(account.StateChangeDate, DateTime.Now) <= maxStateChangeMonth
               where !(includOtherPayments.Contains(account.AccountType) ||
                       account.AccountType.Contains("Overdra") || 
                       account.AccountType.Contains("Mortgage") || 
                       account.AccountType.Contains("Revolv") || 
                       account.AccountType.Contains("*Credit*Card*"))
               where account.AccountStatusId != excludeStatusId
               select (decimal?)account.MinimumInstallment).ToList()
               .Sum(minimumInstallment => Math.Abs((decimal)(minimumInstallment)));
            return sum;

这篇关于从SQL查询到LINQ，该怎么做?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！