Mapping Conceptual Model Function to Complex Type in Entity Framework 4.0

by mosessaur| 07 April 2010| 2 Comments

Introduction

You might hear of KiGG, the open source project that is currently live as http://dotnetshoutout.com. I wanted to expose part of KiGG data as an OData Service for read only. But I figured out that exposing raw KiGG schema might not be useful. So I had to choose between 2 options that were up to my mind:

  • Build some views on the physical store -database-. Create a new entity data model for those views and use the new data model context for DataServiceContext.
  • Use Entity Framework 4.0 conceptual model function feature with complex types to simulate views. It’s like building views on the conceptual model itself and not the store model.

I picked the 2nd option. The sample downloadable sample is available at the end of this post.

KiGG Entity Data ModelKiGG Entity Data Model

I needed to expose “most viewed” stories from the data model in a simple way. However, the query seemed to be complex when using LINQ over OData or through a URL.

The query will require retrieving stories from Stories entity set and count each story view in StoryViews entity set.

To expose this through an OData service, I thought it would be better to make it as service operation “MostViewed” that will return an IQueryable of a certain complex type.

Creating Complex Type StorySummaryStorySummary Complex Type

At the beginning I used Entity Framework designer to create a complex type called StorySummary with 3 properties, Title, Url and Views as shown in the next figure.

Read more about creating complex types using Entity Framework designer in VS.Net 2010-

And here is another cool post by Julie Lerman “Complex Types in the EDM Designer in EF4 and a look at updating complex types in code”

 

LINQ to Entities Vs Conceptual Model Functions

One question might raise here! Why use eSQL with Conceptual Model functions and not using LINQ to Entities directly?

In my case here, the answer is very straight forward. I cannot use LINQ to Entities here, because constructing Complex Type inside LINQ to Entities query is not support so far. That means I’ll need to construct anonymous type instead! And Of course I cannot use IQueryable<Anonymous type> as a return result for my service operation.

On the other hand, Entity SQL (ESQL) gives a good facility to construct complex types inside a query. And better, I can put this as reusable Conceptual Model Function that can be invoked inside LINQ to Entities queries -How to: Call Model-Defined Functions in Queries (LINQ to Entities)-. And also can be invoked as ObjectContext method -How to: Call Model-Defined Functions as Object Methods (LINQ to Entities)-

Create Conceptual Model Function for StorySummary

To create a conceptual model function I’ll need to switch to XML view of my EDMX.

  1. Right click on the model and select “Open With…” option
  2. From the dialog window “Open With” select “Automatic Editor Selector (XML)” option

Scroll to the end of your conceptual model as show in the following screenshot

ConceptualModelFunction

Note that It's mandatory to place the parameters in the same order as the properties of the class are declared as shown in the above code snippet figure.

Create your model function using Function element as the following:

  1. <Function Name="GetMostViewedStories"
  2.           ReturnType="Collection(Kigg.Model.StorySummary)">
  3.   <DefiningExpression>
  4.     SELECT
  5.       VALUE(Kigg.Model.StorySummary(innerQ.Title, innerQ.Url, innerQ.Views))
  6.     FROM
  7.     (
  8.       SELECT
  9.       sv.Story.Title as Title,
  10.       sv.Story.Url as Url,
  11.       Count(sv.StoryId) as Views
  12.       FROM KiggContainer.StoryViews as sv
  13.       GROUP BY sv.Story.Title,sv.Story.Url ORDER BY Views DESC
  14.     ) as innerQ
  15.   </DefiningExpression>
  16. </Function>

In Name attribute I specified the Edm function name. And in ReturnType I specified the expected result type of this function. Because this function isn’t scalar, and it should return a list of result, I specified the return type to be “Collection(Kigg.Model.StorySummary)”.

Inside Function element I added DefiningExpression element. Its content will be the body of the model function. In my case the Entity SQL query that will return result set of StorySummary with views count calculated for each story as shown in the query above.

HINT: I have an important hint about constructing Complex Type inside EntitySql. In the screenshot above, note the arrows (1, 2 & 3) made beside StorySummary complex type properties. Then in the Entity SQL query, note the sequence of parameters passed to StorySummary constructor inside the query. This sequence must match the same sequence of the defined properties of the complex type.

The generated SQL of the above query is this:

  1. SELECT
  2. 1 AS [C1],
  3. [GroupBy1].[K1] AS [Title],
  4. [GroupBy1].[K2] AS [Url],
  5. [GroupBy1].[A1] AS [C2]
  6. FROM ( SELECT
  7.     [Extent2].[Title] AS [K1],
  8.     [Extent2].[Url] AS [K2],
  9.     COUNT([Extent1].[StoryId]) AS [A1]
  10.     FROM  [dbo].[StoryView] AS [Extent1]
  11.     INNER JOIN [dbo].[Story] AS [Extent2] ON [Extent1].[StoryId] = [Extent2].[Id]
  12.     GROUP BY [Extent2].[Title], [Extent2].[Url]
  13.   )AS [GroupBy1]

To be able to invoke this model function from LINQ to Entities, I’m going to create a function in my generated ObjectContext that will map to this function as the following:

  1. public partial class KiggContainer
  2. {
  3.     [EdmFunction("Kigg.Model", "GetMostViewedStories")]
  4.     public IQueryable<StorySummary> GetMostViewedStories()
  5.     {
  6.  
  7.         return QueryProvider.CreateQuery<StorySummary>(
  8.             Expression.Call(Expression.Constant(this),
  9.             (MethodInfo)MethodBase.GetCurrentMethod()));
  10.     }
  11. }

For more details about this please refer to How to: Call Model-Defined Functions as Object Methods (LINQ to Entities)

Calling Conceptual Model Function inside OData Service Operation

This is very simple here is the code snippet:

  1. [WebGet(RequestFormat = WebMessageFormat.Json,
  2.         ResponseFormat = WebMessageFormat.Json,
  3.         BodyStyle = WebMessageBodyStyle.Wrapped)]
  4. public IQueryable<StorySummary> MostViewed()
  5. {
  6.     return CurrentDataSource.GetMostViewedStories();
  7. }

Invoking this method is as simple as this:
“http://localhost:2274/KiggOData.svc/MostViewed” Or
“http://localhost:2274/KiggOData.svc/MostViewed?$filter=Views ge 6”

And filtering will be applied on server side, and you can check that using SQL Server profiler.

References

Conclusion

This post was about Conceptual Model Function, and how it could be useful to create reusable views using Entity SQL the can return result set of complex type. Also we explored how to invoke EDM functions using LINQ to Entities and use it with OData Service Operation.

I hope this post was useful and you enjoyed it.

Attachments: EF4 Conceptual Model Functions with Complex Types (EF4ModelDefinedFunctionWithComplexTypes.zip)

Comments (2) -

Alex
Alex Russia on 4/7/2010 6:16 AM Please fix http://odara.org to http://odata.org in the beginning of the article.
|
Muhammad Mosa
Muhammad Mosa Egypt on 4/7/2010 8:56 PM Fixed. Thanks for the correction Smile
|

Pingbacks and trackbacks (7)+

Comments are closed