Entity Framework EDMX Model & Stored Procs

Question

I am an Entity Framework newbie and am wondering if I am able to use it in the way I would like to. I am using Visual Studio 2010 and .NET 4.

I have a Content Management system which stores all the data in an xml field of a table. The table - cmsContent - contains only two fields, id and XmlNode

I want to create stored procs that query the XML field to bring back meaningful data, Product data in this case. e.g...

SELECT C.id AS [Id], C.xmlNode.value('(/node/data[@alias = ''ProdName''])[1]', 'NVARCHAR(1024)') AS ProductName, C.xmlNode.value('(/node/data[@alias = ''ProdDesc''])[1]', 'NVARCHAR(1024)') AS ProductDescription FROM cmsContent C WHERE C.xNode.query('data(/node[1]/@nodeTypeAlias)').value('.', 'VARCHAR(30)') = 'Product'

I then want to map the output of these stored procs to my entity classes via a the EDMX enity diagram. So I would want to map the above query to a class called Products. The products class will be read only, there is no need to upate these object as this is doen via the CMS.

Would this be posssible to use the entiry framework in this way? If so how, as I can’t get the Stored Procs to generate the required classes in the EDMX diagram as this doesn't happen when i use the wizard?

Answer 1

I think you have to create a complex type, and map the SP to this. See this MSDN article.

Answer 2

If it's readonly, then you can provide a DefiningQuery for the EntitySet in the Storage Model. This essentially acts as a read-only view of the store and you can define the result shape however you want. You can then either map it to an entity or a complex type (tho a complex type may be easier if it's read-only.)

<Schema ...>
  <EntityContainer ...>
    <EntitySet Name="blah" EntityType="BlahModel.Store.blah">
      <DefiningQuery>
        select ... from blah
      </DefiningQuery>
    </EntitySet>
  </EntityContainer>
  ...
</Schema>