Multiple database schemas in EF model first

Question

I want use model first approach to create database with Entity Framework. I also want use multiple database schemas. Is there any possibility to create additional database schemas (say "security") along with default "dbo"?

I also wondered why after executing "Generate Database From Model" command, my EntitySets Schema attributes in SSDL part of .edmx file is reset back to "dbo".

<?xml version="1.0" encoding="utf-8"?>
<edmx:Edmx Version="3.0" mlns:edmx="http://schemas.microsoft.com/ado/2009/11/edmx">
  <!-- EF Runtime content -->
  <edmx:Runtime>
    <!-- SSDL content -->
    <edmx:StorageModels>
    <Schema Namespace="ThewiseModel.Store" Alias="Self" provider="System.Data.SqlClient" ProviderManifestToken="2012" xmlns:store="http://schemas.microsoft.com/ado/2007/12/edm/EntityStoreSchemaGenerator" xmlns="http://schemas.microsoft.com/ado/2009/11/edm/ssdl">
  <EntityContainer Name="ThewiseModelStoreContainer">
    <EntitySet Name="PermissionTypes" EntityType="ThewiseModel.Store.PermissionTypes" store:Type="Tables" Schema="Security" />
    <EntitySet Name="PermissionValues" EntityType="ThewiseModel.Store.PermissionValues" store:Type="Tables" Schema="Security" />
    <EntitySet Name="Roles" EntityType="ThewiseModel.Store.Roles" store:Type="Tables" Schema="Security" />
    <EntitySet Name="Users" EntityType="ThewiseModel.Store.Users" store:Type="Tables" Schema="Security" />
    <EntitySet Name="Memberships" EntityType="ThewiseModel.Store.Memberships" store:Type="Tables" Schema="Security" />
...

Not trying to troll, but EDMX and EF are an utter mess. Move away if you can. — Andomar, Aug 30 '14 at 18:30

user1454265 · Answer 1 · 2014-12-29T22:03:15.987

As far as I can figure out, it's not possible in model-first using a single EDMX. The EDMX designer only supports a single "Database Schema Name" field for the whole model in the Properties window.

As you've found, you can edit the EDMX manually to hack the schema names in there. However, when you generate DDL from that model (right-click, "Generate Database from Model") it seems to only partially use the right schema names. The script ends up using the schema names in some sections, but not in others:

-- --------------------------------------------------
-- Dropping existing tables
-- --------------------------------------------------

IF OBJECT_ID(N'[yourSchema].[YourTable]', 'U') IS NOT NULL
    DROP TABLE [yourSchema].[YourTable];

-- --------------------------------------------------
-- Creating all tables
-- --------------------------------------------------

CREATE TABLE [defaultSchema].[YourTable] ( ... );

There's also an issue open on CodePlex that seems to support this conclusion: http://entityframework.codeplex.com/workitem/268

There might be a way to accomplish multiple-schema support by modifying the T4 template that generates the DDL, but I haven't researched this. The default file is "SSDLToSQL10.tt" found (on my machine) at C:\Program Files (x86)\Microsoft Visual Studio 11.0\Common7\IDE\Extensions\Microsoft\Entity Framework Tools\DBGen.

(Tested on VS2012 Premium with EF 6.1.1.)

Multiple database schemas in EF model first

1 Answers1