Complex LINQ query from SQL

Question

I have following SQL query:

SELECT Count(*) AS CountOfRecs 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 Type] IN ('OD','CL00','PL00')) OR 
      (tblAccount.[Account Type]  LIKE '%Overdra%'))

and I want to translate it to LINQ. I have created following LINQ but It is not returning same count. SQL is returning 2, LINQ is returning 0.

public int OverDraftCount(long AccountOwnerRegistryId = 731752693037116688)
{
    CreditRegistryContext context = new CreditRegistryContext();
    string notAllowedAccountTypes = "CA00, CA01, CA03, CA04, CA02, PA00, PA01, PA02, PA03, PA04";
    var subList = notAllowedAccountTypes.Split(',');
    string AllowedAccountTypes = "OD,CL00,PL00";
    var subList1 = AllowedAccountTypes.Split(',');

    var query = from c in context.AccountOwners
                .Where(p => p.CreditorRegistryId == p.Account.CreditRegistryId
                    && p.AccountNo == p.Account.AccountNo
                    && p.AccountOwnerRegistryId == AccountOwnerRegistryId
                    && !subList.Contains(p.Account.AccountType)
                    && (EntityFunctions.DiffMonths(
                           p.Account.StateChangeDate, DateTime.Now) < 6
                           || p.Account.StateChangeDate == null
                           && (subList1.Contains(p.Account.AccountType)
                           || p.Account.AccountType.Contains("Overdra"))))
                select c;
    return query.Count();
}

Please suggest solution.

Looks like your parentheses grouping is not quite the same. Also, is that supposed to be < 6 or <= 6 for DiffMonths? — mellamokb
– mellamokb, Commented May 14, 2011 at 17:20
@mellamokb its <6 and SQL is correct and problem is with LINQ because I translated this SQL to LINQ — DotnetSparrow
– DotnetSparrow, Commented May 14, 2011 at 17:23

Josh M. · Accepted Answer · 2011-05-14 17:35:56Z

1

Your parentheses were off in the last 4 lines of the Where clause:

var query = from c in context.AccountOwners
                .Where(p => p.CreditorRegistryId == p.Account.CreditRegistryId
                    && p.AccountNo == p.Account.AccountNo
                    && p.AccountOwnerRegistryId == AccountOwnerRegistryId
                    && !subList.Contains(p.Account.AccountType)
                    && (EntityFunctions.DiffMonths(p.Account.StateChangeDate, DateTime.Now) < 6
                       || p.Account.StateChangeDate == null)
                    && (subList1.Contains(p.Account.AccountType)
                       || p.Account.AccountType.Contains("Overdra")))
                select c;

answered May 14, 2011 at 17:35

Josh M.

28.1k27 gold badges135 silver badges223 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Complex LINQ query from SQL

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related