Marten is a .NET document database library that uses an underlying PostgreSQL database to store objects as JSON. The library has a variety of features that allow the logging of SQL statements issued to the underlying PostgreSQL including previewing LINQ query SQL statements.  One of the other logging features available allows custom logging to be created for individual session operations such as successfully issued database SQL commands, failed commands, and changes that were saved. There are also numerous other logging/extension points that can be utilized such as logging schema change SQL and automatically using a logger for all sessions.

The following code shows a console application that writes two customers and then retrieves them:

using System;
using static System.Console;
using Marten;

namespace MartenFKDemo
{
    class Customer
    {
        public int Id { get; set; }
        public string Name { get; set; }
        public override string ToString() => $"{Id} {Name}";
    }

    class Program
    {
        static void Main(string[] args)
        {
            const string connectionString = "host = localhost; database = OrderDb; password = g7qo84nck22i; username = postgres";

            var store = DocumentStore.For(connectionString);

            WriteLine("Creating new customer");
            using (var session = store.OpenSession())
            {
                session.Store(new Customer {Name = "Amrit"}, new Customer {Name = "Sarah"});

                session.SaveChanges();
            }


            WriteLine("All customers:");
            using (var session = store.QuerySession())
            {
                foreach (Customer customer in session.Query<Customer>())
                {
                    WriteLine(customer);
                }
            }
            
            ReadLine();
        }
    }
}

Running the application results in the following output:

Creating new customer
All customers:
9001 Amrit
9002 Sarah

To create a custom session logger, the IMartenSessionLogger interface can be implemented, a simple version that logs to the console is shown as follows:

class ColorConsoleLogger : IMartenSessionLogger
{
    public void LogSuccess(Npgsql.NpgsqlCommand command)
    {
        ForegroundColor = ConsoleColor.Green;

        WriteLine(command.CommandText); // additional properties (e.g. SQL parameters) are available
    }

    public void LogFailure(Npgsql.NpgsqlCommand command, Exception ex)
    {
        ForegroundColor = ConsoleColor.Red;

        WriteLine(command.CommandText); // additional properties (e.g. SQL parameters) are available
        WriteLine(ex);
    }

    public void RecordSavedChanges(IDocumentSession session, IChangeSet commit)
    {
        ForegroundColor = ConsoleColor.Gray;

        foreach (object insertedItem in commit.Inserted) //  updated/deleted are also available
        {
            WriteLine(insertedItem);
        }
    }
}

To configure individual sessions to use this logger, the sessions Logger property can be set as the following modified code demonstrates:

ResetColor();
WriteLine("Creating new customer");
using (var session = store.OpenSession())
{
    // Set logger for this session
    session.Logger = new ColorConsoleLogger();

    session.Store(new Customer {Name = "Amrit"}, new Customer {Name = "Sarah"});

    session.SaveChanges();
}

ResetColor();
WriteLine("All customers:");
using (var session = store.QuerySession())
{
    // Set logger for this session
    session.Logger = new ColorConsoleLogger();

    foreach (Customer customer in session.Query<Customer>())
    {
        ResetColor();
        WriteLine(customer);
    }
}

This produces the following output:

Creating new customer
select public.mt_upsert_customer(doc := :p0, docDotNetType := :p1, docId := :p2, docVersion := :p3);select public.mt_upsert_customer(doc := :p4, docDotNetType := :p5, docId := :p6, docVersion := :p7);
13001 Amrit
13002 Sarah
All customers:
select d.data, d.id, d.mt_version from public.mt_doc_customer as d
13001 Amrit
13002 Sarah

To learn more about the document database features of Marten check out my Pluralsight courses: Getting Started with .NET Document Databases Using Marten and Working with Data and Schemas in Marten or the documentation.

You can start watching with a Pluralsight free trial.

SHARE:

Marten is a .NET document database library that uses an underlying PostgreSQL database to store objects as JSON. The library has a variety of features that allow the logging of SQL statements issued to the underlying PostgreSQL database in addition to being able to do things such as get the PostgreSQL query plan for a given LINQ query.

One simple way to get the generated SQL for a Marten LINQ query is to use the ToCommand() extension method.

As an example, suppose we are developing some query code as follows (this code uses the Include method to include the related documents in a single database round-trip):

Customer customer = null;

List<Order> orders = session.Query<Order>()
                            .Include<Customer>(joinOnOrder => joinOnOrder.CustomerId, includedCustomer => customer = includedCustomer)
                            .Where(x => x.CustomerId == 4001).ToList();

If we want to get an idea of what SQL Marten will generate for this LINQ query, we can change the code as shown in the following:

Customer customer = null;

IQueryable<Order> orders = session.Query<Order>()
                                  .Include<Customer>(joinOnOrder => joinOnOrder.CustomerId, includedCustomer => customer = includedCustomer)
                                  .Where(x => x.CustomerId == 4001);

// Get the SQL command that will be issued when the query executes
NpgsqlCommand cmd = orders.ToCommand();

// Output some selected command info
Console.WriteLine(cmd.CommandText);

foreach (NpgsqlParameter parameter in cmd.Parameters)
{
    Console.WriteLine($"Parameter {parameter.ParameterName} = {parameter.Value}");
}

// Ensure included customer variable is populated
List<Order> orderResults = orders.ToList();

Console.WriteLine(customer.Name);
foreach (Order order in orderResults)
{
    Console.WriteLine($" Order {order.Id} for {order.Quantity} items");
}

Running this preceding code  results in the following console output:

select d.data, d.id, d.mt_version, customer_id.data, customer_id.id, customer_id.mt_version from public.mt_doc_order as d INNER JOIN public.mt_doc_customer as customer_id ON d.customer_id = customer_id.id where d.customer_id = :arg0
Parameter arg0 = 4001
Sarah
 Order 3001 for 42 items
 Order 4001 for 477 items
 Order 5001 for 9 items

To learn more about the document database features of Marten check out my Pluralsight courses: Getting Started with .NET Document Databases Using Marten and Working with Data and Schemas in Marten.

You can start watching with a Pluralsight free trial.

SHARE:

Marten is a .NET document database library that uses an underlying PostgreSQL database to store objects as JSON.

Ordinarily, Marten takes care of retrieving the JSON from the database and deserializing it into an object. We can however instruct Marten to perform a query to retrieve document(s) and not perform the deserialization, instead giving us the JSON as it appears in the underlying PostgreSQL record. If we are exposing documents via a Web API, this feature can be taken advantage of to reduce some processing overhead on the server.

As an example, we could have the following Customer document defined:

public class Customer
{
    public int Id { get; set; }
    public string Name { get; set; }

    // etc.
}

And in a CustomersController we could start with a method as follows to add Customers to the database:

public void Post(Customer customer)
{
    // no validation

    // DocumentStore would normally be created only once in app, e.g. via IOC singleton 
    using (var store = DocumentStore.For(ConnectionString))
    {
        using (var session = store.LightweightSession())
        {
            session.Store(customer);
            session.SaveChanges();
        }
    }
}

To get all Customers, the following method could be written:

// GET api/customers
public IEnumerable<Customer> Get()
{
    // DocumentStore would normally be created only once in app, e.g. via IOC singleton 
    using (var store = DocumentStore.For(ConnectionString))
    {
        using (var session = store.QuerySession())
        {
            return session.Query<Customer>();
        }
    }
}

The preceding method however incurs the additional overhead of Marten deserializing the database JSON into Customer objects, only to be serializing it again back into JSON on the way out of the API.

When creating the LINQ query, the Marten ToJsonArray() method can be added to instruct Marten to simply return the JSON directly from the database.

We can then modify the Get method as follows:

public HttpResponseMessage Get()
{
    // DocumentStore would normally be created only once in app, e.g. via IOC singleton 
    using (var store = DocumentStore.For(ConnectionString))
    {
        using (var session = store.QuerySession())
        {
            string rawJsonFromDb = session.Query<Customer>().ToJsonArray();

            var response = Request.CreateResponse(HttpStatusCode.OK);
            response.Content = new StringContent(rawJsonFromDb, Encoding.UTF8, "application/json");
            return response;
        }
    }
}

We could also write the parameterized Get method and use Marten’s AsJson() method to get the JSON string for the individual Customer document as  in the following code:

public HttpResponseMessage Get(int id)
{
    // DocumentStore would normally be created only once in app, e.g. via IOC singleton 
    using (var store = DocumentStore.For(ConnectionString))
    {
        using (var session = store.LightweightSession())
        {
            var rawJsonFromDb = session.Query<Customer>().Where(x => x.Id == id).AsJson().FirstOrDefault();

            if (string.IsNullOrEmpty(rawJsonFromDb))
            {
                throw new HttpResponseException(HttpStatusCode.NotFound);
            }

            var response = Request.CreateResponse(HttpStatusCode.OK);
            response.Content = new StringContent(rawJsonFromDb, Encoding.UTF8, "application/json");
            return response;
        }
    }
}

To learn more about the document database features of Marten check out my Pluralsight courses: Getting Started with .NET Document Databases Using Marten and Working with Data and Schemas in Marten.

You can start watching with a Pluralsight free trial.

SHARE:

Marten is a .NET document database library to allows objects to be stored, retrieved, and queried as documents stored as JSON in an underlying PostgreSQL database. This new course is a follow-on from the previous Getting Started with .NET Document Databases Using Marten course, if you’re new to Marten I’d recommend checking out the previous course first before continuing with this new one.

Among other topics, this new course covers how to log/diagnose the SQL that is being issued to PostgreSQL; how to enable offline optimistic concurrency; bulk document inserts; a number of ways to improve query performance; and the customization of database schema objects.

You can check out the new course on the Pluralsight site.

You can start watching with a Pluralsight free trial.

SHARE:

If you have some data in a web page but there is no API to get the same data, it’s possible to use (the often brittle and error prone) technique of screen scraping to read the values out of the HTML.

By leveraging Azure Functions, it’s trivial (depending on how horrendous the HTML is) to create a HTTP Azure Function that loads a web page, parses the HTML, and returns the data as JSON.

The fist step is to create an HTTP-triggered Azure Function:

To help with the parsing, we can use the HTML Agility Pack NuGet package. To add this to the function, create a project.json file and add the NuGet reference:

{
  "frameworks": {
    "net46":{
      "dependencies": {
        "HtmlAgilityPack": "1.4.9.5"
      }
    }
   }
}

Once this file is saved, the NuGet package will be installed and a using directive can be added: using HtmlAgilityPack;

Now we can download the required HTML page as a string, in the example below the archive page from Don’t Code Tired, use some LINQ to get the post titles, and return this as the HTTP response. Correctly selecting/parsing the required data from the HTML page is likely to be the most time-consuming part of the function creation.

The full function source code is as follows. Notice that there’s no error checking code to simplify the demo code. Screen scraping should usually be a last resort because of its very nature it can often break if the UI changes or unexpected data exists in the HTML. It also requires the whole page of HTML be downloaded which may raise performance concerns.

using System.Net;
using HtmlAgilityPack;

public static async Task<HttpResponseMessage> Run(HttpRequestMessage req, TraceWriter log)
{
    HttpClient client = new HttpClient();

    string html = await client.GetStringAsync("http://dontcodetired.com/blog/archive");    

    HtmlDocument doc = new HtmlDocument();
    doc.LoadHtml(html); 
    
    var postTitles = doc.DocumentNode
                        .Descendants("td")
                        .Where(x => x.Attributes.Contains("class") && x.Attributes["class"].Value.Contains("title"))
                        .Select(x => x.InnerText);

    return req.CreateResponse(HttpStatusCode.OK, postTitles);
}

We can now call the function via HTTP and get back a list of all Don’t Code Tired article titles as the following Postman screenshot shows:

To jump-start your Azure Functions knowledge check out my Azure Function Triggers Quick Start Pluralsight course.

You can start watching with a Pluralsight free trial.

SHARE:

Marten is a .NET document database library that uses an underlying PostgreSQL database to store objects as JSON.

In the  previous article (Enforcing Referential Integrity Between Documents with Marten and PostgreSQL) we saw how we can enable referential integrity between documents and the example showed a Customer document may have many related Order documents.

When creating LINQ queries with Marten, one feature is the idea of included documents. So for example we might want to get a Customer document and all the Order documents for that customer.

We could write some code as follows to get Customer 4001 and a list of their Orders:

// 2 trips to database, 2 queries
using (var session = store.QuerySession())
{
    Customer customer = session.Query<Customer>()
                               .Single(x => x.Id == 4001);

    Console.WriteLine(customer.Name);

    IEnumerable<Order> orders = session.Query<Order>()
                                       .Where(x => x.CustomerId == 4001);

    foreach (var order in orders)
    {
        Console.WriteLine($" Order {order.Id} for {order.Quantity} items");
    }
}

The preceding code however will result in two round trips to the database which may result in a performance problem:

select d.data, d.id, d.mt_version 
from public.mt_doc_customer as d 
where d.id = 4001 LIMIT 2

select d.data, d.id, d.mt_version
from public.mt_doc_order as d 
where d.customer_id = 4001

The .Include() extension method of Marten allows a single round trip to be made to the database that executes a SQL join and returns both the Customer data and Orders.

The following code shows how to accomplish this, notice that we need to declare an “output” variable to hold the “included” customer.

// 1 trip to database, 1 query
using (var session = store.QuerySession())
{                
    Customer customer = null;

    List<Order> orders = 
         session.Query<Order>()
                .Include<Customer>(joinOnOrder => joinOnOrder.CustomerId, includedCustomer => customer = includedCustomer)
                .Where(x => x.CustomerId == 4001)
                .ToList();


    Console.WriteLine(customer.Name);

    foreach (var order in orders)
    {
        Console.WriteLine($" Order {order.Id} for {order.Quantity} items");
    }  
}

The preceding code now only makes a single request to the database with the following SQL:

select d.data, d.id, d.mt_version, customer_id.data, customer_id.id, customer_id.mt_version 
from public.mt_doc_order as d 
INNER JOIN public.mt_doc_customer as customer_id ON d.customer_id = customer_id.id 
where d.customer_id = 4001

To learn more about the document database features of Marten check out my Pluralsight courses: Getting Started with .NET Document Databases Using Marten and Working with Data and Schemas in Marten.

You can start watching with a Pluralsight free trial.

SHARE:

Even though Marten is a library to enable document database storage semantics, because it’s built on top of (the advanced JSON features) of PostgreSQL, and PostgreSQL itself is relational, we can add constraints between document instances just as we would do in relational normalised databases.

For example, the following two simplified classes represent an order that points to a customer document:

class Customer
{
    public int Id { get; set; }
    public string Name { get; set; }
    
    // etc.
}

class Order
{
    public int Id { get; set; }
    public int CustomerId { get; set; }
    public int Quantity { get; set; }
    
    // etc.
}

By default there is no referential integrity enforcing the idea that the CustomerId in the Order class must point to an existing Customer document in the database. This means that the following code succeeds with no errors:

const string connectionString = "host = localhost; database = OrderDb; password = g7qo84nck22i; username = postgres";

var store = DocumentStore.For(connectionString);

using (var session = store.OpenSession())
{
    var order = new Order
    {
        Quantity=42,
        CustomerId = 34567 // non-existent
    };

    session.Store(order);

    session.SaveChanges(); // no error
}

The preceding code will result in an order document being stored with an invalid CustomerId.

Marten can be configured to enforce this by either adding the [ForeignKey(typeof(Customer))] attribute to the CustomerId property or by configuring the document store as the following code shows:

var store = DocumentStore.For(configure =>
{
    configure.Connection(connectionString);
    configure.Schema.For<Order>().ForeignKey<Customer>(x => x.CustomerId);
});

Now running the code again will result in an exception: insert or update on table "mt_doc_order" violates foreign key constraint "mt_doc_order_customer_id_fkey”.

If the code is modified to create a valid customer in the same session, Marten will generate a customer Id that can then be used in the order. When SaveChanges() is called, Marten will save the dependent customer document first and then the order document that points to it. The following code executes successfully:

using (var session = store.OpenSession())
{
    var customer = new Customer {Name = "Sarah"};

    session.Store(customer);
    
    // customer now has an Id auto-generated 

    var order = new Order
    {
        Quantity = 42,
        CustomerId = customer.Id // valid existing customer ID
    };

    session.Store(order);

    session.SaveChanges();
}

To learn more about the document database features of Marten check out my Pluralsight courses: Getting Started with .NET Document Databases Using Marten and Working with Data and Schemas in Marten.

You can start watching with a Pluralsight free trial.

SHARE:

Microsoft Flow is a tool for creating workflows to automate tasks. It’s similar in concept to If This Then That but feels like it exists more towards the end of the spectrum of the business user rather than the end consumer – though both have a number of channels/services in common. Flow has a number of advanced features such as conditions, loops, timers, and delays.

Flow has a number of services including common ones such as Dropbox, OneDrive, Twitter, and Facebook. There are also generic services for calling HTTP services, including those created as Azure Functions. Essentially, services are the building blocks of a Flow.

Once the free sign up is complete you can create Flows from existing templates or create your own from scratch.

To create a new custom Flow, the web-based workflow designer can be used.

Integrating a Flow with Azure Functions

In the following example, a Flow will be created that picks up files with a specific naming convention from a OneDrive folder, sends the text content to an Azure Function that simply converts to uppercase and returns the result to the Flow. The Flow then writes out the uppercase version to another OneDrive folder.

Reading Files From OneDrive

The first step in the Flow is to monitor a specific OneDrive folder for new files.

As an example of conditions, an “if statement” can be added to only process files that contain the word “data”:

Now if the filename is correct we can go ahead and call an Azure Function (or other HTTP endpoint).

Calling an Azure Function from Microsoft Flow

Now that we are reading specific files, we want to call an Azure Function to convert the text content of the file to upper case.

The following code and screenshot shows the function that will be called – this code is stripped down and doesn’t contain any error checking/handling code for simplicity:

using System.Net;

public static async Task<HttpResponseMessage> Run(HttpRequestMessage req, TraceWriter log)
{
    log.Info($"C# HTTP trigger function processed a request. RequestUri={req.RequestUri}");

    dynamic data = await req.Content.ReadAsAsync<object>();
    
    string text = data.text;

    return  req.CreateResponse(HttpStatusCode.OK, text.ToUpperInvariant());
}

We can test the API in Postman:

Now that we have a working function we can add a new action of type “HTTP” to the Flow and pass the contents of the OneDrive file as JSON data in the request. The final step is to take the response of calling the Azure Function and writing out to a new file in OneDrive as the following screenshot shows:

Now we can create a file “OneDrive\FlowDemo\In\test1data.txt”, the Flow will be trigged, and the output file “OneDrive\FlowDemo\Out\test1data.txt” created.

Microsoft Flow also has a really nice visual representation of runs (individual executions) of Flows:

Microsoft Flow by itself enables a whole host of workflow scenarios, and combined with all the power of Azure Functions (and other Azure features) could enable some really interesting uses.

To jump-start your Azure Functions knowledge check out my Azure Function Triggers Quick Start Pluralsight course.

You can start watching with a Pluralsight free trial.

SHARE:

Document databases are a form of NoSQL database that store items (objects) as single documents rather than potentially splitting the data among multiple relational database tables.

Marten is a .NET library that enables the storing, loading, deleting, and querying of documents. Objects in the application can be stored into the document database and retrieved back as an object from the database. In this approach there is no requirement for the additional “plumbing code” of ORMs.

Marten is not a database itself but rather a library that interacts with the (advanced JSON features) of the open source, cross platform PostgreSQL database.

Once the Marten NuGet package is installed there are a number of steps to storing .NET objects as documents.

First the “document” to be stored is defined. At the most basic level this is a class that has a field or property that represents the document identity as stored in the database. There are a number of ways to define  identity, one of which is to follow the naming convention “Id” as the following class shows:

class Customer
{
    public int Id { get; set; }
    public string Name { get; set; }
    public List<Address> Addresses { get; set; } = new List<Address>();
}

internal class Address
{
    public string Line1 { get; set; }
    public string Line2 { get; set; }
    public string Line3 { get; set; }
}

Notice in the preceding code that the Address class does not have an Id. This is because the address information will be storing in the overall Customer document, rather than for example in a relational database as rows in a separate Address table.

To work with the database a document store instance is required, this can be created with additional configuration or with the simple code shown below:

var connectionString = "host = localhost; database = CustomerDb; password = YOURPASSWORD; username = postgres";

var store = DocumentStore.For(connectionString);

Working with documents happens by way of a session instance, there are a number of types/configurations of sessions available.

To create a new customer object and store it the following code could be used:

// Store document (& auto-generate Id)
using (var session = store.LightweightSession())
{
    var customer =  new Customer
    {
        Name = "Arnold",
        Addresses =
        {
            new Address {Line1="Address 1 Line 1", Line2="Address 1 Line 2",Line3="Address 1 Line 3"},
            new Address {Line1="Address 2 Line 1", Line2="Address 2 Line 2",Line3="Address 2 Line 3"}
        }
    };

    // Add customer to session
    session.Store(customer);

    // Persist changes to the database
    session.SaveChanges();
}

Once the above code executes, the customer will be stored in a PostgreSQL table called “mt_doc_customer”. The Customer object will be serialized to JSON and stored in a special JSONB field in PostgreSQL. The JSON data also contains all the addresses.

{
  "Addresses": [
    {
      "Line3": "Address 1 Line 3",
      "Line2": "Address 1 Line 2",
      "Line1": "Address 1 Line 1"
    },
    {
      "Line3": "Address 2 Line 3",
      "Line2": "Address 2 Line 2",
      "Line1": "Address 2 Line 1"
    }
  ],
  "Id": 1,
  "Name": "Arnold"
}

There are a number of ways to retrieve documents and convert them back into .NET objects. One method is to use the Marten LINQ support as the following code shows:

// Retrieve by query
using (var session = store.QuerySession())
{
    var customer = session.Query<Customer>().Single(x => x.Name == "Arnold");
                
    Console.WriteLine(customer.Id);
    Console.WriteLine(customer.Name);
}

When executed, this code displays the retrieved customer details as the following screenshot shows:

To learn more about the document database features of Marten check out my Pluralsight courses: Getting Started with .NET Document Databases Using Marten and Working with Data and Schemas in Marten.

You can start watching with a Pluralsight free trial.

SHARE:

Just as I created the free C# 6.0: What’s New Quick Start eBook, I will be writing a new book for the new C# 7.0 features.

If you would like to be notified when the book is available you can register your interest.

If you want to fill in the gaps in your C# knowledge be sure to check out my C# Tips and Traps training course from Pluralsight – get started with a free trial.

SHARE: