Flter expanded table with breeze

I am using in my project angular, breeze, WebApi, EF, SqlServer2008. I have table Articles and table ArticleComments, So one article can have many articleComments records.

public class Article{

    public int ArticleId { get; set; }
    public string ArticleName { get; set; }
    public string Description { get; set; }
    public ICollection<ArticleComment> Comments { get; set; }
    public ICollection<ArticleImage> ImagesList { get; set; }
    ...
}

public class ArticleComment
{
    public int ArticleCommentId { get; set; }
    public string CommentText { get; set; }
    public int UserId { get; set; }
    public int Rate { get; set; }
    public DateTime CommentDate { get; set; }
    public int ArticleId { get; set; }
    public Article Article { get; set; }
    public int Status { get; set; }
}

in client I need to get full Article entity with comments, images and others linked entities, But comments need to be only for selected article record and where field "status" == 1.

I try to use such query

          var pred = breeze.Predicate.create('articleId', 'eq', id)
                  .and('comments', 'any', 'status', '==', 1);
          return EntityQuery.from("Articles")
            .where(pred)
            .expand('comments, imagesList...')
            .toType(entityNames.article)
            .using(manager).execute()
            .to$q(querySucceded, self._queryFailed);

this returns all comments for article , but does not filter expanded table ArticleComments by status field.

Solution

Entity Framework does not support filtering when using ".Include(...)" which is what breeze's ".expand(...)" translates to on the server. Similar questions posted here and here.

Your current query is saying:

Give me the article with articleId==id as long as it has at least one comment with status==1. Include all of it's comments and images.

I believe what you want to express is:

Give me the article with articleId==id. Include comments whose status==1 and all of it's images.

I don't know of a way to express this in one query unless you create a dedicated controller action similar to what Jay describes here.

Alternatively you could do something like this:

var pred = breeze.Predicate.create('articleId', 'eq', id);
return EntityQuery.from("Articles")
    .where(pred)
    .expand('comments, imagesList')
    .toType(entityNames.article)
    .using(manager)
    .execute()
    .to$q(
        function(result) {
            // detach all comments whose status!=1
            var commentType = manager.metadataStore.getEntityType(entityNames.articleComment),
                comments = manager.getEntities(commentType)
                     .filter(function(comment) { return comment.status !== 1; });
            comments.forEach(manager.detachEntity);
            querySucceded(result);
        },
        self._queryFailed);

Upside of this approach is it doesn't require server-side modifications. Downside is it loads more comments than you need and forces you to detach them on the client afterwards.

A third approach would be to issue two queries: one to load the article and it's imageList and a second query to load the comments whose articleId==id and status==1. To do this you would need to have an action in your controller that returns an IQueryable<ArticleComment>. Downside of this approach is you'd need to issue two http requests. You could do these in parallel and with the advent of spdy this may not be such a big deal in the long run.