CODEWITHSUNDEEP
c#sqlasp.netentity-frameworkentity-framework-6
Anyname Donotcare
Anyname Donotcare

Reputation: 11423

How to handle self join in EntityDataSource?

I wonder how to handle self join in EntityDataSource .

IF I have i query like this :

SELECT b.degree_name ,c.degree_name as degree_next
FROM EMPDEGPROM a INNER JOIN  DEGREEWORK b
ON a.degree_code=b.degree_code
INNER JOIN DEGREEWORK c
ON a.next_degree_code =c.degree_code
WHERE a.emp_num=6777

How to use this data source as EntityDataSource.?

I try to do this but i can't get degree_next

<asp:EntityDataSource ID="EmpPromotionsDS" runat="server"
            ConnectionString="name=CTX" DefaultContainerName="CTX" EnableFlattening="False"
            EntitySetName="EMPDEGPROMs" EntityTypeFilter="EMPDEGPROM" Where="it.EMP_NUM =@emp_num"  Include="DEGREEWORK" >
            <WhereParameters>
                <asp:SessionParameter Name="emp_num" SessionField="emp_num" DbType="Int32" />
            </WhereParameters>

Upvotes: 2

Views: 205

Answers (2)

Jonathan Allen
Jonathan Allen

Reputation: 70337

Create a view.

CREATE VIEW XXX AS
SELECT a.emp_num, b.degree_name ,c.degree_name as degree_next
FROM EMPDEGPROM a INNER JOIN  DEGREEWORK b
ON a.degree_code=b.degree_code
INNER JOIN DEGREEWORK c
ON a.next_degree_code =c.degree_code

EF has no problem reading from views.

Upvotes: 2

LeBaptiste
LeBaptiste

Reputation: 1186

  • If you want only readonly, you could drop the EntitySetName, EntityTypeFilter, Where and Include properties and use instead use the CommandText property.

  • If you want to use directly your entities, make sure you have a navigation property with a self reference and precise it in your include property.

<asp:EntityDataSource ID="EmpPromotionsDS" runat="server"
            ConnectionString="name=CTX" 
            DefaultContainerName="CTX" 
            EnableFlattening="False"
            CommandText= SELECT b.degree_name ,c.degree_name as degree_next
                         FROM EMPDEGPROM a INNER JOIN  DEGREEWORK b
                         ON a.degree_code=b.degree_code
                         INNER JOIN DEGREEWORK c
                         ON a.next_degree_code =c.degree_code
                         WHERE a.emp_num=@EmployeeId>
<CommandParameters>
  <asp:ControlParameter Name="EmployeeId" ControlID="EmployeeIdTextBox" Type="Int32"/>
</CommandParameters>

Upvotes: 1

Related Questions