EF Core Sidekick: Using this extension for auto code generation

Introduction

EF Core Sidekick is a Visual Studio extension that enhances the power of auto code generation in Visual Studio. It provides a set of tools and templates for generating EF Core entities from existing database, and then generating services and APIs from the entities.

What's New in Beta

The beta version of EF Core Sidekick supports the following environment:

  • Supports Visual Studio 2022
  • Supports Entity Framework Core 6.0 and 7.0
  • Supports .NET 6.0 and 7.0
  • Supports SQL Server, SQLite, PostgreSQL, Oracle, and MySQL databases through ODBC or native driver

The beta version of EF Core Sidekick provides the following functionalities:

  • Creates EF Core entities from existing databases
  • Generates services and APIs from entities
  • Generates APIs from controllers, implementations, or interfaces

Installation

Make sure your environment meets the following requirements:

  1. Visual Studio 2022 (17.0 thru 17.6) has been installed.

    The following workloads are required to install:

    • ASP.NET and web development
    • Data storage and processing

  2. Visual Studio IDE should NOT be running when installing EF Core Sidekick.

  3. EF Core Sidekick supports the following database connection:

    SQL Server: MicrosoftSqlServerProvider, SqlServerDotNetProvider, or ODBC

    SQLite: VisualStudio.Data.Sqlite or ODBC

    PostgreSQL: Npgsql PostgreSQL Integration or ODBC

    Oracle: Oracle Developer Tools for Visual Studio or ODBC

    MySQL: ODBC

User Guide

Create entities from an existing database

Step 1: Launch the functionality

To access this functionality, you can

  • Select the IDE menu Tools > EF Core Sidekick > New Entities from DB, or
  • Right click the project and select EF Core Sidekick > New Entities from DB, or
  • Right click the database connection in Server Explorer and select EF Core Sidekick > New Entities from DB.

Step 2: Connect to the database

  1. Right click the project and select EF Core Sidekick > New Entities from DB.

    image-20230620134539012

  2. Click New to create a new database connection.

    image-20230620135115270

  3. In the Connection Properties dialog that displays, specify the connection information for the database.

    The following takes SQL Server database as an example. For more information about connecting to the SQL Server database, refer to https://learn.microsoft.com/en-us/visualstudio/data-tools/add-new-connections?view=vs-2022.

    image-20230620135020930

    The Data source field indicates the database type and the data provider to be used for the connection. You can change this by clicking the Change button and then selecting a different data source and data provider. You will need to install the data provider beforehand.

    image-20230620135051343

  4. After you specify the connection properties in the Connection Properties dialog, click Test Connection to make sure the database connection is successful.

Step 3: Select tables or views

After creating the database connection, you can select it in the Generate entities from database window, and then you can select one or more tables and/or views from the database (①). If the list is long, you can search for the name of the table or view.

The Enable selection of associated tables and references option (②) is selected by default, so when you select a table, the tables that are referenced by the selected table (so called referenced tables) will be automatically selected and the reference relationship will be displayed in the DataType and Mapping Type columns (⑥).

The tables that make references to the selected table (so called referencing tables) are not selected. When the Enable selection of associated tables and references option is selected, the referencing columns will be visible in the References section (③), if you manually select the referencing columns in the References section, the corresponding referencing tables (and the referenced tables of these tables) will be automatically selected.

You can change the default name for the entity and/or property (④) when necessary.

You can view the data type of each field (⑤), and the relationship of tables (⑥).

Click Next to proceed.

image-20230620144758590

Step 4: Specify the generation settings

After you select the tables and/or views, you can specify settings for the entity generation.

You can select an existing generation profile or click New to create a new profile.

General configuration

  • Override: Overwrites the existing files with the same file names.

Entity configuration

  • Add the schema name as a prefix to the entity name: The name of the schema will be added as the prefix of the entity file name. For example, dbo_cur_emp.cs contains a schema prefix "dbo_" and cur_emp.cs contains no schema prefix.
  • Create a schema sub folder for storing the entity file(s): Creates a folder using the name of the schema and store the entities in this folder.

Target configuration

  • You can specify where the files will be generated, and which namespace will be used.

  • You can modify the relative output path where the files will be generated.

  • For Dal (data access layer), you can also specify the ORM (only EFCore 6/7 is supported currently).

image-20230620135511667

Step 5: Generate entities

Click Next to generate the entity files.

After entities are generated successfully, you will need to copy the scripts from the screen to inject the database context in Program.cs and add connection string in appsettings.json file respectively. Modify the connection string according to your database information.

image-20230621172915446

After the files are generated successfully, they will be listed under the project.

image-20230620140004354

Generate services and APIs from an entity

After the entity files are generated successfully, you can generate services and APIs from the entity.

Step 1: Launch the functionality

To access this functionality, you can

  • Select one or more CS files and then right click and select EF Core Sidekick > Generate services and APIs, or
  • Right click in the script editor of the CS file and select EF Core Sidekick > Generate services and APIs.

Step 2: Configure the services and APIs

Select APIs

Select one or more entities and then right click and select EF Core Sidekick > Generate services and APIs.

image-20230620141012629

In the Generate services and APIs window, Generate all APIs (①) is selected by default which means APIs will be generated for all base services. You can also select Generate API (②) to generate API for the particular service(s) only.

image-20230621162038973

Configure authorization

You can select the authorization strategy for the controller (①) (the upper-level of API), so all APIs will by default use this strategy. You can also select different strategy for each API in the API Settings page (②).

For controller, you can select one of the following basic authorization strategies: None, Use application default strategy, Use anonymous strategy, or use other custom policies.

  • None: Use no authorization strategy:

  • Use application default strategy: Uses the default strategy configured for the application.

  • Use anonymous strategy: Uses the anonymous access strategy. Anonymous access allows requests to go through without authentication.

image-20230621155317796

To add a new custom policy, you will need to write codes and implement the policy first, and then add the policy here, by selecting New Strategy at the bottom of the strategy list box.

In the Authorize strategy dialog box, click Add (①) to add a policy and then click the Rename icon (②) to change the default name to the actual policy name (③), or select it as the default policy (④).

image-20230621160904771

Configure service

You can configure the settings for each service in the Service settings tab.

Parameter settings

Lists the request parameters of the service.

Sort settings

(For GetList and GetPage) Click Add sort option to sort the data in the database. You can select a field or input an expression and then specify to sort by ascending or descending order.

Assignment settings

(For Add, AddRange, and Update) You can set values for fields. You can select a field or input a value or expression.

Return settings

(For Get, GetList and GetPage) Specify the return value of each response field of the service. You can select a field or input a value or expression.

(For Add, AddRange, Update, and Delete) List the return value of the service.

Sorting the return data

(For GetList and GetPage) Click Sort sources to select an entity and then click Add sort option to select a field or input an expression to sort the data.

Description: The description of the service.

image-20230620141039184

Configure API

You can configure the setting for each API in the API settings tab.

Name: The name of the API.

Method type: The default method type of the API.

Parameter settings

You can select to get parameters from the following: FromQuery, FromRoute, FromForm, FromBody, or FromHeader.

  • FromQuery: Binds parameters from the query string.

  • FromRoute: Binds parameters from the request route.

  • FromForm: Binds parameters from the form data in the request body.

  • FromBody: Binds parameters from the request body.

  • FromHeader: Binds parameters from the request header.

Authorization settings: For the current API, you can select one of the following basic authorization strategies: Use upper-level strategy, Use application default strategy, Use anonymous strategy, or use other custom policies.

  • Use upper-level strategy: Uses the upper-level strategy. For an API, the upper-level is controller. By default the API will use the strategy that is selected for the controller.

  • Use application default strategy: Uses the default strategy configured for the application.

  • Use anonymous strategy: Uses the anonymous access strategy. Anonymous access allows requests to go through without authentication.

Description: The description of the API.

image-20230620141132120

Click Next to proceed.

Step 3: Specify the generation settings

Step 3: Specify settings for the services and APIs generation. You can select an existing generation profile or click New to create a new profile.

General configuration

  • Override: Overwrites the existing files with the same file names.

Target configuration

  • You can specify where the files will be generated, and which namespace will be used.
  • You can modify the relative output path where the files will be generated.

image-20230621173352610

Step 4: Generate services and APIs

Click Next to generate the files.

After services and APIs are generated successfully, you will need to copy the scripts from the screen to inject the repository and service in Program.cs respectively.

image-20230621173458590

After the files are generated successfully, they will be listed under the project.

image-20230620141607872

Generate APIs

You can also generate APIs only from the controller, implementation, or interface.

To access this functionality, you can

  • Select one or more CS files and then right click and select EF Core Sidekick > Generate APIs, or
  • Right click in the script editor of the CS file and select EF Core Sidekick > Generate APIs.

Follow the instructions in Configure authorization and Configure API to configure and generate the APIs.

Troubleshooting tips

Failed to connect with the MySQL database

When connecting with the MySQL database through the ODBC driver, the following error occurs:

Retrieving database table/view failure: ERROR [22018] [MySQL] [ODBC 8.0(a) Driver] [mysqld-5.6.30]. Please verify that the current connection is valid.

image-20230620141607872

Solution:

  1. Make sure the version of driver matches that of the database.
  2. Make sure the character set of driver matches that of the database.

image-20230620141607872

Try the full-featured version, DevMagic Studio, for free: https://www.devmagic.com.

DevMagic Studio is a FREE, low-code tool designed specifically for Web API development with EF Core. It offers visual designers, powerful code generation capabilities, and adheres to cloud-native architecture. The generated code is standard C# code that can be edited in any C# IDE without any vendor lock.