EF Core: Execute a SQL Query on a different Database How to execute raw SQL queries to different databases and/or non-mapped tables with Entity Framework Core using the HasNoKey and FromSqlRaw methods

BuildWebHost - Unable to create an object of type ApplicationDbContext error in EF Core 2.x - How to Fix

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.

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.

For additional info regarding the DbContext class, we strongly suggest to read the official documentation on Microsoft Docs.

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:

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:

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:

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:

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.

 

About Ryan

IT Project Manager, Web Interface Architect and Lead Developer for many high-traffic web sites & services hosted in Italy and Europe. Since 2010 it's also a lead designer for many App and games for Android, iOS and Windows Phone mobile devices for a number of italian companies. Microsoft MVP for Development Technologies since 2018.

View all posts by Ryan

Leave a Reply

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.