Handling View Relationships in the Entity Framework

Question

We have a set of views (quite a few) that we're trying to implement in the Entity Framework with their relationships. These views have primary keys defined but for some reason when I create an Entity model for them I received the message:

The table/view 'vwFoo' does not have a primary key defined. The key has been inferred and the definition was created as a read-only table/view.

Besides making them read-only, which we don't want, the relationships aren't being preserved. Is there any way to get these to load into the model correctly?

The backend database is SQL Server 2005 but we also need to support 2000.

Any help would be appreciated,

UPDATE & SOLUTION

Here's an XSLT transform that worked successfully to turn two Views from our EDMX into tables. It has two issues: 1) adding bad xmlns="" attributes, and 2) not correctly setting UTF-8 encoding. I fixed both of these in a separate Perl script.

<?xml version="1.0" encoding="utf-8" ?>
<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
                xmlns:edmx="http://schemas.microsoft.com/ado/2007/06/edmx"
                xmlns:store="http://schemas.microsoft.com/ado/2007/12/edm/EntityStoreSchemaGenerator"
                xmlns:ssdl="http://schemas.microsoft.com/ado/2006/04/edm/ssdl"
                xmlns:edm="http://schemas.microsoft.com/ado/2006/04/edm"
                xmlns:cs="urn:schemas-microsoft-com:windows:storage:mapping:CS"
                exclude-result-prefixes="ssdl edm store cs" version="1.0">
    <xsl:output method="xml"/>

    <!-- Copy any nodes that aren't specifically transformed -->
    <xsl:template match="@* | node()">
        <xsl:copy>
            <xsl:apply-templates select="@* | node()"/>
        </xsl:copy>
    </xsl:template>

    <!-- Transform vwPerson -->
    <xsl:template match="edmx:Edmx/edmx:Runtime/edmx:StorageModels/ssdl:Schema/ssdl:EntityType[@Name='vwPerson']/ssdl:Key">
        <Key>
            <PropertyRef Name="fkPersonID" />
        </Key>
    </xsl:template>

    <xsl:template match="edmx:Edmx/edmx:Runtime/edmx:ConceptualModels/edm:Schema/edm:EntityType[@Name='vwPerson']/edm:Key">
        <Key>
            <PropertyRef Name="fkPersonID" />
        </Key>
    </xsl:template>

    <xsl:template match="edmx:Edmx/edmx:Runtime/edmx:StorageModels/ssdl:Schema/ssdl:EntityContainer/ssdl:EntitySet[@Name='vwPerson']">
        <EntitySet Name="vwPerson"
                   EntityType="DataMachineModel.Store.vwPerson"  
                   store:Type="Tables" 
                   Schema="dbo" />
    </xsl:template>

    <!-- Transform vwPersonAddress -->
    <xsl:template match="edmx:Edmx/edmx:Runtime/edmx:StorageModels/ssdl:Schema/ssdl:EntityType[@Name='vwPersonAddress']/ssdl:Key">
        <Key>
            <PropertyRef Name="PersonID" />
        </Key>
    </xsl:template>

    <xsl:template match="edmx:Edmx/edmx:Runtime/edmx:ConceptualModels/edm:Schema/edm:EntityType[@Name='vwPersonAddress']/edm:Key">
        <Key>
            <PropertyRef Name="PersonID" />
        </Key>
    </xsl:template>

    <xsl:template match="edmx:Edmx/edmx:Runtime/edmx:StorageModels/ssdl:Schema/ssdl:EntityContainer/ssdl:EntitySet[@Name='vwPersonAddress']">
        <EntitySet Name="vwPersonAddress"
                   EntityType="DataMachineModel.Store.vwPersonAddress"  
                   store:Type="Tables" 
                   Schema="dbo" />
    </xsl:template>

</xsl:stylesheet>

This does seem to work so far as making the views function like tables; however it doesn't preserve relationships between them. I tried adding the FK associations in the EDMX via the XSLT but had no success.

We've decided to make real tables from our views and migrate the view logic into a separate class that can do the filtering of the data we need.

Answer 1

Yes, the EF designer can't derive the PK of a view, so you have to help it out.