Archive

Archive for the ‘Entity Framework’ Category

Using Stored Procedure with ADO.NET Entity framework

February 4, 2009 Leave a comment

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.

Categories: Entity Framework