How to filter records using group functionality in BreezeJs

Question

I'm developing a client app that uses breezejs and Entity Framework 6 on the back end. I've got a statement like this:

        var country = 'Mexico';

        var customers = EntityQuery.from('customers')
            .where('country', '==', country) 
            .expand('order')

I want to use There may be hundreds of orders that each customer has made. For the purposes of performance, I only want to retrieve the latest order for each customer. This will be based on the created date for the order. In SQL, I could write something like this:

    SELECT c.customerId, companyName, ContactName, City, Country, max(o.OrderDate) as LatestOrder FROM Customers c
        inner join Orders o on c.CustomerID = o.CustomerID
        group by c.customerId, companyName, ContactName, City,  Country

If this was run against the northwind database, only the most recent order row is returned for each customer.

How can I write a similar query in breeze, so that it runs on the server side and therefore returns less data to the client. I know I could handle this all on the client but writing some javascript in a querysucceeded method that could be run by the client - but that's not the goal here.

thanks

Answer 1

For a case like this, you should create a special endpoint method that will perform your query.
Then you can use an Entity Framework query to do what you want, using the LINQ syntax. Here are two Web API examples:

[HttpGet]
public IQueryable<Object> CustomersLatestOrderEntities()
{
    // IQueryable<Object> containing Customer and Order entity
    var entities = ContextProvider.Context.Customers.Select(c => new { Customer = c, LatestOrder = c.Orders.OrderByDescending(o => o.OrderDate).FirstOrDefault() });
    return entities;
}

[HttpGet]
public IQueryable<Object> CustomersLatestOrderProjections()
{
    // IQueryable<Object> containing Customer and Order entity
    var entities = ContextProvider.Context.Customers.Select(c => new { Customer = c, LatestOrder = c.Orders.OrderByDescending(o => o.OrderDate).FirstOrDefault() });

    // IQueryable<Object> containing just data fields, no entities
    var projections = entities.Select(e => new { e.Customer.CustomerID, e.Customer.ContactName, e.LatestOrder.OrderDate });
    return projections;
}

Note that you have a choice here. You can return actual entities, or you can return just some data fields. Which is right for you depends upon how you are going to use them on the client. If they are just for display in a non-editable list, you can just return the plain data (CustomersLatestOrderProjections above). If they can potentially be edited, then return the object containing the entities (CustomersLatestOrderEntities). Breeze will merge the entities into its cache, even though they are contained inside this anonymous object.

Either way, because it returns IQueryable, you can use the Breeze filtering syntax from the client to further qualify the query.

var projectionQuery = breeze.EntityQuery.from("CustomersLatestOrderProjections")
    .skip(20)
    .take(10);

var entityQuery = breeze.EntityQuery.from("CustomersLatestOrderEntities")
    .where('customer.countryName', 'startsWith', 'C');
    .take(10);