-1

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?

Quick-gun Morgan
  • 338
  • 12
  • 31
  • If you're using an ORM like Entity Framework then the joins will be taken care of for you, assuming the foreign keys are setup correctly. – BurnsBA Aug 06 '18 at 16:15
  • Yes I can handle that part, I am not sure on where to add group by and how to use having count. – Quick-gun Morgan Aug 06 '18 at 16:16
  • 1
    Then you should be able to use `Where` on a `GroupBy` result: https://stackoverflow.com/a/2078745/1462295 and also https://stackoverflow.com/a/7325306/1462295 – BurnsBA Aug 06 '18 at 16:19
  • How do I expand https://stackoverflow.com/questions/7325278/group-by-in-linq/7325306#7325306 to perform joins on multiple tables after select? I do not have foreign keys set up in my DB so I need to explicitly do that in my SQL. – Quick-gun Morgan Aug 06 '18 at 16:26
  • 1
    Possible duplicate of [Group by in LINQ](https://stackoverflow.com/questions/7325278/group-by-in-linq) – Chad Aug 06 '18 at 16:30
  • @Chad in that posts, g.ToList() returns the list of cars but I dont see how that can be applied in my case where I would be needing the access to B.c_id after grouping. – Quick-gun Morgan Aug 06 '18 at 16:33
  • 1
    @Quick-gunMorgan - you should probably start with a query that returns the information you are asking for. – Chad Aug 06 '18 at 16:39

1 Answers1

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; }
    }
jdweng
  • 33,250
  • 2
  • 15
  • 20