Entity Framework with Stored Procedure (1)

Entity Framework, a.k.a. ADO.NET Data Model, defaults to dynamic SQL queries when it accesses the storage layer for retrieving and manipulating the data. While that gives the framework tremendously flexibility in its query generator which is doing a decent job. For example, most of the dynamic queries generated are parameterized, which helps ease the worry about SQL injection. Regarding the SQL Server performance, since parameterized queries, like stored procedures, are mostly plan-cacheable, it should not be an issue. The main concern I have is with respect to security - dynamic queries require the user to have full data access permissions on the underlying tables. That is a no-no in most shops. That leads one to stored procedures. How does EF support stored procedures? I will have a series of blogs exploring various aspects of using stored procedures in EF. I will use a simple data model throughout the series. So let's start with defining the data model.

---- Create a database named WORK - I am using the Feb 2008 CTP version of SQL Server 2008.
-- There is nothing special in the work that needs features of SQL Server 2008. SQL Server 2005 will work too.
USE master;
-- Create Database
IF EXISTS (SELECT * FROM sys.databases WHERE [name] = 'WORK') BEGIN
DROP DATABASE WORK;
END;

CREATE DATABASE WORK
ON
( NAME = WORK_DAT,
-- TODO: make sure the file name is correct
FILENAME = 'C:\SQL Data - 2008\WORK_Data.MDF',
SIZE = 10MB,
MAXSIZE = UNLIMITED,
FILEGROWTH = 10% )
LOG ON
( NAME = WORK_LOG,
-- TODO: make sure the file name is correct
FILENAME = 'C:\SQL Data - 2008\WORK_Log.LDF',
SIZE = 5MB,
MAXSIZE = UNLIMITED,
FILEGROWTH = 10% );
GO

---- Create a schem: ef to hold our SQL objects
USE WORK;
CREATE SCHEMA ef AUTHORIZATION dbo;

---- Tables
-- ef.Policy: master table. It has an IDENTITY field. It will be interesting to see how EF handles it.
CREATE TABLE ef.Policy (
policyId INT NOT NULL IDENTITY(1, 1),
expDt DATE NOT NULL,
riskState VARCHAR(25) NOT NULL,
description VARCHAR(100) NULL,
CONSTRAINT PK_ef_Policy PRIMARY KEY CLUSTERED (policyId)
);
-- ef.Driver: a detail tabel. It references ef.Policy and has a GUID primary key with default.
CREATE TABLE ef.Driver (
driverId UNIQUEIDENTIFIER NOT NULL,
policyId INT NOT NULL,
age TINYINT NULL,
CONSTRAINT PK_ef_Driver PRIMARY KEY CLUSTERED (driverId),
--CONSTRAINT DF_ef_Driver_driverId DEFAULT (NEWSEQUENTIALID()) FOR driverId,
CONSTRAINT FK_ef_Driver_policyId FOREIGN KEY (policyId) REFERENCES ef.Policy (policyId)
);
ALTER TABLE ef.Driver ADD CONSTRAINT DF_ef_Driver_driverId DEFAULT (NEWSEQUENTIALID()) FOR driverId;
-- ef.DriverName: a sub detail table. It references ef.Driver and has a GUID primary key with default.
DROP TABLE ef.DriverName;
CREATE TABLE ef.DriverName (
nameId UNIQUEIDENTIFIER NOT NULL
CONSTRAINT PK_ef_DriverName PRIMARY KEY CLUSTERED,
driverId UNIQUEIDENTIFIER NOT NULL
CONSTRAINT FK_ef_DriverName_driverId FOREIGN KEY REFERENCES ef.Driver (driverId),
lastName VARCHAR(100) NOT NULL,
firstName VARCHAR(25) NULL,
middleName VARCHAR(25) NULL,
effDt DATE NOT NULL
);
ALTER TABLE ef.DriverName ADD CONSTRAINT DF_ef_DriverName_nameId DEFAULT (NEWSEQUENTIALID()) FOR nameId;

After the database objects are created on the SQL server, I then open Visual Studio 2008 SP1 Beta1 which has beta3 of the Entity Framework (ADO.NET Data Model). I create a Console application. Then add an ADO.NET Data Model item - the wizard leads to the connection of the above WORK database, and I rename the item to be WORK.edmx and the other names were given by default.

At this point, I do a little customization:
The auto-generated code name both the EntityType and EntitySet the same. For, example, for the Policy entity, both its type and collection are named Policy. That can be confusion in future coding. So I decide to rename the EntitySet to be Policyes in this case. The same for Driver and DriverName. To do that, we need to make some changes in the XML file WORK.edmx as follows:
In CSDL: under , change under , change .If necessary, change
In MSL: change
Save WORK.edmx
In VS, right-click WORK.edmx and Run Custom Tool.
Since the TIMESTAMP field ts is server-generated and is intended for consistency control (discussed in details when we implement the Update and Insert stored procedures) and is not for client code manipulation, I make its Setter private just to be sure.
So now we have a database data model and its related entity object model. In future blogs of this series, we will introduce stored procedures and see how they can be used within EF.