For some time, I've been trying to transform this SQL into linq:

```sql
SELECT 
    Name
FROM
    Director d
JOIN 
    Movie m ON d.Id = m.DirectorId
JOIN 
    MovieActor ma ON m.Id = ma.MovieId
WHERE 
    ReleaseDate <= '2005-12-31'
    AND Rating >= 9
GROUP BY 
    Name
HAVING 
    COUNT(DISTINCT ma.ActorId) BETWEEN 3 AND 7 
    AND COUNT(DISTINCT CASE WHEN m.DurationMinutes >= 60 THEN m.DurationMinutes END) >= 2
;

```

Here 100% working part, which quite simple.

```cs
var query4 = (from d in directors
              join m in movies on d.Id equals m.DirectorId
              join ma in movieActors on m.Id equals ma.MovieId
              where m.ReleaseDate <= Convert.ToDateTime("2015-12-31")
              where m.Rating >= 9

              );

```

Mostly I'm struggling with these issues:

1. Referring to `d, m, ma` as single table instead of 3 connected somehow, because when I'm trying `group d by ... into g` I'm losing the other two tables.
2. I can't understand how count one parameter (to do condition check) without losing others.

Also `HAVING` syntax in linq is not obvious.

---

**Edit:**
I'm using LINQ to Objects provider, and here some:
Classes by initilization, which are bassiclly field containers

```cs
public Director(int _Id, string _Name)
public Movie(int _Id, string _Name, int _DurationMinutes, DateTime _ReleaseDate, int _DirectorId, int _Rating)
public Actor(int _Id, string _Name, int _Age)
public MovieActor(int _MovieId, int _ActorId)

```

And data I'm using for testing (gathering lists from these arrays)

```cs
var directorArr = new (int, string)[] { (1, "Bebopvsky"), (2, "Tarrantino"), (3, "CubeRick") };
            var actorArr = new (int, string, int)[] 
            {   (1, "Dat Maan",75), (2, "That Man", 28),
                (3, "Dat Women", 32), (4, "That Women", 22),
                (5, "Already Women", 12) };
            var moviesArr = new (int, string, int, DateTime, int, int)[] 
            {   (1, "Platform for soul", 121, Convert.ToDateTime("2018-12-31"), 2, 9),
                (2, "Full-featured access management", 42, Convert.ToDateTime("2019-01-01"), 3, 7),
                (3, "Robust LDAP server for Java", 13, Convert.ToDateTime("2005-05-25"), 3, 4),
                (4, "Man of Rain", 114, Convert.ToDateTime("2004-07-21"), 1, 10),
                (5, "Man of Arms", 152, Convert.ToDateTime("2003-02-17"), 1, 9),
                (6, "Man of War", 93, Convert.ToDateTime("2017-07-05"), 2, 8),
                (7, "Man of Legs", 33, Convert.ToDateTime("2018-11-11"), 1, 9),
                (8, "Mof", 55, Convert.ToDateTime("2015-11-11"), 2, 8) };
            var movieActorArr = new (int, int)[] 
            {   (1,1), (1,3), (1,4), (1,5),
                (2,1), (2,5),
                (3,4),
                (4,1), (4,2), (4,3), (4,4),
                (5,1), (5,2), (5,3), (5,4), (5,5),
                (6,1), (6,2), (6,3),
                (7,2), (7,4), (7,5),
                (8,1), (8,4) };

```