In this example, we will create Ed-Fi Extensions for a student transcript. This example Extension covers data points such as the postsecondary institution that the student is attending, special education graduation status and other data points. When complete, the extended entities and additional elements will be exposed via the Ed-Fi ODS / API.

It is assumed that the Ed-Fi ODS / API has been successfully downloaded and is running as in a local environment per the instructions in the Getting Started documentation.

The steps can be summarized as:

Each step is outlined in detail, below.

Step 1. Author C# Extension Files

Set Up the Project Template

Open a PowerShell instance and navigate to the location of the Extension templates (i.e., <source directory>\Ed-Fi-ODS\Utilities\VisualStudioProjectTemplates\Ed-Fi-Extension-Templates).
1. Execute the PowerShell script InitTemplates.ps1 by executing the command:
  .\InitTemplates.ps1
2. When prompted, enter Extension2. This value will been appended to the project base name when importing the templates into Visual Studio.
Determine the location of the Visual Studio ProjectTemplates folder on your machine as follows:
- Open Visual Studio and go to Tools > Options > Projects and Solutions.
- Get the value in the "User project templates locations". (The default location is C:\Users\[User]\Documents\Visual Studio 2015\Templates\ProjectTemplates).
Copy the following folders (located under ...\Ed-Fi_Extension-Templates) to the Visual Studio Project Templates folder (determined in 2 above).
- Ed-Fi-Extension-Assembly-Template
- Ed-Fi-Extension-SemanticModel-Assembly-Template
Verify the Ed-Fi Extension Templates project type has been registered, by selecting File > New > Project and navigating to Installed-Templates-Visual C#.

Install the New Extension Projects

To add a project to your Ed-Fi-Ods Visual Studio Solution, right-click on the Ed-Fi Extensions Folder. Select Add > New Project.
Select the Ed-Fi Extension Assembly Template option. In the Name: field enter EdFi.Ods.Standard.Extensions.Extension2 and click OK.
Add another project to the Ed-Fi Extensions folder. This time, select the Ed-Fi Extension Semantic Model Assembly Template option. In the Name: field enter EdFi.Ods.Standard.SemanticModels.Extensions.Extension2 and click OK.

Edit the Extension Projects

Locate the DomainMetadata-Extension.xml file within the EdFi.Ods.Standard.SemanticModels.Extensions.Extension2\SupportingArtifacts\Metadata folder. This file defines the extensions that exist for the new project. Example values have been provided for convenience, and need to be modified as follows:

Replace schema="{Physical Schema Value}" to match the physical database schema (in this example, schema="extension2").

Replace the sample aggregate metadata to represent StudentTranscript:

DomainMetadata-Extension.xml

<AggregateExtensions logicalSchema="Extension2" schema="extension2">
    <Aggregate root="InstitutionControlDescriptor">
        <Entity table="InstitutionControlDescriptor" isA="Descriptor"/>
    </Aggregate>
    <Aggregate root="InstitutionLevelDescriptor">
        <Entity table="InstitutionLevelDescriptor" isA="Descriptor"/>
    </Aggregate>
    <Aggregate root="SpecialEducationGraduationStatusDescriptor">
        <Entity table="SpecialEducationGraduationStatusDescriptor" isA="Descriptor"/>
    </Aggregate>
    <Aggregate root="SubmissionCertificationDescriptor">
        <Entity table="SubmissionCertificationDescriptor" isA="Descriptor"/>
    </Aggregate>
    <Aggregate root="PostSecondaryOrganization">
        <Entity table="PostSecondaryOrganization"/>
    </Aggregate>
</AggregateExtensions>

Step 2. Integrate Extension into the Solution

To integrate the extension into the solution, perform the following tasks:

Locate the EdFi.Ods.WebApi project, within the "Entry Points" folder. Right-click, select Add > Reference..., then select the EdFi.Ods.Standard.Extensions.Extension2 and EdFi.Ods.Standard.SemanticModels.Extensions.Extension2 projects.
Open the EdFi.Ods.WebApi.Startup.ConfigurationSpecificSandboxStartup.cs file
1. Add a line to include the Extension2 assembly:
  EdFi.Ods.WebApi.Startup.ConfigurationSpecificSandboxStartup.cs
```
using EdFi.Ods.Standard.Extensions.Extension2;
```
2. Locate the EnsureAssembliesLoaded method in the same file and add a line for the new extension assembly marker to ensure it is loaded at runtime.
  EdFi.Ods.WebApi.Startup.ConfigurationSpecificSandboxStartup.cs
```
AssemblyLoader.EnsureLoaded<Marker_EdFi_Ods_Standard_Extensions_Extension2>();
```
Right-click the solution and select Project Dependencies. Select EdFi.Ods.Admin.Models in the project dropdown and add a dependency on the EdFi.Ods.Standard.SemanticModels.Extensions.Extension2 project.

Step 3. Author Database Schema Extensions

First, create an extension SQL script called 1003-EdFi_Ods_Extension2.sql and place it in the C:\Ed-Fi-ODS-Implementation\Database\Structure\EdFi folder. This script defines the database schema for the extension.

Next, author the database schema extensions in the 1003-EdFi_Ods_Extension2.sql file. The SQL script is downloadable via the link on this page, or you can use the source below:

1003-EdFi_Ods_Extension2.sql

--------------------------------------------------
--  Extension Schema
--------------------------------------------------
 
IF NOT EXISTS (SELECT 1 FROM sys.schemas WHERE name = 'extension2')
BEGIN
    EXEC('CREATE SCHEMA [extension2] AUTHORIZATION dbo')
END
GO
 
--------------------------------------------------
--  InstitutionControlDescriptor
--------------------------------------------------
 
CREATE TABLE [extension2].[InstitutionControlDescriptor] (
    [InstitutionControlDescriptorId] [INT] NOT NULL,
    CONSTRAINT [PK_InstitutionControlDescriptor] PRIMARY KEY CLUSTERED (
        [InstitutionControlDescriptorId] ASC
    )
)
GO
 
ALTER TABLE [extension2].[InstitutionControlDescriptor] WITH CHECK ADD CONSTRAINT [FK_InstitutionControlDescriptor_Descriptor]
FOREIGN KEY ([InstitutionControlDescriptorId])
REFERENCES [edfi].[Descriptor] ([DescriptorId])
ON DELETE CASCADE
GO
 
--------------------------------------------------
--  InstitutionLevelDescriptor
--------------------------------------------------
 
CREATE TABLE [extension2].[InstitutionLevelDescriptor] (
    [InstitutionLevelDescriptorId] [INT] NOT NULL,
    CONSTRAINT [PK_InstitutionLevelDescriptor] PRIMARY KEY CLUSTERED (
        [InstitutionLevelDescriptorId] ASC
    )
)
GO
 
ALTER TABLE [extension2].[InstitutionLevelDescriptor] WITH CHECK ADD CONSTRAINT [FK_InstitutionLevelDescriptor_Descriptor]
FOREIGN KEY ([InstitutionLevelDescriptorId])
REFERENCES [edfi].[Descriptor] ([DescriptorId])
ON DELETE CASCADE
GO
 
--------------------------------------------------
--  SpecialEducationGraduationStatusDescriptor
--------------------------------------------------
 
CREATE TABLE [extension2].[SpecialEducationGraduationStatusDescriptor] (
    [SpecialEducationGraduationStatusDescriptorId] [INT] NOT NULL,
    CONSTRAINT [PK_SpecialEducationGraduationStatusDescriptor] PRIMARY KEY CLUSTERED (
        [SpecialEducationGraduationStatusDescriptorId] ASC
    )
)
GO
 
ALTER TABLE [extension2].[SpecialEducationGraduationStatusDescriptor] WITH CHECK ADD CONSTRAINT [FK_SpecialEducationGraduationStatusDescriptor_Descriptor]
FOREIGN KEY ([SpecialEducationGraduationStatusDescriptorId])
REFERENCES [edfi].[Descriptor] ([DescriptorId])
ON DELETE CASCADE
GO
 
--------------------------------------------------
--  SubmissionCertificationDescriptor
--------------------------------------------------
 
CREATE TABLE [extension2].[SubmissionCertificationDescriptor] (
    [SubmissionCertificationDescriptorId] [INT] NOT NULL,
    CONSTRAINT [PK_SubmissionCertificationDescriptor] PRIMARY KEY CLUSTERED (
        [SubmissionCertificationDescriptorId] ASC
    )
)
GO
 
ALTER TABLE [extension2].[SubmissionCertificationDescriptor] WITH CHECK ADD CONSTRAINT [FK_SubmissionCertificationDescriptor_Descriptor]
FOREIGN KEY ([SubmissionCertificationDescriptorId])
REFERENCES [edfi].[Descriptor] ([DescriptorId])
ON DELETE CASCADE
GO
 
--------------------------------------------------
--  PostSecondaryOrganization
--------------------------------------------------
 
CREATE TABLE [extension2].[PostSecondaryOrganization] (
    [NameOfInstitution] [NVARCHAR](75) NOT NULL,
    [InstitutionLevelDescriptorId] [INT] NOT NULL,
    [InstitutionControlDescriptorId] [INT] NOT NULL,
    [AcceptanceIndicator] [BIT] NOT NULL,
    [Id] [UNIQUEIDENTIFIER] NOT NULL,
    [LastModifiedDate] [DATETIME] NOT NULL,
    [CreateDate] [DATETIME] NOT NULL,
    CONSTRAINT [PK_PostSecondaryOrganization] PRIMARY KEY CLUSTERED (
        [NameOfInstitution] ASC
    )
)
GO
 
ALTER TABLE [extension2].[PostSecondaryOrganization] ADD CONSTRAINT [PostSecondaryOrganization_DF_Id] DEFAULT (newid()) FOR [Id]
ALTER TABLE [extension2].[PostSecondaryOrganization] ADD CONSTRAINT [PostSecondaryOrganization_DF_LastModifiedDate] DEFAULT (getdate()) FOR [LastModifiedDate]
ALTER TABLE [extension2].[PostSecondaryOrganization] ADD CONSTRAINT [PostSecondaryOrganization_CreateDate] DEFAULT (getdate()) FOR [CreateDate]
GO
 
ALTER TABLE [extension2].[PostSecondaryOrganization] ADD CONSTRAINT [FK_PostSecondaryOrganization_InstitutionLevelDescriptorId]
FOREIGN KEY ([InstitutionLevelDescriptorId])
REFERENCES [extension2].[InstitutionLevelDescriptor] ([InstitutionLevelDescriptorId])
GO
 
ALTER TABLE [extension2].[PostSecondaryOrganization] ADD CONSTRAINT [FK_PostSecondaryOrganization_InstitutionControlDescriptor]
FOREIGN KEY ([InstitutionControlDescriptorId])
REFERENCES [extension2].[InstitutionControlDescriptor] ([InstitutionControlDescriptorId])
GO
 
--------------------------------------------------
--  StudentAcademicRecordExtension
--------------------------------------------------
 
CREATE TABLE [extension2].[StudentAcademicRecordExtension] (
    [StudentUSI] [INT] NOT NULL,
    [EducationOrganizationId] [INT] NOT NULL,
    [SchoolYear] [SMALLINT] NOT NULL,
    [TermDescriptorId] [INT] NOT NULL,
    [NameOfInstitution] [NVARCHAR](75) NOT NULL,
    [SubmissionCertificationDescriptorId] [INT] NOT NULL,
    CONSTRAINT [PK_StudentAcademicRecordExtension] PRIMARY KEY CLUSTERED (
        [StudentUSI],
        [EducationOrganizationId],
        [SchoolYear],
        [TermDescriptorId]
    )
)
GO
 
ALTER TABLE [extension2].[StudentAcademicRecordExtension] ADD CONSTRAINT [FK_StudentAcademicRecordExtension_NameOfInstitution]
FOREIGN KEY ([NameOfInstitution])
REFERENCES [extension2].[PostSecondaryOrganization] ([NameOfInstitution])
GO
 
ALTER TABLE [extension2].[StudentAcademicRecordExtension] ADD CONSTRAINT [FK_StudentAcademicRecordExtension_SubmissionCertificationDescriptorId]
FOREIGN KEY ([SubmissionCertificationDescriptorId])
REFERENCES [extension2].[SubmissionCertificationDescriptor] ([SubmissionCertificationDescriptorId])
GO
 
ALTER TABLE [extension2].[StudentAcademicRecordExtension] ADD CONSTRAINT [FK_StudentAcademicRecordExtension_StudentAcademicRecord]
FOREIGN KEY ([EducationOrganizationId], [SchoolYear], [StudentUSI], [TermDescriptorId])
REFERENCES [edfi].[StudentAcademicRecord]([EducationOrganizationId], [SchoolYear], [StudentUSI], [TermDescriptorId])
GO
 
--------------------------------------------------
--  StudentAcademicRecordClassRankingExtension
--------------------------------------------------
 
CREATE TABLE [extension2].[StudentAcademicRecordClassRankingExtension] (
    [StudentUSI] [INT] NOT NULL,
    [SchoolYear] [SMALLINT] NOT NULL,
    [TermDescriptorId] [INT] NOT NULL,
    [EducationOrganizationId] [INT] NOT NULL,
    [SpecialEducationGraduationStatusDescriptorId] [INT] NOT NULL,
    CONSTRAINT [PK_StudentAcademicRecordClassRankingExtension] PRIMARY KEY CLUSTERED (
        [StudentUSI],
        [SchoolYear],
        [TermDescriptorId],
        [EducationOrganizationId]
    )
)
GO
 
ALTER TABLE [extension2].[StudentAcademicRecordClassRankingExtension] ADD CONSTRAINT [FK_StudentAcademicRecordClassRankingExtension_StudentAcademicRecordClassRanking]
FOREIGN KEY ([EducationOrganizationId], [SchoolYear], [StudentUSI], [TermDescriptorId])
REFERENCES [edfi].[StudentAcademicRecordClassRanking] ([EducationOrganizationId], [SchoolYear], [StudentUSI], [TermDescriptorId] )
GO
 
ALTER TABLE [extension2].[StudentAcademicRecordClassRankingExtension] ADD CONSTRAINT [FK_StudentAcademicRecordClassRankingExtension_SpecialEducationGraduationStatusDescriptorId]
FOREIGN KEY ([SpecialEducationGraduationStatusDescriptorId])
REFERENCES [extension2].[SpecialEducationGraduationStatusDescriptor] ([SpecialEducationGraduationStatusDescriptorId])
GO

Step 4. Security Configuration

PostSecondaryOrganization has no relationship to people or education organizations and therefore has no authorization strategy that can be applied to it. It is in effect a stand alone table and will need an authorization strategy of 'NoFurtherAuthorizationRequired' for API requests to be made against it. If security is desired, another authorization strategy can be implemented to handle this entity.

To enable NoFurtherAuthorizationRequired, first create a security SQL script called 0001-PostSecondaryOrganization_No_Further_Auth_Required.sql and place it in the C:\Ed-Fi-ODS-Implementation\Database\Data\EdFiSecurity folder (Create 'EdFiSecurity' folder if it does not exist). Copy the contents of the following SQL DML script into the newly created file and save.

0001-PostSecondaryOrganization_No_Further_Auth_Required.sql

DECLARE @AuthorizationStrategyId INT 
DECLARE @ResourceClaimId INT 

SELECT @AuthorizationStrategyId = AuthorizationStrategyId 
FROM   AuthorizationStrategies 
WHERE  AuthorizationStrategyName = 'NoFurtherAuthorizationRequired' 

SELECT @ResourceClaimId = resourceclaimid 
FROM   ResourceClaims 
WHERE  ResourceName = 'postSecondaryOrganization' 

INSERT INTO ResourceClaimAuthorizationMetadatas 
SELECT ActionId, 
       @AuthorizationStrategyId, 
       @ResourceClaimId, 
       NULL 
FROM   Actions a 
WHERE  NOT EXISTS (SELECT 1 
                   FROM   ResourceClaimAuthorizationMetadatas 
                   WHERE  Action_ActionId = a.ActionId 
                          AND AuthorizationStrategy_AuthorizationStrategyId = @AuthorizationStrategyId 
                          AND ResourceClaim_ResourceClaimId = @ResourceClaimId)

Step 5. Author Bulk/XSD Schema Extensions

Support for bulk loading of data into extension entities is not supported in the Technical Preview release. This capability is currently under development, tracked by ODS-1750 - ODS_API 3.0: Support for Bulk Loading Extension Entities OPEN .

Step 6. Run Code Generation and Verify Changes

Save all modified files, close Ed-Fi-ODS.sln, and re-run the code generation steps outlined in the Getting Started Guide, (i.e., from a PowerShell prompt run Initialize-PowershellForDevelopment.ps script, followed by the initdev command). Then, run the application and view the Ed-Fi ODS / API in the Swagger UI.

The new postSecondaryOrganizations API resource should be visible, as well as the postSecondaryOrganizationReference in the studentAcademicRecord resource.

In the Technical Preview release, there are transient cases where the initdev process does not complete successfully after adding an extension project. If you encounter failures running initdev (usually with a series of errors including, "error : Running transformation: System.Exception: Domain model is invalid."), a workaround is to manually force code generation to run by executing Run Custom Tool against the text template in the semantic model project of your extension. This forces the semantic model to regenerate.

Downloads

The following github link contains source files for this extensibility sample.

Student Transcript Source Files

How To: Extend the Ed-Fi ODS / API - Student Transcript Example (Tech Preview)