WCF RIA Services – Manually Adding a View to existing Entity Framework

When the real-world automatic stuff doesn’t work… you can add entities manually

Complex Views – Appear to get skipped upon “Update Schema from Database”

I’ve got one database view that is more complex than the visual editor seems to be capable of handling.  The “View” painter in Visual Studio chokes on it and I am pretty sure this is why EntityFramework doesn’t know what to do with it.

I wanted to include the view in my Silverlight 4, WCF RIA Services application but no matter what I tried, I couldn’t get the view to go into the Entity Framework Model.  Here is the definition for my SQL Server 2008 database view… if anyone knows how I could write this in a better way or one that Visual Studio likes please let me know.

with AllCategories
as (
	(select
		p.category_id,
		p.parent_id,
		p.description as parent_desc,
		p.description,
		0 as [level],
		p.url,
		p.sort_order,
		'N' as virtual_category
	from ld_category p
	where p.parent_id = 0)
		union all
	(select
		p.category_id,
		p.parent_id,
		(select c5.description from ld_category as c5 where c5.category_id = p.parent_id) as parent_desc,
		p.description,
		[level] + 1 ,
		p.url,
		p.sort_order,
		'N' as virtual_category
	from	ld_category p
			inner join AllCategories as c on p.parent_id = c.category_id and c.virtual_category = 'N')
		union all
    (SELECT
		q.category_id,
		q.parent_id,
		(select c6.description from ld_category as c6 where c6.category_id = q.parent_id) as parent_desc,
		(select c4.description from ld_category c4 where c4.category_id = q.category_id) as description,
		[level] + 1,
		(select c4.url from ld_category c4 where c4.category_id = q.category_id) as url,
		q.sort_order,
		'Y' as virtual_category
     FROM ld_category_clone AS q
		  inner join AllCategories as c ON q.parent_id = c.category_id and c.virtual_category = 'N'))
select  c.category_id, c.parent_id, c.parent_desc, c.description, level, c.sort_order, c.url,  c.virtual_category
from AllCategories c

Adding my Database View to Entity Framework Manually

Right click the Entity Framework Class and open with an XML editor.

Notice the three sections ( StorageModels, ConceptualModels, and Conceptual to Storage Mapping )  You need to add code to all three sections.

Entity Framework (Storage, Conceptual & Mapping)

First add your new entity to the StorageModels section.

<EntitySet Name="v_category" EntityType="linkdirectoryModel.Store.v_category" store:Type="Tables" Schema="dbo" />

<EntityType Name="v_category">
  <Key>
   <PropertyRef Name="category_id" />
   <PropertyRef Name="parent_id" />
  </Key>
  <Property Name="category_id" Type="int" Nullable="false"/>
  <Property Name="parent_id" Type="int" Nullable="false"/>
  <Property Name="parent_desc" Type="nvarchar" MaxLength="100" />
  <Property Name="description" Type="nvarchar" MaxLength="100" />
  <Property Name="level" Type="int" />
  <Property Name="sort_order" Type="int" />
  <Property Name="url" Type="nvarchar" MaxLength="200" />
  <Property Name="virtual_category" Type="varchar" MaxLength="1" />
</EntityType>

Second Add to the ConceptualModels section

note: my pluralization is consistent with others but in bad English form. 😉

<EntitySet Name="v_categorys" EntityType="linkdirectoryModel.v_category" />

<EntityType Name="v_category">
  <Key>
   <PropertyRef Name="category_id" />
   <PropertyRef Name="parent_id" />
  </Key>
  <Property Name="category_id" Type="Int32" Nullable="false" />
  <Property Name="parent_id" Type="Int32" Nullable="false"  />
  <Property Name="parent_desc" Type="String" MaxLength="100" />
  <Property Name="description" Type="String" MaxLength="100" />
  <Property Name="level" Type="Int32" />
  <Property Name="sort_order" Type="Int32" />
  <Property Name="url" Type="String" MaxLength="200" />
  <Property Name="virtual_category" Type="String" MaxLength="1" />
</EntityType>

Last Add Mappings to Runtime Mappings Section

<EntitySetMapping Name="v_categorys">
<EntityTypeMapping TypeName="linkdirectoryModel.v_category">
<MappingFragment StoreEntitySet="v_category">
	<ScalarProperty Name="category_id" ColumnName="category_id" />
	<ScalarProperty Name="parent_id" ColumnName="parent_id" />
	<ScalarProperty Name="parent_desc" ColumnName="parent_desc" />
	<ScalarProperty Name="description" ColumnName="description" />
	<ScalarProperty Name="level" ColumnName="level" />
	<ScalarProperty Name="sort_order" ColumnName="sort_order" />
	<ScalarProperty Name="url" ColumnName="url" />
	<ScalarProperty Name="virtual_category" ColumnName="virtual_category" />
</MappingFragment>
</EntityTypeMapping>
</EntitySetMapping>

You need to make sure all errors are resolved before you can open again in “visual” mode.

Sincerely,
Rich (aka DisplacedGuy)