Flter expanded table with breeze

Question

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.

score 2 · Answer 1 · answered Jan 04 '15 at 04:10

Jeremy is right. This is a long standing EF pain. It's not made any better by the fact that OData doesn't support a way to filter the expand.

You can sort of do what you want on the server side with a projection. I played with this idea in the DocCode sample.

I added a new CustomersAnd1998Orders method to the NorthwindRepository which is exposed to the client on the NorthwindController

This is a fully queryable endpoint. It returns Customers and just those of their Orders that were ordered in 1998. The oddity is that it returns JSON typed as CustomerDto rather than Customer. You have to adjust for those with your client-side query ... as I'll explain below.

I don't know why I have to do this. If I project into Customer, I get a runtime exception from EF.

Here's the `NorthwindRepositoryCustomersAnd1998Orders:

private class CustomerDto : Customer { } // EF requires a shadow class to make the LINQ query work
public IQueryable<Customer> CustomersAnd1998Orders {
  get
  {
    return ForCurrentUser(Context.Customers)
    .Select(c => new CustomerDto {
      CustomerID = c.CustomerID,
      CompanyName =  c.CompanyName,
      ContactName =  c.ContactName,
      ContactTitle = c.ContactTitle,
      Address = c.Address,
      City = c.City,
      Region = c.Region,
      PostalCode = c.PostalCode,
      Country = c.Country,
      Phone =  c.Phone,
      Fax = c.Fax,
      RowVersion = c.RowVersion,

      Orders = c.Orders
                .Where(o =>  o.OrderDate != null && o.OrderDate.Value.Year == 1998)
                .ToList()
    });
  }
}

A bit tedious but if you need it, you need it.

The filter on 'Orders' selects for orders that were ordered in the year 1998 (this is old Northwind data). The OrderDate is nullable<DateTime> so we have to check for null and then pull the year out of the Value.

Notice that it projects with a .Select clause into CustomerDto, a private, "do-nothing" subclass of Customer, so I'm technically returning an IQueryable<CustomerDto>. But it works.

The NorthwindController.NorthwindRepositoryCustomersAnd1998Orders betrays nothing:

[HttpGet]
public IQueryable<Customer> CustomersAnd1998Orders() {
  return _repository.CustomersAnd1998Orders;
}

Query on the Breeze client as you would the 'Customers' but without specifying the .expand clause; the server will take care of that.

There's one twist. See if you can detect it in this test from DocCode:

var query = EntityQuery.from('CustomersAnd1998Orders')
    .where('CompanyName', 'startsWith', 'C')
    .orderBy("CompanyName")
    .toType('Customer'); // Essential ... unless fix with a JsonResultsAdapter

verifyQuery(newEm, query,
    "Customers from 'CustomersAnd1998Orders' projection",
    showCompanyNamesAndOrderCounts,
    assertCustomersInCache,
    assertOrdersInCache,
    assertAllOrdersIn1998);

Yup. You have to cast the result to Customer because the type on the wire is "CustomerDto"

$type: "DocCode.DataAccess.NorthwindRepository+CustomerDto, DocCode.DataAccess.EF"

But at least you can filter (.where()) and sort (orderBy()) and page (take() and skip()) on the data tier.

Seems to me a small price to pay for this result?

score 1 · Accepted Answer · edited May 23 '17 at 11:48

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.

Thank you for expanded answer and your suggestions. – user3136402 Jan 01 '15 at 17:46 — user3136402, Jan 01 '15 at 17:46

Flter expanded table with breeze

2 Answers2

Linked