Eager Loading With Views

Hi

It seems that when a View is used with a Find<> query, any eager loading SQL statements that get generated in the same batch do not make use of the specified View

 e.g.

                    Find<Property>(new Query
                   {
                       ViewName = "PropertiesWithCurrentStatus",
                       QueryExpression = (Entity.Attribute("CurrentStatusId") == stateId.Value),
                   })

generates:

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
      [Property].*
    FROM
      [dbo].[Property]
    WHERE
      [Property].[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
          [Property].*
        FROM
          [dbo].[Property]
        WHERE
          [Property].[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
              [Property].*
            FROM
              [dbo].[Property]
            WHERE
              [Property].[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
          [Property].*
        FROM
          [dbo].[Property]
        WHERE
          [Property].[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
              [Property].*
            FROM
              [dbo].[Property]
            WHERE
              [Property].[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
          [Property].*
        FROM
          [dbo].[Property]
        WHERE
          [Property].[Id] = [Event].[PropertyId] AND
          [PropertiesWithCurrentStatus].[CurrentStatusId] = @p0
      )
  )',N'@p0 bigint',@p0=3

where the expected behaviour would be:

FROM
  [dbo].[PropertiesWithCurrentStatus]

for all the queries, not just the first, rather than:

FROM
  [dbo].[Property]

Cheers,

Donovan.

Hi Donovan,

Thanks - we have tracked this one down and committed a fix for this which will show up in future nightly builds (20090409 and higher), let us know how you get on with this :)

Jeremy

Thanks for letting us know about this.  We've fixed the issue and the fix will be included in nightly builds dated 9 Apr 2009 and above, available from about 1430 GMT.

 Hi,

 finally got round to testing this fix. It does exactly what it says on the tin and solved my intial problem which is great! Thanks very much for the speed of response!!

I put it to test with the following view:

IF  EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[PropertiesWithCurrentStatus]'))
DROP VIEW [dbo].[PropertiesWithCurrentStatus]
GO
CREATE VIEW PropertiesWithCurrentStatus
AS

    SELECT
        p1.*,
        e1.eventTypeId as CurrentStatusId
    FROM Property p1
    INNER JOIN Event e1
    ON p1.Id = e1.PropertyId
    WHERE e1.CreatedOn =
    (
        SELECT MAX(CreatedOn)
        FROM Event e2
        WHERE e2.EventTypeId BETWEEN 1 AND 7 --Hard-coded EventTypeId's YUCK
        AND e2.DeletedOn IS NULL
        AND e2.PropertyId = e1.PropertyId
    )

    UNION
    SELECT
        p2.*,
        NULL as CurrentStatusId
    FROM property p2
    WHERE NOT EXISTS
    (
        SELECT 1
        FROM Event e3
        WHERE e3.EventTypeId BETWEEN 1 AND 7
        AND e3.DeletedOn IS NULL
        AND e3.PropertyId = p2.Id
    )

 which (ignoring the nasty hardcoded id's!) adds an extra colum so as to be able to always find the latest status and to be able to use that in the where statement of a query. This works fine for queries which use the view, but any query which just wants to reference the table will throw a "CurrentStatusId column not found exception".

 Perhaps an attribute, similar to Transient, would be useful. Something like Optional or ReadOnly, or even ViewOnly maybe, which attaches the column to a specific view. Haven't tried it yet, but I guess that giving all the non-view properties in the entity an aggregate name would solve the problem from a different angle.

Basically, the requirement is to have extra column(s) in a view for an entity which can be easily excluded from normal operations on that entity.  Is there a simpler way of doing this that already exists?

 Cheers,

Donovan.

Thanks for the suggestion.  This could be a little bit tricky for us to implement, so I'd initially suggest exploring the named aggregate / lazy-load approach.  Aggregates are our way of representing load strategies so they're probably the appropriate way of representing this, though they're not be a perfect fit -- you'd need to synchronise your use of aggregates with your use of views -- but I think it would be useful to see if they can address your current requirement (and, if not, where they fall short) before implementing anything new.

 Hi Ivan,

lazy loading seems to cover the additional view columns that may occur. Looked at the code generated for the LockVersion property and it seemed to match the bill and so got away with this:

public partial class Property

{

.....

        [EagerLoad(AggregateName = "WithCurrentStatus")]
        private readonly long? _currentStatusId;

.....

}

 which allows this statement to work:

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

but does feel a bit kludgy, and it wouldn't work for a Linq WHERE statement.

I suppose the real requirement is for every view to be able to have associated server-generated read only columns which are queryable, but read-only. This would avoid the need to remember to have a matching pair of ViewName and AggregateName for every query.

It works for now, but interesting edge use case to consider.

 Cheers,

Donovan.