Monday, 13 June 2011

Modifying ADO.NET Entity Data Models

Continuing our look at Entity Data Models (EDMs), you'll now see how to modify an existing model. During the development process, it's not unusual to find that you need to access more data than originally planned. In Visual Studio, you can simply run the Update Wizard to add entities linked to additional tables that you need or define your own custom entities to the model and the database at the same time.

I'll continue working with the model that I created in the previous post, which contains entities for sales territory, sales orders, and store contacts. I now decide that the application needs additional information from the Customer table in the database. The context menu in the Entity Designer window enables you to launch the Update Wizard and select the new tables that you want to use.


And you can see that the model automatically generates the associations between the new and existing entities.


If I want to use data that is not currently stored in my data source, I can define an entity in the model and then update the database to create a table to hold the new information. The Entity Designer context menu enables you to add new items to the model.


I can define the entity and then add properties and associations to it.



I can then either map the new entity to existing data in my enterprise or use the Generate Database Wizard to generate the Transact-SQL code necessary to add tables for the entity to my database.


Note that the Generate Database Wizard generates a script that defines the entire EDM and begins by dropping all the existing tables mapped to the model. If you want to create only a subset of the model, you can use the wizard to generate the script, copy the code from the wizard, and then edit the code to use only the sections that you need.

You can also map entities in the model to stored procedures or views in a database. I've already added a stored procedure named spNumberOfOrdersForAContact to the AdventureWorks database as defined below.



USE [AdventureWorks]

CREATE PROCEDURE [Sales].[spNumberOfOrdersForAContact]
@ContactID [int] = 1
FROM [Sales].[SalesOrderHeader]
WHERE [ContactID] = @ContactID

This stored procedure takes a ContactID as an input and then returns the number of orders that the contact has made as a scalar value.

To access the stored procedure, I first add it to the model by using the Update Wizard similarly to adding a new table to the model and then create a function import in the model to retrieve the data.


So you've now seen how easy it is to modify models to include additional data in them. In my next post, I'll show you more details about how the model is mapped to the data source.

No comments: