Umbraco CMS
CloudHeartcoreDXPMarketplace
15.latest
15.latest
  • Umbraco CMS Documentation
  • Legacy Documentation
    • Our Umbraco
    • GitHub
  • Release Notes
  • Contribute
  • Sustainability Best Practices
  • Fundamentals
    • Get to know Umbraco
    • Setup
      • Requirements
      • Installation
        • Install using .NET CLI
        • Running Umbraco in Docker using Docker Compose
        • Install using Visual Studio
        • Local IIS With Umbraco
        • Install using Visual Studio Code
        • Installing Nightly Builds
        • Running Umbraco on Linux/macOS
        • Unattended Installs
      • Upgrade your project
        • Version Specific Upgrades
          • Upgrade from Umbraco 8 to the latest version
          • Migrate content to Umbraco 15
          • Migrate content to Umbraco 8
          • Minor upgrades for Umbraco 8
          • Upgrade to Umbraco 7
          • Minor upgrades for Umbraco 7
      • Server setup
        • Running Umbraco On Azure Web Apps
        • Hosting Umbraco in IIS
        • File And Folder Permissions
        • Runtime Modes
        • Running Umbraco in Docker
        • Umbraco in Load Balanced Environments
          • Load Balancing Azure Web Apps
          • Standalone File System
          • Advanced Techniques With Flexible Load Balancing
          • Logging With Load Balancing
    • Backoffice
      • Sections
      • Property Editors
        • Built-in Property Editors
          • Checkbox List
          • Collection
          • Color Picker
          • Content Picker
          • Document Picker
          • DateTime
          • Date
          • Decimal
          • Email Address
          • Eye Dropper Color Picker
          • File Upload
          • Image Cropper
          • Label
          • Markdown Editor
          • Media Picker
          • Member Group Picker
          • Member Picker
          • Multi Url Picker
          • Repeatable Textstrings
          • Numeric
          • Radiobutton List
          • Slider
          • Tags
          • Textarea
          • Textbox
          • Toggle
          • User Picker
          • Block Editors
            • Block Grid
            • Block List
            • Block Level Variance
          • Dropdown
          • Rich Text Editor
            • Configuration
            • Extensions
            • Blocks
            • Style Menu
            • Change Rich Text Editor UI
          • Rich Text Editor TinyMce
            • Configuration
            • Styles
            • Plugins
            • Blocks
      • Login
      • Document Blueprints
      • Sidebar
      • Log Viewer
      • Language Variants
      • Settings Dashboards
    • Data
      • Defining Content
        • Default Document Types
        • Document Type Localization
      • Creating Media
        • Default Data/Media Types
      • Members
      • Data Types
        • Default Data Types
      • Scheduled Publishing
      • Using Tabs
      • Users
        • API Users
      • Relations
      • Dictionary Items
      • Content Version Cleanup
    • Design
      • Templates
        • Basic Razor Syntax
        • Razor Cheatsheet
      • Rendering Content
      • Rendering Media
      • Partial Views
      • Stylesheets And JavaScript
    • Code
      • Service APIs
      • Subscribing To Notifications
      • Creating Forms
      • Debugging
        • Logging
      • Source Control
  • Implementation
    • Learn how Umbraco works
    • Routing
      • Controller & Action Selection
      • Execute Request
      • Request Pipeline
    • Custom Routing
      • Adding a hub with SignalR and Umbraco
    • Controllers
    • Data Persistence (CRUD)
    • Composing
    • Integration Testing
    • Nullable Reference Types
    • Services and Helpers
      • Circular Dependencies
    • Unit Testing
  • Customizing
    • Extend and customize the editing experience
    • Project Bellissima
    • Setup Your Development Environment
      • Vite Package Setup
      • TypeScript setup
    • Extension Overview
      • Extension Registry
        • Extension Registration
        • Extension Manifest
        • Replace, Exclude or Unregister
      • Extension Types
        • Sections
          • Sections
          • Section Sidebar
          • Section View
        • Workspaces
          • Workspace Actions
          • Workspace Context
          • Workspace Views
        • Route Registration
        • Menu
        • Header Apps
        • Icons
        • Block Custom View
        • Bundle
        • Kind
        • App Entry Point
        • Backoffice Entry Point
        • Extension Conditions
        • Dashboards
        • Entity Actions
        • Entity Bulk Actions
        • Entity Create Option Action
        • Property Value Preset
        • Trees
        • Global Context
        • Localization
        • Modals
          • Confirm Dialog
          • Custom Modals
      • Extension Kind
      • Extension Conditions
      • Custom Extension types
    • Foundation
      • Working with Data
        • Repositories
        • Context API
        • Store
        • States
      • Contexts
        • Property Dataset Context
      • Umbraco Element
        • Controllers
          • Write your own controller
      • Sorting
      • Routes
      • Icons
      • Backoffice Localization
      • Terminology
    • Sections & Trees
    • Searchable Trees (ISearchableTree)
    • Property Editors
      • Property Editors Composition
        • Property Editor Schema
        • Property Editor UI
      • Property Value Converters
      • Property Actions
      • Integrate Property Editors
      • Tracking References
      • Content Picker Value Converter Example
      • Property Dataset
      • Integrate Validation
    • Workspaces
    • Umbraco Package
    • UI Library
  • Extending
    • Build on Umbraco functionality
    • Health Check
      • Health Check Guides
        • Click-Jacking Protection
        • Content Content Security Policy (CSP)
        • Content/MIME Sniffing Protection
        • Cross-site scripting Protection (X-XSS-Protection header)
        • Debug Compilation Mode
        • Excessive Headers
        • Fixed Application Url
        • Folder & File Permissions
        • HTTPS Configuration
        • Notification Email Settings
        • SMTP
        • Strict-Transport-Security Header
    • Language Files & Localization
      • .NET Localization
    • Backoffice Search
    • Creating a Custom Database Table
    • Creating a Custom Seed Key Provider
    • Embedded Media Providers
    • Custom File Systems (IFileSystem)
      • Using Azure Blob Storage for Media and ImageSharp Cache
    • Configuring Azure Key Vault
    • Packages
      • Creating a Package
      • Language file for packages
      • Listing a Package on the Umbraco Marketplace
      • Good practice and defaults
      • Packages on Umbraco Cloud
      • Installing and Uninstalling Packages
      • Maintaining packages
      • Create accessible Umbraco packages
      • Example Package Repository
  • Reference
    • Dive into the code
    • Configuration
      • Basic Authentication Settings
      • Connection strings settings
      • Content Dashboard Settings
      • Content Settings
      • Data Types Settings
      • Debug settings
      • Examine settings
      • Exception filter settings
      • FileSystemProviders Configuration
      • Global Settings
      • Health checks
      • Hosting settings
      • Imaging settings
      • Indexing settings
      • Install Default Data Settings
      • Logging settings
      • Maximum Upload Size Settings
      • Models builder settings
      • Cache Settings
      • Package Migration
      • Plugins settings
      • Request handler settings
      • Runtime settings
      • Security Settings
      • Serilog settings
      • Type finder settings
      • Unattended
      • Web routing
    • Templating
      • Models Builder
        • Introduction
        • Configuration
        • Builder Modes
        • Understand and Extend
        • Using Interfaces
        • Tips and Tricks
      • Working with MVC
        • Working with MVC Views in Umbraco
        • View/Razor Examples
        • Using MVC Partial Views in Umbraco
        • Using View Components in Umbraco
        • Querying & Traversal
        • Creating Forms
      • Macros
    • Querying & Models
      • IMemberManager
      • IPublishedContentQuery
      • ITagQuery
      • UDI Identifiers
      • UmbracoContext helper
      • UmbracoHelper
      • IPublishedContent
        • IPublishedContent Collections
        • IPublishedContent IsHelpers
        • IPublishedContent Property Access & Extension Methods
    • Routing & Controllers
      • Custom MVC controllers (Umbraco Route Hijacking)
      • Custom MVC Routes
      • Custom Middleware
      • URL Rewrites in Umbraco
      • Special Property Type aliases for routing
      • URL Redirect Management
      • Routing in Umbraco
        • FindPublishedContentAndTemplate()
        • IContentFinder
        • Inbound request pipeline
        • Outbound request pipeline
        • Published Content Request Preparation
      • Surface controllers
        • Surface controller actions
      • Umbraco API Controllers
        • Porting old Umbraco API Controllers
    • Content Delivery API
      • Custom property editors support
      • Extension API for querying
      • Media Delivery API
      • Protected content in the Delivery API
        • Server to server access
      • Output caching
      • Property expansion and limiting
      • Additional preview environments support
    • Webhooks
      • Expanding Webhook Events
    • API versioning and OpenAPI
    • Searching
      • Examine
        • Examine Management
        • Examine Manager
        • Custom indexing
        • PDF indexes and multisearchers
        • Quick-start
    • Using Notifications
      • Notification Handler
      • CacheRefresher Notifications Example
      • ContentService Notifications Example
      • Creating And Publishing Notifications
      • Determining if an entity is new
      • MediaService Notifications Example
      • MemberService Notifications Example
      • UserService Notifications Example
      • Sending Allowed Children Notification
      • Umbraco Application Lifetime Notifications
      • EditorModel Notifications
        • Customizing the "Links" box
      • Hot vs. cold restarts
    • Inversion of Control / Dependency injection
    • Management
      • Using Umbraco services
        • Consent Service
        • Media Service
        • Relation Service
        • Content Service
        • Content Type Service
        • Localization Service
        • User Service
    • Plugins
      • Creating Resolvers
      • Finding types
    • Cache & Distributed Cache
      • Cache Seeding
      • Accessing the cache
      • ICacheRefresher
      • IServerMessenger
      • Getting/Adding/Updating/Inserting Into Cache
      • Examples
        • Working with caching
    • Response Caching
    • Security
      • API rate limiting
      • BackOfficeUserManager and Events
      • Cookies
      • Replacing the basic username/password check
      • External login providers
      • Locking of Users and password reset
      • Reset admin password
      • Umbraco Security Hardening
      • Umbraco Security Settings
      • Sensitive data
      • Sanitizing the Rich Text Editor
      • Setup Umbraco for a FIPS Compliant Server
      • HTTPS
      • Two-factor Authentication
      • Server-side file validation
    • Scheduling
    • Common Pitfalls & Anti-Patterns
    • API Documentation
    • Debugging with SourceLink
    • Language Variation
    • UmbracoMapper
    • Distributed Locks
    • Management API
      • External Access
      • Setup OAuth using Postman
    • Custom Swagger API
    • Umbraco Flavored Markdown
    • Content Type Filters
  • Tutorials
    • Overview
    • Creating a Basic Website
      • Getting Started
      • Document Types
      • Creating Your First Template
      • CSS and Images
      • Displaying the Document Type Properties
      • Creating a Master Template
      • Creating Pages and Using the Master Template
      • Setting the Navigation Menu
      • Articles and Article Items
      • Adding Language Variants
      • Conclusions
    • Creating your First Extension
    • Creating a Custom Dashboard
      • Adding localization to the dashboard
      • Adding functionality to the Dashboard
      • Using Umbraco UI library in the Dashboard
    • Creating a Property Editor
      • Adding configuration to a Property Editor
      • Integrating context with a Property Editor
      • Custom value conversion for rendering
      • Adding server-side validation
        • Default Property Editor Schema aliases
    • Creating a Multilingual Site
    • Add Google Authentication (Users)
    • Add Microsoft Entra ID authentication (Members)
    • Creating Custom Database Tables with Entity Framework
    • Migrating Macros
    • The Starter Kit
      • Install the Starter Kit
      • Lessons
        • Customize the Starter Kit
        • Add a Blog Post Publication Date
          • Add a Blog Post Publication Date
          • Add a Blog Post Publication Date
        • Add Open Graph
          • Add Open Graph - Step 1
          • Add Open Graph - Step 2
          • Add Open Graph - Step 3
          • Add Open Graph - Step 4
          • Add Open Graph - Summary
        • Ask For Help and Join the Community
    • Editor's Manual
      • Getting Started
        • Logging In and Out
        • Umbraco Interface
        • Creating, Saving and Publishing Content Options
        • Finding Content
        • Editing Existing Content
        • Sorting Pages
        • Moving a Page
        • Copying a Page
        • Deleting and Restoring Pages
      • Working with Rich Text Editor
      • Version Management
        • Comparing Versions
        • Rollback to a Previous Version
      • Media Management
        • Working with Folders
        • Working with Media Types
        • Cropping Images
      • Tips & Tricks
        • Refreshing the Tree View
        • Audit Trail
        • Notifications
        • Preview Pane Responsive View
        • Session Timeout
    • Multisite Setup
    • Member Registration and Login
    • Custom Views for Block List
    • Connecting Umbraco Forms and Zapier
    • Creating an XML Sitemap
    • Implement Custom Error Pages
    • Create a custom maintenance page
    • Creating a backoffice API
      • Documenting your controllers
      • Adding a custom Swagger document
      • Versioning your API
      • Polymorphic output in the Management API
      • Umbraco schema and operation IDs
      • Access policies
    • Extending the Help Menu
Powered by GitBook
On this page
  • Introduction
  • Prerequisite
  • Step 1: Create Model Class
  • Step 2: Create DBContext class
  • Step 3: Register the DbContext
  • Step 4: Create the notification handler
  • Step 5: Register the notification handler
  • Going Further
  • Working with the data in the Custom Database Tables

Was this helpful?

Edit on GitHub
Export as PDF
  1. Tutorials

Creating Custom Database Tables with Entity Framework

This tutorial will show you how to get started with creating custom database tables with the Entity Framework Core in Umbraco.

Introduction

Custom database tables let you store additional data in the Umbraco database that you don't want to be stored as normal content nodes.

Using custom tables can be great for many things such as storing massive amounts of data that you do not need to edit from the backoffice.

Decoupling part of your data from being managed by Umbraco as content is a way to achieve better performance for your site. It will no longer take up space in indexes and caches, and the Umbraco database.

Be aware that storing data in custom database tables is by default not manageable by Umbraco.

This means that if you need to edit or display this data, you need to implement the underlying functionality to support this.

The case is the same if you need this data to be transferred or kept synchronized between multiple sites or environments.

Data stored in custom tables are not supported by default by add-ons such as Umbraco Deploy and will not be deployable by default.

Prerequisite

  • An Umbraco project with content

  • EFCore CLI tool

    • Can be installed by running dotnet tool install --global dotnet-ef in the terminal

If you are using EF Core, and have installed the Microsoft.EntityFrameworkCore.Design 8.0.0 package

You need to be aware of some things if you are using EF Core, and have installed the Microsoft.EntityFrameworkCore.Design 8.0.0 package:

  • This package has a transient dependency to Microsoft.CodeAnalysis.Common which clashes with the same transient dependency from Umbraco.Cms 13.0.0. This happens because Microsoft.EntityFrameworkCore.Design 8.0.0 requires Microsoft.CodeAnalysis.CSharp.Workspaces in v4.5.0 or higher.

  • If there are no other dependencies that need that package then it installs it in the lowest allowed version (4.5.0). That package then has a strict dependency on Microsoft.CodeAnalysis.Common version 4.5.0. The problem is Umbraco.Cms through its own transient dependencies that require the version of Microsoft.CodeAnalysis.Common to be >= 4.8.0.

  • This can be fixed by installing Microsoft.CodeAnalysis.CSharp.Workspaces version 4.10.0 as a specific package instead of leaving it as a transient dependency. This is because it will then have a strict transient dependency on Microsoft.CodeAnalysis.Common version 4.8.0, which is the same that Umbraco has.

Step 1: Create Model Class

First, create a class and add the following code:

namespace Umbraco.Demo;

public class BlogComment
{
    public int Id { get; set; }

    public Guid BlogPostUmbracoKey { get; set; }

    public required string Name { get; set; }

    public required string Email { get; set; }

    public required string Website { get; set; }

    public string Message { get; set; } = string.Empty;
}

Step 2: Create DBContext class

Now that we have the model, we create a DbContext class so we can interact with the database and add the following code:

using Microsoft.EntityFrameworkCore;

namespace Umbraco.Demo;

public class BlogContext : DbContext
{
    public BlogContext(DbContextOptions<BlogContext> options)
        : base(options)
    {
    }

    public required DbSet<BlogComment> BlogComments { get; set; }

    protected override void OnModelCreating(ModelBuilder modelBuilder) =>
        modelBuilder.Entity<BlogComment>(entity =>
        {
            entity.ToTable("blogComment");
            entity.HasKey(e => e.Id);
            entity.Property(e => e.Id).HasColumnName("id");
            entity.Property(e => e.BlogPostUmbracoKey).HasColumnName("blogPostUmbracoKey");
            entity.Property(e => e.Message).HasColumnName("message");
            entity.Property(e => e.Website).HasColumnName("website");
            entity.Property(e => e.Email).HasColumnName("email");
        });
}

Step 3: Register the DbContext

We need to register the DbContext to be able to use it in Umbraco.

To do this we can use this helpful extension method:

builder.Services.AddUmbracoDbContext<BlogContext>(options => 
{
    options.UseSqlServer("{YOUR CONNECTIONSTRING HERE}");
    //If you are using SQlite, replace UseSqlServer with UseSqlite
});

If you are using SQlite, replace UseSqlServer with UseSqlite.

  1. Add the method in the Program.cs file:

builder.CreateUmbracoBuilder()
    .AddBackOffice()
    .AddWebsite()
    .AddDeliveryApi()
    .AddComposers()
    .Build();

builder.Services.AddUmbracoDbContext<BlogContext>(options => 
{
    options.UseSqlServer("{YOUR CONNECTIONSTRING HERE}");
    //If you are using SQlite, replace UseSqlServer with UseSqlite
});

We can then access the database via the BlogContext. First, we need to migrate the database to add our tables. With EFCore, we can autogenerate the migrations with the terminal.

For package developers and not only, but in general as well, it's recommended to use the UseUmbracoDatabaseProvider logic. This is because it will then figure out what the correct database is used:

builder.Services.AddUmbracoDbContext<CustomDbContext>((serviceProvider, options) =>
    {
        options.UseUmbracoDatabaseProvider(serviceProvider);
    });
  1. Open your terminal and navigate to your project folder.

  2. Generate the migration by running:

dotnet ef migrations add InitialCreate --context BlogContext

If you use another class library in your project to store models and DBContext classes such as Project.Core (Project.Web being the main startup Project):

  • Go to the project folder where you have your custom class library such as /Project.Core

  • Run the following script with the relative path to your main startup project Project.Web:

dotnet ef migrations add initialCreate -s ../Project.Web/ --context BlogContext

In this example, we have named the migration InitialCreate. However, you can choose the name you like.

We've named the DbContext class BlogContext, however, if you have renamed it to something else, make sure to also change it when running the command.

This might be confusing at first, as when working with EFCore you would inject your Context class. You can still do that, it is however not the recommended approach in Umbraco.

In Umbraco, we use a concept called Scope which is our implementation of the Unit of work pattern. This ensures that we start a transaction when using the database. If the scope is not completed (for example when exceptions are thrown) it will roll it back.

Step 4: Create the notification handler

Next, we create the notification handler that will handle our migrations. We need to create a new class and add the following code to it:

using Umbraco.Cms.Core;
using Umbraco.Cms.Core.Events;
using Umbraco.Cms.Core.Migrations;
using Umbraco.Cms.Core.Notifications;
using Umbraco.Cms.Core.Scoping;
using Umbraco.Cms.Core.Services;
using Umbraco.Cms.Infrastructure.Migrations;
using Umbraco.Cms.Infrastructure.Migrations.Upgrade;

namespace Umbraco.Demo;

public class RunBlogCommentsMigration : INotificationAsyncHandler<UmbracoApplicationStartedNotification>
{
    private readonly BlogContext _blogContext;

    public RunBlogCommentsMigration(BlogContext blogContext)
    {
        _blogContext = blogContext;
    }

    public async Task HandleAsync(UmbracoApplicationStartedNotification notification, CancellationToken cancellationToken)
    {
        IEnumerable<string> pendingMigrations = await _blogContext.Database.GetPendingMigrationsAsync();

        if (pendingMigrations.Any())
        {
            await _blogContext.Database.MigrateAsync();
        }
    }
}

Step 5: Register the notification handler

Lastly, we have to register the notification handler, with an IComposer class and add the following code:

using Umbraco.Cms.Core.Composing;
using Umbraco.Cms.Core.Notifications;

namespace Umbraco.Demo;

public class BlogCommentsComposer : IComposer
{
    public void Compose(IUmbracoBuilder builder) => builder.AddNotificationAsyncHandler<UmbracoApplicationStartedNotification, RunBlogCommentsMigration>();
}

After registering the notification handler, build the project and take a look at the database and we can see our new table:

We now have some custom database tables in our database that we can work with through the Entity framework.

Going Further

Working with the data in the Custom Database Tables

To create, read, update, or delete data from your custom database tables, use the IEFCoreScopeProvider<T> (T is your DbContext class) to access the EFCore context.

The example below creates a UmbracoApiController to be able to fetch and insert blog comments in a custom database table.

  • This example uses the BlogComment class, which is a database model. The recommended approach would be to map these over to a ViewModel instead, that way your database & UI layers are not coupled. Be aware that things like error handling and data validation have been omitted for brevity.

using Microsoft.AspNetCore.Mvc;
using Umbraco.Cms.Persistence.EFCore.Scoping;

namespace Umbraco.Demo;

[ApiController]
[Route("/umbraco/api/blogcomments")]
public class BlogCommentsController : Controller
{
    private readonly IEFCoreScopeProvider<BlogContext> _efCoreScopeProvider;

    public BlogCommentsController(IEFCoreScopeProvider<BlogContext> efCoreScopeProvider)
        => _efCoreScopeProvider = efCoreScopeProvider;

    [HttpGet("all")]
    public async Task<IActionResult> All()
    {
        using IEfCoreScope<BlogContext> scope = _efCoreScopeProvider.CreateScope();
        IEnumerable<BlogComment> comments = await scope.ExecuteWithContextAsync(async db => db.BlogComments.ToArray());
        scope.Complete();
        return Ok(comments);
    }

    [HttpGet("getcomments")]
    public async Task<IActionResult> GetComments(Guid umbracoNodeKey)
    {
        using IEfCoreScope<BlogContext> scope = _efCoreScopeProvider.CreateScope();
        IEnumerable<BlogComment> comments = await scope.ExecuteWithContextAsync(async db =>
        {
            return db.BlogComments.Where(x => x.BlogPostUmbracoKey == umbracoNodeKey).ToArray();
        });

        scope.Complete();
        return Ok(comments);
    }

    [HttpPost("insertcomment")]
    public async Task InsertComment(BlogComment comment)
    {
        using IEfCoreScope<BlogContext> scope = _efCoreScopeProvider.CreateScope();

        await scope.ExecuteWithContextAsync<Task>(async db =>
        {
            db.BlogComments.Add(comment);
            await db.SaveChangesAsync();
        });

        scope.Complete();
    }
}

PreviousAdd Microsoft Entra ID authentication (Members)NextMigrating Macros

Last updated 29 days ago

Was this helpful?

The tutorial will show how to create custom database tables using a composer and a notification handler. With this pattern, you create and run a similar migration but trigger it in response to a .

notification handler
Database result of a migration