Page 1 of 1

Ignore columns in SSDL

Posted: Thu 22 Aug 2013 17:02
by jacksparrow
Hello,

We use dotconnect for Oracle and have three schemas (Master, Staging and Prod) in our database. We have three SSDL files, one corresponding to each schema. Until now all the schemas had the same table structure, but now we have less number of columns in one of the table in one schema. We have one Data Model for all three schemas and use the different SSDL files when querying the corresponding schema. Is there a way to ignore columns in the SSDL file (or elsewhere), so we can continue using a single Data Model?

Thanks.

Re: Ignore columns in SSDL

Posted: Thu 29 Aug 2013 15:31
by Shalex
Assuming there is the DEPT table:

Code: Select all

CREATE TABLE DEPT (
  DEPTNO NUMBER(4) CONSTRAINT PK_DEPT PRIMARY KEY,
  DNAME VARCHAR2(14) ,
  LOC VARCHAR2(13)
);
And we want to ignore the LOC column. For this, we should employ defining query and set StoreGeneratedPattern="Identity" for LOC (it can be done in design time with Entity Developer):

Code: Select all

  <edmx:Runtime>
    <edmx:StorageModels>
...
        <EntityContainer Name="MyEntitiesStoreContainer">
          <EntitySet Name="Entity1s" EntityType="My.Store.Entity1" store:Type="Tables">
            <DefiningQuery>select deptno, dname, 'NEW YORK' as loc from dept</DefiningQuery>
          </EntitySet>
        </EntityContainer>
        <EntityType Name="Entity1">
          <Key>
            <PropertyRef Name="DEPTNO" />
          </Key>
          <Property Name="DEPTNO" Type="int" Nullable="false" />
          <Property Name="DNAME" Type="VARCHAR2" MaxLength="14" />
          <Property Name="LOC" Type="CHAR" MaxLength="8" StoreGeneratedPattern="Identity" />
        </EntityType>
      </Schema>
    </edmx:StorageModels>
...
  </edmx:Edmx>
After this, the code:

Code: Select all

    using (MyEntities context = new MyEntities()) {
        Entity1 e = new Entity1() { DEPTNO=1, DNAME="a", LOC="a"};
        context.Entity1s.AddObject(e);
        context.SaveChanges();
    }
generates the following SQL:

Code: Select all

DECLARE
  updatedRowid ROWID;
BEGIN
INSERT INTO (select deptno, dname, 'NEW YORK' as loc from dept)(DEPTNO, DNAME)
VALUES (:p0, :p1)
RETURNING ROWID INTO updatedRowid;
OPEN :outParameter FOR SELECT LOC FROM (select deptno, dname, 'NEW YORK' as loc from dept) WHERE ROWID = updatedRowid;
END;