Home > Entity Framework > Using Stored Procedure with ADO.NET Entity framework

Using Stored Procedure with ADO.NET Entity framework

I think that Microsoft ADO.NET Entity framework (EF) has a big future which goes beyond just an ORM area. However, the first release is not perfect. Erik Kindblad discusses bunch of shortfalls of VS 2008 SP1 EF implementation. The biggest one is inability to map Entity to the stored procedure. My believe is that in the production environment the access to the tables has to be revoked and the only way to access the data in the table would be a stored procedure. I found several good articles aiming to resolve the same issue, but most of them require additional coding. Then I looked at edmx file to see what mapping xml has been produces by EF designer.

<?xml version="1.0" encoding="utf-8"?>
<edmx:Edmx Version="1.0" xmlns:edmx="http://schemas.microsoft.com/ado/2007/06/edmx">
<!– EF Runtime content –>
<edmx:Runtime>
<!– SSDL content –>
<edmx:StorageModels>
<Schema Namespace="ClinetSODAModel.Store" Alias="Self" Provider="System.Data.SqlClient" ProviderManifestToken="2005" xmlns:store="http://schemas.microsoft.com/ado/2007/12/edm/EntityStoreSchemaGenerator" xmlns="http://schemas.microsoft.com/ado/2006/04/edm/ssdl">
<EntitySet Name="Barrier" EntityType="ClinetSODAModel.Store.Barrier" store:Type="Tables" store:Schema="dbo" store:Name="Barrier">
<DefiningQuery>
SELECT
[Barrier].[Barrier_CD] AS [Barrier_CD]
,[Barrier].[Description] AS [Description]
,[Barrier].[Last_Mod_Dt] AS [Last_Mod_Dt]
,[Barrier].[Last_Mod_ID] AS [Last_Mod_ID]
FROM [dbo].[Barrier] AS [Barrier]
</DefiningQuery>
</EntitySet
 

My first reaction was to put stored procedure call instead of the SELECT statement.

<?xml version="1.0" encoding="utf-8"?>
<edmx:Edmx Version="1.0" xmlns:edmx="http://schemas.microsoft.com/ado/2007/06/edmx">
<!– EF Runtime content –>
<edmx:Runtime>
<!– SSDL content –>
<edmx:StorageModels>
<Schema Namespace="ClinetSODAModel.Store" Alias="Self" Provider="System.Data.SqlClient" ProviderManifestToken="2005" xmlns:store="http://schemas.microsoft.com/ado/2007/12/edm/EntityStoreSchemaGenerator" xmlns="http://schemas.microsoft.com/ado/2006/04/edm/ssdl">
<EntitySet Name="Barrier" EntityType="ClinetSODAModel.Store.Barrier" store:Type="Tables" store:Schema="dbo" store:Name="Barrier">
<DefiningQuery>
EXEC [dbo].[sp_GetAllBarrier]
</DefiningQuery>
</EntitySet>

The T-SQL generated by EF run-time failed, but gave me a material to play with

SELECT
[GroupBy1].[A1] AS [C1]
FROM ( SELECT cast(1 as bit) AS X ) AS [SingleRowTable1]
LEFT OUTER JOIN (SELECT
    COUNT(cast(1 as bit)) AS [A1]
    FROM (
EXEC [dbo].[sp_GetAllBarrier]
) AS [Extent1] ) AS [GroupBy1] ON 1 = 1

 

Finally, I found a construct that would let me to use stored procedure.

<?xml version="1.0" encoding="utf-8"?>
<edmx:Edmx Version="1.0" xmlns:edmx="http://schemas.microsoft.com/ado/2007/06/edmx">
<!– EF Runtime content –>
<edmx:Runtime>
<!– SSDL content –>
<edmx:StorageModels>
<Schema Namespace="ClinetSODAModel.Store" Alias="Self" Provider="System.Data.SqlClient" ProviderManifestToken="2005" xmlns:store="http://schemas.microsoft.com/ado/2007/12/edm/EntityStoreSchemaGenerator" xmlns="http://schemas.microsoft.com/ado/2006/04/edm/ssdl">
<EntityContainer Name="ClinetSODAModelStoreContainer">
<EntitySet Name="Barrier" EntityType="ClinetSODAModel.Store.Barrier" store:Type="Tables" store:Schema="dbo" store:Name="Barrier">
<DefiningQuery>
SELECT * FROM OPENQUERY(LOCALSERVER, ‘EXEC SODA.dbo.sp_GetAllBarrier’)
</DefiningQuery>
</EntitySet>
 

Yes, it would require me to register local server

sp_addlinkedserver @server = ‘LOCALSERVER’, @srvproduct = ,
@provider = ‘SQLOLEDB’, @datasrc = @@servername

 

And the usage of full stored procedure name including DB name is not convenient, but step forward and buys some time before Microsoft issues new EF release.

Advertisements
Categories: Entity Framework
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: