Blazor Helpers Server App: Reverse Reverse Engineering with Entity Framework
blazor blazor entity-framework csharp blazor-server reflection
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
- Entity Framework
- Entity Framework Core tools reference - Package Manager Console in Visual Studio
- EF Reverse Engineering
- Entity Framework Tutorial
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 |