TOP

Entity Framework with extended properties

Here is an example that show how you can extend your Entity Framework entities with properties, which you might return from a stored procedure, a database view or inline SQL. The use case if when you want and need to return more data from your database than what is in your 1:1 mapped entity models. All my entity models have a 1 to 1 relationship between the physical and the logical models. It’s a requirement in Entity Framework that all mappings have a defined definition, so it’s not easy to just extend the model with extra properties that you might use in a few cases.

What we’re trying to achieve here is something that traditionally has been very easy with ADO.NET datasets, where you can populate the entity with data from extra columns even though the original up-front-design didn’t contain those columns.

This example shows how to extend the entity model and use it with WCF RIA Services without using the built-in feature for entity inheritance and complex types in Entity Framework. Initially I tried to use these features, but was unable to make it work properly in combination with WCF RIA Services. Current version of WCF RIA Services for Silverlight 4 and .NET 4 does not support Complex Types, only Entity Objects. The simplest way to use Stored Procedures in Entity Framework, if you don’t need WCF RIA Services support, is to import the function into the model and auto-generate a Complex Type based upon the return structure from your procedure.

This example is for read-only operations, for CUD-operations you can use the normal pattern.

Entity Model

Here is my simple model which contains Catalogs which holds Items. A catalog can only hold certain types of items, hence the ItemType table connected to the Catalog table. In our example we need to extend the Catalog entity with three more properties: ItemCount, ItemTypeName and Icon. As you can see, my table structure has no relationships. If you have relations between the entities, you will require to handle some compilation errors as you try to expose the custom type through the WCF RIA Services. I don’t have a solution for that in this post.

Entity_Model

Entity Inheritance

As I mentioned earlier, this is not the built-in entity inheritance in Entity Framework. This has been explained by Muhammad Mosa here: Inheritance and Associations with Entity Framework Part 1 and Zeeshan Hirani here: Single Table Inheritance in Entity Framework.

First thing you need to do is create a new class that inherits from your generated entity model. I called my custom type for CatalogView and it extend the Catalog type with three simple properties.

public class CatalogView : Catalog
{
    [DataMemberAttribute()]
    public string Icon { get; set; }

    [DataMemberAttribute()]
    public int ItemCount { get; set; }

    [DataMemberAttribute()]
    public string ItemTypeName { get; set; }
}

The next step is to make the Catalog entity type aware of this custom type, this is a requirement for WCF. Open the *.Designer.cs for your Entity Model, find the correct entity type and add the following attribute to the class.

[KnownType(typeof(CatalogView))]
public partial class Catalog : EntityObject

This is required for WCF to be able to properly parse your custom entity. Remember that this change will be lost the next time you update your model. Without this property, you should see a similar compilation error: The derived entity type ‘CatalogView’ must be declared in a KnownTypeAttribute on the root entity ‘Catalog’.

Data Access Layer

Now we need to write some data access code which will query our database and return a list of CatalogView objects. For simplicity I’m using inline SQL in this example, yet the code would be very similar if you move the SQL code into a stored procedure.

public class BoksDataAccess
{
    public List<CatalogView> GetCatalogs(Guid userId)
    {
        List<CatalogView> catalogs = new List<CatalogView>();

        string connectionString = Properties.Settings.Default.BoksConnectionString;

        using (var conn = new SqlConnection(connectionString))
        {
            using (var cmd = new SqlCommand())
            {
                cmd.Connection = conn;
                cmd.Parameters.Add("UserId", System.Data.SqlDbType.UniqueIdentifier).Value = userId;
                cmd.CommandText = @"SELECT
                                    distinct(c.Id),
                                    c.Name,
                                    c.Created,
                                    c.Modified,
                                    c.UserId,
                                    c.ItemTypeId,
                                    it.Icon,
                                    it.Name AS ‘ItemTypeName’,
                                    (SELECT COUNT(i.Id) from Item i where i.CatalogId = c.Id) AS ‘ItemCount’
                                FROM Catalog c
                                LEFT JOIN ItemType it
                                ON c.ItemTypeId = it.Id
                                WHERE c.UserId = @UserId";
                conn.Open();

                var reader = cmd.ExecuteReader();

                while (reader.Read())
                {
                    var catalog = new CatalogView();

                    catalog.Id = (Guid)reader["Id"];
                    catalog.Name = (string)reader["Name"];
                    catalog.Created = (DateTime)reader["Created"];

                    if (reader["Modified"] != DBNull.Value)
                    {
                        catalog.Modified = (DateTime)reader["Modified"];
                    }

                    catalog.UserId = (Guid)reader["UserId"];
                    catalog.ItemTypeId = (int)reader["ItemTypeId"];
                    catalog.Count = (int)reader["ItemCount"];
                    catalog.ItemTypeName = (string)reader["ItemTypeName"];
                    catalog.Icon = (string)reader["Icon"];

                    catalogs.Add(catalog);
                }

                conn.Close();
            }
        }

        return catalogs;
    }

Domain Service

With our new custom data access layer in place, we can extend our domain service for the Catalog with an method that calls this operation. Here is my example:

public IEnumerable<CatalogView> GetCatalogsWithSummary()
{
    return new BoksDataAccess().GetCatalogs(new Guid("0DD43518-8AA2-4FF7-8A0B-6DECCB6C4412"));
}

Within your Silverlight application, you can now call this method to return the entities with the extended properties. Here is a simple example for a click event on a button within my app:

private void button1_Click(object sender, RoutedEventArgs e)
{
    Web.Services.CatalogContext service = new Web.Services.CatalogContext();
    service.Load(service.GetCatalogsWithSummaryQuery(), CatalogViewCallback, null);
}

private void CatalogViewCallback(LoadOperation<Models.Data.CatalogView> loadOperation)
{
    foreach (var catalogView in loadOperation.Entities)
    {
        MessageBox.Show(catalogView.Name + " " + catalogView.ItemCount);
    }
}

Conclusions

The procedure explained in this post should be considered a work-around for using extended properties with Entity Framework in relation with WCF RIA Services. There might be other ways of achieving the same, this is a solution I came up with after many hours of scanning through the examples and samples available on the web.

One positive feature of this implementation, as oppose to writing a new custom complex type that maps to your custom SQL query, is that you get inheritance of the entities on the WCF RIA Service client. You don’t get two different types with duplicate properties, but real inheritance. Here is the declaration of the CatalogView generated code on the client:

[DataContract(Namespace="http://schemas.datacontract.org/2004/07/Boks.Models.Data")]
public sealed partial class CatalogView : Catalog

Leave a Reply

Your email is never published nor shared.

You may use these HTML tags and attributes:<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>