Connect Using the Microsoft Entity Framework Core (EF Core)

The dotnet tool command can be used to install and manage tools that extend .NET. The following are a few examples that can be run to show help, to list the local and globally installed tools, to uninstall dotnet-ef if an incompatible version is installed, and to search the repository for version details of the dotnet-ef tool.

dotnet tool -?
dotnet tool list -?
dotnet tool list
dotnet tool list -g
dotnet tool uninstall dotnet-ef -g
dotnet tool search dotnet-ef --detail

The SAP HANA Client 2.17 release supports EF Core 6.0 & 7.0. For a list versions and support dates see EF Core releases and planning and SAP Note 3165810 - SAP HANA Client Supported Platforms.

Run the following command to install version 7 of the dotnet-ef tool.

dotnet tool install dotnet-ef --version 7.0.17 -g
dotnet tool list -g

The help for the .NET Command Line Tools can be displayed as shown below.

dotnet ef -h

1. Create a new console app with the below commands:

   Shell (Microsoft Windows)

   Copy

   cd %HOMEPATH%/HANAClientsTutorial
   dotnet new console -o EFCore
   

   Shell (Linux or Mac)

   Copy

   cd $HOME/HANAClientsTutorial
   dotnet new console -o EFCore
   

2. Open the EFCore.csproj file:

   Shell (Microsoft Windows)

   Copy

   cd EFCore
   notepad EFCore.csproj
   

   Shell (Linux or Mac)

   Copy

   cd EFCore
   pico EFCore.csproj
   

   Add the following below the PropertyGroup section (within the Project section) to indicate where to load the SAP HANA Client .NET and entity driver from. Modify the HintPath section with the information about where the dlls are located on your machine.

   Shell (Microsoft Windows)

   Copy

   <ItemGroup>
       <Reference Include="Sap.Data.Hana.Core.v6.0">
           <HintPath>C:\SAP\hdbclient\dotnetcore\v6.0\Sap.Data.Hana.Net.v6.0.dll</HintPath>
       </Reference>
   
       <Reference Include="Sap.EntityFrameworkCore.Hana.v7.0">
           <HintPath>C:\SAP\hdbclient\dotnetcore\v6.0\Sap.EntityFrameworkCore.Hana.v7.0.dll</HintPath>
       </Reference>
   
       <PackageReference Include="Microsoft.EntityFrameworkCore" Version="7.0.17">
       </PackageReference>  
       <PackageReference Include="Microsoft.EntityFrameworkCore.Relational" Version="7.0.17">
       </PackageReference>
   </ItemGroup>
   

   Shell (Linux or Mac)

   Copy

   <ItemGroup>
       <Reference Include="Sap.Data.Hana.Core.v6.0">
           <HintPath>/home/dan/sap/hdbclient/dotnetcore/v6.0/Sap.Data.Hana.Net.v6.0.dll</HintPath>
       </Reference>
       <Reference Include="Sap.EntityFrameworkCore.Hana.v7.0">
           <HintPath>/home/dan/sap/hdbclient/dotnetcore/v6.0/Sap.EntityFrameworkCore.Hana.v7.0.dll</HintPath>
       </Reference>
   
       <PackageReference Include="Microsoft.EntityFrameworkCore" Version="7.0.17">
       </PackageReference>  
       <PackageReference Include="Microsoft.EntityFrameworkCore.Relational" Version="7.0.17">
       </PackageReference>
   </ItemGroup>
   

   

   Once the dotNet.csproj file has been updated, save, and close the file.

3. Run the app to validate that SAP hdbclient DLLs can be loaded:

   Shell

   Copy

   dotnet run
   

   The expected output is Hello, World!.

   If a warning occurs mentioning that a SAP reference could not be resolved, revisit the EFCore.csproj file and double check that the hintpath is correct.

4. Open an editor and create a file named HotelModel.cs.

   Shell (Windows)

   Copy

   notepad HotelModel.cs
   

   Shell (Linux or Mac)

   Copy

   pico HotelModel.cs
   

5. Copy the below code into HotelModel.cs with the code below:

   C#

   Copy

   using Microsoft.EntityFrameworkCore;
   using Sap.EntityFrameworkCore.Hana;
   
   public class HotelContext : DbContext
   {
       public DbSet<HotelEF> Hotel { get; set; }
   
       public HotelContext()
       {
           var folder = Environment.SpecialFolder.LocalApplicationData;
           var path = Environment.GetFolderPath(folder);
   
           Database.EnsureDeleted();
           Database.EnsureCreated();
       }
   
       protected override void OnConfiguring(DbContextOptionsBuilder options) {
           options.UseHana("Server=xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxx.hana.prod-xxxx.hanacloud.ondemand.com:443;UserName=User2;Password=Password2;CurrentSchema=User2");
       }
   }
   
   public class HotelEF
   {
       public int Id { get; set; } = 0;
       public string Name { get; set; } = string.Empty;
       public string Address { get; set; } = string.Empty;
   }
   

   Be sure to update the host URL and optionally the user name and password. Note that calls to EnsureDeleted and EnsureCreated will delete and recreate the objects in the schema USER2. As documented at RelationalDatabaseCreator.EnsureDeleted Method it will delete all objects in the schema USER2.

6. Open an editor to edit the file Program.cs.

   Shell (Windows)

   Copy

   notepad Program.cs
   

   Shell (Linux or Mac)

   Copy

   pico Program.cs
   

7. Replace the entire contents of Program.cs with the code below. Save and close the file when finished.

   C#

   Copy

   using var db = new HotelContext();
   
   // Create
   Console.WriteLine("Inserting a new Hotel");
   db.Add(new HotelEF { Id = 1, Name = "The Inn of Waterloo", Address = "475 King St N, Waterloo" });
   db.Add(new HotelEF { Id = 2, Name = "The Walper Hotel", Address = "20 Queen St S, Kitchener" });
   db.SaveChanges();
   
   // Read
   Console.WriteLine("Querying for a hotel");
   var hotels = db.Hotel
       .OrderBy(b => b.Name).Last();
   Console.WriteLine("Found: " + hotels.Name);
   

   Further details on SAP HANA Client entity core driver can be found at Entity Framework Core Support. Further .NET API details can be found in the .NET API browser.

8. Run the app:

   Shell

   Copy

   dotnet run
   

   Before running the program make sure to be in the directory where Program.cs is saved

   

1. Open Visual Studio Code. If needed, download the application here.

2. If you have not already done so, choose File | Add Folder to Workspace, and then add the HANAClientsTutorial folder.

   

3. Open the file Program.cs and set a breakpoint.

4. Select Run | Start Debugging | .NET Core. A configuration will be added. Choose Run | Start Debugging.

   Notice that the debug view becomes active and that the RUN option is .NET Launch.

   Notice that the program stops running at the breakpoint that was set.

   Observe the variable values in the leftmost pane. Step through code.

   

   For further information on debugging .NET apps consult Tutorial: Debug a .NET Core console application using Visual Studio Code and Instructions for setting up the .NET Core debugger.

The following steps demonstrate the process of generating entity type classes and a DbContext class based on an existing database schema. Additional details can be found at Scaffolding (Reverse Engineering).

1. Create a new console app with the below commands:

   Shell (Microsoft Windows)

   Copy

   cd %HOMEPATH%/HANAClientsTutorial
   dotnet new console -o EFCoreScaffold
   

   Shell (Linux or Mac)

   Copy

   cd $HOME/HANAClientsTutorial
   dotnet new console -o EFCoreScaffold
   

2. Open the EFCoreScaffold.csproj file:

   Shell (Microsoft Windows)

   Copy

   cd EFCoreScaffold
   notepad EFCoreScaffold.csproj
   

   Shell (Linux or Mac)

   Copy

   cd EFCoreScaffold
   pico EFCoreScaffold.csproj
   

   Add the following below the PropertyGroup section (within the Project section) to indicate where to load the SAP HANA Client .NET and entity driver from. Modify the HintPath section with the information about where the dlls are located on your machine.

   Shell (Microsoft Windows)

   Copy

   <ItemGroup>
       <Reference Include="Sap.Data.Hana.Core.v6.0">
           <HintPath>C:\SAP\hdbclient\dotnetcore\v6.0\Sap.Data.Hana.Net.v6.0.dll</HintPath>
       </Reference>
       <Reference Include="Sap.EntityFrameworkCore.Hana.v7.0">
           <HintPath>C:\SAP\hdbclient\dotnetcore\v6.0\Sap.EntityFrameworkCore.Hana.v7.0.dll</HintPath>
       </Reference>
   
       <PackageReference Include="Microsoft.EntityFrameworkCore" Version="7.0.17">
       </PackageReference>  
       <PackageReference Include="Microsoft.EntityFrameworkCore.Relational" Version="7.0.17">
       </PackageReference>
   </ItemGroup>
   

   Shell (Linux or Mac)

   Copy

   <ItemGroup>
       <Reference Include="Sap.Data.Hana.Core.v6.0">
           <HintPath>/home/dan/sap/hdbclient/dotnetcore/v6.0/Sap.Data.Hana.Net.v6.0.dll</HintPath>
       </Reference>
       <Reference Include="Sap.EntityFrameworkCore.Hana.v7.0">
           <HintPath>/home/dan/sap/hdbclient/dotnetcore/v6.0/Sap.EntityFrameworkCore.Hana.v7.0.dll</HintPath>
       </Reference>
   
       <PackageReference Include="Microsoft.EntityFrameworkCore" Version="7.0.17">
       </PackageReference>  
       <PackageReference Include="Microsoft.EntityFrameworkCore.Relational" Version="7.0.17">
       </PackageReference>
   </ItemGroup>
   

3. Install the required package Microsoft.EntityFrameworkCore.Design.

   Shell

   Copy

   dotnet add package Microsoft.EntityFrameworkCore.Design --version 7.0.17
   

   The list of installed packages can be seen using the below command.

   Shell

   Copy

   dotnet list package
   

   

   Additional details can be found at dotnet add package and Microsoft.EntityFrameworkCore.Design

4. Use the scaffold command to generate entity classes for the HOTELS schema. Update the SQL endpoint.

   Shell

   Copy

   dotnet ef dbcontext scaffold "Server=xxxxxx-4782-bc7e-297099099b59.hana.prod-ca10.hanacloud.ondemand.com:443;uid=USER2;pwd=Password2;Current Schema=HOTELS" Sap.EntityFrameworkCore.Hana.v7.0 --schema HOTELS --context HotelsContext
   

   

   Should you wish to regenerate the files in the future and overwrite the existing files, the --force parameter can be used. Additional details on the scaffold command can be found at .NET Core CLI.

5. Open an editor to edit the file Program.cs.

   Shell (Windows)

   Copy

   notepad Program.cs
   

   Shell (Linux or Mac)

   Copy

   pico Program.cs
   

6. Replace the entire contents of Program.cs with the code below. Save and close the file when finished.

   C#

   Copy

   using EFCoreScaffold;
   
   using var db = new MyHotelsContext(true);
   
   // Create
   Console.WriteLine("Inserting a new maintenance item");
   db.Add(new Maintenance { Mno = 3, Description = "Replace cracked mirror in lobby bathroom" });
   db.SaveChanges();
   
   // Read
   Console.WriteLine("Querying for a maintenance item");
   var maintenanceItems = db.Maintenances
       .OrderBy(b => b.Hno).Last();
   Console.WriteLine("Found item#: " + maintenanceItems.Mno + "  Desc: " + maintenanceItems.Description);
   

7. Open an editor to edit the file HotelsContext.cs.

   Shell (Windows)

   Copy

   notepad HotelsContext.cs
   

   Shell (Linux or Mac)

   Copy

   pico HotelsContext.cs
   

8. Delete the OnConfiguring method. This will be added to the MyHotelsContext.cs class.

9. Open an editor to create and edit a new file named MyHotelsContext.cs.

   Shell (Windows)

   Copy

   notepad MyHotelsContext.cs
   

   Shell (Linux or Mac)

   Copy

   pico MyHotelsContext.cs
   

10. Add the code below. Update the Server= line to match your SAP HANA Cloud SQL endpoint. Save and close the file when finished. Note that the schema is changed to be USER2 while the original objects are in the schema HOTELS.

    C#

    Copy

    using Microsoft.EntityFrameworkCore;
    using Sap.EntityFrameworkCore.Hana;
    
    namespace EFCoreScaffold;
    internal class MyHotelsContext : HotelsContext
    {
        public MyHotelsContext(bool createTables) : base()
        {
            if (createTables)
            {
                // Delete the existing database tables and re-create new tables.
                Database.EnsureDeleted();
                Database.EnsureCreated();
            }
        }
    
        protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        {
            optionsBuilder.UseHana("Server=xxxxxxxx-4782-bc7e-297099099b59.hana.prod-ca10.hanacloud.ondemand.com:443;uid=USER2;pwd=Password2;Current Schema=USER2");
        }
    }
    

11. Run the app:

    Shell

    Copy

    dotnet run
    

    

Congratulations! You have now created and debugged a .NET application that connects to and queries an SAP HANA database.