DEV Community: Gunnar Peipman

Using data from MSSQL linked server with EF Core

Gunnar Peipman — Thu, 27 Feb 2020 22:39:43 +0000

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.

Querying MySQL from SQL Server using linked server

Gunnar Peipman — Thu, 06 Feb 2020 04:40:56 +0000

SQL Server has interesting feature called Linked Servers. It’s about linking other databases to SQL Server and using their data like it’s local. There are many powerful open-source systems written on PHP and they are mostly using MySQL as database. This blog post shows how to link MySQL database to SQL Server and how to use linked server in SQL queries.

What is linked server?

Linked server in MSSQL is some other database server connected to given one, making it possible to query and manipulate data in other databases. By example, we can link some MySQL database to MSSQL and use it almost like any other database on MSSQL.

Image is taken from MSSQL 2019 documentation page

Linked Servers (Database Engine)

Although communication with linked servers goes through OLE DB providers, there is also OLE DB provider for ODBC and we can use it if our external database doesn’t have OLE DB provider.

NB! Linked server is available for whole SQL Server instance. It means that all SQL Server databases can use linked server to retrieve data.

Linking MySQL to SQL Server

Adding linked server and configuring connection setting is not always easy and straightforward.

To get MySQL linked to SQL Server I needed to create ODBC DSN for MySQL (I named it as MySQLCrm). Before going to next steps, make sure that ODBC data source works.

Follow these steps to link MySQL to SQL Server:

Run SQL Server Management Studio (SSMS)
Connect to your server
Expand Server Objects node from tree at left
Right-click on Linked Servers
Select New Linked Server…

You should see the following dialog (or bit different but the idea remains the same).

NB! Pay extra attention to what you insert to this dialog. With this set of data I made link work. I tried different values and if something is one millimeter wrong then connection fails. It’s damn sensitive dialog.

Connection string to MySQL database should be like shown here:

DRIVER=(MySQL ODBC 8.0 Unicode Driver); SERVER=localhost; PORT=3306; DATABASE=crmlinked; USER=crmuser; PASSWORD=crm_user_password; OPTION=3;

Also pay attention to OPTION=3 – without this I got only errors back when connecting to linked server.

Try to save by clicking OK and see if you can browse to linked server. If you get errors then right-click on server and select properties. Keeping the dialog open, move to Server Options page. Set RPC and RPC Out settings to True.

I’m still not very sure what are these options doing but some of those who had issues with link to MySQL made it work after setting RPC-s to true.

To make querying actually work, we need one more little change that affects whole OLE DB provider and therefore all connections using it. Open Providers node under Linked Servers, right-click on MSDASQL (this is OLE DB provider for ODBC data sources) and select properties.

Check the box before Level Zero only and click OK to save changes.

Querying data from linked server

Querying linked databases is actually easy. Here’s the customers table from crmlinked database in MySQL. This database is linked to my SQL Server.

Syntax for querying linked server is a little bit different from what we usually write on SQL Server. We need to use four-part names: server.database.schema.table. As there’s no schemas on MySQL and connection string specifies database name, then we can leave these out like shown here.

select * from MYSQLCRM...customers

Running this query from SSMS gives the following output. It’s the same data that is in MySQL customers table.

Of course, we can also write more complex queries. Everything that ODBC can handle is okay.

Mixing data from local and linked server

Tables from linked server are not totally isolated from local database tables and views. We can also mix data from local and linked server.

To demonstrate mixed query over local and linked tables let’s write simple query to get all customers from local table and their credit ratings from linked table.

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

Running this query gives us the following output.

As Mark is not present in MySQL database (suppose he is new customer in e-shop and sales department doesn’t have him yet in their CRM system) then he doesn’t have credit rating available. Credit ratings for John and Mary are coming from MySQL in current case.

Using OPENQUERY() to execute query in linked server

The examples above make all data processing on SQL Server. It can be very unoptimal if there’s a lot of data in linked server’s tables. We may want – or usually want – to process some data in linked server before SQL Server starts local processing. For this we have OPENQUERY().

Here’s the example of using OPENQUERY() function in mixed query. We have to specify linked server name and SQL query to run in linked server when calling OPENQUERY(). The query in red is executed in MySQL server and results are read to SQL Server for further processing.

SELECT
    c.FirstName,
    c.LastName,
    crm_c.credit_rating as CreditRating
FROM 
    Customers c
    LEFT JOIN OPENQUERY(MYSQLCRM, '
            SELECT 
                c.credit_rating
            FROM 
                customers p
                left join loyalty_points lp on
                    c.customer_id = lp.customer_id
            WHERE
                lp.points > 1000
        ') crm_c ON
        c.ssn = crm_c.ssn
ORDER BY
    crm_c.credit_rating,
    c.LastName,
    c.FirstName

OPENQUERY() is great way to optimize and speed up mixed queries by running more complex queries over linked server data in linked server.

Wrapping up

Linked servers is powerful feature of SQL Server making it easy for us to use data from external servers. There are two ways to write queries using data from linked servers – direct queries that do all processing on SQL Server and OPENQUERY() that lets us do some processing in remote server. Linked server is integration and therefore using it needs extra care. Planning and performance measuring are must-be activities when planning to use linked server.

The post Querying MySQL from SQL Server using linked server appeared first on Gunnar Peipman - Programming Blog.

Turn EF Core DbContext to real unit of work that hosts repository instances

Gunnar Peipman — Mon, 03 Feb 2020 05:00:30 +0000

My last bold statement was that we don’t need custom unit of work and repository classes with Entity Framework Core. One issue remained unsolved and it was querying part of repositories (yeah, those custom querying methods). After some experiments to get querying interface of repositories to DbContext I worked out something that I kind of like. I managed to make DbContext to work like classic unit of work that hosts repository instances. Here’s my experiment and the solution I worked out.

NB! My goal here is to stay strictly with Entity Framework Core or some other mapper that supports LINQ queries. Replacing ORM is usually extreme case and during my 20+ years in software development I have seen only few cases when it was done. If you prefer NHibernate over EF Core then check out my post NHibernate on ASP.NET Core to see how imitate DbContext with NHibernate.

“Repositorifying” DbContext

Making DbContext similar to typical unit of work that contains repository instances is not easy. One weakness of repository pattern is that it can be easily abused as a querying methods store. I have seen repositories that are thousands of lines long thanks to querying methods. But hey, they are still easy to use in unit tests as they implement interfaces we can mock. DbContext is different beast. It can easily be injected to repositories and other classes but it’s not easy to inject querying part of repositories there.

It would be nice to have something like shown here.

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

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

    // Other DbSets
}

public void DoSomething()
{
    var debtors = dbContext.Customers.ListDebtors();

    // ...

    var date = DateTime.Now.AddDays(-14);
    var newCustomers = dbContext.Customers.ListNewRegistrations(date);
}

There are some obstacles on our way that are hard – if not impossible – to remove with simple tricks. Let’s briefly explore our options:

DbSet is not for inheriting – internals of instantiating DbSet are complex and luckily these complexities are hidden from us. Still these complexities are good enough reason for product group to not support extending of DbSet. Let’s agree with them and abandon the idea of using digital violence to get our will.
Extension methods for DbSet – we can build extension methods for given DbSet and host these methods in some static class in same namespace. It’s the easy way out but it comes with price – we can’t easily mock extension methods. We have to use type isolation and there is limited number of tools with this feature.
Querying methods in DbContext – another easy way out but again there’s price to pay. Piling all querying methods to DbContext will bloat this class. It can get even worse if querying methods call each other. I wouldn’t go with this approach.
Injecting querying interfaces to DbContext class – creates chicken-egg situation because DbContext needs querying classes to be injected and query classes need DbContext to be injected.

Of approaches mentioned above only to are worth to experiment: extension methods for DBSet and querying classes that are injected to DbContext.

Extension methods for DbSet

Extension methods are easiest ones to implement. They work until we stay with pure LINQ. In case of multiple database types and custom SQL queries this approach doesn’t work. Let’s write extension methods for DbSet.

public static class SalesDbContextCustomersExtensions
{
    public static IQueryable<Customer> ListDebtors(this DbSet<Customer> customers)
    {
        return customers.Where(c => c.Balance < 0);
    }

    public static IQueryable<Customer> ListNewRegistrations(this DbSet<Customer> customers, DateTime fromDate)
    {
        return customers.Where(c => c.Created >= fromDate);
    }
}

We have separate class with extension methods and we don’t bloat DbContext with queries. Still these extension methods are easy to use.

public void DoSomething()
{
    var debtors = dbContext.Customers.ListDebtors();

    // ...

    var date = DateTime.Now.AddDays(-14);
    var newCustomers = dbContext.Customers.ListNewRegistrations(date);
}

Those who want to isolate direct access to DbSets can’t use extension methods as marking DbSets protected restricts external access to them.

If you want to go with extension methods then here few more ideas what you can do in data layer: Implementing Query Specification pattern in Entity Framework Core and Readable fluent queries with Entity Framework Core.

Injecting querying classes to DbContext

If extension methods are not an option then we have to introduce querying classes that are injected to DbContext. Like mentioned above there’s a chicken and egg problem, but it’s not hard to solve. Let’s start with intrface and class for querying customers. We use interface because we probably want to use fake version of querying class when writing unit tests.

public interface ICustomerQueries
{
    void SetDbContext(SalesDbContext dbContext);

    IQueryable<Customer> ListDebtors();
    IQueryable<Customer> ListNewRegistrations(DateTime date);
}

public class CustomerQueries : ICustomerQueries
{
    private SalesDbContext _dbContext; 

    public virtual IQueryable<Customer> ListDebtors()
    {
        return _dbContext.Customers.Where(c => c.Balance < 0);
    }

    public virtual IQueryable<Customer> ListNewRegistrations(DateTime date)
    {
        return _dbContext.Customers.Where(c => c.Created >= date);
    }

    public void SetDbContext(SalesDbContext dbContext)
    {
        _dbContext = dbContext;
    }
}

Notice two things. SetDbContext() method – we have to assign DbContext when instance of ICustomerQueries is injected to DbContext. Otherwise there’s no connection between these two classes. Customer queries class has virtual methods. We can keep there pure LINQ methods and extend this class if we need let’s say SqlServerCustomerQueries class that overrides some methods to use SQL Server specific SQL commands.

Here’s our DbContext after changes.

public class SalesDbContext : DbContext
{
    public SalesDbContext(DbContextOptions<SalesDbContext> options, 
                          ICustomerQueries customerQueries) : base(options)
    {
        CustomerQueries = customerQueries;
        customerQueries.SetDbContext(this);
    }

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

    // Other DbSets

    public ICustomerQueries CustomerQueries { get; private set; }

    // Other queries
}

In assigns itself to DbContext property of all injected querying classes. It’s easy but still something we have to remember to do when introducing new quering class for some DbSet. Our previous dummy demo method looks now just a little bit different than before.

public void DoSomething()
{
    var debtors = dbContext.CustomerQueries.ListDebtors();

    // ...

    var date = DateTime.Now.AddDays(-14);
    var newCustomers = dbContext.CustomerQueries
                                .ListNewRegistrations(date);
}

For ASP.NET Core we have to add querying interface and class to dependency injection.

services.AddScoped<ICustomerQueries, CustomerQueries>();

Now we have a bit inconveniet DbContext that actually acts as an unit of work with local instances of repositories.

Supporting database-specific commands

As I previously mentioned we need sometimes database-specific commands. These commands can contain SQL that is understood only by one supported database. It’s clear we cannot run these commands against other types of databases.

Database-specific commands are usual in applications that have reporting. Some ideas about how to implement common reporting with Entity Framework Core are given in my posts Execute raw SQL commands in Entity Framework Core and DataSet and DataTable based ad-hoc reporting with ASP.NET Core.

This is customer queries class we created above with one little change – DbContext is now available for inheriting classes.

public class CustomerQueries : ICustomerQueries
{
    protected SalesDbContext dbContext { get; private set; }

    public virtual IQueryable<Customer> ListDebtors()
    {
        return dbContext.Customers.Where(c => c.Balance < 0);
    }

    public virtual IQueryable<Customer> ListNewRegistrations(DateTime date)
    {
        return dbContext.Customers.Where(c => c.Created >= date);
    }

    public void SetDbContext(SalesDbContext context)
    {
        dbContext = context;
    }
}

We marked querying methods as virtual and there was a reason to do so. Suppose ListDeptors() method is tricky and we need custom SQL for this when using MySQL.

public class MySqlCustomerQueries : CustomerQueries
{
    public override IQueryable<Customer> ListDebtors()
    {
        return dbContext.Customers.FromSql("MySQL specific SQL");
    }
}

Same way we can also build specialized classes for SQL Server, Oracle, Postgre etc.

Mocking querying classes in unit tests

Querying classes we inject using their interfaces can be easily mocked in unit tests. Suppose we have CustomersService class with kind method to zero all debts.

public class CustomersService : ICustomersService 
{
    private readonly SalesDbContext _salesDbContext;

    public CustomersService(SalesDbContext salesDbContext)
    {
        _salesDbContext = salesDbContext;
    }

    public void ClearDebts()
    {
        var debtors = _salesDbContext.CustomerQueries.ListDebtors();

        foreach(var debtor in debtors)
        {
            debtor.Balance = 0;
        }

        _salesDbContext.SaveChanges();
    }
}

Using xUnit and Moq we can write the following test class to check if ClearDebts() sets all debts to zero.

public class CustomerServiceTests
{
    private Mock<ICustomerQueries> _customerQueriesMock;
    private SalesDbContext _dbContext;

    public CustomerServiceTests()
    {
        _customerQueriesMock = new Mock<ICustomerQueries>();

        var options = new DbContextOptionsBuilder<SalesDbContext>()
                                .UseInMemoryDatabase(Guid.NewGuid().ToString())
                                .Options;

        _dbContext = new SalesDbContext(options, _customerQueriesMock.Object);
    }

    [Fact]
    public void ClearDebths_should_set_debtors_balance_to_zero()
    {
        var service = new CustomersService(_dbContext);
        var debtors = new List<Customer>
        {
            new Customer { Id = 1, Name = "John Doe", Balance = -20 },
            new Customer { Id = 2, Name = "Jane Doe", Balance = -5 }
        };
        _customerQueriesMock.Setup(c => c.ListDebtors())
                            .Returns(debtors.AsQueryable());

        service.ClearDebts();

        Assert.Equal(0, debtors[0].Balance);
        Assert.Equal(0, debtors[1].Balance);
    }
}

In real world we should have more tests but for illustration this one is okay. We can create class-level mocks also for other querying classes we need in DbContext. Those who don’t like mocks can use stub classes. It’s easy because querying classes have interfaces.

How this approach works in practice?

After getting to point where I was kind of okay with the solution, of course I was eager to try it out on some real project. Luckily I have one application under development that is perfect candidate for test-drive. It’s very process-centric having complex service classes that make heavy querying.

Some characteristics to understand the system I’m using as lab rat:

two web application projects
one project with background services
shared projects for business logic and data layers
database with ~40 tables (3 tables with many fields)
ten service classes (few of them really big)

I’m using DbContext in applications through interface meaning that application see only what they need to see. I can restrict direct access to DbSet properties and introduce generic Get, Save and Delete methods if needed. Querying classes form nice layer that handles all queries and I can build additional layers behind these if demands on querying happen to grow.

Leaving out the inconvenience on assigning DbContext to querying classes after they are injected to DbContext, I’m okay with my solution. Also it makes unit tests smaller and cleaner. I can easily mock querying objects and I don’t have to write code to insert correct test data to DbContext to make queries return the results that tests expect.

Wrapping up

Getting repositories querying interface to DbContext the way that we may have multiple implementations wasn’t very easy task. To be flexible we had to create querying classes, inject these to DbContext and assign DbContext to their data context property. Even if can safely extend DbSet the problem will be the same – DbSet needs to know DbContext. We created flexible query classes. There’s base class that hosts all pure LINQ-based querying methods. If we don’t have anything database specific then we can use these base classes. If we have something database-specific then we can extend database-specific querying classes from base class and override methods that need custom SQL. We have now DbContext that works as unit of wort containing set of repository instances.

The post Implementing repository querying interface in EF Core DbContext appeared first on Gunnar Peipman - Programming Blog.

Creating subdomains to Azure DNS from ASP.NET Core

Gunnar Peipman — Sun, 02 Feb 2020 20:01:52 +0000

Multitenant wep applications detect current tenant usually by URL checking name of first level folder or subdomain. Usually tenants are defined by subdomain as it is easier to distribute them over data center, cloud services or hosting accounts. This blog post demonstrates how to build Azure DNS service client to create DNS records for multitenant application subdomains.

So, not everybody has domain these days? Yes, right you are. Private users and starting companies doesn’t often have domain. Modern era businesses sometimes even doesn’t bother having domain. They are doing sales and marketing in social media channels and this is where their customers are.

Those who don’t have domain and those who don’t know how to make DNS entries usually need some solution by us. Our service is hosted on some domain anyway and I’m confident we know how to create subdomains and DNS records. For users who need subdomain we have to set subdomain up also in our DNS server.

Creating DNS record for Azure DNS service is something we luckily can automate.

Azure DNS terms

Before we get started let’s define DNS and Azure DNS service related terms.

DNS zone – any distinct, contiguous portion of the domain name space in the DNS for which administrative responsibility has been delegated to a single manager. By example, gunnarpeipman.com is one DNS zone in my Azure DNS service.
DNS record set – collection of records in a zone that have the same name and are the same type. Record set can contain one or more mappings between host name and IP-addresses. blah.gunnarpeipman.com can be record set because it points to two different IP-addresses. Also jekyll.gunnarpeipman.com is record set although it contains one CNAME record.
DNS record – mapping in record set. Every record matches host name with some IP address or other host name. There are also other types of records available but we don’t need these right here.

With first DNS wisdoms inside our heads it’s time to jump in make hands dirty.

Azure DNS service

Azure DNS service is for managing DNS zones and records you own. It’s simple, reliable, stable and performant. The service relies on Microsoft global network of DNS servers.

Microsoft is not domain registrar! Microsoft doesn’t provide domain registrar services on Azure cloud. If you need new domain then it must be registered with some ISP who provides domain registration service. Your domain will be there where you registered it. To use Azure DNS service you need to configure your domain DNS servers at the site where you registered the domain.

What we will build here is shown on the screenshot below. Suppose we are building administration application for our multitenant SaaS application. When new tenant is added by admin (or why not from self-service portal by new customer) we want to create subdomain for this new tenant automatically.

Screenshot below demonstrates how it looks. We have Azure app service with host name demomultitenant.azurewebsites.net with multiple DNS records pointing to it. If our multitenant service has host name bigsaasapp.com then we have subdomains like contoso.bigsaasapp.com, northwind.bigsaasapp.com etc.

We don’t need subdomain and CNAME record for customers who want to use their own subdomain (like bigsaas.bigcorp.com). We don’t control those domains and this is up to customer to set everything up on their side.

You need Azure AD service principal. To make this example work you need service principal for your web application. I don’t cover these steps here, so please jump to documentation page How to: Use the portal to create an Azure AD application and service principal that can access resources if you don’t know how to create service principal on Azure AD. After creating service principal you have to make sure it has permissions to Azure DNS service.

Configuration for Azure DNS

Before everything else we need Azure DNS service configuration. For this I created simple class.

public class DnsServerSettings
{
    public string TenantId { get; set; }
    public string ClientId { get; set; }
    public string ClientSecret { get; set; }
    public string SubscriptionId { get; set; }
    public string ResourceGroupName { get; set; }
    public string ZoneName { get; set; }
}

Here’s the example of configuration in appSettings.json file. ID-s here are random, of course.

"DnsServerSettings": {
  "TenantId": "3f2ff00d-9465-4dca-8c2a-736ac4e94a55",
  "ClientId": "2e60a18a-3760-4f09-9663-4dbe436b2bfc",
  "ClientSecret": "a7s9d69h3denkscxAS7sjndkASJDH",
  "SubscriptionId": "77a81331-9341-4e4f-9c65-e6f0051b52de",
  "ResourceGroupName": "WebAppHosting",
  "ZoneName": "demomultitenant.azurewebsites.net"
}

To make things more convenient I inject DNS service settings to framework-level dependency injection.

var dnsServerSettings = Configuration.GetSection("DnsServerSettings").Get<DnsServerSettings>();
services.AddSingleton(dnsServerSettings);

Single instance of settings is okay as we have one DNS service for all multitenant application subdomains.

DSN service client interface

To have flexibility on coming up with better or more advanced implementation of DNS service client, I created simple interface.

public interface IDnsServiceClient
{
    Task<bool> HasCName(string cName);
    Task CreateCName(string cName);
}

This interface is to carry out two operations on Azure DNS service:

check if given CNAME record is already registered in DNS zone
create new CNAME record to given DNS zone

It’s possible to extend this interface later if there’s need but for this post the interface shown is good enough.

NB! Before going further you need the following NuGet package:

Microsoft.Azure.Management.Dns

DNS service client

As we already get Azure DNS configuration from framework-level dependency injection and we have thin interface to communicate with service, it’s time to build client class we can use in tenant admin application.

public class DnsServiceClient : IDnsServiceClient
{
    private DnsServerSettings _serverSettings;

    public DnsServiceClient(DnsServerSettings serverSettings)
    {
        _serverSettings = serverSettings;
    }

    public async Task CreateCName(string cName)
    {
        throw new NotImplementedException();
    }

    public async Task<bool> HasCName(string cName)
    {
        throw new NotImplementedException();
    }

    protected async Task<ServiceClientCredentials> GetCredentials()
    {
        return await ApplicationTokenProvider.LoginSilentAsync(
                _serverSettings.TenantId,
                _serverSettings.ClientId,
                _serverSettings.ClientSecret
            );
    }
}

The code above is skeleton for Azure DNS client. It does almost nothing useful yet. Still it gets Azure DNS configuration and is able to generate credentials needed to communicate with service.

Adding CNAME record to DNS

First let’s implement CreateCName() method of Azure DNS service client. We habe to create new record set with CNAME record and save it using DnsManagementClient() class.

public async Task CreateCName(string cName)
{
    var credentials = await GetCredentials();
    using (var dnsClient = new DnsManagementClient(credentials))
    {
        dnsClient.SubscriptionId = _serverSettings.SubscriptionId;

        var recordSet = new RecordSet { 
            CnameRecord = new CnameRecord("demoservice.azurewebapps.net"),
            TTL = 14400
        };

        await dnsClient.RecordSets.CreateOrUpdateAsync(
            _serverSettings.ResourceGroupName, 
            _serverSettings.ZoneName, 
            cName, 
            RecordType.CNAME, 
            recordSet
        );
    }
}

It was easy, although call to CreateOrUpdateAsync() may first seem a little strange.

Quering DNS service for CNAME

Querying for existing DNS record is not so easy and straightforward as adding new records. We may have tens of thousands DNS records and it would put too much load to service when it returns all records for zone with one shot. To keep load under control, it uses paging. We have to read next portion of records from service until NextPageLink property of paged result is empty or null.

public class DnsServiceClient : IDnsServiceClient
{
    private DnsServerSettings _serverSettings;

    public DnsServiceClient(DnsServerSettings serverSettings)
    {
        _serverSettings = serverSettings;
    }

    public async Task CreateCName(string cName)
    {
        var credentials = await GetCredentials();
        using (var dnsClient = new DnsManagementClient(credentials))
        {
            dnsClient.SubscriptionId = _serverSettings.SubscriptionId;

            var recordSet = new RecordSet { 
                CnameRecord = new CnameRecord("demoservice.azurewebapps.net"),
                TTL = 14400
            };

            await dnsClient.RecordSets.CreateOrUpdateAsync(
                _serverSettings.ResourceGroupName, 
                _serverSettings.ZoneName, 
                cName, 
                RecordType.CNAME, 
                recordSet
            );
        }
    }

    public async Task<bool> HasCName(string cName)
    {
        var credentials = await GetCredentials();
        using (var dnsClient = new DnsManagementClient(credentials))
        {
            dnsClient.SubscriptionId = _serverSettings.SubscriptionId;

            var page = await dnsClient.RecordSets.ListAllByDnsZoneAsync(
                                _serverSettings.ResourceGroupName, 
                                _serverSettings.ZoneName
                             );

            while (true)
            {
                foreach (var x in page)
                {
                    if (x.Type == "Microsoft.Network/dnszones/CNAME" &&
                        x.Name.ToLower() == cName.ToLower())
                    {
                        return true;
                    }
                }

                if (string.IsNullOrEmpty(page.NextPageLink))
                {
                    break;
                }

                page = await dnsClient.RecordSets.ListAllByDnsZoneNextAsync(page.NextPageLink);
            }

            return false;
        }
    }

Now we are done with Azure DNS service client and we can inject it to dependency injection.

Using DNS service client in ASP.NET Core application

Although you probably have some application service class with tenant adding workflow, I will keep things simple and illustrative. Here’s the Create() method of tenants controller in admin application that uses DNS service client through dependency injection to create new CName record with new tenant.

public async Task<IActionResult> Create(TenantEditModel model)
{
    if(!ModelState.IsValid)
    {
        return View(model);
    }

    if(await _dnsClient.HasCName(model.CName))
    {
        ModelState.AddModelError("CName", "CName already exists");
        return View(model);
    }

    return RedirectToAction(nameof(Index));
}

If CNAME is already taken then admin is taken back to Create view and error for CNAME record is shown.

Wrapping up

When building administrative interface for multitenant applications we usually need to communicate with bunch of external services. One of these is usually some DNS server or service where we may want to add new CNAME record with subdomain for new tenant. We used Azure DNS service for our multitenant application and it wasn’t complex to check for existing CNAME records and add new ones.

The post Creating subdomains to Azure DNS from ASP.NET Core appeared first on Gunnar Peipman - Programming Blog.

Building experimental hybrid Blazor WebAssembly application

Gunnar Peipman — Sat, 01 Feb 2020 06:42:48 +0000

After getting done with Blazor desktop applications I tried to build kind of hybrid Blazor WebAssembly application that can run on desktop and in web equally. Although I failed to make it as one single application, I still got it work with a little different architecture. And what’s best – Blazor desktop applications run already now as self-contained executables. Here’s my experiment.

Warning! WebWindow is experimental technology by Steve Sanderson to host Blazor WebAssembly applications in desktop windows using local browser’s web view. Right now it’s not even sure if WebWindow makes its way to official codebase of Blazor. Play with it, but don’t go live with it yet.

Architecture of shared Blazor WebAssembly application

What I tried to do was architecture like shown on the following screenshot.

Blazor WebAssembly application is implemented as a shared library that contains user interface artifacts like pages, layouts and code-behind files. Also it is possible to host shared interfaces in this project.

WebWindow and ASP.NET Core based web server projects are just for building application for target environment and configuring dependency injection to use implementations for given environment.

Shared application solution

After moving all user interface files to shared project and deleting these files from applications projects I finished up with projects like shown on screenshot. wwwroot folder is necessary as there are static files needed by application. Most important one is index.html as it is different for both applications.

Screenshot clearly shows how applications projects have only wwwroot folder (later more about it), Program and Startup classes and implementation of weather data service. Notice that I managed to move also App.razor to shared project. As application projects doesn’t have any user interface components then I got rid of _Imports.razor files too.

NB! There’s one issue – static assets in shared library are not supported by Blazor on desktop. When publishing these applications we can copy static assets from wwwroot of shared project to desktop application publishing folder. It’s hack, yes, but don’t forget we are messing with pre-alpha and beta level tooling.

Differences in applications

It’s important to see what’s the difference between desktop and web application. I tried first to make total hybrid application – one application running wherever you want, but it turned out to be task over my skills.

First and most important difference is in project file definition – applications use different project SDK.

<!-- Desktop application -->
<Project Sdk="Microsoft.NET.Sdk.Razor">

<!-- Web application -->
<Project Sdk="Microsoft.NET.Sdk.Web">

Here are Program and Startup class of desktop application.

public class Program
{
    public static void Main(string[] args)
    {
        ComponentsDesktop.Run<Startup>("My Blazor App", "wwwroot/index.html");
    }
}

public class Startup
{
    public void ConfigureServices(IServiceCollection services)
    {
        services.AddSingleton<IWeatherService, LocalDiskWeatherService>();
    }

    public void Configure(IComponentsApplicationBuilder app)
    {
        app.AddComponent<App>("app");
    }
}

Let’s compare these two with ones from web application project.

public class Program
{
    public static void Main(string[] args)
    {
        CreateHostBuilder(args).Build().Run();
    }

    public static IWebAssemblyHostBuilder CreateHostBuilder(string[] args) =>
        BlazorWebAssemblyHost.CreateDefaultBuilder()
            .UseBlazorStartup<Startup>();

    public class Startup
    {
        public void ConfigureServices(IServiceCollection services)
        {
            services.AddSingleton<IWeatherService, WebWeatherService>();
        }

        public void Configure(IComponentsApplicationBuilder app)
        {
            app.AddComponent<App>("app");
        }
    }
}

Desktop application uses ComponentDesktop class to initialize program while web application uses Blazor WebAssembly host builder. Startup classes are similar in big part, but ConfigureServices method is different – this is where dependency injection happens.

There are also small differences in index.html files. Let’s start with desktop application.

<!DOCTYPE html>
<html>
<head>
    <meta charset="utf-8" />
    <meta name="viewport" content="width=device-width" />
    <title>MyDesktopApp</title>
    <base href="/" />
    <link href="css/bootstrap/bootstrap.min.css" rel="stylesheet" />
    <link href="css/site.css" rel="stylesheet" />
</head>
<body>
    <app>Loading...</app>

    <script src="framework://blazor.desktop.js"></script>
</body>
</html>

Here is index.html for Blazor web application. Notice how stylesheets are linked from _content folder as Blazor web application supports static assets hosted in component projects. Also compare how Blazor application script is included. Blazor desktop application uses URL starting with framework:// while Blazor web application uses regular web server location.

<!DOCTYPE html>
<html>

<head>
    <meta charset="utf-8" />
    <meta name="viewport" content="width=device-width" />
    <title>BlazorDemoWeb</title>
    <base href="/" />
    <link href="\_content/BlazorDemoShared/css/bootstrap/bootstrap.min.css" rel="stylesheet" />
    <link href="\_content/BlazorDemoShared/css/site.css" rel="stylesheet" />
</head>

<body>
    <app>Loading...</app>

    <div id="blazor-error-ui">
        An unhandled error has occurred.
        <a href="" class="reload">Reload</a>
        <a class="dismiss"></a>
    </div>
    <script src="\_framework/blazor.webassembly.js"></script>
</body>

</html>

With these minor things differences end. Application and its user interface is living in shared project. In both cases application looks the same.

There’s one minor difference – desktop application runs inside operating system window and web application runs in browser.

Blazor desktop application as self-contained executable

After getting things work I wanted to try out one final thing – what happens if I publish desktop application as self-contained executable with assembly trimming enabled. Assembly trimming removed all code from resulting assembly that is not executed by application.

I opened Blazor desktop application folder in command prompt and executed the following command:

dotnet publish -c Release -r win10-x64 /p:PublishSingleFile=true /p:PublishTrimmed=true

Instead of publish folder with many files I got something really small.

Without assembly trimming executable will take ~70MB. Assembly trimming saved us roughly 50% in resulting assembly size.

No WebAssembly JavaScript files for desktop! After publishing and running Blazor desktop application I had one question: where are those JavaScript files that make Blazor possible? I couldn’t find these from publish folder. Well, it seems like Blazor desktop apps are able to load WebAssembly application without need for separate JavaScript files. And framework:// protocol we saw before seems to make the trick. Cool!

Wrapping up

Although not all things work like they should, I was still able to build kind of hybrid Blazor WebAssembly application. User interface is in shared component library and application projects are there to build and publish application for target environment or platform. Suprising moment was that assembly trimming works like charm with Blazor desktop applications and all I have to try out now is to buid a simple installer. Blazor WebWindow is in pre-alpha but already damn powerful toy to play with.

The post Building experimental hybrid Blazor WebAssembly application appeared first on Gunnar Peipman - Programming Blog.

Create fake user for ASP.NET Core controller tests

Gunnar Peipman — Fri, 31 Jan 2020 05:50:42 +0000

I think most of ASP.NET Core applications have authentication enabled. When writing unit tests for controllers we have one set of tests that need authenticated user and other set of tests that need anonymous user. Faking User property of controller is a little bit tricky. This blog post shows how to do it.

Source code available! Source code and sample ASP.NET Core solution for this post is available at my GitHub repository gpeipman/AspNetCoreTests.

Testing controllers with authenticated user

Let’s start with my favorite example of controller action that checks if user is authenticated. For anonymous users we have PublicIndex view and for authenticated users there is default Index view. Both are returned from same Index() action of HomeController.

public IActionResult Index()
{
    if (User.Identity.IsAuthenticated)
    {
        return View();
    }

    return View("PublicIndex");
}

We can write unit test to see if Index view is returned for authenticated user. This solution is borrowed from my blog post Faking Azure AD identity in ASP.NET Core unit tests.

[Fact]
public void Index_should_return_private\view\for_authenticated_user()
{
    var logger = new NullLogger<HomeController>(); 
    var user = new ClaimsPrincipal(new ClaimsIdentity(new Claim[] {
                                        new Claim(ClaimTypes.NameIdentifier, "SomeValueHere"),
                                        new Claim(ClaimTypes.Name, "gunnar@somecompany.com")
                                        // other required and custom claims
                                   },"TestAuthentication"));
    var controller = new HomeController(logger);     
    controller.ControllerContext = new ControllerContext();     
    controller.ControllerContext.HttpContext = new DefaultHttpContext { User = user };

    var result = controller.Index() as ViewResult;
    var viewName = result.ViewName;

    Assert.True(string.IsNullOrEmpty(viewName) || viewName == "Index");
}

This test will pass as all conditions for it to work are met.

NB! Authentication provider is important! If we don’t specify authentication provider (“TestAuthentication” in our case) when building ClaimsIdentity then user is not considered as authenticated. The fact that user has name and name identifier claims available doesn’t matter.

I’m using similar trick also for lightweight custom authentication in ASP.NET Core.

Testing controllers with anonymous user

First test idea that probably comes to our minds is simple test like this.

[Fact]
public void Index_should_return_public_view_for_anonymous_user()
{
    var logger = new NullLogger<HomeController>(); 
    var controller = new HomeController(logger); 

    var result = controller.Index() as ViewResult; 

    Assert.Equal("PublicIndex", result.ViewName);
}

But this test fails with NullReferenceException.

It happens on the line where we check if user is authenticated. But why?

There’s always user. When ASP.NET Core application is running there’s always user available but it is possible that user is not authenticated. But there’s always a user.

To make anonymous user test pass we must provide claims identity with no authentication provider.

[Fact]
public void Index_should_return_public_view_for_anonymous_user()
{
    var logger = new NullLogger<HomeController>(); 
    var user = new ClaimsPrincipal(new ClaimsIdentity()); 
    var controller = new HomeController(logger); 
    controller.ControllerContext = new ControllerContext(); 
    controller.ControllerContext.HttpContext = new DefaultHttpContext { User = user }; 

    var result = controller.Index() as ViewResult; 

    Assert.Equal("PublicIndex", result.ViewName);
}

Now the test will pass.

Moving user matters to extension methods

Our tests pass now but we are not yet done. We have repeated code and only two tests this far. Suppose we have few hundreds of tests one day and they all set up identity for controller like shown above. If we want to change it then it’s few hundred unit tests to change. So, it’s time to make some home cleaning.

To keep identity initialization in one place I added extension methods class for ASP.NET controllers.

public static class ControllerTestExtensions
{
    public static T WithIdentity<T>(this T controller, string nameIdentifier, string name) where T: Controller
    {
        controller.EnsureHttpContext();

        var principal = new ClaimsPrincipal(new ClaimsIdentity(new Claim[]
                        {
                                new Claim(ClaimTypes.NameIdentifier, nameIdentifier),
                                new Claim(ClaimTypes.Name, name)
                                // other required and custom claims
                        }, "TestAuthentication"));

        controller.ControllerContext.HttpContext.User = principal;

        return controller;
    }

    public static T WithAnonymousIdentity<T>(this T controller) where T : Controller
    {
        controller.EnsureHttpContext();

        var principal = new ClaimsPrincipal(new ClaimsIdentity());

        controller.ControllerContext.HttpContext.User = principal;

        return controller;
    }

    private static T EnsureHttpContext<T>(this T controller) where T : Controller
    {
        if (controller.ControllerContext == null)
        {
            controller.ControllerContext = new ControllerContext();
        }

        if (controller.ControllerContext.HttpContext == null)
        {
            controller.ControllerContext.HttpContext = new DefaultHttpContext();
        }

        return controller;
    }
}

Using these extension methods we can make controller tests shorter.

[Fact]
public void Index_should_return_public_view_for_anonymous_user()
{
    var logger = new NullLogger<HomeController>();
    var controller = new HomeController(logger).WithAnonymousIdentity();

    var result = controller.Index() as ViewResult;

    Assert.Equal("PublicIndex", result.ViewName);
}

[Fact]
public void Index_should_return_private_view_for_authenticated_user()
{
    var logger = new NullLogger<HomeController>();
    var controller = new HomeController(logger)
                            .WithIdentity("SomeValueHere", "gunnar@somecompany.com");

    var result = controller.Index() as ViewResult;
    var viewName = result.ViewName;

    Assert.True(string.IsNullOrEmpty(viewName) || viewName == "Index");
}

User related repeated code is gone now and our tests are clean.

Wrapping up

Unit testing ASP.NET Core controllers can be tricky. When web application runs then ASP.NET Core sets up many things automatically and we don’t even notice it. But if we start testing our controllers we will discover these things step by step. Getting User property of controller set up correctly was a little bit tricky but still pretty easy. Using extension methods we were able to keep User property related code in one place and avoid code duplications.

The post Create fake user for ASP.NET Core controller tests appeared first on Gunnar Peipman - Programming Blog.

Exploring WebWindow examples for Blazor on desktop

Gunnar Peipman — Wed, 29 Jan 2020 06:16:59 +0000

Blazor on desktop is one of latest hot topics and .NET Conf: Focus on Blazor only added more fuel to fire. Blazor seems to come everywhere and it’s unstoppable. One of interesting desktop experiments is WebWindow by Steve Sanderson. It’s cross-platform component to make Blazor WebAssembly applications run on desktop. Let’s take a closer look at WebWindow and Blazor on desktop.

Warning! WebWindow is experimental project by Steve Sanderson – one of masterminds behind Blazor. There are no ready-made stable packages or components, no beautiful templates and other luxury like this. Get ready to make hands really dirty with raw bits and bytes!

Why Blazor on desktop?

Blazor WebAssembly needs only rendering engine with WebAssembly support to run and most of modern browsers support WebAssembly. There Blazor applications doesn’t necessarily need web server to run. Web server is just one option to deliver Blazor application so browser can download and run it.

If web server is not necessary then there’s question to ask: can we somehow remove web server from equation and make those Blazor WebAssembly apps look and feel more like desktop application? Blazor on desktop is here to address this question with high chances to answer “yes”.

What is WebWindow?

WebWindow is .NET Core library that makes it possible to run HTML, JavaScript and WebAssembly applications in local browser window. It uses some existing browser or web renderer library to create a window where web application is loaded and run.

WebWindow is cross-platfrom library. Current experimental implementation of WebWindow works on following platforms:

Windows – needs Chromium-based Edge
Linux – uses WebKit
Mac – needs Safari

Here’s how we start Blazor on desktop on all three platforms.

using WebWindows.Blazor;

namespace BlazorDesktopApp
{
    public class Program
    {
        static void Main(string[] args)
        {
            ComponentsDesktop.Run<Startup>("My Blazor App", "wwwroot/index.html");
        }
    }
}

WebWindow is made available on same desktop platforms where .NET Core runs.

Downloading and running WebWindow examples

Experimental WebWindow code lives in GitHub repository SteveSandersonMS/WebWindow.

Clone or download latest WebWindows version from master branch
Run Visual Studio 2019 and update it to latest version
Open WebWindow.Samples.sln file in Visual Studio

There are three projects in samples solution:

HelloWorldApp – simple Hello, World! demonstrating basics of WebWindow
VuewFileExplorer – sample of JavaScript based local file browser
BlazorDesktopApp – running Blazor WebAssembly app on desktop

Let’s see what’s inside those sample applications.

Hello WebView

First project to explore is Hello, world! It is not about Blazor but WebWindow component that hosts HTML and Blazor applications. This is console application that creates new WebWindow and shows local index.html file.

using WebWindows;

namespace HelloWorldApp
{
     class Program
     {
         static void Main(string[] args)
         {
             var window = new WebWindow("My first WebWindow app");
             window.NavigateToLocalFile("wwwroot/index.html");
             window.WaitForExit();
         }
     }
}

WaitForExit() method of WebWindow stops execution of Main() method until windows is closed.

When we run application then it opens in local web view window. The following screenshot shows Hello, World application running on Windows 10.

With this example our first step is made.

Browsing file system of host machine

Next WebWindow example is in project called VueFileExplorer. It makes step further with WebWindow and introduces communication between user interface shown in web view and .NET Core process hosting it. WebWindow defines OnWebMessageReceived event handler and SendMessage command. These two members of WebWindow class enable two-way communication between web view and hosting process.

VueFileExplorer uses Vue.js to build two-pane window to explore file system. Left pane is for files and folders, right pane is for file content like shown on the following screenshot.

When file or folder is clicked then JavaScript sends message to window. This is the Vue app serving user interface and communicating with host application.

var app = new Vue({
     el: '#app',
     data: {
         directoryInfo: null,
         fileInfo: null
     },
     methods: {
         navigateTo: function (relativePath, event) {
             event.preventDefault();
             window.external.sendMessage(JSON.stringify({
                 command: 'navigateTo',
                 basePath: app.directoryInfo.name,
                 relativePath: relativePath
             }));
         },
         showFile: function (fullName, event) {
             event.preventDefault();
             window.external.sendMessage(JSON.stringify({
                 command: 'showFile',
                 fullName: fullName
             }));
         }
     }
});

window.external.receiveMessage(function (messageJson) {
     var message = JSON.parse(messageJson);
     switch (message.command) {
         case 'showDirectory':
             app.directoryInfo = message.arg;
             break;
         case 'showFile':
             app.fileInfo = message.arg;
             break;
     }
});

window.external.sendMessage(JSON.stringify({ command: 'ready' }));

Here’s the fragment of code from Program class demonstrating how WebWindow messages are listened and processed.

static void Main(string[] args)
{
     var window = new WebWindow(".NET Core + Vue.js file explorer");
     window.OnWebMessageReceived += HandleWebMessageReceived;
     window.NavigateToLocalFile("wwwroot/index.html");
     window.WaitForExit();
}

static void HandleWebMessageReceived(object sender, string message)
{
     var window = (WebWindow)sender;
     var parsedMessage = JsonDocument.Parse(message).RootElement;
     switch (parsedMessage.GetProperty("command").GetString())
     {
         case "ready":
             ShowDirectoryInfo(window, Directory.GetCurrentDirectory());
             break;
         case "navigateTo":
             var basePath = parsedMessage.GetProperty("basePath").GetString();
             var relativePath = parsedMessage.GetProperty("relativePath").GetString();
             var destinationPath = Path.GetFullPath(Path.Combine(basePath, relativePath))
                                         .TrimEnd(Path.DirectorySeparatorChar);
             ShowDirectoryInfo(window, destinationPath);
             break;
         case "showFile":
             var fullName = parsedMessage.GetProperty("fullName").GetString();
             ShowFileContents(window, fullName);
             break;
     }
}

Based on message sent from web view the response is built and sent to web view as message. Here’s how showing of file contents is implemented.

private static void ShowFileContents(WebWindow window, string fullName)
{
     var fileInfo = new FileInfo(fullName);
     SendCommand(window, "showFile", null); // Clear the old display first
     SendCommand(window, "showFile", new
     {
         name = fileInfo.Name,
         size = fileInfo.Length,
         fullName = fileInfo.FullName,
         text = ReadTextFile(fullName, maxChars: 100000),
     });
}

SendCommand is simple static method that sends prepared command to web view.

static void SendCommand(WebWindow window, string commandName, object arg)
{
     window.SendMessage(JsonSerializer.Serialize(new { command = commandName, arg = arg }));
}

As we can see then we can use WebWindow to host JavaScript application and make it use host system through messaging.

Blazor desktop application

The pearl of WebWindow example applications is the one that hosts Blazor WebAssembly. The example uses default Blazor WebAssembly application with famous Counter and Fetch data views. Here is the example of weather forecasts we have seen in numerous Blazor demos. Now it is running on your desktop instead of web server.

Where comes the data to weather forecast view if Blazor application is hosted in web window and there’s no web server running? It’s coming straight from disk. Here’s the FetchData page from Blazor desktop application project.

@page "/fetchdata"
@using System.IO
@using System.Text.Json

<h1>Weather forecast</h1>

<p>This component demonstrates fetching data from the server.</p>

@if (forecasts == null)
{
     <p><em>Loading...</em></p>
}
else
{
     <table class="table">
         <thead>
             <tr>
                 <th>Date</th>
                 <th>Temp. (C)</th>
                 <th>Temp. (F)</th>
                 <th>Summary</th>
             </tr>
         </thead>
         <tbody>
             @foreach (var forecast in forecasts)
             {
                 <tr>
                     <td>@forecast.Date.ToShortDateString()</td>
                     <td>@forecast.TemperatureC</td>
                     <td>@forecast.TemperatureF</td>
                     <td>@forecast.Summary</td>
                 </tr>
             }
         </tbody>
     </table>
}

@code {
     WeatherForecast[] forecasts;
     protected override async Task OnInitializedAsync()
     {
         var forecastsJson = await File.ReadAllTextAsync("wwwroot/sample-data/weather.json");
         forecasts = JsonSerializer.Deserialize<WeatherForecast[]>(forecastsJson, new JsonSerializerOptions { PropertyNameCaseInsensitive = true });
     }
     public class WeatherForecast
     {
         public DateTime Date { get; set; }
         public int TemperatureC { get; set; }
         public string Summary { get; set; }
         public int TemperatureF => 32 + (int)(TemperatureC / 0.5556);     }
}

Instead of using HttpClient like examples of server hosted Blazor do, here they are accessing file system to load and deserialize weather forecast data.

In all other means it is the same Blazor application we get when we create new Blazor application. But this time it runs on desktop.

Wrapping up

Blazor WebApplication (formerly known as client-side Blazor) is going through revolutionary times. There are many things happening around it and making Blazor run on desktop is perhaps the most interesting part. Although WebWindow is in early experimental stages and it’s not even clear if this approach will stay here long enough to get to stable version, it is still very promising. WebWindow and desktop support are in my opinion important part of Blazor-everywhere vision.

The post Exploring WebWindow examples for Blazor on desktop appeared first on Gunnar Peipman - Programming Blog.

Start today with Surface Duo development on preview emulator and SDK

Gunnar Peipman — Mon, 27 Jan 2020 05:10:57 +0000

Surface Neo and Surface Duo are new devices by Microsoft, planned to launch for holidays season this year. Surface Neo runs Windows and Surface Duo is based on Android. For Surface Duo there’s already preview tooling and SDK available by Microsoft. Here’s the introduction to Surface Duo development, tools and patterns.

With Surface Neo and Surface Duo Microsoft enters a era of hybrid devices. Both new devices are targeted between mobile phones and tablets. Surface Neo is more on tablets side while Surface Duo is closer to mobile devices.

Save the date! If you are more than interested in new dual-screen experiences and want to see next big announcements when they are made then Microsoft 365 Developer Day on 11th of February is the event for you.

Unique dual-screen approach

Instead of targeting as thin folding line between two screens as possible, Microsoft intentionally decided to have suprisingly wide line. It seems unexpected and irrational at first but when we dig deeper it’s actually damn clever approach. Why? On real device a folding line between screens is wide and narrow at same time. It’s narrow enough to be not annoying for views extended over two screens and it’s wide enough to be a separation line between master-detail views where master part of view is opened on one and details part on second screen.

Dual nature in user experiences is something that makes Surface Neo and Surface Duo special. It also introduces new concepts in UI design and new UI patterns. Perhaps most challenging part of those new style UI-s is supporting one and two screen modes as users can decide on how many screens they want application to cover.

Getting Surface Duo SDK preview

Surface Duo comes as zip-archive containing installer file. Make sure you have latest Visual Studio 2019 installed with Xamarin.

Download Surface Duo SDK Preview
Use the Surface Duo emulator (useful hints to make emulator work)

Installer creates folder for Surface Duo system images. Emulator can be started by running batch files in installation folder or using icon on desktop.

Hyper-V warning! I ran into different troubles with emulator when Hyper-V was running on my machine. Stopping Hyper-V services was not enough – I had to uninstall Hyper-V and also Windows 10 virtualization features to get Surface Duo emulator running.

Surface Duo emulator

As Surface Duo has foldable screen it needs specialized emulator where foldable or two-part screen is already considered at operating system level. Screenshot below shows Surface Duo emulator running with no applications open.

Regular applications open on one screen (left or right of black folding line). They can be moved from one screen to another. If application is moved under folding line then both screens are highlighted. At this point comes difference between regular and new applications if user releases window. Regular application moves to left or right screen leaving the other one as it was before. New application enters extended mode and covers both screens.

Dual screen patterns

Before getting to code let me introduce new dual screen patterns for Surface Neo and Surface Duo. As with all new UI concepts there are at least some rules to follow to guarantee great user experience. Dual screens world is not the exception.

Extended Canvas – extending the canvas allows users to take advantage of the larger screen real-estate provided by dual-screen devices.

Use with map and drawing canvas apps.

Master-Detail – separating navigation or overview from details allows users to drill deeper into content while staying grounded regarding their position in the overall list/aggregate.

Use with apps that have lists or galleries, mail and scheduling apps, photos or image curation apps, music apps with playlists and song details, apps with strong navigation structure.

Two Page – leveraging the skeuomorphic metaphor of a book to showcase one page on each screen, so it’s more conducive to reading.

Use with document oriented apps, apps with content that is paginated, apps made for reading, apps with itemized canvas.

Dual View – having multiple views of the same app in the same container, allowing comparison of similar-type content side by side.

Use with editing tools that benefit from having before/after states side-by-side, content and context side-by-side, apps that let the user compare similar items, having two canvases with coordinated content but keeping each page separate.

Companion Pane – show complementary context to augment users’ tasks, usually with a primary/secondary relationship, by elevating to the surface previously buried level 2 functionalities for quicker access.

Use with productivity apps that have supplemental information that appears next to the main content, creative tools like image drawing app, music or video editor apps, gaming apps.

Exploring Surface Duo examples

Surface Duo examples are available for Xamarin Native and Xamarin Forms. For native apps there are one project per demo. For Xamarin Forms there’s just one project demonstrating all dual screen application patterns. Those who want to get quick idea how new application works and how new UI patterns are implemented should start from Xamarin Forms applications.

NB! Some sample forms need Google Maps key. You can acquire on from Google Developers Console. Maps key is needed in HTML files located in assets folders. Once you have a key, just copy and paste it where it is said in HTML.

Before starting application make sure emulator is running. For Visual Studio emulator is like a device it can connect to.

Although it doesn’t have much informative name, it still works and Microsoft will come up with better name for Surface Duo emulator in future.

Screenshot below shows Xamarin Forms application opened on left screen. You can click buttons to see demos of different dual-screen application patterns implemented for Surface Duo. I used the very same application to make screenshots demonstrating dual-screen patterns.

On bottom part of left screen there’s white line on black border. We can point mouse on emulator (or finger on real device) on the white line and move application to right screen or let is span over both screens. Spanning application over both screens and moving it between two screens is supported in emulator.

Here’s the screen recording of Xamarin demo application.

Watch video

With these examples we have implementations of all dual-screen patterns available.

Exploring code in Visual Studio

Xamarin application uses Xamarin.DuoSdk NuGet package that provides access to hinge sensor and dual-screen features.

HingeSensor class is for monitoring hinge sensor. This class can tell us if device has a hinge and notify us when sensor value changed. ScreenHelper gives us information about hinge size and screen rotation. It has few helpful methods more that we can use to react to hinge sensor changes.

When sniffing around n Visual Studio project I found IHingeService and ILayoutService interfaces from “shared” Xamarin project. Their implementations – HingeService and LayoutService – are in Xamarin Android project. Both of these classes are so clean of sample application specifics that they should be part of some NuGet package in my opinion. There’s anyway Xamarin.DuoSdk NuGet package available.

Pages in sample application are all implemented in “shared” project. There are components like TwoPaneView and FormsWindow that are used in pages. Both components are generic enough to have them as NuGet package.

It was a little surprising for me that there are no base pages or ready-made templates for new dual-screen pages implementing corresponding patterns mentioned above. It’s possible that we will get something when preview version of Surface Neo is released.

Wrapping up

Surface Duo preview SDK and emulator are first real tools for developers to get started with dual-screen apps development. Although emulator doesn’t look as slick as real device, it works well enough. As a developers we can really start experimenting now and extend our new applications to Surface Neo as soon as tooling for Surface Neo is available. Documentation that is available now is not complete yet, but still there’s enough information to get an idea how things work for dual-screen experiences.

References

The post Start with Surface Duo development on preview emulator and SDK today appeared first on Gunnar Peipman - Programming Blog.

Readable fluent queries with Entity Framework Core

Gunnar Peipman — Mon, 27 Jan 2020 04:51:28 +0000

After my first experiments with Query Specification pattern on Entity Framework Core I came to interesting idea – why not using extension methods that wrap query specifications or add directly some more conditions to IQueryable the way that queries are easy to read. Here’s my experiment and thoughts of fluent readable queries.

Warning! This blog post covers my new experiments with readable fluent queries. Don’t take it as a new pattern or final solution or something too serious. I have no idea if this approach is applicable in real codebase and what are the limits or side effects. It’s just an interesting idea to play with.

Wired query specifications

My previous post Implementing Query Specification pattern in Entity Framework Core introduced query specification classes and it was possible to wire these one after another. Here’s the last code example from this post.

var invoices = _dataContext.Invoices.Specify(new DueInvoicesSpecification())
                                    .Specify(new CustomerInvoicesSpecification(100))
                                    .OrderBy(i => i.Customer.Name)
                                    .ToList();

Code itself looks okay to me. Specify() method that applies query specifications looks okay and clean. It doesn’t use actions and funcs line other Entity Framework Core extension methods but still it looks fine and it’s easy to read.

Issues with wired query specifications

One thing started suddenly annoying me when I just watched the wiring of specifications and thought if it’s okay or are there other ways to do it better. Red rectangle on the following image shows the problematic part.

Few things:

Names of query specification classes repeat the word Specification, making class names too long.
Calls to Specify() method doesn’t add any literal value.
Call to Specify() method with query specification class is too long and not enough informative.

We can be sure that the longer the list of specifications grows the uglier the code gets until it’s not easy to follow with eyes.

Introducing readable fluent queries

After some experiments with my previous query specification code I wrote the following extension methods for IQueryable<Invoice>.

public static class InvoiceQueries
{
    public static IQueryable<Invoice> GetOverDueInvoices(this IQueryable<Invoice> query)
    {
        return query.Specify(new DueInvoicesSpecification());
    }

    public static IQueryable<Invoice> ForCustomer(this IQueryable<Invoice> query, int customerId)
    {
        return query.Specify(new CustomerInvoicesSpecification(customerId));
    }
}

These methods are actually dummy wrappers for query specification classes but the their effect is pretty cool. Take a look at the following code example and compare it to one with Specify() methods above.

var invoices = _dataContext.Invoices.GetOverDueInvoices()
                                    .ForCustomer(100)
                                    .OrderBy(i => i.Customer.Name)
                                    .ToList();

Instead of query stuffed with technical lingo we have now query that make clear and honest confession about what they will actually do.

Longer fluent queries

Let’s wire some more specifications to query and see how code looks when using query specifications. I added SentUsing and Total property to Invoice class and created the following specifications.

public class SentUsingEmailSpecification : BaseSpecification<Invoice>
{
    public SentUsingEmailSpecification()
    {
        Criteria = i => i.SentUsing == SendMethodEnum.Email;
    }
}

public class HavingTotalAtLeastSpecification : BaseSpecification<Invoice>
{
    public HavingTotalAtLeastSpecification(double total)
    {
        Criteria = i => i.Total >= total;
    }
}

Now let’s add these new specification to sample query.

var invoices = _dataContext.Invoices.Specify(new DueInvoicesSpecification())
                                    .Specify(new CustomerInvoicesSpecification(100))
                                    .Specify(new SentUsingEmailSpecification())
                                    .Specify(new HavingTotalAtLeastSpecification(150))
                                    .OrderBy(i => i.Customer.Name)
                                    .ToList();

So, readability is gone. When we look at code above it’s messy – we can’t catch what query is doing with taking quick look on it. We have to read through all lines where Specify() method is called to understand what the query is doing.

Using same dirty trick I did in previous post I add two extension methods to wrap new query specifications.

public static IQueryable<Invoice> SentUsingEmail(this IQueryable<Invoice> query)
{
    return query.Specify(new SentUsingEmailSpecification());
}

public static IQueryable<Invoice> HavingTotalAtLeast(this IQueryable<Invoice> query, float total)
{
    return query.Specify(new HavingTotalAtLeastSpecification(total));
}

After making sample query use these extension methods we get it back on track.

var invoices = _dataContext.Invoices.GetOverDueInvoices()
                                    .ForCustomer(100)
                                    .SentUsingEmail()
                                    .HavingTotalAtLeast(150)
                                    .OrderBy(i => i.Customer.Name)
                                    .ToList();

Our query is still readable and communicates its purpose. Query specification classes made sample query ugly and hard to read but extension methods solved these issues.

Pros and cons of fluent queries

It’s good question if we need query specifications when going with extension methods like the ones above. Maybe we can go with extension methods and abandon query specifications as we can host query conditions in extension methods instead. It makes sense but we cannot forget one thing – specification and query specification classes are more familiar to developers.

There’s also another issue with extension methods that query specifications doesn’t have – moving queries through layers of application. Extension method is used where it is called. We can move extension method by using actions but I’m not sure if this is something we want to do. Query specification classes allow us to create instances and use these instances with method calls to service layer or as parameters of command classes.

Although I’m not sure if extension methods like show here are good or bad they still look damn nice and readable to me.

Wrapping up

Playing with patterns and their implementations and stepping further with all kind of random ideas may sometimes lead to something interesting. This time I started with implementation of query specification pattern and got to an idea of fluent readable queries. Although I have not much idea if this idea leads to somewhere or if it is applicable in practice it still seems promising when looking at my readable fluent queries. As it’s not yet safe to go live with my queries I will try these out in some smaller project to see where it leads me.

The post Readable fluent queries with Entity Framework Core appeared first on Gunnar Peipman - Programming Blog.

Execute raw SQL commands in Entity Framework Core

Gunnar Peipman — Sun, 26 Jan 2020 21:06:33 +0000

I have never seen a real-life project where object-relational mapper generates 100% of needed SQL. There have always been those special cases when raw SQL commands are needed. In this post I will demonstrate how to run raw SQL commands in Entity Framework Commands and how to read data from database without DbSet and query types.

Executing raw SQL commands

Entity Framework Core has ExecuteSqlCommand() and ExecuteSqlCommandAsync() methods to run custom SQL queries and commands. Here’s the example of running stored procedure to update balance for all customers.

public async Task UpdateBalanceForCustomers()
{
    await Database.ExecuteSqlCommandAsync("EXEC sp_BalanceUpdate");
}

We can also use parameters with these methods like shown here.

public async Task UpdateBalanceForCustomer(int customerId)
{
    await Database.ExecuteSqlCommandAsync("EXEC sp_Customer BalanceUpdate @p1", customerId);
}

These methods are fine but how they are executed is decided by Entity Framework Core.

Using ADO.NET

We can use ADO.NET data objects to get complete control and go around all wisdom coming with Entity Framework Core. Here’s the code from first sample of this post but it is implemented using raw database objects.

public async Task UpdateBalanceForCustomers()
{
    using (var command = Database.GetDbConnection().CreateCommand())
    {
        command.CommandText = "sp_BalanceUpdate";
        command.CommandType = CommandType.StoredProcedure;

        await command.ExecuteNonQueryAsync();
    }
}

This code is pretty primitive. It just executes stored procedure and this is it.

Getting data from raw SQL commands

Sometimes we need to get data back from custom raw SQL. Good example is reporting data we are showing as a table on web page. It’s sometimes tempting to return data reader to keep things minimal but be aware – you are taking a great responsibility! Before running any other command on same connection the data reader must be closed or you end up with exception.

What has best worked for me is loading data to DataTable like shown here.

public async Task<DataTable> GetAverageTemperatures(DateTime forDate)
{
    using(var command = Database.GetDbConnection().CreateCommand())
    { 
        command.CommandText = "sp_AverageTemperaturesReport";
        command.CommandType = CommandType.StoredProcedure;

        var forDateParam = command.CreateParameter();
        forDateParam.ParameterName = "@ForDate";
        forDateParam.DbType = DbType.Date;
        forDateParam.Value = forDate;
        command.Parameters.Add(forDateParam);

        using(var reader = await command.ExecuteReaderAsync())
        { 
            var table = new DataTable();
            table.Load(reader);

            return table;
        }
    }
}

Using this code we can also run custom SQL commands and not only stored procedures. We are still running commands in database context but what we are doing is not monitored by Entity Framework core. For reporting DataTable is ideal – we can change the structure of data returned by SQL command and everything still works.

Common view for DataTable. If you write views or procedures for reporting where field names are replaced by human readable names then you can use my common DataTable view to display DataTables. You can also derive your own common view for your DataTables using my code as a starting point.

Wrapping up

There are multiple ways to execute raw SQL commands using Entity Framework Core. This post focused on a shortcut methods like ExecuteSqlCommand() and ExecuteSqlCommandAsync() and also took raw ADO.NET to focus as it is sometimes the work horse we actually need. The code here doesn’t conflict with Entity Framework database context and it is safe to use for data reading as it doesn’t leave any data readers open.

The post Execute raw SQL commands in Entity Framework Core appeared first on Gunnar Peipman - Programming Blog.

DataSet and DataTable based ad-hoc reporting with ASP.NET Core

Gunnar Peipman — Sat, 25 Jan 2020 04:21:00 +0000

In one of my projects I have some ASP.NET Core views that display multiple tables with reporting data. Data comes from SQL Server views and stored procedures and these can be modified in database without deploying application to server again. I came out with very common solution in ASP.NET Core to solve this problem using raw SQL commands and shared views for DataTable and DataSet. Here’s what I did.

Getting DataTable from ORM

It doesn’t matter if you use Entity Framework Core or NHibernate as they both give you access to database connection to execute direct queries. Here is the example for Entity Framework Core from my blog post Execute raw SQL commands in Entity Framework Core. This method can be member of database context or some repository class.

public async Task<DataTable> GetAverageTemperatures(DateTime forDate)
{            
    using(var command = Database.GetDbConnection().CreateCommand())
    { 
        command.CommandText = "sp_AverageTemperaturesReport";
        command.CommandType = CommandType.StoredProcedure;

        var forDateParam = command.CreateParameter();
        forDateParam.ParameterName = "@ForDate";
        forDateParam.DbType = DbType.Date;
        forDateParam.Value = forDate;
        command.Parameters.Add(forDateParam);

        using(var reader = await command.ExecuteReaderAsync())
        { 
            var table = new DataTable();
            table.Load(reader);

            return table;
        }
    }
}

It reads data from custom SQL to DataTable keeping data reader open while data is copied to DataTable. After this data reader is disposed and database connection is “free” again.

Building DataSet

Those who remember early days of .NET should also remember DataSet class. It’s a container holding DataTable objects and relations between them. As I had to show multiple reports in one ASP.NET Core view I decided to go with DataSet. I ask DataTables and build DataSet of them in database context or repository method like shown here.

public async Task<DataSet> GetActiveCustomersReport(DateTime forDate)
{
    var dataSet = new DataSet("Customers report");

    var table = await GetTopGoldCustomersForDate(forDate);
    table.TableName = "Top gold customers";
    dataSet.Tables.Add(table);

    table = await GetTopSilverCustomersForDate(forDate);
    table.TableName = "Top silver customers";
    dataSet.Tables.Add(table);

    table = await GetTopActiveCustomersForDate(forDate);
    table.TableName = "Top active customers";
    dataSet.Tables.Add(table);

    return dataSet;
}

Using this method I get DataSet with all tables I need to show on report.

Common view for DataTable

For a moment I head back to my post Simple view to display contents of DataTable just to grab a common ASP.NET MVC DataTable view I proposed there. For your convenience the markup of view is here. I keep this view in shared views folder.

@model System.Data.DataTable
@using System.Data;

<h2>@Model.TableName</h2>

<table>
    <thead>
        <tr>
            @foreach (DataColumn col in Model.Columns)
            {
                <th>@col.ColumnName</th>
            }
        </tr>
    </thead>
    <tbody>
        @foreach (DataRow row in Model.Rows)
        {
            <tr>
                @foreach (DataColumn col in Model.Columns)
                {
                    <td>@row[col.ColumnName]</td>
                }
            </tr>
        }
    </tbody>
\</table\>

If you need to display DataTable you can use this view from controller and provide DataTable as a model.

Common view for DataSet

To display DataSet we need another common view. To keep it minimal I will re-use DataTable view shown above. This view goes also to shared views folder of ASP.NET Core application.

@model System.Data.DataSet

<h1>@Model.DataSetName</h1>

@foreach(var table in Model.Tables)
{
    <partial name="DataTable" model="table" />
}

This view shows DataSet name and iterates through tables collection calling DataTable view for every table.

Using common DataSet view

There’s one final piece left to build – common view for DataSet. As it re-uses shared DataTable view it is small. Here it is.

@model System.Data.DataSet

<h1>@Model.DataSetName</h1>

@foreach(var table in Model.Tables)
{
    <partial name="DataTable" model="table" \>
}

Here is the controller action that calls GetActiveCustomersReport() shown method above and uses DataSet view to show results.

public async Task<IActionResult> CustomersReport()
{
    var report = await _dbContext.GetActiveCustomersReport(DateTime.Now.Date);

    return View("DataSet", report);
}

After applying some styling to views here’s the result – simple customers and orders report generated by SQL Server views and stored procedures and shown by commong views for DataSets and DataTables.

Be warned! It can be tempting to use this solution as a base for more advanced reporting and after first steps you are probably impressed how much it is possible to do with those common views. But be warned – this solution doesn’t replace any advanced reporting service or reporting components. It’s perfect when you just need to pump out some tabular reports to display data from database server but again – it is not base for advanced reporting.

Wrapping up

If we need to show simple dynamic reports in ASP.NET Core application we can use raw SQL queries and common views for DataSet and DataTable. It’s easy ad-hoc style reporting where changes to application are not needed when queries or stored procedures are tweaked. I have used this approach successfully in practice from ASP.NET MVC times. It’s easy to extend this solution but don’t go too far – if you need advanced reporting then better go with professional reporting components or services.

The post DataSet and DataTable based ad-hoc reporting with ASP.NET Core appeared first on Gunnar Peipman - Programming Blog.

Behind the compiler: 20 examples of C# code before and after compiling

Gunnar Peipman — Wed, 22 Jan 2020 05:00:16 +0000

Over years I have written many blog posts about C# and .NET that demonstrate also how things work internally and what C# compiler produces from the code we write. I have called these chapters usually as “Behind the compiler”.

Behind the compiler posts

The post Behind the compiler: 20 examples of C# code before and after compiling appeared first on Gunnar Peipman - Programming Blog.