CODEWITHSUNDEEP
c#sql-serverlinq
Baganaakh
Baganaakh

Reputation: 67

Nested 3 level LINQ query

Here is my working SQL query I want write LINQ to I have no idea to convert write 3 level nested query

Select *
from demo.dbo.Account
where accType = 3
and LinkAcc IN (
  select accNum
  from demo.dbo.Account
  here inkAcc IN (
    select accNum
    from demo.dbo.Account
    where memberid = 20
    and accType= 0 
  )
  and accType = 2
)

Upvotes: 0

Views: 292

Answers (1)

Kurt Hamilton
Kurt Hamilton

Reputation: 13525

When writing LINQ equivalents to a SQL IN(), you have to think about it in reverse.

Rather than the SQL

where entity-value IN sub-values

the LINQ expression becomes

where sub-values contains entity-value

Because writing this in one monolithic LINQ statement is mind-bending, I have broken each subquery into a separate variable.

using System.Linq;

public IEnumerable<Account> FilterAccounts(IEnumerable<Account> accounts)

  // start with the deepest subquery first
  var memberAccountNums = accounts
    .Where(x => x.MemberId == 20 && x.AccType == 0)
    .Select(x => x.AccNum)
    .ToArray();

  var linkAccountNums = accounts
    .Where(x => x.AccType == 2 && memberAccountNums.Contains(x.AccNum))
    .Select(x => x.AccNum)
    .ToArray();

  var result = accounts
    .Where(x => x.AccType == 3 && linkAccountNums.Contains(x.AccNum))
    .ToArray();

  return result;
}

I have used a method here to demonstrate a compilable version of the code (assuming the class and property names are correct). You would obviously want to parameterise this to meet your needs.

If you want to combine them for some reason (say LINQ-to-SQL), then you could write it as one query, or you could instead use a series of IQueryable variables instead of calling .ToArray().

I have created a working demo here: https://dotnetfiddle.net/pg0WLC

I assume that the logic is you want to return all accounts with AccType 3 where there is also a matching AccNum for AccType 0 and 2? This assumes that the MemberId property will match if the AccNum properties do.

Another way of doing this with LINQ would be to use group by:

int[] types = new int[] { 0, 2, 3 };

return accounts
  .Where(x => x.MemberId == 20 && types.Contains(x.AccType))          
  .GroupBy(x => x.AccNum)
  .Where(x => x.Count() == types.Count())         
  .SelectMany(x => x)
  .Where(x => x.AccType == 3);

Upvotes: 1

Related Questions