CODEWITHSUNDEEP
c#.netlinqlambda
Michele Boscagin
Michele Boscagin

Reputation: 97

LINQ Lambda Join with Count

I'd like to get all teams without the current team and get all teams with 4 or more player. I try to write this linq lambda query:

teams = connection.Team 
    .Join(connection.Player,
        t => t.ID,
        p => p.IDTeam,
        (t, p) => new { Team = t, Player = p })
    .Where(tp => tp.Player.IDTeam == tp.Team.ID
        && tp.Team.ID != team.ID
        && tp.Team.IsVisible == true
        && !tp.Team.DeleteDate.HasValue)
    .Select(tp => tp.Team)
    .ToList();

but I cannot count on where condition how many players have the teams. How to do? Which is, for info, the query in SQL? Thanks for help!

EDIT: As Required the classes (generate from DBFirst): No relation between the classes. The match is Team.ID == Player.IDTeam

Upvotes: 2

Views: 112

Answers (4)

Renan Araújo
Renan Araújo

Reputation: 3641

How about this?

var teams = 
           (from t in connection.Team
           join p in connection.Player on p.TeamId equals t.ID into playersInTeam
           select new 
           { 
                Team = t, 
                PlayersCount = playersInTeam.Count(x => x.TeamId == t.Id) 
           })
           .Where(x => x.PlayersCount >= 4)
           .ToList();

Upvotes: 0

Ajay Reddy
Ajay Reddy

Reputation: 113

Try this:

teams = connection.Team 
.Join(connection.Player,
    t => t.ID,
    p => p.IDTeam,
    (t, p) => new { Team = t, Player = p })
.Where(tp => tp.Player.IDTeam == tp.Team.ID
    && tp.Team.ID != team.ID
    && tp.Team.IsVisible == true
    && !tp.Team.DeleteDate.HasValue)
.Select(tp => tp.Team)
.ToList().GroupBy(i=>i.ID).Where(i=>i.Count()>=4);

Upvotes: 0

jdweng
jdweng

Reputation: 34433

Try a GroupBy() :

var teams = (from tp in connection.Team
   join p in connection.Team on tp.Player.IDTeam equals p.Team.ID
   select new { Team = tp, Player = p })
   .Where(tp =>  tp.Team.IsVisible == true && !tp.Team.DeleteDate.HasValue)
   .GroupBy(x => x.Team.ID)
   .Where(x => x.Count >= 4)
   .ToList();

Upvotes: 0

Darjan Bogdan
Darjan Bogdan

Reputation: 3910

Based on classes above, you can extend Team class and add Players navigational property.

When adding navigational property, make sure you have database relationship between Team and Player tables in place. Also, if needed configure your DbContext.

public class Team
{
    //...other properties
    public virtual ICollection<Player> Players { get; set; }
}

When you add navigational property, it will be trivial to achieve your requirement:

connection.Teams
          .Where(t => t.ID != team.ID && t.IsVisible == true && !t.DeleteDate.HasValue && t.Players.Count() >= 4)

Upvotes: 3

Related Questions