Entity Framework 無法匯入 Oracle View？！

Entity Framework 無法匯入 Oracle View？

同事反應在使用 Entity Framework EDMX 更新 Model 時一直無法將 View 加入(未看先猜是 primary key 所造成的問題，果不其然真的猜中XD)

DB View 的 priamry key 問題在 Entity Framework 使用上很常遇到，只是以往搭配的 DB 都是 SQL Server，想不到這次改成 Oracle，過去的做法就行不通了，就來看看 Oracle 該如何解決吧

錯誤訊息

edmx 選擇加入 View 但 designer 未出現
編譯不會出現錯誤訊息
使用 xml 編輯器打開 edmx 才會看到錯誤
```
Errors Found During Generation:
warning 6013: The table/view 'TESTIDENTITY.ORGUNIT' does not have a primary key defined and no valid primary key could be inferred. This table/view has been excluded. To use the entity, you will need to review your schema, add the correct keys, and uncomment it.
```
- SQL Server 的解決方式可以參考保哥的文章解決 SQL Server 檢視表 (Views) 無法匯入 EDMX 的問題，大意就是使用 SQL Server 的 isnull 語法調整 View，讓 Entity Framework 可以推斷出 primary key 即可
- 我嘗試透過使用 Oracle 的 nvl 及 COALESCE 語法(效果等同 SQL Server 的 isnull 語法)都無法讓 Entity Framework 順利推斷出 primary key

只有特定 View 無法使用？

同個 db 中其他 View 可以正常匯入

無法匯入的類型：使用 union all 語法

透過 table 的欄位(parentid) 來儲存父層資料

10bu

11budata

使用 cte 遞迴取出資料

WITH allunit ( id,parentid,Name,Level1)
AS
(select id,parentid,Name, 0 AS Level1
from bu 
where Parentid=0
union all
select a.id,a.parentid,a.Name, Level1 + 1
from bu  a
INNER JOIN allunit b
ON b.id = a.parentid
where  a.Parentid<>0 )
SELECT *
FROM allunit

12viewdata

原因尚待請教專業 DBA，可以先參考 Is there a way to explicitely have a not-null column in a view及UNION of non-nullable columns is nullable

解決方式

手動調整 edmx 內容，以下紀錄調整方式

SSDL

取消 EntityType 註解

<EntityType Name="ORGUNIT">
    <Property Name="ID" Type="number" Precision="10" Scale="0" />
    <Property Name="PARENTID" Type="number" Precision="10" Scale="0" />
    <Property Name="NAME" Type="varchar2" MaxLength="20" />
    <Property Name="LEVEL1" Type="number" Precision="38" Scale="0" />
</EntityType>

加入 key

<Key>
    <PropertyRef Name="ID" />
</Key>

將 key 加上 Nullable=“false”
```
Nullable="false"
```

將 View 加至 EntityContainer 中，並指定 store type 為 Views

<EntitySet Name="ORGUNIT" EntityType="Self.ORGUNIT" Schema="TESTIDENTITY" store:Type="Views" />

完整內容

<EntityType Name="ORGUNIT">
    <Key>
    <PropertyRef Name="ID" />
    </Key>
    <Property Name="ID" Type="number" Precision="10" Scale="0" Nullable="false" />
    <Property Name="PARENTID" Type="number" Precision="10" Scale="0" />
    <Property Name="NAME" Type="varchar2" MaxLength="20" />
    <Property Name="LEVEL1" Type="number" Precision="38" Scale="0" />
</EntityType>
<EntityContainer Name="ModelStoreContainer">
    <EntitySet Name="ORGUNIT" EntityType="Self.ORGUNIT" Schema="TESTIDENTITY" store:Type="Views" />
</EntityContainer>

5SSDL

CSDL

這個可以在 model explorer 中加入，可以透過 xml 加入

使用 xml

將 View 加至 EntityContainer 中

<EntitySet Name="OrgUnit" EntityType="Model.OrgUnit" />

加入 EntityType 及屬性定義

<EntityType Name="OrgUnit">
<Key>
    <PropertyRef Name="ID" />
</Key>
<Property Name="ID" Type="Int32" Nullable="false" />
<Property Name="NAME" Type="String" Nullable="false" />
<Property Name="PARENTID" Type="Int32" Nullable="false" />
<Property Name="LEVEL1" Type="Int32" Nullable="false" />
</EntityType>

6CSDL