| Yuriy님의 프로필ygizhitsa사진블로그리스트 |  도구  도움말 |
|
2월 4일 Using Stored Procedure with ADO.NET Entity frameworkI 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. 트랙백이 블로그의 트랙백 URL은 다음과 같습니다. http://ygizhitsa.spaces.live.com/blog/cns!8A7B4991A271531A!203.trak 이 블로그를 참조하는 웹 로그
|
|
|
