Ordering on Value Objects

 Hi,

 Given an entity with an attached value object, such as Address, it would be great to be able to order by the entity results by fields in the value object. I realise this is possible with Linq to Objects, but as queries get more complicated doing everything in memory can create a high burden on the server.

Expected syntax:

                 return Properties.OrderBy(p => p.Address.StreetName)
                                         .ToList();

Required Syntax:

                return Properties.ToList()
                                        .OrderBy(p => p.Address.StreetName)
                                        .ToList();

 Cheers,

Donovan.

Hi Donovan,

Thanks for the suggestion.  I've implemented this and it will be included in nightly builds dated 9 Apr 2009 and above, available from about 1430 GMT.  Please let us know if you run into any problems.

 Works great! Thanks very much for the quick implementation. Noticed that the ordering is done in memory, rather than in SQL. Out of interest, what was the rationale behind that?

 Cheers,

Donovan.

That's odd.  In our test suites the generated SQL for an order by on a value object member is:

SELECT
  Contributions.Id,
  Contributions.CoordinatesGeoX,
  Contributions.CoordinatesGeoY,
  Contributions.Title,
  Contributions.Duration
FROM
  Contributions
ORDER BY
  Contributions.CoordinatesGeoX DESC;

i.e. the ordering is being done in SQL where it ought to be.

This may possibly be database dependent -- I seem to recall that some of the in-memory databases don't support paging, so if your query involves paging as well as sorting and you are on, say VistaDB or SQL Server Compact, then that might force us to fall back to in-memory sorting and paging -- but I would have thought even then we would be getting the database to do the sorting and just handling the paging in memory.  (I'm not too familiar with that bit of LightSpeed so I'm not 100% sure about this!)

If you can let us know what database you're using, and provide us with your entity and value object declarations and SQL CREATE TABLE scripts and an example of the kind of query you're doing and the resulting SQL you're seeing, we can look into this for you.

Hi Ivan,

thanks for the reply! I'm using Sql 2005 SP3 and this is the query :

                     Find<Property>(new Query
                    {
                        ViewName = "PropertiesWithCurrentStatus",
                        QueryExpression = (Entity.Attribute("CurrentStatusId") == stateId.Value),
                        AggregateName = "WithCurrentStatus"
                    })
                   .OrderBy(p => p.Address.StreetName)
                   .ToList()

where Property has a value object called Address. The query generates this SQL output:

exec sp_executesql N'SELECT
  [PropertiesWithCurrentStatus].[Id],
  [PropertiesWithCurrentStatus].[AddressArea],
  [PropertiesWithCurrentStatus].[AddressCountry],
  [PropertiesWithCurrentStatus].[AddressCounty],
  [PropertiesWithCurrentStatus].[AddressFlatNumber],
  [PropertiesWithCurrentStatus].[AddressHouseNumber],
  [PropertiesWithCurrentStatus].[AddressPostCode],
  [PropertiesWithCurrentStatus].[AddressStreetName],
  [PropertiesWithCurrentStatus].[Brand],
  [PropertiesWithCurrentStatus].[BusinessId],
  [PropertiesWithCurrentStatus].[CurrentStatusId],
  [PropertiesWithCurrentStatus].[Description],
  [PropertiesWithCurrentStatus].[HomeFinderId],
  [PropertiesWithCurrentStatus].[LockVersion],
  [PropertiesWithCurrentStatus].[NumberOfBedrooms]
FROM
  [dbo].[PropertiesWithCurrentStatus]
WHERE
  [PropertiesWithCurrentStatus].[CurrentStatusId] = @p0;

SELECT
  [Event].[Id],
  [Event].[Comment],
  [Event].[CreatedById],
  [Event].[CreatedOn],
  [Event].[DeletedById],
  [Event].[DeletedOn],
  [Event].[DeletionReason],
  [Event].[EventTypeId],
  [Event].[LockVersion],
  [Event].[PropertyId],
  [Event].[Amount],
  [Event].[HappenedOn],
  [Event].[Purchaser],
  [Event].[CompletedOn],
  [Event].[EstateAgentId],
  [Event].[ContractorId],
  [Event].[SolicitorId],
  [Event].[MadeBy],
  [Event].[Accepted],
  [Event].[SdnNumber],
  [Event].[InspectionType],
  [Event].[CostType],
  [Event].[InstructedOn],
  [Event].[InvoiceNumber],
  [Event].[WeekNumber]
FROM
  [dbo].[Event]
WHERE
  (EXISTS (
    SELECT
      [PropertiesWithCurrentStatus].*
    FROM
      [dbo].[PropertiesWithCurrentStatus]
    WHERE
      [PropertiesWithCurrentStatus].[Id] = [Event].[PropertyId] AND
      [PropertiesWithCurrentStatus].[CurrentStatusId] = @p0
  ) AND [Event].[DeletedOn] IS NULL);

SELECT
  [Party].[Id],
  [Party].[ContactEmailAddress],
  [Party].[ContactFaxNumber],
  [Party].[ContactFirstName],
  [Party].[ContactLastName],
  [Party].[ContactPhoneNumber],
  [Party].[DeletedOn],
  [Party].[LockVersion],
  [Party].[OrganisationId],
  [Party].[PartyType],
  [Party].[StaffNumber]
FROM
  [dbo].[Party]
WHERE
  (EXISTS (
    SELECT
      [Event].*
    FROM
      [dbo].[Event]
    WHERE
      [Event].[CreatedById] = [Party].[Id] AND
      EXISTS (
        SELECT
          [PropertiesWithCurrentStatus].*
        FROM
          [dbo].[PropertiesWithCurrentStatus]
        WHERE
          [PropertiesWithCurrentStatus].[Id] = [Event].[PropertyId] AND
          [PropertiesWithCurrentStatus].[CurrentStatusId] = @p0
      )
  ) AND [Party].[DeletedOn] IS NULL);

SELECT
  [Organisation].[Id],
  [Organisation].[AddressArea],
  [Organisation].[AddressCountry],
  [Organisation].[AddressCounty],
  [Organisation].[AddressFlatNumber],
  [Organisation].[AddressHouseNumber],
  [Organisation].[AddressPostCode],
  [Organisation].[AddressStreetName],
  [Organisation].[LockVersion],
  [Organisation].[Name]
FROM
  [dbo].[Organisation]
WHERE
  EXISTS (
    SELECT
      [Party].*
    FROM
      [dbo].[Party]
    WHERE
      [Party].[OrganisationId] = [Organisation].[Id] AND
      EXISTS (
        SELECT
          [Event].*
        FROM
          [dbo].[Event]
        WHERE
          [Event].[CreatedById] = [Party].[Id] AND
          EXISTS (
            SELECT
              [PropertiesWithCurrentStatus].*
            FROM
              [dbo].[PropertiesWithCurrentStatus]
            WHERE
              [PropertiesWithCurrentStatus].[Id] = [Event].[PropertyId] AND
              [PropertiesWithCurrentStatus].[CurrentStatusId] = @p0
          )
      )
  );

SELECT
  [Party].[Id],
  [Party].[ContactEmailAddress],
  [Party].[ContactFaxNumber],
  [Party].[ContactFirstName],
  [Party].[ContactLastName],
  [Party].[ContactPhoneNumber],
  [Party].[DeletedOn],
  [Party].[LockVersion],
  [Party].[OrganisationId],
  [Party].[PartyType],
  [Party].[StaffNumber]
FROM
  [dbo].[Party]
WHERE
  (EXISTS (
    SELECT
      [Event].*
    FROM
      [dbo].[Event]
    WHERE
      [Event].[DeletedById] = [Party].[Id] AND
      EXISTS (
        SELECT
          [PropertiesWithCurrentStatus].*
        FROM
          [dbo].[PropertiesWithCurrentStatus]
        WHERE
          [PropertiesWithCurrentStatus].[Id] = [Event].[PropertyId] AND
          [PropertiesWithCurrentStatus].[CurrentStatusId] = @p0
      )
  ) AND [Party].[DeletedOn] IS NULL);

SELECT
  [Organisation].[Id],
  [Organisation].[AddressArea],
  [Organisation].[AddressCountry],
  [Organisation].[AddressCounty],
  [Organisation].[AddressFlatNumber],
  [Organisation].[AddressHouseNumber],
  [Organisation].[AddressPostCode],
  [Organisation].[AddressStreetName],
  [Organisation].[LockVersion],
  [Organisation].[Name]
FROM
  [dbo].[Organisation]
WHERE
  EXISTS (
    SELECT
      [Party].*
    FROM
      [dbo].[Party]
    WHERE
      [Party].[OrganisationId] = [Organisation].[Id] AND
      EXISTS (
        SELECT
          [Event].*
        FROM
          [dbo].[Event]
        WHERE
          [Event].[DeletedById] = [Party].[Id] AND
          EXISTS (
            SELECT
              [PropertiesWithCurrentStatus].*
            FROM
              [dbo].[PropertiesWithCurrentStatus]
            WHERE
              [PropertiesWithCurrentStatus].[Id] = [Event].[PropertyId] AND
              [PropertiesWithCurrentStatus].[CurrentStatusId] = @p0
          )
      )
  );

SELECT
  [EventType].[Id],
  [EventType].[HelpText],
  [EventType].[MenuGroup],
  [EventType].[MenuOrder],
  [EventType].[Title]
FROM
  [dbo].[EventType]
WHERE
  EXISTS (
    SELECT
      [Event].*
    FROM
      [dbo].[Event]
    WHERE
      [Event].[EventTypeId] = [EventType].[Id] AND
      EXISTS (
        SELECT
          [PropertiesWithCurrentStatus].*
        FROM
          [dbo].[PropertiesWithCurrentStatus]
        WHERE
          [PropertiesWithCurrentStatus].[Id] = [Event].[PropertyId] AND
          [PropertiesWithCurrentStatus].[CurrentStatusId] = @p0
      )
  )',N'@p0 bigint',@p0=2

which has the correct view named in the FROM statements, but is missing the ORDER BY that you might expect. Domain model is too complex to include in a post.

Hope that's enough info. If not let me know.

 Cheers,

Donovan.

This is occurring because your LINQ OrderBy operator is running over the results of a Find<>, not a Query<>.

When you do an IUnitOfWork.Find, LightSpeed performs the required query against the database, and returns the results of the database query as an list.  Any LINQ operators you apply to that list -- such as OrderBy -- will therefore run client-side, using the IEnumerable extension methods.  If you want operations to happen in the database, they must be specified as part of the Find, not applied to the results of the Find.

When you do an IUnitOfWork.Query (using the LINQ to LightSpeed extension method), any LINQ operators applied to that query -- such as an OrderBy -- *are* folded into the database query.

The main reason for this difference is that Find was build before LINQ came along, and is required to remain compatible with pre-LINQ versions of the framework.

So how do you fix your query to perform the ordering on the database?  There are two possible approaches:

1. Put the ordering into the Find statement:

uow.Find<Property>(new Query { ..., Order = Order.By("Address.StreetName") }).ToList();

2. Change the Find to a LINQ Query:

uow.Query<Property>("PropertiesWithCurrentStatus")
      .Where(p => p.CurrentStatusId = ...)
      .OrderBy(p => p.Address.StreetName)
      .WithAggregate("WithCurrentStatus")
      .ToList();

(If you are using the designer and have attached the view to the Property entity, then you should be able to replace the first line with uow.PropertiesWithCurrentStatus.  If you are not using the designer, you will need to reference the Mindscape.LightSpeed.Linq assembly and add a using Mindscape.LightSpeed.Linq; statement to your code in order to access the Query extension method.)

 Hi Ivan,

thanks for the rapid and detailed response! Definite case of RT#M!!! Failed to spot that Find<> returned IList and not IQueryable<>. Ordering works as advertised.

 Thanks again.

Donovan. 

Hi Ivan,

just been testing the ordering functionality a bit further and I've found a scenario where the fix doesn't quite work:

              HalifaxUnitOfWork
                               .Clients
                                //.ToList()
                               .OrderBy(s => s.Organisation.Name)
                               .ThenBy(s => s.Organisation.Address.Area) //This line causes the failure
                               .ThenBy(s => s.Contact.FirstName)
                               .ThenBy(s => s.Contact.LastName)
                               .ToList()

where Client and Organisation are Entities and Contact and Address are Value Objects.

Hope that helps.

Donovan

I've implemented a candidate fix for this, which will be included in nightly builds dated 21 Apr 2009 and above, available from about 1430 GMT.  Please let us know if you still see the problem or if it does not work in your scenario.

Note that only one level of entity traversal is supported, e.g. Loans.OrderBy(l => l.Client.Organisation.Address.Area), where Client and Organisation are entities, is still not allowed.  This is a limitation of our join support and will not be addressed before 3.0.