Unable to cast object of type 'System.Linq.Expressions.FieldExpression' to type 'System.Linq.Expressions.ParameterExpression

entity-framework entity-framework-core linq

Question

I am using Entity Framework rc1-final in ASP.NET5.

I have the following table.

public class PlayerComment 
{
    [Key]
    public int Id { get; set; }

    public int? PeriodId { get; set; }

    [ForeignKey("PeriodId")]
    public Period Period { get; set; }

    public int? PlayerId { get; set; }
    [ForeignKey("PlayerId")]
    public Player Player { get; set; 
    public DateTime? CommentDate { get; set; }

    public string Comment { get; set; }

}

PlayerComment is linked to Player which is linked to SubGroup which is linked to Group

I have the following LINQ Query

public async Task<IEnumerable<PlayerComment>>  SearchQueryable(int? groupId, int? subGroupId = null, int? playerId = null)
    {

        var table = (from pc in _db.PlayerComments
                     join p in _db.Players on pc.PlayerId equals p.Id
                     join sg in _db.SubGroups on p.SubGroupId equals sg.Id
                     where (sg.GroupId == groupId || groupId == null)
                         &&
                         (p.SubGroupId == subGroupId || subGroupId == null)
                           &&
                         (p.Id == playerId || playerId == null)
                     select pc);
        return table.ToListAsync();
    }

This works correctly.

Every Comment falls in a Period so in my output I need to include the Period so I add the .Include("Period")

so my code looks like this

public async Task<IEnumerable<PlayerComment>>  SearchQueryable(int? groupId, int? subGroupId = null, int? playerId = null)
    {

        var table = (from pc in _db.PlayerComments
                     join p in _db.Players on pc.PlayerId equals p.Id
                     join sg in _db.SubGroups on p.SubGroupId equals sg.Id
                     where (sg.GroupId == groupId || groupId == null)
                         &&
                         (p.SubGroupId == subGroupId || subGroupId == null)
                           &&
                         (p.Id == playerId || playerId == null)
                     select pc).Include(p => p.Period);
        return table.ToListAsync();
    }

However now it throws a runtime exception and gives me:

"Unable to cast object of type 'System.Linq.Expressions.FieldExpression' to type 'System.Linq.Expressions.ParameterExpression'."

I read on github there is an issue with OrderBy giving the error but I'm not even using order by.

Is there any workaround I can use to fix this?

I seem to have narrowed it down thanks to the answer provided by @octavioccl.

Changing my code to this:

        var table = _db.PlayerComments.Include(q => q.Period)
                      .Include(sg => sg.Player.SubGroup);
        IQueryable<PlayerComment> tableFiltered;
        if (playerId != null)
        {
            tableFiltered = table.Where(p => p.Player.Id == playerId)
        }
        else
        {
            if (subGroupId != null)
            {
                tableFiltered = table.Where(p => p.Player.SubGroupId == subGroupId)
            }
            else
            {
                if (groupId != null)
                {
                    tableFiltered = table.Where(p => p.Player.SubGroup.GroupId == groupId)
                }
                else
                {
                    tableFiltered = table
                }
            }

        }
        return tableFiltered;

All combinations work, except when I select GroupId and keep the others null. Since SubGroup works I can only deduce it is an issue when you use an Include and use where clause 3 levels deep.

Popular Answer

You should try calling Include method in the DbSet that you want to load the related entity:

 var table = (from pc in _db.PlayerComments.Include(p => p.Period)
              //...

And I think your query would be simpler if you use navigation properties instead explicit joins:

var table =await _db.PlayerComments.Include(p => p.Period)
                                   .Include(p => p.Player.SubGroup.Group)
                                   .Where(pc=>  ( pc.Player.SubGroup.Group.GroupId == groupId || groupId == null) 
                                             && ( pc.Player.SubGroup.SubGroupId == subGroupId || subGroupId == null)
                                             && ( pc.Player.Id == playerId || playerId == null))
                                   .ToListAsync();

Update

Try moving the conditions where you check if the parameters are null outside of your query.

bool groupIdIsNull=groupId == null;
bool subGroupIdIsNull=subGroupId == null;
bool playerIdIsNull= playerId==null;

var table =await _db.PlayerComments.Include(p => p.Period)
                                   .Include(p => p.Player.SubGroup.Group)
                                   .Where(pc=>  ( groupIdIsNull || pc.Player.SubGroup.Group.GroupId.Value == groupId.Value) 
                                             && ( subGroupIdIsNull || pc.Player.SubGroup.SubGroupId.Value == subGroupId.Value )
                                             && ( playerIdIsNull || pc.Player.Id.Value == playerId.Value))
                                   .ToListAsync();


Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why