Filter linq query in entity framework core, many-to-many relationship

asp.net asp.net-core entity-framework entity-framework-core

Question

I'm using the ASP Net Core 2. I have a test model:

public class Player
{
    public int Id { get; set; }
    public string Name { get; set; }
    public string Position { get; set; }
    public int Age { get; set; }

    [IgnoreDataMember]
    public ICollection<PlayerTeam> PlayerTeams { get; set; }
    public Player()
    {
        PlayerTeams = new List<PlayerTeam>();
    }
}
public class PlayerTeam
{
    public int PlayerId { get; set; }
    public Player Player { get; set; }

    public int TeamId { get; set; }
    public Team Team { get; set; }
}
public class Team
{
    public int Id { get; set; }
    public string Name { get; set; } // название команды
  //  [IgnoreDataMember]
    public ICollection<PlayerTeam> PlayerTeams { get; set; }
    public Team()
    {
        PlayerTeams = new List<PlayerTeam>();
    }
}

this is my DBcontext:

public class FootbollContext: DbContext
{
    public DbSet<Player>  Players { get; set; }
    public DbSet<Team> Teams { get; set; }
    public FootbollContext(DbContextOptions<FootbollContext> options)
        : base(options)
    {

    }
    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {           

        modelBuilder.Entity<PlayerTeam>()
            .HasKey(t => new { t.TeamId, t.PlayerId });

    }
}

I have a query in my controller:

FootbollContext db;
var teams = db.Teams.Select(team => new { 
    TeamName = team.Name,
    PlayersOlder20 = team.PlayerTeams.Where(pt => pt.Player.Age > 20).Select(s => s.Player)
});

and it works fine, but I want to use the Include()/ThenInclude() methods for this query, and I want to get the same equal results ie.

var teams = db.Teams.Include(p => p.PlayerTeams).ThenInclude(d => d.Player)

but I don't want to load all data! and I don't know how I can filter results by property "Players age (> 20)" in the relative table (not in the selectable!!) in one SQL Query.

1
2
12/5/2017 2:15:14 PM

Popular Answer

So you want to configure a many-to-many relation between Team and Player: every Team consists of zero or more Players, and every Player is a member of zero or more Teams.

I see some aberrations from the Entity Framework Code-first conventions. This causes the difficulties you encounter

You are right that in a relational database you need an junction table to configure a many-to-many relationship. Although entity framework uses this mechanism, it is better not to define it.

If you follow the default conventions for a many-to-many relation you won't need the junction table in any of your queries.

class Player
{
    public int Id { get; set; }
    public string Name { get; set; }
    ...
    // every Player plays in zero or more teams:
    public virtual ICollection<Team> Teams {get; set;}
}

class Team
{
    public int Id { get; set; }
    public string Name { get; set; } // название команды
    ...
    // every Team has zero or more PLayers:
    public virtual ICollection<Player> Players {get; set;}
}
class FootbollContext: DbContext
{
    public DbSet<Player> Players { get; set; }
    public DbSet<Team> Teams { get; set; }
}

This is all information Entity Framework needs to understand that you want a many-to-many between Players and Teams.

Note that I've left out the junction table for PLayerTeam. You won't need it. Let entity framework decide what table it needs.

Besides I also didn't create the Lists like you do in the constructors. First of all, your collection aren't lists; what would Player[4] mean? Secondly, whenever you query them from the database these Lists are immediately replaced by the objects that your DbSet IQueryable returns.

Now if you want some Teams with some of their Players you'll do the following:

var result = dbContext.Teams
    .Where(team => ...
    .select(team => new
    {   // select only the properties you will be using:
        Name = team.Name,
        ...
        // Select only the Players of this team you want:
        OlderPlayers = team.Players
            .Where(player => player.Age > 20)    
            .Select(player => new
            {   // select only the player properties you plan to use:
                Name = player.Name,
                Position = player.Position,
                ...
             }),
    });

Entity framework knows about the Many-to-many relationship between Team And Players. It will automatically do the correct joins with the created junction table (of which you officially don't know that it exists) and return only the properties you'll need.

Be aware that Linq uses deferred execution. The statement above doesn't hold your data yet. It only knows how to fetch the data as soon as you ask for it. This is usually done using functions like ToList(), FirstOrDefault(), Count(), etc. This means you'll have to make sure that the data is queried before you Dispose your dbcontext, before you leave your using statement.

As a second example: all names of Players older than 20 with the names of the Teams he plays:

var result = dbContext.Players
    .Where(player => player.Age > 20)
    .Select(player => new
    {
        Name = player.Name,
        TeamNames = player.Teams
            // if desired: Where(team => ...)
            .Select(team => team.Name)
            .ToList(),
    });
    .Where(play => player
0
12/5/2017 3:02:12 PM


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow