Using data from MSSQL linked server with EF Core

My previous blog post Querying MySQL from SQL Server using linked server introduced how to link MySQL database to MSSQL and how to write queries that gather data from local and linked server. This blog post demonstrates how to get data mixed from those sources to Entity Framework Core.

Using data from MSSQL linked servers

Linked servers are not directly supported by Entity Framework Core at LINQ level. There are few options how to handle data in linked servers:

• Custom SQL – we can write custom SQL queries for DbContext. On positive side we have full control over all SQL we want to run. On negative side we have custom SQL and we have to go around DbSets and create all kind of nasty hacks.
• Custom views – we can hide querying from linked servers to views and have DbSets like we usually do. It needs more work on database side but we don’t have to hack DbContext.

In this post I don’t stop on custom SQL approach as this is same as running custom SQL queries through DbContext and it’s well covered in many other public sources. Also I don’t prefer this approach as too much database gets into application code.

NB! This blog post doesn’t cover data modification in linked server. It seems like simple topic but there are actually many complexities to get over. In my practice data from linked servers is only for displaying it as otherwise there are good chances that direct changes to data will conflict with business rules set by other systems.

Sample customer tables

All the following code is based on my blog post Querying MySQL from SQL Server using linked server. I had two customers databases – one on MSSQL and other on MySQL. MySQL database was linked to MSSQL. The following screenshot shows customers table in both databases. Some fields are matching and some fields are different.

You can find guidance about setting up linked server from my blog post Querying MySQL from SQL Server using linked server.

Creating view with data from local and linked server

Using SQL Server Management Studio (SSMS) it’s easy to write a query to mix data from both customer tables shown above.

SELECT
    c.FirstName,
    c.LastName,
    crm_c.credit_rating as CreditRating
FROM 
    Customers c
    LEFT JOIN MYSQLCRM...customers crm_c ON
        c.ssn = crm_c.ssn
ORDER BY
    crm_c.credit_rating,
    c.LastName,
    c.FirstName

We take all fields from customers table on MSSQL and ask credit rating field from customers table on MySQL. It works like charm if link between databases is set up and configured appropriately.

For EF Core we want to have DbSet so we don’t need to mess with custom SQL in our code. The closer we will stay to DbSet pattern the better it is. Happily EF Core doesn’t care if data is coming from table or view. Let’s take the SQL query shown above and save it as view to MSSQL.

CREATE VIEW [dbo].[VW_CUSTOMERS]
AS
    SELECT        
        c.Id,
        c.FirstName,
        c.LastName,
        c.Ssn,
        c.BillingAddress,
        crm_c.credit_rating as CreditRating
    FROM
        dbo.Customers c
        LEFT JOIN MYSQLCRM...customers crm_c ON
            c.Ssn = crm_c.ssn
GO

We can open it in SSMS and see table with results.

There’s one thing left to do – we have to make sure that deletes to VW_CUSTOMERS view doesn’t affect linked database. For this we can use INSTEAD OF DELETE trigger. This trigger is run when rows are deleted from view and it replaces default delete behavior.

CREATE TRIGGER [dbo].[VW_CUSTOMERS_DELETE]
   ON [dbo].[VW_CUSTOMERS]
   INSTEAD OF DELETE
AS 
BEGIN

    SET NOCOUNT ON

    DELETE FROM Customers WHERE Id IN (SELECT Id FROM deleted)

END

What happens without this trigger? Without this trigger MSSQL detects the situation it cannot handle – modification to multiple tables. The result is error and cancelled delete operation.

Using linked server table from DbContext

To demonstrate using data from linked server with EF Core I created simple customer class based on VW_CUSTOMERS view.

public class SalesCustomer
{
    public int Id { get; set; }
    public string FirstName { get; set; }
    public string LastName { get; set; }
    public string Ssn { get; set; }
    public string BillingAddress { get; set; }
    public int? CreditRating { get; set; }
}

We also need DbContext class to communicate with database. It looks almost like any other DbContext but there’s one trick – CreditRating property is marked as read-only. Of course, to make DbContext use VW_CUSTOMERS view instead of looking for SalesCustomer table we have to call ToTable() method on SalesCustomer entity.

public class SalesDbContext : DbContext
{
    public SalesDbContext(DbContextOptions<SalesDbContext> options) : base(options)
    {
    }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<SalesCustomer>().ToTable("VW_CUSTOMERS");
        modelBuilder.Entity<SalesCustomer>()
                    .Property(p => p.CreditRating)
                    .ValueGeneratedOnAddOrUpdate();

        base.OnModelCreating(modelBuilder);
    }

    public DbSet<SalesCustomer> Customers { get; set; }
}

We can create more properties based on fields in linked tables but we have to mark them all as read-only to avoid conflicting changes to databases owned by other systems.

Wrapping up

Although MSSQL linked servers are not something we are using everyday and EF Core doesn’t have out-of-box tooling for it, we can still work out views and triggers to make mixed data from local and linked server visible as a table for EF Core. We marked customer entity properties that come from linked server as read-only to avoid making changes to linked table. This is usual requirement if linked table belongs to some other system that has its own business rules. We left all complex stuff to database and on EF Core we are using views as tables.

The post Using data from MSSQL linked server with EF Core appeared first on Gunnar Peipman - Programming Blog.