DjsBlog: Blazor Helpers Server App -Reverse Reverse Engineering with Entity Framework

Site Calendar

With Entity Framework, you can either use Code-First or Database-First but never the twain shall meet … A work around is presented here.

Entity Framework Links

Getting Started with Entity Framework Core

Using a Microsoft SQL Server (eg on Azure or Local)

Create a relevant project (Eg .NET Console or Blazor)
In the Nuget Package Manager Console run:
- install-package Microsoft.EntityFrameworkCore.SqlServer
- Install-Package Microsoft.EntityFrameworkCore.Tools
Create a SQL Server database and get the connection string.

Code-First v Database-First

So I have a Blazor server app for managing various aspects of a sporting club. The main entities are Members, Helpers, Tasks and Records. The data is defined in classes and so Code-First Entity Framework is used:

Create the class in code ( as a .cs file) … Member class in what follows.
Give it a DbContext eg. public DbSet<Member> Members { get; set; }
Run add-migration Member1 in the Nuget Package Manager Console. Member1 is just a tag and so can be anything.
Run update-database in the Nuget Package Manager Console.

The alternative is to use Database-First with Entity Framework. This involves Reverse Engineering of the classes from the database tables. This means that you create the tables and interrelations there of, in the database via scripting or in SQL Server Management Studio.

Create the database and tables
Run the scaffolding: in the Nuget Package Manager Console.

scaffold-dbcontext -provider Microsoft.EntityFrameworkCore.SqlServer -connection "THE CONNECTION STRING” -OutputDir Models

This will generate a class file in the Models directory for each table in the database. It will also create a DbContext for each class (by appending an s to each table name) in a context file in the Models folder. Its name will be the database name with Context apppended. eg The Helper database generates HelperContext.cs

But …

In the case in hand, I needed to import to a new table, from a CSV file. I have previously done this. Whilst I could have written code within the Blazor app to parse the CSV file, I would have needed to manually define the relevant class properties and then use Code-First. I was though able to directly import the CSV data into a new SQL Server database table using SSMS. Database-First is attractive for this data.

But can you use both Code-First and Database-First in the same EF app? The pundits seem to say NO!

I am open to correction on this though. Please leave comments.

Solution

The workaround was to create a separate .NET Console app and use EF Database-First to import the new table. Then copy the relevant class code to Blazor project, give it a slightly different name and then do the Code-First generation of the table in the database as above. Then go back to the Console app or a new one are repeat the scaffolding. You now have two identical table in the database; one populated, one not; one recognised by Code-First in the Blazor app one not. Just need to copy the populated table data to the newest one. Can do this in the Console app but want to do this without explicitly coding the properties…Enter Reflection

Using Reflection to Copy a Table

Each Table has the same properties in terms on name and type.
- The property types are all string, but other simple types could be handled
So a straight copy (except for the Id which will be autogenerated in the target.).

The Code

using System.Data;
using Microsoft.EntityFrameworkCore;
using System.Reflection;
using ConsoleApp6.Models;

// .NET 6 Console App
Console.WriteLine("Hello, EFs!");

// Copy one table to another in a generic manner (using Reflection) 
// context.TempTable => context.Member
// Where both EF classes for the table types have exact same properties
// And each has a separate DBContext.
using (var context = new HelperLog200Context())
{
    var reks = await context.TempTables.ToListAsync();
    PropertyInfo[] Props = typeof(Member).GetProperties(BindingFlags.Public | BindingFlags.Instance);
    string[] propertyNames = reks[0].GetType().GetProperties().Select(p => p.Name).ToArray();

For each record in the source table create a new target instance

    foreach (var rek in reks)
    {
        // Create a new instance of the target
        Member rec = new Member();
        // Iterate through the source properties

For each property in the source instance get the value:

        foreach (var prop in propertyNames)
        {
            // Get the source property value
#pragma warning disable CS8602 // Dereference of a possibly null reference.
            object? propValue = rek.GetType().GetProperty(prop).GetValue(rek, null);
#pragma warning restore CS8602 // Dereference of a possibly null reference.
            if (propValue != null)
            {

Find the corresponding property in the target and set it:
Could use some succinct Linq here rather than iterating.

                foreach (PropertyInfo property in Props)
                {
                    if (property.Name != prop)
                        continue;
                    if (property.Name != "Id")
                    {
                        if (property.CanWrite)
                        {
                            try
                            {
                                // All properties in both are string except Id
                                if (property.PropertyType.Name == "String")
                                {
                                    string val = (string)propValue;
                                    property.SetValue(rec, val);
                                }
                                // Can handle other types here.
                            }
                            catch (Exception ex)
                            {
                                //CanWrite should take care of this.
                                if (ex.Message == "Property set method not found.")
                                    continue;
                            }
                        }
                    }
                }
            }
        }

Save the embellished instance target to the target database table:

        Console.WriteLine("*"); 
        await context.Members.AddAsync(rec);
        await context.SaveChangesAsync();
    }
}

Link

The above code is in the GitHub Project EFReflectionTableCopy

Conclusion

Wala! That works! The Members table is now available in the Blazor app! 😀

Comment

Whilst there is a simpler way to do this, see the next bog, it does demonstrate using Reflection and Entity Framework Database-First to avoid having to manually define class properties for an existing database table.

	Topic	Subtopic

	*This Category Links*
*Category:*	Blazor Index:	Blazor
Next: >	Blazor Helpers Server App	Dynamic CSS
< Prev:	Blazor Helpers Server App	.NET 6 and Telerik V3 Controls Upgrades

Blog on Microsoft Technologies esp. IoT