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 -h
dotnet tool list -h
dotnet tool list
dotnet tool list -g
dotnet tool uninstall dotnet-ef -g
dotnet tool search dotnet-ef --detail

The SAP HANA Client 2.28 release supports EF Core 9.0 among other versions. 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 9 of the dotnet-ef tool.

dotnet tool install dotnet-ef --version 9.0.14 -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 Copy

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

   Shell Copy

   cd $HOME/HANAClientsTutorial
   dotnet new console -o EFCore

2. Add the required packages including the SAP HANA .NET data provider which is available on nuget. A list of available providers from SAP is available at SAP-SE.

   Shell Copy

   cd EFCore
   dotnet add package Sap.EntityFrameworkCore.Hana.v9.0
   dotnet add package Microsoft.EntityFrameworkCore.Relational --version 9.0.14

   

   The packages can be listed using the command below.

   Shell Copy

   dotnet list package

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

   Shell Copy

   dotnet run

   The expected output is Hello, World!.

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

   Shell Copy

   notepad HotelModel.cs

   Shell 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-.hanacloud.ondemand.com:443;UserName=User2;Password=Password2;Current Schema=USER2");
       }
   }
   
   public class HotelEF
   {
       public int Id { get; set; } = 0;
       public string Name { get; set; } = string.Empty;
       public string Address { get; set; } = string.Empty;
   }

   Show all 27 lines Show less

   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 Copy

   notepad Program.cs

   Shell 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 Copy

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

   Shell Copy

   cd $HOME/HANAClientsTutorial
   dotnet new console -o EFCoreScaffold

2. Install the required packages.

   Shell Copy

   cd EFCoreScaffold
   dotnet add package Sap.EntityFrameworkCore.Hana.v9.0
   dotnet add package Microsoft.EntityFrameworkCore.Relational --version 9.0.14
   dotnet add package Microsoft.EntityFrameworkCore.Design --version 9.0.14

   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

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

   Shell Copy

   dotnet ef dbcontext scaffold "Server=xxxxxxxx-.hanacloud.ondemand.com:443;uid=USER2;pwd=Password2;Current Schema=HOTELS" Sap.EntityFrameworkCore.Hana.v9.0 --schema HOTELS --context HotelsContext

   Notice that classes have been generated for each object in the schema HOTELS.

   

   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.

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

   Shell Copy

   notepad Program.cs

   Shell Copy

   pico Program.cs

5. 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);

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

   Shell Copy

   notepad HotelsContext.cs

   Shell Copy

   pico HotelsContext.cs

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

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

   Shell Copy

   notepad MyHotelsContext.cs

   Shell Copy

   pico MyHotelsContext.cs

9. 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.

   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-.hanacloud.ondemand.com:443;uid=USER2;pwd=Password2;Current Schema=USER2");
       }
   }

10. Run the app:

    Shell Copy

    dotnet run

    

    Notice that tables such as CUSTOMER, HOTEL, MAINTENANCE etc have now been created in the USER2 schema.

    

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