Entity framework with Oracle inserting parent with child

To insert data to two tables have parent and child relationship we will doing flow steps:

1. Create two tables:

Table CUSTOMER

-- Create table
create table CUSTOMER
(
CUSTOMER_ID   NUMBER(10) not null,
CUSTOMER_NAME NVARCHAR2(1024),
EMAIL         NVARCHAR2(512) not null,
PHONE         NVARCHAR2(100),
ADDRESS       NVARCHAR2(1000),
CUSTOMER_TYPE NVARCHAR2(100)
)
tablespace USERS
pctfree 10
initrans 1
maxtrans 255
storage
(
initial 64K
next 1M
minextents 1
maxextents unlimited
);
-- Create/Recreate primary, unique and foreign key constraints
alter table CUSTOMER
add constraint PK12 primary key (CUSTOMER_ID)
using index
tablespace USERS
pctfree 10
initrans 2
maxtrans 255
storage
(
initial 64K
next 1M
minextents 1
maxextents unlimited
);

Table INVOICE

-- Create table
create table INVOICE
(
INVOICE_ID       NUMBER(19) not null,
INVOICE_CODE     VARCHAR2(512),
INVOICE_STATUS   NUMBER(4),
ORDER_CODE       VARCHAR2(256),
PAY_VALUE        NUMBER(19),
PAY_DATE         TIMESTAMP(6),
PAYMENT_TYPE     VARCHAR2(256),
TRANSACTION_CODE VARCHAR2(256),
RECEIVE_URL      VARCHAR2(636),
RESPONSE_URL     VARCHAR2(512),
DESCRIPTION      VARCHAR2(512),
CREATED_BY       NUMBER(19),
CREATED_DATE     TIMESTAMP(6),
PROVIDER_ID      NUMBER(38) not null,
CUSTOMER_ID      NUMBER(10) not null
)
tablespace USERS
pctfree 10
initrans 1
maxtrans 255
storage
(
initial 64K
next 1M
minextents 1
maxextents unlimited
);
-- Create/Recreate primary, unique and foreign key constraints
alter table INVOICE
add constraint PK30 primary key (INVOICE_ID)
using index
tablespace USERS
pctfree 10
initrans 2
maxtrans 255
storage
(
initial 64K
next 1M
minextents 1
maxextents unlimited
);
alter table INVOICE
add constraint REFCUSTOMER4 foreign key (CUSTOMER_ID)
references CUSTOMER (CUSTOMER_ID);
alter table INVOICE
add constraint REFPROVIDER2 foreign key (PROVIDER_ID)
references PROVIDER (PROVIDER_ID);
-- Create/Recreate indexes
create index REF102 on INVOICE (PROVIDER_ID)
tablespace USERS
pctfree 10
initrans 2
maxtrans 255
storage
(
initial 64K
next 1M
minextents 1
maxextents unlimited
);
create index REF124 on INVOICE (CUSTOMER_ID)
tablespace USERS
pctfree 10
initrans 2
maxtrans 255
storage
(
initial 64K
next 1M
minextents 1
maxextents unlimited
);

2. Create sequences

create sequence CUSTOMER_SEQ
minvalue 1
maxvalue 9999999999999999999999999999
start with 0
increment by 1
cache 20;

create sequence INVOICE_SEQ
minvalue 1
maxvalue 9999999999999999999999999999
start with 0
increment by 1
cache 20;

3. Create Triggers

CREATE OR REPLACE TRIGGER CUSTOMER_INS_TRG
  BEFORE INSERT ON CUSTOMER  
  FOR EACH ROW
BEGIN
  IF(:NEW.CUSTOMER_ID IS NULL OR :NEW.CUSTOMER_ID < 0) THEN
      SELECT "CUSTOMER_SEQ".NEXTVAL INTO :NEW."CUSTOMER_ID" FROM DUAL;
  END IF;
END CUSTOMER_INS_TRG;

CREATE OR REPLACE TRIGGER INVOICE_INS_TRG
  BEFORE INSERT ON INVOICE  
  FOR EACH ROW
BEGIN
  IF(:NEW.INVOICE_ID IS NULL OR :NEW.INVOICE_ID < 0) THEN
     SELECT "INVOICE_SEQ".NEXTVAL INTO :NEW."INVOICE_ID" FROM DUAL;
  END IF;
END INVOICE_INS_TRG;

3. Create a Entity Data Model

image

4. Open .edmx file with the XML editor and look the section that begins with the flowing line:

<!-- SSDL content -->

Below should be an EntityType tag and in it is a definition of the database table. Make sure that the property for your ID column has StoreGeneratedPattern=”Identity” in it.

It is look like:

<EntityType Name="CUSTOMER">
	  <Key>
		<PropertyRef Name="CUSTOMER_ID" />
	  </Key>
	  <Property Name="CUSTOMER_ID" Type="number" StoreGeneratedPattern="Identity" Nullable="false" Precision="10" />
	  <Property Name="CUSTOMER_NAME" Type="nvarchar2" MaxLength="1024" />
	  <Property Name="EMAIL" Type="nvarchar2" Nullable="false" MaxLength="512" />
	  <Property Name="PHONE" Type="nvarchar2" MaxLength="100" />
	  <Property Name="ADDRESS" Type="nvarchar2" MaxLength="1000" />
	  <Property Name="CUSTOMER_TYPE" Type="nvarchar2" MaxLength="100" />
	</EntityType>

<EntityType Name="INVOICE">
	  <Key>
		<PropertyRef Name="INVOICE_ID" />
	  </Key>
	  <Property Name="INVOICE_ID" Type="number" StoreGeneratedPattern="Identity" Nullable="false" Precision="19" />
	  <Property Name="INVOICE_CODE" Type="varchar2" MaxLength="512" />
	  <Property Name="INVOICE_STATUS" Type="number" Precision="4" />
	  <Property Name="ORDER_CODE" Type="varchar2" MaxLength="256" />
	  <Property Name="PAY_VALUE" Type="number" Precision="19" />
	  <Property Name="PAY_DATE" Type="timestamp" />
	  <Property Name="PAYMENT_TYPE" Type="varchar2" MaxLength="256" />
	  <Property Name="TRANSACTION_CODE" Type="varchar2" MaxLength="256" />
	  <Property Name="RECEIVE_URL" Type="varchar2" MaxLength="636" />
	  <Property Name="RESPONSE_URL" Type="varchar2" MaxLength="512" />
	  <Property Name="DESCRIPTION" Type="varchar2" MaxLength="512" />
	  <Property Name="CREATED_BY" Type="number" Precision="19" />
	  <Property Name="CREATED_DATE" Type="timestamp" />
	  <Property Name="PROVIDER_ID" Type="number" Nullable="false" />
	  <Property Name="CUSTOMER_ID" Type="number" Nullable="false" Precision="10" />
	</EntityType>

5. In code behind

//Create Customer
CUSTOMER customer = new CUSTOMER {
	CUSTOMER_NAME = buyerDetail.NAME,
	ADDRESS = buyerDetail.ADDRESS_TEXT,
	PHONE = buyerDetail.PHONE,
	EMAIL =  "abc@xyz.com",
	CUSTOMER_TYPE = "P"
};

//Create invoice
INVOICE invoice = new INVOICE
{
	ORDER_CODE = orderCode,
	DESCRIPTION = description,
	PAY_VALUE = payValue,
	PROVIDER_ID = 1, 
	INVOICE_STATUS = 1, 
	CREATED_DATE = System.DateTime.Now,
	CUSTOMER = customer
};

// insert and commit
unitOfWork.Invoice.Insert(invoice);
unitOfWork.Commit();

Happy coding!

Advertisements
This entry was posted in Entity framework. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s