c# - Nested filter on Data Transfer Object using OData Wep Api

Question

I have a wep api project consumes data using odata but I'm having some problems with odata wep api.

when I execute that query

/api/values?$top=50&$filter=Comments/Fortuneteller/FullName eq 'some string'

it gives me following error

"Message": "The query specified in the URI is not valid.", "ExceptionMessage": "The parent value for a property access of a property 'Fortuneteller' is not a single value. Property access can only be applied to a single value."

I don't want to return entity object from controller. Is there any way to filter the entity via DTO?

I'm using Repository + Service layer pattern in my project and structure of my project is like that

api controller <-> service <-> repository <-> EF

api controller

    [Queryable]
    public IQueryable<FortuneDTO> Get()
    {
        return service.FiterBy((_ => true));
    }

service

    public IQueryable<FortuneDTO> FiterBy(Expression<Func<tblFortune, bool>> filter)
    {
        return repository.List().Where(filter).Select(_ => new FortuneDTO
        {
            CreatedByFullName = _.aspnet_Users.FullName,
            Id = _.FortuneId,
            Comments = _.tblComment.Select(c => new CommentDTO
            {
                Id=c.CommentId,
                Comment = c.Comment,
                Fortuneteller = new FortunetellerDTO { 
                    FullName=c.aspnet_Users.FullName,
                    Id=c.aspnet_Users.UserId
                }
            }).AsQueryable()
        });
    }

repository

    public virtual IQueryable<TEntity> List()
    {
        return context.CreateObjectSet<TEntity>();
    }

DTO's

public class FortuneDTO
{
    public int Id { get; set; }
    public string CreatedByFullName { get; set; }
    public IQueryable<CommentDTO> Comments { get; set; }
}
public class CommentDTO
{
    public int Id { get; set; }
    public string Comment { get; set; }
    public FortunetellerDTO Fortuneteller { get; set; }
}
public class FortunetellerDTO
{
    public Guid Id { get; set; }
    public string FullName { get; set; }
}

Answer 1

As the exception message tells you, the query that you have is invalid.

/api/values?$top=50&$filter=Comments/Fortuneteller/FullName eq 'some string'

is equivalent to the linq expression

fortuneDTOs.Where(f => f.Comments.Fortuneteller.FullName == "some string").Top(50)

As you can see fortuneDTOs.Comments.Fortuneteller is incorrect as Comments is a collection and it doesn't have a property named 'FullName'.

You should use Any/All to filter on collections. For example, if you are trying to find all the fortunes where one of the commentators is 'some string', you can do

/api/values?$top=50&$filter=Comments/any(c: c/Fortuneteller/FullName eq 'some string')

If instead you want to find out all the fortunes where all the comments are made by only one commentator 'some string', you can do

/api/values?$top=50&$filter=Comments/all(c: c/Fortuneteller/FullName eq 'some string')