If you've stumbled upon this post there's a high chance that you're a ASP.NET Core Developer working on a Web Application accessing a SQL database using Entity Framework Core - the open source object–relational mapping (ORM) framework for ADO.NET which we've talked about several times in this blog: more specifically, you need to execute a SQL query on a different database - or involving tables that are not meant to be "mapped" using C# entities.
I had to deal with this issue a couple of times when developing a Web Application that was using Serilog, a neat third-party package that you can use to persist ASP.NET logging into a SQL database: more precisely, since I've configured Serilog to write its logs on a completely different database (and data structure) hosted elsewhere, EF Core knew nothing about that data source. The issue raised the day I needed to perform some queries to that Database using EF Core. In this article we'll see how we can deal with that using EF Core's HasNoKey and FromSqlRaw methods.
Creating the Entity
The first thing I did was to create the BaseLog entity, so that I could deal with the log records in a structured way.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 |
public class BaseLog { public BaseLog() { } [Key] [Required] public long Id { get; set; } public DateTime Timestamp { get; set; } public string LogLevel { get; set; } public string Message { get; set; } public string MessageTemplate { get; set; } public string Exception { get; set; } public string Properties { get; set; } } |
As we can see, the above entity has nothing special - it has the same structure of all other EF Core entities I already had. The only notable difference is the fact that it doesn't have a corresponding table in the Database managed by EF Core - since it's meant to be used against a completely different data source. This means that we'll have to configure it differently within our DbContext.
Configuring the DbContext
As you probably already know (if you use EF Core), the DbContext is a fundamental component of EF Core: in a nutshell, each DbContext instance represents a session within a database that can be used to perform read and / or write queries.
When we need to configure a standard entity (mapped to an actual DB table) in our DbContext.cs class's OnModelCreating method, we usually do something like this:
|
1 2 |
modelBuilder.Entity<SampleEntity>().ToTable("SampleEntity"); public DbSet<SampleEntity> SampleEntities { get; set; } |
The above lines of code will tell EF Core to map the SampleEntity entity to the the [SampleEntity] Database table - possibly even creating it (when using Code-First / migrations approach) if it doesn't exist yet - and also allow us to access the table records using the neat EF Core's Fluent API in the following way:
|
1 |
dbContext.SampleEntities.Where(x => x.Id == "someId"); |
However, in case of our BaseLog entity we don't want to have it mapped to a given DB table, since such table belongs to a different Database. For that very reason, instead of using the above syntax we need to use the following one:
|
1 2 |
modelBuilder.Entity<BaseLog>().ToTable("Logs", t => t.ExcludeFromMigrations()).HasNoKey(); public DbSet<BaseLog> Logs { get; set; } |
As we can see, we did some different things here. More specifically:
- We're telling EF Core to map the BaseLog entity to a table called "Logs": it's worth noting that this mapping won't be used, since the table is not present in the database handled by EF Core: its unique purpose is to allow us to use the ToTable method overload with the ExcludeFromMigrations method (see next bullet point).
- We're telling EF Core to explicitly exlude the BaseLog entity from any migration with the ExcludeFromMigrations method.
- Last but not least, we're telling EF Core to configure the entity type so that it has no keys using the HasNoKey method.
As a matter of fact, the BaseLog table has its own key: the Id Field. However, since that key pertains to an external Database and is handled by Serilog, it's better for EF Core to just ignore it.
Retrieve the data
Now that we have the BaseLog entity and have told EF Core how to properly deal with it, we can use it in the following way:
|
1 2 3 4 5 |
var logsTable = "Serilog.Logs"; var logList = await context.DbContext.Logs .FromSqlRaw($"SELECT * FROM {logsTable} WHERE LogLevel LIKE 'Error'") .OrderByDescending(l => l.Id) .ToListAsync(); |
As we can see, the data retrieval tasks is handled by the FromSqlRaw method: a convenient way to fetch data from any table not explicitly mapped to an entity within the DbContext.
In the above example, Serilog is the name of the external database, and Logs is the name of the DB table (in the [Serilog] database) containing the log records we want to handle with the BaseLog entity: both of them are specified using the logsTable variable, which value is "Serilog.Logs" (using the typical DBName.TableName SQL syntax).
It goes without saying that we need to give the read permissions for the [Serilog] Database to the same DB user we're using in the EF Core ConnectionString, otherwise the query won't work (for permissions issues).
Conclusion
That's it, at least for now: we hope that this small tutorial will be useful to other ASP.NET Core developers looking for a way to use EF Core to handle external databases and/or tables without giving up the tremendous advantages given by entities, the strongly-typed approach they provide, and the EF Core's Fluent API.
