Model Defined Functions


Today the Entity Framework, and more specifically the Entity Data Model, have a limited notion of Functions.


We are currently restricted to Function Imports that allow stored procedures to be invoked, and Canonical / Store Functions for database independent and database specific functions respectively.


Now however we want to support functions defined, not just declared, in the EDM (aka. the CSDL).


An example would be:

<Function Name=GetAge ReturnType=Edm.Int32>
      <Parameter Name=Person Type=Model.Person />
      <DefiningExpression
            Edm.DiffYears(Edm.CurrentDateTime(), Person.Birthday)
      </DefiningExpression>
</Function>

Here are some things to notice:




  • The DefiningExpression is eSQL.


  • The function can have zero or more parameters.


  • The Function must have a return type.


  • Function Parameters are referenced directly by Name in the DefiningExpression: meaning there is no parameter denoting prefix like @. This means you must be careful to choose parameter names that don’t collide with identifiers you need to use in the rest of eSQL expression.


  • Unlike functions in SSDL, functions in CSDL only support In bound Parameters (i.e Mode=In“) because otherwise they become non-composable.


  • For this reason the Mode of a parameter cannot be set in CSDL (it is always Mode=In).


  • Functions are declared as Global Items and are declared within the <Schema> element. As such there identity is made up of the Schema’s namespace and the function name.


  • The function parameters and return type can be any of the following:



    • A scalar type or collection of scalar types.


    • An entity type or collection of entity types.


    • A complex type or collection of complex types.


    • A row type or collection of row types (See below).


    • A ref type or collection of ref types.


  • Functions with a DefiningExpression do not require mapping, since the eSQL expression is composed out of eSQL fragments that are already mapped.


  • Functions without a DefiningExpression are simply declarations. Today the Entity Framework doesn’t complain when loading a CSDL with such a function, but you can’t invoke it. In the future these functions might be used to support mapping Table Value Functions in the store to functions in the Conceptual Model.


  • Since it is trivial in eSQL to create arbitrary un-named types, imagine a projection that projects 3 of the properties from an Entity, we now need a mechanism for defining these “RowTypes” inline, so that they can be used when defining Function Parameters and ReturnTypes. For example:

    <Parameter Name=Coordinate>
       <RowType>
          <
    Property Name=X Type=int Nullable=false/>
          <
    Property Name=Y Type=int Nullable=false/>
          <
    Property Name=Z Type=int Nullable=false/>
       </
    RowType>
    </Parameter>


  • Since eSQL is primarily set based, we also need a way of defining parameters and return types that are collections of RowTypes:

    <Parameter Name=Coordinates>
       <CollectionType>
          <RowType>
             <
    Property Name=X Type=int Nullable=false/>
             <
    Property Name=Y Type=int Nullable=false/>
             <
    Property Name=Z Type=int Nullable=false/>
          </
    RowType>
       </CollectionType>
    </Parameter>

Using the Function via eSQL:


It is trivial to use the function via eSQL. For example:


SELECT Namespace.GetAge(p)
FROM Container.People AS P
WHERE P.Firstname = ‘Jim’

Here we get Jim’s age, assuming of course there is only one Jim!


It is also possible to compose functions together, you must simply ensure return types and target parameter types are the same (in the case of named types) or structurally equivalent (in the case of row types or collections of row types).


Things get a little trickier when you are dealing with functions that return sets, for example imagine a function that returns someone’s friends, used in conjunction with the GetAge function:


SELECT VALUE (F)
FROM Container.People AS P
CROSS APPLY Namespace.GetFriends(P) AS F
WHERE Namespace.GetAge(P) > 21

Here we get all the friends of people older than 21.


As you can see to do this sort of thing you need a crash course in eSQL.


Using the Function via LINQ:


It is also possible to use these functions in LINQ, but this does require a extra step to create an appropriate stub function in the CLR.


This solution is based on the techniques described here, and involves creating a Stub function in the CLR language of your choice, and annotating it something like this:


[EdmFunction(“Namespace”, “GetAge”)]
public static int GetAge(Person p)

    throw new NotSupportedException(…);
}

The Entity Framework uses the signature of the function and the EdmFunction attribute to map calls to this function when encountered to the appropriate Model Defined Function.


Once you have this stub it is then trivial to use it in a LINQ query like this:


var peopleOver21 =
     from p in ctx.People
     where GetAge(p) < 21
     select p;

Indeed if you are familiar with LINQ you will probably find composing functions together a lot easier too:

var friendOfPeopleOver21 =
     from p in ctx.People
     from f in GetFriends(p)
     where
GetAge(p) < 21
     select f;

Notice that the CLR functions don’t need to be directly callable, in the example above the CLR stub throws an exception if called directly.


However the existence of the stub allows you to create LINQ expressions that compile correctly, and then at runtime, when used in a LINQ to Entities query, the function call is simply translated by the entity framework into a query that runs in the database.


Summary


As you can see Model Defined Function’s are very powerful, and this post has barely scratched the surface of possibilities they open up.


The Entity Framework team would love to hear your comments.


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.

Comments (22)

  1. Kristofer says:

    Sorry, but for consistency shouldn’t the DefiningExpression part live in the SSDL rather than the CSDL?

    I.e. declare the function’s signature in CSDL, declare the function itself in SSDL and map the two in MSL…

    I think that would be more consistent with how everything else is done today in EDMX.

  2. Kristofer says:

    …or maybe I am misunderstanding what you’re trying to achieve.

    To make the problem/solution at hand easier to understand – could you elaborate on what are the benefits of defining the ESQL part in the CSDL and/or what problem you’re trying to solve?

  3. AlexJ says:

    Kristofer,

    I’m not sure that there is anything inconsistent about having something live purely in the CSDL.

    Having said that declaring and defining (with native SQL) a function in the SSDL and mapping that through to a CSDL declaration is an interesting scenario. And is closely related to something we are considering for Table Value Functions. This is why today if you only have a declaration in the CSDL, it won’t fail at metadata loadtime, it means in the future we can support that declaration mapping to something in the SSDL.

    Still there are a number of benefits of functions declared and defined purely in the CSDL:

    1) The functions don’t rely on anything in the MSL or SSDL, i.e. they are fully portable.

    2) In eSQL you get to write functions in terms of the conceptual model, rather than in terms of the storage model. I.e. you don’t need to worry about things like joins for either inheritance or associations, the existing mapping takes care of that.

    3) Since the entity framework can parse and understand eSQL (as opposed to native SQL) we can potentially optimize calls to retrieve only what is required when less than is returned is actually required by the calling code (eSQL or LINQ).

    There are probably other advantages but that is what is coming to mind right now.

    Cheers

    Alex

  4. Kristofer says:

    Thanks, that makes it more clear. Esp. #2 is a very good reason for doing this.

  5. Kristofer says:

    Will you also allow CSDL functions to map to managed code implementations?

    E.g.:

    <Function Name="GetAge" ReturnType="Edm.Int32">

         <Parameter Name="Person" Type="Model.Person" />

         <ManagedMethod Name="GetPersonAge" assembly="PersonExtensions.AgeCalculator, Version=1.2.3295.12345, Culture=neutral, PublicKeyToken=123412341234ABCD" />

    </Function>

    That would provide a nice extensibility point allowing plugins that would also be used by ‘other’ consumers of the model (e.g. reporting tools etc).

  6. AlexJ says:

    Kristofer,

    We have discussed the possibility of a language attribute on the <DefiningExpression> basically for the scenario you talk about.

    However that is definitely more of an V3/V4 kind of a thing, you shouldn’t expect to have built-in support for this in .NET 4.0 (i.e. EF v2).

    Having said that however it will be possible to not include a DefiningExpression and instead have your own structural annotation (see the post on structural annotations on this blog) i.e.

        <mynamespace:ManagedMethod Name="GetPersonAge" assembly="PersonExtensions.AgeCalculator, Version=1.2.3295.12345, Culture=neutral, PublicKeyToken=123412341234ABCD" />

    And use that to support other consumers of the model.

    Cheers

    Alex

  7. Kristofer says:

    …and… While we’re in on the topic of providing extensibility that can be used by all consumers of the model… How about CSDL functions mapped to webservice (and WCF) calls, e.g.:

    <Function Name="GetMarkToMarketValue" ReturnType="Decimal">

        <Parameter Name="MarketTransaction" Type="Model.MarketTransaction" />

        <WebServiceMethod Name="GetMarkToMarketValue" URL="http://hedgehog.huagati.com/test/marktomarket.asmx&quot; />

    </Function>

  8. Alex James says:

    Ah, someone who thinks a little like me 😉

    Well one of the scenarios I see for <Function>s with no <DefiningExpression>, maybe with structural annotations, is the creation of WebService end points etc.

    Given that we are adopting customizable T4 templates for Code Generation (not to mention model first scenarios) in the Entity Framework, I think you can imagine the possibilities.

    Alex

  9. Kristofer says:

    Neat, will the default code generator in v2 be T4 based?

  10. Yes, the default code generator in v2 will be T4 based, but in a sense that will just be an implementation detail.  The interesting part really is just that we will have fully supported templates as a starting point and some tooling integration to make it easy to turn off the default code generator and drop into your project the template so that you can begin customizing it (or even pick from multiple different flavors of templates as starting points).

    – Danny

  11. Are we witnessing a job interview here? 😉

    I wonder if anyone has complained that you are putting business logic into the model with this?

    Will there be designer support for this? Not for the building the expression itslef but so that you don’t have to go into the XML to implement it.

    julie

  12. jlerman@thedatafarm.com says:

    BTW – I wasn’t complaining about business logic, just curious about feedback from others. Alex, you know I’m a fan of definingExpression.

    julie

  13. efdesign says:

    Julie,

    I’m sure some will complain. We all have different ideas about how things should be done.

    Personally I don’t see the conceptual difference between a Model Defined Function and WebService end point. The only difference is that the Entity Framework automatically handles calls to the MDFs.

    And given that you can write Model Declared Functions too (i.e. no definition) you can separate out the implementation from the declaration too, if you think that is important.

    If you do that of course, then you need to handle calls to the function yourself.

    Am I making any sense?

    Alex

  14. shawn says:

    Would it be possible to do this for non-static functions as well, so that you can write the queries in a more object-oriented way?  So instead of:

    var peopleOver21 =

    from p in ctx.People

    where GetAge(p) > 21

    select p;

    Could you map it such that you could query like this:

    var peopleOver21 =

    from p in ctx.People

    where p.Age > 21

    select p;

    That way you are calling p.Age instead of GetAge(p)?  It seems like that would be a cleaner, more OO way to consume the data in many cases.

  15. efdesign says:

    Shawn,

    Well it is not something we have done yet. But it is something we are considering. All we need to do is allow the [EdmFunction] attribute on properties and treat the declaring class as the first parameter in the MDF implicitly

    Sort of like the reverse of Extension Methods I suppose.

    Cheers

    Alex

  16. A while back I wrote a post that introduced the concept of Computed Properties . Since that time we’ve

  17. Bruno Santos says:

    Hi. That’s a great post! Its exacly what I wanted.

    The problem is, it seems the atribute doesn’t exist in the VB.NET is that possible?

    I can only find 2 sites exposing this subject, yours and this:

    http://blogs.msdn.com/efdesign/archive/2008/10/08/edm-and-store-functions-exposed-in-linq.aspx

  18. Thank you for submitting this cool story – Trackback from DotNetShoutout

  19. As this going to support Linq, the return type of Fucntions that return collection would be of type IQueryable right?

    Also in case function return colletion of entities does eager and lazy loading work on this case?!

    I also assume it will support POCO entities by default! Am I right?

  20. progg.ru says:

    Thank you for submitting this cool story – Trackback from progg.ru

  21. @Muhammad,

    These functions are just used to give the function signature for us to figure out how to translate into a part of the overall query sent to the server.  As such the return type does not need to be IQueryable–it can be just IEnumerable.

    Once the query has been executed and the results returned, if the results are entities, then lazy loading will work the same as for any other entities.  Eager loading can also work because this is just a rewrite over the query to bring along extra data.

    Yes, it will work fine for whatever entities you have–POCO or non-POCO.

    – Danny

  22. efNewbie says:

    First off . . . I am a complete newbie to "APPLIED" EF (read about, seen it at Code Camp).

    After a not terribly painful week . . .

    I have the EF implemented in a grid for a very basic header-detail (2 tables).

    I am however feeling a bit nervous re: can the EF do all we need.

    I read people who trash EF as not ready for prime time . . . as a ½-way there technology.

    Here’s where I stand in the trench at the moment . . .

    The detail records are presented in a asp:GridView

     with [‘Edit’ ‘Delete’ ‘Select’] in the leftmost column.

    ‘Edit’ works (though the hiding of columns appears to only be doable following setting the DataSource).

     Is there a way in the EDMX mapping to leave out columns. Apparently no . . . all columns MUST be mapped.

    ‘Delete’ works . . . but how to ‘Delete Confirm ?’. Javascript ?

    ‘Select’ . . . I don’t understand. What is it doing ? How do I wire an imported function to ‘Select’.

     My current biggest hurdle of the moment is how to ‘filter’ the Select sproc.

    I am right now struggling with try to accomplish filtering based on several DropDownLists on the form

    by passing parameters to the .Where of the ObjectQuery, but the .Execute is failing:

     "’tblTable.FieldName’ could not be resolved in the current scope or

    context. Make sure that all referenced variables are in scope, that required

    schemas are loaded, and that namespaces are referenced correctly., near

    multipart identifier, line 6, column 47."

    More questions:

     1. What are the options for the ‘Operator’ column of the mapping dialog of the Model Browser ?

       There is no dropdown list here.

     2. Can DropDownLists be added to the asp:GridView cells ? and is sample code available to do that ?

    And finally a clarification:

     Could it be said that the EF is the new version of what has been known as "Typed Datasets" ?