DEV Community

Unpublished Post. This URL is public but secret, so share at your own discretion.
Jesper Mayntzhusen
Jesper Mayntzhusen

Draft 24 days

Setup

The examples throughout this blogpost will be running Umbraco 12.3.1.

I have a simple setup to begin with where I have a website project running Umbraco, and a referenced Persistence project for all the EF Core data handling. It looks like this:

Image description

For the data example I had ChatGPT help in generating a list of christmas movies with some additional meta data. The dataset looks like this and is an Excel sheet:

Image description

Setting up the database tables

Before we start, if you want to follow along then this is the initial state of the site: https://github.com/jemayn/efcore-and-uibuilder/tree/Setup

Firstly, to import the movie data we need to set up the database tables to hold this data.

To do this we will use EF Core as it makes it pretty easy for us to manage related data.

The Genre column is a comma seperated list of genres, we want to split those off into their own table and then have a many to many relation between the genres and the movies.

First of all we need to install the Umbraco.Cms.Persistence.EFCorepackage in the persistence project, so install the package through NuGet or add

<PackageReference Include="Umbraco.Cms.Persistence.EFCore" Version="12.3.1" />
Enter fullscreen mode Exit fullscreen mode

To the .csproj file.

Set up the EF Core database models

Next we add a Models folder with a Movie.cs file:

using System.ComponentModel.DataAnnotations.Schema;

namespace TwentyFourDays.Persistence.Models;

[Table("movie")]
public class Movie
{
    public int Id { get; set; }
    public required string Name { get; set; }
    public int ReleaseYear { get; set; }
    public string? MainActor { get; set; }
    public List<MovieGenre> Genres { get; set; }
}

[Table("movieGenre")]
public class MovieGenre
{
    public int Id { get; set; }
    public required string Name { get; set; }
    public List<Movie> Movies { get; set; }
}
Enter fullscreen mode Exit fullscreen mode

Tablename will automatically become the class name, but if you want to overwrite it to fx keep with lowercased table names you can add a data annotation.

The important thing to note here is that a movie has a list of MovieGenre and a MovieGenre has a list of Movie. This is what tells EF Core that they have a many to many relationship.

Set up the EF Core database context

To set up the models as EF Core models that relate to database tables we need to set up the EF Core DbContext.

This is what we can use to perform CRUD operations on the db with strongly typed models.

There are many ways to configure the DbContext, but in this example I will show how to set it up to auto generate ids when inserting data, as well as how to automatically generate the many to many relation table.

In a new DbContexts/MovieContext.cs file I've added the following:

using Microsoft.EntityFrameworkCore;
using TwentyFourDays.Persistence.Models;

namespace TwentyFourDays.Persistence.DbContexts;

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

    public required DbSet<Movie> Movies { get; set; }
    public required DbSet<MovieGenre> MovieGenres { get; set; }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<Movie>(entity =>
        {
            entity.HasKey(e => e.Id);
            entity.Property(e => e.Id).ValueGeneratedOnAdd();
        });

        modelBuilder.Entity<Movie>()
            .HasMany(e => e.Genres)
            .WithMany(e => e.Movies)
            .UsingEntity(e => e.ToTable("movieToMovieGenre"))
            .HasKey(e => e.Id);
    }
}
Enter fullscreen mode Exit fullscreen mode

The bottom part saying the Movie entity has many Genres which again has many Movies is how EF Core knows that it's a many to many relation. We can then specify the tablename (movieToMovieGenre) to generate for the relations table - meaning we don't need an explicit model for it.

Once this is done we need to register the MovieContext in DI:

using Microsoft.EntityFrameworkCore;
using Microsoft.Extensions.DependencyInjection;
using TwentyFourDays.Persistence.DbContexts;
using Umbraco.Cms.Core.Composing;
using Umbraco.Cms.Core.DependencyInjection;
using Umbraco.Extensions;

namespace TwentyFourDays.Persistence;

public class Composer : IComposer
{
    public void Compose(IUmbracoBuilder builder)
    {
        builder.Services.AddUmbracoDbContext<MovieContext>((serviceProvider, options) =>
        {
            options.UseUmbracoDatabaseProvider(serviceProvider);
        });
    }
}
Enter fullscreen mode Exit fullscreen mode

Generating migrations

Next we can generate the EF Core migrations code. First of all we need to install another package in the website project:

<ItemGroup>
  <PackageReference Include="Microsoft.EntityFrameworkCore.Design" Version="7.0.13">
    <PrivateAssets>all</PrivateAssets>
    <IncludeAssets>runtime; build; native; contentfiles; analyzers; buildtransitive</IncludeAssets>
  </PackageReference>
</ItemGroup>
Enter fullscreen mode Exit fullscreen mode

This is what allows us to generate migrations code via the command line.

If you do not have it installed already you also need the EF Core CLI tool. It can be installed by running dotnet tool install --global dotnet-ef in the terminal.

Next we can generate the migrations, for a place to put them I've added a Generated folder in the root of the Persistence project.

Assuming you are in the root of the persistence project with your terminal you can call this command:

dotnet ef migrations add CreateTables --context MovieContext -o .\Generated\ -s ..\TwentyFourDays\TwentyFourDays.csproj
Enter fullscreen mode Exit fullscreen mode

So we give it a name (CreateTables), a reference to the Context, an output path and a source project path pointing to the website project.

Now we have the generated migration:

Image description

The final thing to do, is hook it up so the website runs any pending migrations on startup (otherwise you'd need to do another CLI tool command each time you want to apply migrations).

For this we will use an Umbraco INotificationHandler targeting the UmbracoApplicationStartedNotification.

In a new Migrations/MovieMigration.cs file I've added:

using Microsoft.EntityFrameworkCore;
using Microsoft.Extensions.Logging;
using TwentyFourDays.Persistence.DbContexts;
using Umbraco.Cms.Core.Events;
using Umbraco.Cms.Core.Notifications;

namespace TwentyFourDays.Persistence.Migrations;

public class MovieMigration : INotificationAsyncHandler<UmbracoApplicationStartedNotification>
{
    private readonly MovieContext _movieContext;
    private readonly ILogger<MovieMigration> _logger;

    public MovieMigration(MovieContext movieContext, ILogger<MovieMigration> logger)
    {
        _movieContext = movieContext;
        _logger = logger;
    }

    public async Task HandleAsync(UmbracoApplicationStartedNotification notification, CancellationToken cancellationToken)
    {
        _logger.LogInformation("Starting movie migrations");
        var pendingMigrations = await _movieContext.Database.GetPendingMigrationsAsync(cancellationToken);

        if (pendingMigrations.Any())
        {
            _logger.LogInformation($"Found pending movie migrations: {string.Join(',', pendingMigrations)}");
            await _movieContext.Database.MigrateAsync(cancellationToken);
        }
        _logger.LogInformation("Completed movie migrations");
    }
}
Enter fullscreen mode Exit fullscreen mode

Which is finally added in the composer as well:

builder.AddNotificationAsyncHandler<UmbracoApplicationStartedNotification, MovieMigration>();
Enter fullscreen mode Exit fullscreen mode

Now I can start the site and in the log I see it runs the migration and if I check out my database I now have the tables:

Image description

If you want to follow along the code, here is the repo at this point: https://github.com/jemayn/efcore-and-uibuilder/tree/TableMigrations

Importing content

At this point we have the database setup as we want, and we have an excel sheet with data we want to import.

Next we need to look at parsing the data and writing to the database.

For parsing the Excel file we can use the EPPLus package, but to be on the free license it has to be version 4.5.3.3, so we start by adding that:

<PackageReference Include="EPPlus" Version="4.5.3.3" />
Enter fullscreen mode Exit fullscreen mode

Next we set up a MoviesImportService that can handle all the parsing logic:

using Microsoft.Extensions.Hosting;
using Microsoft.Extensions.Logging;
using OfficeOpenXml;
using Umbraco.Cms.Core.Extensions;

namespace TwentyFourDays.Persistence.Services;

public class MoviesImportService
{
    private readonly ILogger<MoviesImportService> _logger;
    private readonly IHostEnvironment _hostEnvironment;

    public MoviesImportService(ILogger<MoviesImportService> logger, IHostEnvironment hostEnvironment)
    {
        _logger = logger;
        _hostEnvironment = hostEnvironment;
    }

    public async Task Import()
    {
        _logger.LogInformation("Starting movies import");
        var excelPath = Path.Combine(_hostEnvironment.MapPathContentRoot(Umbraco.Cms.Core.Constants.SystemDirectories.Data), "chatgpt-25-christmas-movies.xlsx");

        if (!File.Exists(excelPath))
        {
            _logger.LogError($"Couldn't find movies file on path: {excelPath}");
            return;
        }

        // Adapted from https://www.c-sharpcorner.com/article/using-epplus-to-import-and-export-data-in-asp-net-core/
        await using var fileStream = File.Open(excelPath, FileMode.Open);

        using var package = new ExcelPackage(fileStream);

        var worksheet = package.Workbook.Worksheets.FirstOrDefault();
        if (worksheet is null)
        {
            _logger.LogError("Couldn't find worksheet");
            return;
        }

        // TODO: Map movies from excel to Movie model

        // TODO: Insert movies to db
        _logger.LogInformation("Import complete");
    }
}
Enter fullscreen mode Exit fullscreen mode

In my case the Excel sheet is in the websites /umbraco/Data folder so I can easily target its path via some Umbraco extension methods and constants as shown above:

Image description

Next we add some methods that helps parse through this data:

private static IEnumerable<Movie> MapMoviesFromExcel(ExcelWorksheet worksheet)
{
    var dealers = new List<Movie>();
    var rowCount = worksheet.Dimension.Rows;

    // Starts on row 2 as row 1 are headers
    for (var row = 2; row <= rowCount; row++)
    {
        var name = worksheet.Cells[row, 1].Value?.ToString()?.Trim() ?? "";
        int.TryParse(worksheet.Cells[row, 2].Value?.ToString()?.Trim(), out var releaseYear);
        var genres = GetGenres(worksheet.Cells[row, 3].Value?.ToString()?.Trim());
        var mainActor = worksheet.Cells[row, 4].Value?.ToString()?.Trim() ?? "";

        dealers.Add(new Movie
        {
            Name = name,
            ReleaseYear = releaseYear,
            Genres = genres,
            MainActor = mainActor
        });
    }

    return dealers;
}

private static List<MovieGenre> GetGenres(string? value)
{
    var movieGenres = new List<MovieGenre>();

    if (string.IsNullOrWhiteSpace(value))
    {
        return movieGenres;
    }

    var genres = value.Split(',');

    foreach (var genre in genres)
    {
        var trimmedGenre = genre.Trim();

        if (string.IsNullOrWhiteSpace(trimmedGenre))
        {
            continue;
        }

        movieGenres.Add(new MovieGenre
        {
            Name = trimmedGenre
        });
    }

    return movieGenres;
}
Enter fullscreen mode Exit fullscreen mode

So we just get the content of each field, and map it into the Movie model. Only special thing is we take the content of the genre field - split on comma and create a new list of MovieGenre that gets added to the Movie.

The final step is to insert the movies into the database, for that we will create a new repository at Repositories/MovieRepository.cs:

using TwentyFourDays.Persistence.DbContexts;
using TwentyFourDays.Persistence.Models;
using Umbraco.Cms.Persistence.EFCore.Scoping;

namespace TwentyFourDays.Persistence.Repositories;

public class MovieRepository
{
    private readonly IEFCoreScopeProvider<MovieContext> _scopeProvider;

    public MovieRepository(IEFCoreScopeProvider<MovieContext> scopeProvider)
    {
        _scopeProvider = scopeProvider;
    }

    public async Task Insert(Movie movie)
    {
        using var scope = _scopeProvider.CreateScope();

        await scope.ExecuteWithContextAsync<Task>(async db =>
        {
            var moviesFromDb = db.Movies.ToList();

            var movieExists = moviesFromDb.FirstOrDefault(x => x.Id == movie.Id);

            if (movieExists is not null)
            {
                return;
            }

            var movieGenresFromDb = db.MovieGenres.ToList();
            var movieGenreList = new List<MovieGenre>();

            foreach (var movieGenre in movie.Genres)
            {
                // If a genre of the same name already exists then we just use that as to not have duplicates
                var exists = movieGenresFromDb.FirstOrDefault(x => x.Name == movieGenre.Name);
                movieGenreList.Add(exists ?? movieGenre);
            }

            movie.Genres = movieGenreList;

            db.Movies.Add(movie);
            await db.SaveChangesAsync();
        });

        scope.Complete();
    }
}
Enter fullscreen mode Exit fullscreen mode

For each movie we check if one with the same ID exists and then return it right away. In the case of calling it with the Excel sheet data that will never happen as they don't have an ID, but this repo could be called from other code.

We also need to check if a movieGenre with the same name exists, as otherwise we will have lots of duplicates.

It would technically be better to bulk insert performance wise, but then we would need to manage the ids of all movie genres ourselves.

Now we can finish up the import service:

var movies = MapMoviesFromExcel(worksheet);

foreach (var movie in movies)
{
    await _movieRepository.Insert(movie);
}
Enter fullscreen mode Exit fullscreen mode

And add them to the Composer:

builder.Services.AddTransient<MoviesImportService>();
builder.Services.AddTransient<MovieRepository>();
Enter fullscreen mode Exit fullscreen mode

Finally we can call the importservice from somewhere - for quick testing I added an API controller, but down the line it would probably be something like a Hangfire job or some other button that can be pushed in the backoffice.

After triggering the import, data is populated in the database tables:

Image description

Image description

Image description

If you want to follow along the code, here is the repo at this point: https://github.com/jemayn/efcore-and-uibuilder/tree/DataImport

Top comments (0)

Read next

jackhatetitanic profile image

CSS Grid Layout Tutorial

悦者生存 -

rutamstwt profile image

Langchain Retrieval

Rutam Bhagat -

scofieldidehen profile image

Why I failed in Tech 

Scofield Idehen -

josemiguelsandoval profile image

¿Cómo crear una base de datos de PostgreSQL en Mac?

Jose Miguel Sandoval -