ADO.NET Entity Framework initially generates a 1:1 (one to one) mapping between the database schema and the conceptual schema in most of the cases. In the relational schema, the elements are composed of the tables, with the primary and foreign keys gluing the related tables together. In contrast, the Entity Types define the conceptual schema of the data. One is sure – ADO.NET Entity Framework makes it easy when it comes to work with a database. But is it sure?
Suppose you have created your entities using the Visual Studio 2008 wizard.
You can edit the mappings between entities and database tables using the Mapping Details pane in Visual Studio 2008.
Everything is fine so far. But you want to encapsulate the logic, concerning retrieving and updating the entities, in the database. Is it possible? Yes, it is. You can create stored procedures for insert, update and delete for your entities. Then you can map these procedures using the Mapping Details pane in Visual Studio 2008 again.
Hm.. but you want not only to update your entities via a stored procedure, but you also want to create stored procedures for retrieving custom data. Is this possible? Yes, it is possible, too. But, there are some things you should think of. How will the result from the stored procedure be mapped to an entity? Do you have to create a custom entity for the result, or you can use one of already created? ADO.NET Entity Framework designer let’s import stored procedures and map them to an entity.
When you add the function, you can access it from your entities conext object. Everything does not seem so bad, yet.
The Problem
When you create a function import, it will work only with entities as a result. It means, that it won’t work if you choose a scalar value or None from the window above. Another problem is that if you have navigation properties, they won’t be updated. Suppose you have the following query
|
1 2 3 4 |
SELECT P.[pid], P.[product_name], P.[discontinued_date], C.[cid], C.[category_name] FROM Products P JOIN Categories C ON P.[cid] = C.[cid] WHERE P.[pid] = ... |
ADO.NET Entity Framework can map the result to a User entity, but it will populate only its Name and ID properties. Its navigation property Category won’t be populated. And if you want to access it, you should make another call to the database which is redundant provided that you have retrieved tha necessary data for populating the Category property.
The Solution
The Entity Framework support for read stored procedures is not as full featured as we would like in the first release of the product. As was mentioned above you can have stored procedures which will read from the database but the built in support requires that those procedures return entities–if you want to have a stored procedure that returns some other structure, things get more complicated. (http://forums.asp.net/t/1363046.aspx#2848052)
The ADO.NET Entity Framework Extensions library includes utilities that make querying stored procedures, creating typed results from DB data readers and state tracking external data much easier in the Entity Framework. A sample application demonstrates several patterns using these utilities, including stored procedures with multiple result sets, materialization of CLR types, and registering entities in the Entity Framework state manager. Here is list of the library features:
- Execution of store commands via the ObjectContext.
- Connection lifetime management.
- State management of entities from external sources.
- Materialization of arbitrary CLR types given a data reader or DB command.
- Stored procedure mapping:
- Multiple result sets.
- Column renames, polymorphic results and nested structures via the materialization service.
- Getting and setting key values for entity references.
- Rewrite InvocationExpressions in LINQ queries and expressions
Seems pretty good. All you have to create is a Materializer object, which actually does the work to map the result from a stored procedure to an object. You should also create a predefined version of the entity sets, because they should be of different type. Just extend the entities context class (it is partial) and add the following:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 |
private EntitySet productSet; public EntitySet ProductSet { get { if (this.productSet == null) this.productSet = new EntitySet(this); return this.productSet; } } .... private static readonly Materializer<KeyValuePair<Product, Category>> ProductAndCategoryMaterializer = new Materializer<KeyValuePair<Product, Category>>>(r => new KeyValuePairKeyValuePair<Product, Category>> ( new Product { ProductID = r.Field("pid"), Name = r.Field("pname") }, new Category { CategoryID = r.Field("cid"), Name = r.Field("cname") } )); .... public Product GetProductAndCategory(int pid) { DbCommand command = this.CreateStoreCommand("GetProductAndCategory", CommandType.StoredProcedure, new SqlParameter("pid", pid)); var productAndCategory = ProductAndCategoryMaterializer.Materialize(command).SingleOrDefault(); Product product = this.ProductSet.FindOrAttach(productAndCategory.Key); if (product != null) { product.CategoryReference.Attach(this.CategorySet.FindOrAttach(productAndCategory.Value)); } return product; } |
Links