CODEWITHSUNDEEP
sql-serverweb-servicesentity-framework-4
CodeDigger
CodeDigger

Reputation: 217

EF4 how to switch schema (e.g. dbo -> custId) so that identical tables are stored under multiple schemas

I would like to design an application that serves a number of customers
I would like to have data for different customers in the same database but each customer's data in his/her own schema
so:

Questions:

  1. would this scenario work with EF4?
  2. have 1 webservice that services all customers?
  3. point EF4 per customer information to correct schema?
  4. how would I switch between schemas during web service requests?

Upvotes: 2

Views: 484

Answers (3)

Ladislav Mrnka
Ladislav Mrnka

Reputation: 364359

This is possible but it is much harder then it looks like. EDMX file consists of three parts which define mapping metadata: SSDL (db description), CSDL (entities description), MSL (mapping between SSDL and CSDL). Schema information is part of SSDL. If you want to access different schema you must switch the whole SSDL document = you need new entity connection or connection string. You must also create SSDL per customer.

Here is example of SSDL declaration for single entity (you can see schema defineded at edmx:model/Schema/EntityContainer/EntitySet/@Schema):

<!-- SSDL content -->
<edmx:StorageModels>
  <Schema Namespace="Model.Store" Alias="Self" Provider="System.Data.SqlClient"
          ProviderManifestToken="2008" 
          xmlns:store="http://schemas.microsoft.com/ado/2007/12/edm/EntityStoreSchemaGenerator" 
          xmlns="http://schemas.microsoft.com/ado/2009/02/edm/ssdl">
    <EntityContainer Name="ModelStoreContainer">
      <EntitySet Name="TestEntitySet" EntityType="Model.Store.TestEntitySet" 
                 store:Type="Tables" Schema="dbo" />
    </EntityContainer>
    <EntityType Name="TestEntitySet">
      <Key>
        <PropertyRef Name="Id" />
      </Key>
      <Property Name="Id" Type="int" StoreGeneratedPattern="Identity" 
                Nullable="false" />
    </EntityType>
  </Schema>
</edmx:StorageModels>
  1. Yes it would work but as I described it is not a nice solution.
  2. Yes you can have one service which serves all customers but you must design correct authentication to connect to correct set of tables. Your company can have very big legal problems if you work with sensitive data and your customer will accidentally (because of bug) access data of another customer.
  3. Problem of authentication and mapping authenticated users to correct set of tables - this is completely outside of EF.
  4. You will use either multiple connection strings (one per customer) or you will build entity connection dynamically.

The correct approach here is creating new database per customer. It is better maintainable and setting security for such scenario is much easier.

Upvotes: 1

Wyatt Barnett
Wyatt Barnett

Reputation: 15663

Haven't tried anything like this, but it could be easier with FluentNHibernate -- you can specify schema affinity in code there for sure.

Could also tackle this from the configuration direction -- SQL users can have a default schema set so you can reference everything schema-less and then it will line up in production presuming the configuration is correct.

Upvotes: 0

jonchicoine
jonchicoine

Reputation: 464

I can't get my mind around your question. I can't understand why you would need multiple schemas...

It sounds to me like you just need to add a CustId foreign key field to the top level tables so you can used joins to filter data by customer using the same schema...

Perhaps your question needs more detail...

Upvotes: 0

Related Questions