Coalesce and Dataverse

What Is Coalesce?

Coalesce is an open-source framework created by IntelliTect that accelerates the development of ASP.NET Core web applications. It automates many repetitive tasks by generating Data Transfer Objects (DTOs), API controllers, and TypeScript files directly from your Entity Framework Core data model. Coalesce allows you as a developer to quickly build interactive web applications with a modern front-end powered by Vue.js and Vite. Coalesce is also highly extensible, offering robust security features and the flexibility to override default behaviors, making it a solid tool for rapid development.

What Is Dataverse?

Dataverse is a database platform that integrates seamlessly with Microsoft products, centralizing data from various sources like Dynamics, Salesforce, and SharePoint. It ensures secure, compliant data management with robust role-based access control. Built on Azure, Dataverse simplifies business operations through pre-defined entities. Custom fields are also able to be included in existing entities allowing for further customization.

But Can You Use Both Together?

Yes! Coalesce and Dataverse can used be in conjunction together to speed up and streamline web app development especially when your web application is tied directly with Microsoft products. I was recently tasked with building out an authentication system and authorization system using Dataverse and it was very successful! This blog serves as an example way to initially start using Coalesce and Dataverse together and can definitely be expanded on in the future. Let’s do this!

Creating a Coalesce Web App

You can create a Coalesce Web App by following this introduction here: https://intellitect.github.io/Coalesce/stacks/vue/getting-started.html

Initial Setup Connecting to Dataverse

In order to connect to Dataverse, you will need a Dataverse connection string. Navigate to your *.Web‘s appsettings.json and observe the following “ConnectionStrings:DefaultConnection” key. Replace the value with your connection string to your Dataverse database.

{
  "ConnectionStrings": {
    "DefaultConnection": "Server=(localdb)\\MSSQLLocalDB;
Database=CoalesceDataverseSample;Trusted_Connection=True;
TrustServerCertificate=True;"
  }
}Code language: PHP (php)

Identify the following from inside your *.Web‘s Program.cs file. Delete the line where it says db.Database.Migrate(); since Dataverse databases are read-only. We will only be able to read the data within it; all write edits will need to be done within Dynamics or another Dataverse editor. Sorry, no writing this way!

#region Launch

// Initialize/migrate database.
using (var scope = app.Services.CreateScope())
{
    var serviceScope = scope.ServiceProvider;

    // Run database migrations.
    using var db = serviceScope.GetRequiredService<AppDbContext>();
    // Delete this line "db.Database.Migrate();"
}

app.Run();
#endregionCode language: PHP (php)

Next, navigate to the pre-generated AppDbContext.cs file within the *.Data project. Delete the line where it says public DbSet<Widget> Widgets => Set<Widget>(); we won’t need to use the pre-generated widget model.

namespace CoalesceDataverseSample.Data;

[Coalesce]
public class AppDbContext : DbContext
{
    // Delete this line public DbSet<Widget> Widgets => Set<Widget>();

    public AppDbContext() { }

    public AppDbContext(DbContextOptions options) : base(options) { }

    protected override void OnModelCreating(ModelBuilder builder)
    {
        base.OnModelCreating(builder);

        // Remove cascading deletes.
        foreach (var relationship in builder.Model.GetEntityTypes().SelectMany(e => e.GetForeignKeys()))
        {
            relationship.DeleteBehavior = DeleteBehavior.Restrict;
        }
    }
}Code language: HTML, XML (xml)

Lastly, in any terminal within your *.Web project run Coalesce (dotnet coalesce). Be sure to set the *.Web project as your default start up project!

dotnet coalesce

Checkpoint Charlie

At this point, you will be able to launch and debug your Coalesce Web App. Navigate around and make yourself at home in your new Coalesce Web App Home Page!

Using Entity Framework Cohesively with Dataverse

Great! Now that you have your Coalesce Web App started, put Dataverse to good use! It’s time to create a Model in your *.Data project. This Model needs to exactly match the name of the Dataverse table name. If you don’t name it the same, Entity Framework will not be able to do its magic with Dataverse. For this sample, I will be using the dbo.Contact table to demonstrate how to connect and retrieve this data.

I will create a Model Contact.cs in my *.Data Models folder. Ensure to use the [Coalesce] tag and any standard Entity Framework syntax for defining this model. Ensure the class properties are the same as how they are defined in Dataverse. They are not case-sensitive however they do need to contain the same data type and name. Here is my Contact.cs file. You can see the [Key] and [Required] annotations. In our specific case, the Guid AccountId is the primary key to the Contact table.

namespace CoalesceDataverseSample.Data.Models;

[Coalesce]
public class Contact
{
    [Key]
    [Required]
    public required Guid AccountId { get; set; }

    [Required]
    public required string FullName { get; set; }

    [Required]
    public required string OwnerIdName { get; set; }

    public string? AccountIdName { get; set; }

}Code language: JavaScript (javascript)

Navigate to your AppDbContext.cs file and add your necessary Models. After adding my new Model this is what my AppDbContext.cs file looks like.

using CoalesceDataverseSample.Data.Models;

namespace CoalesceDataverseSample.Data;

[Coalesce]
public class AppDbContext : DbContext
{
    public DbSet<Contact> Contact { get; set; }
    public AppDbContext() { }

    public AppDbContext(DbContextOptions options) : base(options) { }

    protected override void OnModelCreating(ModelBuilder builder)
    {
        base.OnModelCreating(builder);

        // Remove cascading deletes.
        foreach (var relationship in builder.Model.GetEntityTypes().SelectMany(e => e.GetForeignKeys()))
        {
            relationship.DeleteBehavior = DeleteBehavior.Restrict;
        }
    }
}Code language: HTML, XML (xml)

Then run Coalesce while in your *.Web project.

dotnet coalesce

PS C:\Users\MatthewSzymanski\Desktop\repos\CoalesceDataverseSample\CoalesceDataverseSample.Web> dotnet coalesce
Starting Coalesce 4.0.0-alpha.20231221.1, running under .NET 8.0.3
https://github.com/IntelliTect/Coalesce

Working in 'C:\Users\MatthewSzymanski\Desktop\repos\CoalesceDataverseSample', using 'coalesce.json'
[i:0.180] Loading Projects:
[i:0.204]    C:\Users\MatthewSzymanski\Desktop\repos\CoalesceDataverseSample\CoalesceDataverseSample.Data\CoalesceDataverseSample.Data.csproj
[i:1.108]    C:\Users\MatthewSzymanski\Desktop\repos\CoalesceDataverseSample\CoalesceDataverseSample.Web\CoalesceDataverseSample.Web.csproj
[i:2.397] Gathering Types
[i:3.138] Analyzing 5 Types
[i:3.551] Starting Generation
[i:3.592] Regenerated: CoalesceDataverseSample.Web/src/api-clients.g.ts
[i:3.595] Regenerated: CoalesceDataverseSample.Web/src/viewmodels.g.ts
[i:3.596] Regenerated: CoalesceDataverseSample.Web/src/models.g.ts
[i:3.606] Regenerated: CoalesceDataverseSample.Web/src/metadata.g.ts
[i:3.890] Generated: CoalesceDataverseSample.Web/Api/Generated/ContactController.g.cs
[i:3.895] Generated: CoalesceDataverseSample.Web/Models/Generated/ContactDto.g.cs
[w:3.900] Deleting C:\Users\MatthewSzymanski\Desktop\repos\CoalesceDataverseSample\CoalesceDataverseSample.Web\Models\Generated\WidgetDto.g.cs because it was not in the generation outputs.
[w:3.905] Deleting C:\Users\MatthewSzymanski\Desktop\repos\CoalesceDataverseSample\CoalesceDataverseSample.Web\Api/Generated\WidgetController.g.cs because it was not in the generation outputs.
[i:3.906] Generation Complete
PS C:\Users\MatthewSzymanski\Desktop\repos\CoalesceDataverseSample\CoalesceDataverseSample.Web>
Code language: JavaScript (javascript)

Now that you have your Model and are connected to Dataverse, you can write queries till your heart is content!

Creating a Coalesce Service

In order for Coalesce to know when to do all of its “coalescing” we need to create a Service and denote it with the [Coalesce] and [Service] annotations. Here is the Service I created for my Contact Model.

using CoalesceDataverseSample.Data.Models;

namespace CoalesceDataverseSample.Data.Services;

[Coalesce]
[Service]
public class ContactService(AppDbContext appDb)
{
    public AppDbContext AppDb { get; } = appDb;

    [Coalesce]
    public async Task<List<Contact>> GetContactsAsync()
    {
        var query = AppDb.Contact.AsNoTracking().IgnoreQueryFilters()
.Where(c => c.FullName == "ClientPortal Contact");
        Console.WriteLine(await query.ToListAsync());
        return await query.ToListAsync();
    }
}Code language: JavaScript (javascript)

Once your service is created you will need to register your service within your Program.cs file.

.
.
#region Configure Services

var services = builder.Services;

services.AddDbContext<AppDbContext>(options => options
    .UseSqlServer(builder.Configuration.GetConnectionString("DefaultConnection"), 
opt => opt
        .EnableRetryOnFailure()
        .UseQuerySplittingBehavior(QuerySplittingBehavior.SplitQuery)
    )
    // Ignored because it interferes with the construction of 
    // Coalesce IncludeTrees via .Include()
    .ConfigureWarnings(
warnings => warnings.Ignore(CoreEventId.NavigationBaseIncludeIgnored))
);

services.AddCoalesce<AppDbContext>();
services.AddScoped<ContactService>(); // Register your service here.

services
    .AddMvc()
    .AddJsonOptions(options =>
    {
        options.JsonSerializerOptions.PropertyNamingPolicy = JsonNamingPolicy.CamelCase;
        options.JsonSerializerOptions.ReferenceHandler = ReferenceHandler.IgnoreCycles;
        options.JsonSerializerOptions.DefaultIgnoreCondition = JsonIgnoreCondition.WhenWritingNull;
    });

services.AddAuthentication(CookieAuthenticationDefaults.AuthenticationScheme)
        .AddCookie();
.
.Code language: PHP (php)

After registering a Service, use your terminal in your *.Web directory to run dotnet coalesce. Coalesce will then proceed to orchestrate the generation of all the TypeScript View Models and APIs needed for a successful web application.

Bring It All Together

In your App.vue file, update your script to use your newly generated View Model and Model. In the following sample I will be creating a new ContactServiceViewModel and use my Contact Model.

.
.
<!-- Within your template add your desired data -->
<v-app-bar color="primary" density="compact">
      <v-app-bar-nav-icon @click.stop="drawer = !drawer" />
      <v-toolbar-title>
        <router-link to="/" style="color: inherit">
          Welcome {{ ContactData[0]?.fullName }}!
        </router-link>
      </v-toolbar-title>
    </v-app-bar>
.
.
.
.
<script setup lang="ts">
import { ContactServiceViewModel } from './viewmodels.g';
import { ref } from 'vue';
import { Contact } from './models.g';
const drawer = ref<boolean | null>(null);
const contactService = new ContactServiceViewModel();
Promise.all([contactService.getContacts()])
  .then(() => {

  });
const ContactData: Ref<Contact[]> = computed(() => {
  return contactService.getContacts?.result ?? [];
});
</script>
.
.Code language: HTML, XML (xml)

Wrapping Things Up

Boom. Just like that you can build out a Coalesce Web App while connecting Microsoft Dataverse and Entity Framework. Not too shabby!

Check out these cool links below for more information.
https://intellitect.com/blog/demystified-coalesce-framework/
https://intellitect.github.io/Coalesce
https://learn.microsoft.com/en-us/aspnet/entity-framework
https://learn.microsoft.com/en-us/power-apps/maker/data-platform/data-platform-intro