Perform a select and expand in the same query with breeze is not supported

Question

I develop an asp.net solution with Durandal/breeze.

Here is my code to get all my shippers:

var query = EntityQuery.from('Shippers')
               .select('id, name, street, city');

return manager.executeQuery(query)
        .then(querySucceeded)
        .fail(queryFailed);

Here is the related model:

public class Shipper
{
    [Key]
    public int Id { get; set; }
    public string Name { get; set; }
    public string Street { get; set; }
    public string Number { get; set; }
    public City City { get; set; }
}

public class City
{
    public int Id { get; set; }        
    public string Name { get; set; }
    public string PostCode { get; set; }
    public Country Country { get; set; }
}

Now I need to also include countries

public class Country
{
    [Key]
    public int Id { get; set; }
    public string Code { get; set; }
    public string Name { get; set; }
}

But with the actual query I don't get countries with it.

I try:

var query = EntityQuery.from('Shippers')
               .select('id, name, street, city')
               .expand('City.Country');

but i get the error:

use of both 'expand' and 'select' in the same query is not currently supported

My question: how to get countries?

---

UPDATE

As Jay suggested, we can do:

var query = EntityQuery.from('Shippers')
       .select('id, name, street, city, city.country')

Now, I got a city_Country object:

I don't understand why we got this city_Country because country data are already available in the city \ country object:

Furthermore it gives me problem because my next statement try to map my dto into my entity and this city_Country object don't exist in my entity and an error occured when mapping.

Below we see my entity object and there are no city_Country object:

Do I have to do something special on my mapping to avoid it?

Below is my function for the mapping operation:

function mapToEntity(entity, dto) {
     // entity is an object with observables
     // dto is from json
     for (var prop in dto) {
          if (dto.hasOwnProperty(prop)) {
             entity[prop](dto[prop]);
          }
     }
     return entity;
}

Calida

Answer 1

I'm not sure it's good practice to use a projection to 'partially' fill an entity, which is what it looks like you are doing. Breeze will automatically map any true 'entities' it finds in your result set. So why not simply use

var query = EntityQuery.from('Shippers')
    .where(...)
    .expand('city.country');

The immediate results will be a collection of shippers, but each shipper will have its city and nested country properties fully resolved as entities as well. They will be available via navigation from the returned shippers but they will also be available within the entityManager cache if you wanted to query them directly.

A second note: Breeze 'expand' semantics have the same restrictions that the Entity Framework does. This means that we cannot expand the properties of a projection.

So you can either skip the projection (as above)

var query = EntityQuery.from('Shippers')
   .where(...)
   .expand('city.country')

and get full "Shipper" entities with the 'city" and the 'country' property on the city both populated. ... Or you can do a projection, in which case you perform the equivalant of the expansion yourself. i.e.

In this case you will

var query = EntityQuery.from('Shippers')
  .select('id, name, street, city, city.country')

in which case each item in your result set will consist of 5 properties. Note that in this case only the 'city' and 'city.country' properties will be added to the entityManager's cache becasue these are the only 'true' entities in the result set. i.e. no shipper's

The idea to be clear on is that the 'results' of a query and the 'side effects' of a query are distinct. The top level results of a query will be exactly the shape you expect. The 'side effects' of the query are the result of any 'expand' you perform. These do not change the shape of the query, they simply change the resolution of any nested 'entity' properties within the results.

Hope this helps.

Ethelinda

Thank you for this explanation. Why I used projection in my querries is because some entities may contain a lot of 'heavy' properties like images, ... so in case of querying for a simple list is it better to get only necessary properties or all the properties of an entity? – Calida

One option is simply to map the "heavy" properties to their own EntityType with a one to one correspondence to the 'parentType' which would exclude them. The other is to actually measure the perf/memory footprint of using the "full" entities and see if they are as expensive as you suspect. I'd start with the simplest architecture and only complicate/optimize it once you prove to yourself that it is non-performant. i.e. don't optimize too early. – Ethelinda

Answer 2

We do allow this:

var query = EntityQuery.from('Shippers')
           .select('id, name, street, city, city.country')

Ethelinda

I updated my question to show the result. Can you take a look? Thanks. – Calida

Doing this to a one-to-many property doesn't work ExceptionMessage: "Unable to locate property 'UserName' on type 'System.Collections.Generic.ICollection`1[Domain.Entities.UserProfile]'.", – Rightist