Clash Royale CLAN TAG#URR8PPP

How to use Group by in LINQ subquery with multi table joins?

I am trying to learn LINQ and I went to couple of posts dealing with same issue but cant wrap my head around it.

How do I write below query in LINQ?

SELECT 1
FROM A
WHERE NOT EXISTS (SELECT 1
FROM B
JOIN C ON C.id = B.c_id
JOIN D ON D.id = C.d_id
WHERE A.b_id = B.id
AND D.Active = 1
GROUP BY B.id
HAVING COUNT(D.id) >= 5)


Edit : I looked into Group by in LINQ but I am not sure how I will have access to B.c_id after the group by and select caluses. How do I join to tables C and D?

Where

GroupBy

1 Answer
1

Try following. I used classes to make it easier to understand

class Program

static void Main(string args)

List<A> aS = new List<A>();
List<B> bS = new List<B>();
List<C> cS = new List<C>();
List<D> dS = new List<D>();

var temp = (from b in bS
join c in cS on b.id equals c.id
join d in dS on b.id equals d.id
select new b = b, c = c, d = d )
.GroupBy(x => x.b.id)
.Where(x => x.Any(y => y.d.Active == 1) && (x.Count() >= 5))
.ToList();

var results = aS.Where(x => !temp.Any(y => y.First().b.id == x.id)).ToList();





public class A

public int id get; set;

public class B

public int id get; set;

public class C

public int id get; set;

public class D

public int id get; set;
public int Active get; set;



By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.