I want to use Linq to duplicate this T-SQL query on a sports teams database, to look up the experienced players in handball teams:

Select TE.TeamName, PL.FirstName, PL.LastName 
From T_Team as TE 
Inner Join T_Player As PL 
On PL.Team_ID = TE.Team_ID 
And PL.ExpLevel = 'Experienced' 
Where TE.SportName = 'Handball'

I've tried creating two entities for my two tables:

public class TTeam 
{
    public int TeamId { get; set; }
    public string TeamName { get; set; }
    public string SportName { get; set; }

    public virtual List<TPlayer> TeamPlayers { get; set; }

    // Called in the context OnModelCreating() method
    public static void CreateModel(EntityTypeBuilder<TTeam> p_ebpTable)
    {
        p_etbTable.ToTable("T_TEAM");

        p_etbTable.HasKey(t => new { t.TeamId }).HasName("PK_TEAMID_T_TEAM");

        // Column definitions

        // Foreign Keys
        p_etbTable.HasMany(t => t.TeamPlayers).
                   WithOne(p => p.CurrentTeam).
                   HasPrincipalKey(t => t.TeamId).
                   HasForeignKey(p => p.TeamId);
    }
}

and

public class TPlayer
{
    public int PlayerId { get; set; }
    public int TeamId { get; set; }
    public string FirstName { get; set; }
    public string LastName { get; set; }
    public string ExpLevel { get; set; }

    public virtual TTeam CurrentTeam { get; set; }

    // Called in the context OnModelCreating() method
    public static void CreateModel(EntityTypeBuilder<TPlayer> p_ebpTable)
    {
        p_etbTable.ToTable("T_PLAYER");

        p_etbTable.HasKey(t => new { t.PlayerId }).HasName("PK_PLAYERID_T_PLAYER");

        // Column definitions

        // Foreign Keys
        p_etbTable.HasOne(p => p.CurrentTeam).
                   WithMany(t => t.TeamPlayers).
                   HasForeignKey(p => p.TeamId).
                   HasPrincipalKey(t => t.TeamId);
    }
}

then use them in

using Microsoft.EntityFrameworkCore;

IEnumerable<TTeam> z_enbHandballTeams = z_dbcDbContext.TTeamRepository
    .Where(te => te.SportName == "Handball")
    .Include(te => te.TeamPlayers.Where(pl => pl.ExpLevel == "Experienced"));

but looping through z_enbHandballTeams in a foreach, throws an InvalidOperationException with the message "Lambda expression used inside Include is not valid".

(I guess it goes without saying that ExpLevel is a number and SportName is actually SportId, but I felt it would look easier to read that way.)

What am I doing wrong?

CodePudding user response：

EF Core 3.1.x do not support filtered Include. Workaround is to do that via Select

var z_enbHandballTeams = z_dbcDbContext.TTeamRepository
    .Where(te => te.SportName == "Handball")
    .Select(te => new TTeam
    {
        TeamId = te.TeamId,
        TeamName = te.TeamName,
        SportName = te.SportName,

        TeamPlayers = te.TeamPlayers.Where(pl => pl.ExpLevel == "Experienced")
            .ToList()
    });