Computed Properties One Pager

Article
06/27/2008

UPDATE: this blog post is out of date. Please review this instead.

If you've been paying attention to what the Entity Framework team has been saying. You will remember hearing us talk about using the Entity Data Model (EDM) as a common way of describing models that can be understood by many different applications.

As of V1, the Entity Framework supports ad-hoc line of business applications. In the future though we also want to add extra services, such as reporting. To make this work we need to make a number of key extensions both to the EDM and the Entity Framework itself.

The first of these is Computed Properties.

The Reporting Model requirement, is the ability to create reports in terms of properties that are not mapped to fields in the database, but are instead computed.

Supporting this idea is then vital if we imagine a world where Reporting Services works over the EDM.

Computed Properties however are useful for more than just Reporting scenarios. What follows is the initial one pager (okay more like 5 pages) on the topic:

Scenario:

We want to support Reporting Services (and other customers too) that want to be able to have some properties of the entity be computed in the store.

Examples range from a Person’s Fullname which is simply the Person’s Firstname and Surname concatenated, to more complicated scenarios that use Navigations, and / or completely random eSQL.

Scenario Detailed Walkthrough:

In the EntityType definition a user creates a Computed Property, either with or without the help of tooling:

In this example the “Fullname” property is computed using the eSQL included in the DefiningExpression under the Property.

Interesting Points:

We re-use the <Property> element for calculated properties, the thing that distinguishes computed properties is that the property has a <DefiningExpression> sub element.
Calculated Properties are always read only.
We also want to introduce a ReadOnly attribute to the Property element that can be used to mark non-calculated properties as read only too (See the CreatedDate property above) .
In the DefiningExpression’s eSQL we add support for a contextual variable, tentatively called “it”, which can be used by programmers to reference properties and navigation properties on the Entity.
While any eSQL can be written in the DefiningExpression, the expect shape of the resulting query must be projectable into the Type specified at the Property level. For example in the above, the Fullname property is of type string, so the DefiningExpression is valid only because its return shape is expected to be a single row with a single string column.
Calculated Properties can’t be referenced in the Key of the Entity.
Can Calculated Properties reference other Calculated Properties in the same Entity? Ideally yes.
Can Calculated Properties reference Calculated Properties in other Entities? Ideally yes.
Invalid eSQL or invalid return type of the expression should ideally result in a Parsing Error at runtime.

Computed Property Return Types:

The example given so far, is a special case, in that the eSQL in the DefiningExpression only references properties of the current entity. We can therefore deduce that there will be just one row and from the shape of the projection we can deduce there is one string column, hence we can safely assume this eSQL can be materialized into a single string column or property in the reader or object respectively.

However a DefiningExpression can easily produce multiple rows, either by using a NavigationProperty hanging off the current Entity, or by virtue of being a standard query with an undetermined set of results.

This means we need to also look at handing computed properties that return a Collection.

Additionally the eSQL can return more than one column, so the collection may not necessarily be a Collection of scalars.

Ideally then we should support a collection of ComplexTypes, Entities and perhaps even RowTypes.

Collections of Scalars:

An example would be this:

<EntityType Name="Person">
    <Key>
      <PropertyRef Name="ID" />
    </Key>
    <Property Name="ID" Type="Int32" />
    <Property Name="Firstname" Type="String" MaxLength="50" />
    <Property Name="Surname" Type="String" MaxLength="50" />
    <Property Name="Fullname" Type="String">
<DefiningExpression>
it.Firstname + ‘ ’ + it.Surname
</DefiningExpression>
    </Property>
    <NavigationProperty Name="Friends" Relationship="FriendShip" FromRole="FriendOf" ToRole="Friend" />
    <Property Name="Acquaintances" Type="Collection(String)">
<DefiningExpression>
        SELECT VALUE f.Fullname
FROM it.Friends AS f
</DefiningExpression>
    </Property>
</EntityType>

In this example Acquaintances navigates through a navigation property “Friends”, which returns a Set, so the result is a set of the projected Fullnames.

Another way of writing the calculated property that hard-codes the target set in the model would be:

<Property Name="Acquaintances" Type="Collection(String)">
<DefiningExpression>
SELECT VALUE f.Fullname
FROM [Person] AS P, P.Friends AS F
WHERE P.ID = it.ID
</DefiningExpression>
</Property>

This approach is not to be recommended because it mixes the Entity structure with the extents or entitysets, which of course won’t work well with MEST.

Incidentally there is an unrelated proposal for RS to extend eSQL to make it possible to express the above in this simplified syntax

<Property Name="Acquaintances" Type="Collection(String)">
<DefiningExpression>
SELECT VALUE P.Friends..Fullname
FROM [Person] P
WHERE P.ID = it.ID
</DefiningExpression>
</Property>

Note if in the future ‘..’ notation is supported in eSQL, then it should be a no-op on our end to support this too:

<Property Name="Acquaintances" Type="Collection(String)">
<DefiningExpression>
it.Friends..Fullname
</DefiningExpression>
</Property>

You can also imagine a query that doesn’t use “it” at all, and is completely hardcoded:

<Property Name="TopGuns" Type="Collection(String)">
<DefiningExpression>
SELECT VALUE f.Fullname
FROM [Person] AS P
WHERE P.Firstname IN {‘Colin’, ‘Kati’}
</DefiningExpression>
</Property>

Object Services:

Computed properties that return a single scalar value are treated the same as normal properties, the only difference is that they are read-only, which probably only means that the ObjectStateManager needs to ignore changes to these properties rather than making them impossible to change altogether by removing the Setter.

For Collections of Scalar types the Property should be a IEnumerable<T>, i.e. in the Acquaintances example above there should be a IEnumerable<String> property called Acquaintances on the entity. Again changes should be ignored.

Entities, ComplexTypes and RowTypes:

The eSQL in the DefiningExpression can easily project multiple columns so we need a way of setting the type of Property accordingly. There are only 3 real options: Entities, ComplexTypes and RowTypes.

In this example we reverse a navigation property as a computed property, and return a collection of Person entities. I.e. the People who have the current person as a friend:

<Property Name="FriendOf" Type="Collection(Person)">
<DefiningExpression>
SELECT VALUE p
FROM [Person] AS p, p.Friends AS f
WHERE f.ID = it.ID
</DefiningExpression>
</Property>

The query itself it unimportant, what is important, is that we are returning a set of Entities. Likewise we could do something similar to return a list of ComplexTypes:

<Property Name="FriendsAddresses" Type="Collection(Address)">
<DefiningExpression>
SELECT VALUE f.Address
FROM it.Friends f
</DefiningExpression>
</Property>

If we use a navigation property that returns a single Entity then you could do something like this too:

<Property Name="MaternalGrandmother" Type="Person">
<DefiningExpression>
it.Mother.Mother
</DefiningExpression>
</Property>

Or a single ComplexType:

<Property Name="MothersAddress" Type="Address">
<DefiningExpression>
it.Mother.Address
</DefiningExpression>
</Property>

Ideally we should support, both single and Collections of both Entities and ComplexTypes.

Random projections (i.e. RowTypes) are much more difficult.

If they can be coerced into a wellknown shape by property name matching then it might be possible to project into ComplexTypes. But if not, then there is no O-Space equivalent of a RowType that is appropriate.

Random Examples:

Including an FK in the structure of an Entity:

Note: While this is a handy, we should not confuse this with a solution to requirement for collocation of FK data, because as mentioned previously calculated properties are always read-only and we need a read-write solution to the collocation problem, to allow setting the FK in the Entity directly.

Why DefiningExpression?

DefiningExpression is something we will probably use for C-Side Functions (i.e. functions defined in Entity Data Model in the CSDL). So it makes sense to re-use this term, in order to keep the CSDL vocabulary and mental load smaller.

Also today we already have DefiningQuery in the storage model and QueryView in the mapping, so re-using either of those terms could easily lead to confusion.

Also and perhaps most interestingly, if we interpret the eSQL as a expression, then:

1 => int

Whereas if we interpret as a query:

1 => Collection(int)

This former has useful semantics because we can call functions in the eSQL fragment and declare that the property has a scalar return type rather than a Collection(scalar) return type.

...eof...

I for one would love to hear your thoughts.

Alex James
Program Manager,
Entity Framework Team

This post is part of the transparent design exercise in the Entity Framework Team. To understand how it works and how your feedback will be used please look at this post .