Entity Framework & DbContext Tools

Cofoundry uses Entity Framework Core as its ORM strategy. You are free to use other tools and methodologies for your data access but using EF will allow you to re-use some of the existing Cofoundry features.

DbContext Tools

There are a handful of ways to create a DbContext in EF, but our approach is to hand-code it using a few helpers to cut down on boilerplate. Here's an example:

using Cofoundry.Core;
using Cofoundry.Domain.Data;
using Microsoft.EntityFrameworkCore;

public class MySiteDbContext : DbContext
{
    private readonly DatabaseSettings _databaseSettings;

    public MySiteDbContext(DatabaseSettings databaseSettings)
    {
        _databaseSettings = databaseSettings;
    }

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        optionsBuilder.UseSqlServer(_databaseSettings.ConnectionString);
    }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder
            .UseDefaultConfig()
            .ApplyConfiguration(new CatMap())
            .ApplyConfiguration(new DogMap());
    }

    public DbSet<Cat> Cats { get; set; }
    public DbSet<Dog> Dogs { get; set; }
}

See the SPASite Sample for an example of this.

Constants

• DbConstants.ConnectionStringName: The connection string name for the Cofoundry db connection which you can re-use for other connections to the Cofoundry database.
• DbConstants.DefaultAppSchema: The default/suggested schema for your applications tables "app", but you can alternatively use you own.
• DbConstants.CofoundrySchema: The schema for Cofoundry tables "Cofoundry"

Mapping

• modelBuilder.UseDefaultConfig(): Shortcut that sets the default schema to 'app' and removes the PluralizingTableNameConvention.

CMS Data Class Mapping

You can mix classes from the Cofoundry DbContext into your own DbContext if you want to link to entities like Images, Users or Pages. To pull in the mappings you can use modelBuilder.MapCofoundryContent() in the OnModelCreating override.

Executing Stored Procedures & Raw Sql

IEntityFrameworkSqlExecutor helps you to execute raw SQL statements against an EF DbContext, including methods for executing table queries, scalar queries, commands and commands with an output parameter.

using Cofoundry.Core.EntityFramework;
using Cofoundry.Domain;
using Cofoundry.Domain.CQS;
using System.Data.SqlClient;
using System.Threading.Tasks;

public class SetCatFavoriteCommandHandler 
    : IAsyncCommandHandler<SetCatFavoriteCommand>
    , ILoggedInPermissionCheckHandler
{
    private readonly IEntityFrameworkSqlExecutor _entityFrameworkSqlExecutor;
    private readonly CofoundryDbContext _dbContext;
    
    public SetCatFavoriteCommandHandler(
        IEntityFrameworkSqlExecutor entityFrameworkSqlExecutor,
        CofoundryDbContext dbContext
        )
    {
        _entityFrameworkSqlExecutor = entityFrameworkSqlExecutor;
        _dbContext = dbContext;
    }

    public Task ExecuteAsync(SetCatFavoriteCommand command, IExecutionContext executionContext)
    {
        return _entityFrameworkSqlExecutor
            .ExecuteCommandAsync(_dbContext, 
                "app.Cat_SetFavorite",
                new SqlParameter("@CatId", command.CatId),
                new SqlParameter("@UserId", executionContext.UserContext.UserId),
                new SqlParameter("@IsLiked", command.IsFavorite),
                new SqlParameter("@CreateDate", executionContext.ExecutionDate)
                );
    }
}

Managing transactions

For the most part transaction management isn't necessary because EF wraps SaveChanges() in a transaction for us. For occasions when we need to run a transaction across multiple statements Cofoundry has ITransactionScopeFactory which abstracts EF transactions and additionally allows us to use nested transaction scopes.

public class AddBlogPostHandler
{
    private readonly MyDbContext _myDbContext;
    private readonly ITransactionScopeFactory _transactionScopeFactory;

    public AddBlogPostHandler(
        MyDbContext myDbContext,
        ITransactionScopeFactory transactionScopeFactory
        )
    {
        _myDbContext = myDbContext;
        _transactionScopeFactory = transactionScopeFactory;
    }

    public async Task ExecuteAsync(AddBlogPostCommand command)
    {
        using (var scope = _transactionScopeFactory.Create())
        {
            // .. code to create and add a draft blog post to the context
            await _myDbContext.SaveChangesAsync();

            var publishBlogPostCommand = new PublishBlogPostCommand();
            // ..set some variables on the PublishBlogPostCommand
            await PublishBlogPostAsync(command);

            scope.Complete();
        }
    }
}