Local queries, projected properties and sort order failure.

[david-grogan]

I have a pageable query which projects some properties from a navigation property entity, and then applies an 'orderby' using one of these properties. e.g:

    var query = breeze.EntityQuery.from(resource)
            .select("Id, CustomerId, ProductId, Product.Code, Product.Details.PartNum, CostPrice, SellingPrice")
            .orderBy("Product.Details.PartNum")
            .skip((page - 1) * pageSize)
            .take(pageSize)
            .inlineCount();

Note that Product.Details represents an EF Complex Type, and not a navigation property.

This query works correctly (and the results are turned into properly attached breeze entities, using the Partial Entity pattern), and after this I can then page forward and backward, where I then use the following to check if the items have already been loaded:

manager.executeQueryLocally(query)

...on a similarly constructed query to the initial remote query (minus the 'select' because the entities in the cache already have the projected properties created), only re-issuing the remote query if it doesn't find anything.

The sort order is not preserved correctly by the local query (the first item from the 2nd page appears as the first item on the 1st page, and as you page forwards and backwards the sort order goes out by one more item each time). If I only use remote queries, the sort order remains intact.

I tried to add a 'select' to the local query, but this didn't work at all. It creates the first projected navigation property (Product.Code) but not the 2nd, instead it creates 'Product_Details.PartNum' instead of 'Product_Details_PartNum', and both properties are null anyway.

One way of getting around this is to use .expand("Product") on the original query, but I don't want to incur the additional overhead.

Digging into the source I believe I've found the problem, not familiar with the rest of the codebase or maybe I'm doing something else wrong here, so not sure if my solution is the correct way to resolve it, but if you look in the SelectClause 'class' (line #12447), in the ctor function you are using string.replace to split nested properties, but this only changes the first occurrence, so I changed it the following:

  var ctor = function (propertyPaths) {
    this.propertyPaths = propertyPaths;
    this._pathNames = propertyPaths.map(function (pp) {
        //return pp.replace(".", "_");
        return pp.split(".").join("_");
    });
  };

...this fixes the problem of creating the 2nd nested projection property (Product.Details.PartNum).

Then around line # 12505, I changed the getPropertyPathValue function to the following:

    var properties = Array.isArray(propertyPath) ? propertyPath : propertyPath.split(".");
    if (properties.length === 1) {
        return obj.getProperty(propertyPath);
    } else {
        var nextValue = obj;
        // hack use of some to perform mapFirst operation.
        properties.some(function (prop) {
            nextValue = nextValue.getProperty(prop);
            return nextValue == null;
        });
        if (nextValue === null) {
            var projectedPath = properties.join("_");
            if (obj.hasOwnProperty(projectedPath)) {
                return obj.getProperty(projectedPath);
            }
        }   
        return nextValue;
    }

...this fixes the null values resulting from both the local query select and orderBy (the sort order being corrupted was caused by comparing null with null for every entity when sorting).

[Maja-Inetec]

Hello.

I believe we are experiencing a similar (or the same) issue. We are using version 1.4.16.

We have a select query with inlineCount, where, orderBy, skip and take clauses. If we orderBy a column we know has different data for each row, everything works fine. However, when we orderBy a column that has the same value (or null) for most rows, or, more precisely, for the first N rows, where N is larger than our page size, the following happens:

• the first item on the first page is missing (compared to the analogous query executed on DB)
• the last item on the first page is the same as the first item on the second page
• that first item cannot be found on any page
• in the end, count is correct (one item is missing, one is duplicated)

I hope this helps.

[steveschmitt]

I will see it this is still an issue with breeze-client 2.x