In this post we will look at using Entity Frameworks Entity models and using our DbContext to create mappings and use these mappings in our Azure function. Azure functions use Code First approaches to Entity Framework.

Source code for this demo can be found here

For Azure Functions Entity framework - Part 1 covers the basics of adding Entity Framework to our Azure Function.

Getting Started

project.json

{
 "frameworks": 
 {  
  "net46":
  { 
   "dependencies":
   {
     "Newtonsoft.Json": "10.0.3",
     "EntityFramework": "6.1.3",
     "System.Data.Common": "4.3.0"
   }
  }
 }
}

Our run.csx will look something like this.

#r "System.Data"

using System.Net;
using System.Data;
using System.Data.SqlClient;
using Newtonsoft.Json;
using System.Linq;
using System.Data.Entity;

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

    dynamic body = await req.Content.ReadAsStringAsync();
    var e = JsonConvert.DeserializeObject<Person>(body as string);
    e.Created_DT = System.DateTime.Now;
    
    try
    {        
        using (PeopleContext context = new PeopleContext())
        {
            context.Persons.Add(e); 
            context.SaveChanges();            
        }
    }
    catch(System.Data.Entity.Infrastructure.DbUpdateException ex)
    {
        log.Info(string.Format("Failure with database update {0}.", ex.Message));        
    }      

    return req.CreateResponse(HttpStatusCode.OK, "Ok");
}

public class PeopleContext : DbContext
{
    public PeopleContext()
        : base("XXXX")
    { }

  public DbSet<Person> Persons { get; set; }
}

public class Person{
    public int Id { get; set; }
    public string FirstName_VC {get;set;}
    public string LastName_VC {get;set;}
    public string Email_VC {get;set;}
    public DateTime Created_DT {get;set;}
}

So lets go through what we're doing here. We'll start on our code from the bottom up.

Entity Model

public class Person{
    public int Id { get; set; }
    public string FirstName_VC {get;set;}
    public string LastName_VC {get;set;}
    public string Email_VC {get;set;}
    public DateTime Created_DT {get;set;}
}

We have our Person Class, this the entity we're going to use. We have a table in the database called "Person"

Note: Azure functions use a code first approach to EF. So if the person table doesn't exist it gets created the first time you try to insert data.

DbContext

With Entity framework we create a Dbcontext and create the mapping for our classes to the database.

public class PeopleContext : DbContext
{
    public PeopleContext()
        : base("XXXX")
    { }

  public DbSet<Person> Persons { get; set; }
}

We create a PeopleContext and pull in the DbContext. We then set the connection string as we want.

public DbSet<Person> Persons { get; set; }

Sets up our entity we can use Persons in our code now to get the table.

Wire the function

    dynamic body = await req.Content.ReadAsStringAsync();
    var e = JsonConvert.DeserializeObject<Person>(body as string);
    e.Created_DT = System.DateTime.Now;

Will take the Json Payload from the request and Deserialize it into an object of type Person for e.

We take e and open a context to our database.

    try
    {        
        using (PeopleContext context = new PeopleContext())
        {
            context.Persons.Add(e);
            context.SaveChanges();            
        }
    }
    catch(System.Data.Entity.Infrastructure.DbUpdateException ex)
    {
        log.Info(string.Format("Failure with database update {0}.", ex.Message));        
    }   

We add e to our persons entity and then save our changes.

This will result in a new record in the table.

Sample Payload.

{
 "FirstName_VC": "serversncode",
 "LastName_VC": "Entity",
 "Email_VC": "test@test.com"
}

That's it, in a single run.csx file we can build on our EF models to create a database connection. Remember functions are short lived things and "You had one job" kind of things so keeping our models simple and clear is important when it comes to functions.

I've made the sample as narrow as I could, for more on Serverless in Azure check out our series on Azure Functions

In this post we'll walk through loading Entity Framework into an Azure function, Saving data to our database. There can be alot of ceremony in setting up Entity Framework but it's straight forward to do.

In this post we will load Entity Framework and use our DbContext to send data into the database.

Entity Framework (EF) is an object-relational mapper (ORM) that enables developers to work with relational data using domain-specific objects.

Azure functions for more information check out my series of posts on Azure functions

Source code can be found here

HTTP Trigger Function

We'll start with our favourite kind of function a HTTP Trigger function. I've cleared the default template and used a simpler version for this demo

run.csx

#r "System.Data"

using System.Net;
using System.Data;
using System.Data.SqlClient;
using Newtonsoft.Json;

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

    string name = "";
    
    dynamic body = await req.Content.ReadAsStringAsync();
    var e = JsonConvert.DeserializeObject<Person>(body as string);

    name = e.firstname + " " + e.lastname;

    

    return req.CreateResponse(HttpStatusCode.OK, "Ok");
}

public class Person{
    public string firstname {get;set;}
    public string lastname {get;set;}
    public string email {get;set;}
}

Project.json

{
 "frameworks": 
 {  
  "net46":
  { 
   "dependencies":
   {
     "Newtonsoft.Json": "10.0.3"
   }
  }
 }
}

Entity Framework

The first part of bringing Entity Framework to play is to add it into our project.json file.

{
 "frameworks": 
 {  
  "net46":
  { 
   "dependencies":
   {
     "Newtonsoft.Json": "10.0.3",
     "EntityFramework": "6.1.3",
     "System.Data.Common": "4.3.0"
   }
  }
 }
}

Add the 2 nuget references for Entity Framework and System.Data.common to the dependencies.

     "EntityFramework": "6.1.3",
     "System.Data.Common": "4.3.0"

And Save. Allow the function to restore the nuget packages.

run.csx

Now in our run.csx we can start to get down to work

First up we need to pull in the namespaces we want to use.

You know what to do.

using System.Linq;
using System.Data.Entity;

Now we add our code into the run.csx

    try
    {        
        using (var context = new DbContext(XXXX))
        {
            context.Database.Connection.Open();
            
            context.Database.ExecuteSqlCommand(string.Format("INSERT INTO [dbo].[SubscriberDemo] ([FirstName_VC],[LastName_VC], [Email_VC],[Created_DT])" +  
                    "VALUES ('{0}', '{1}', '{2}',GETDATE()) ", e.firstname,e.lastname, e.email));
                    
            context.Database.Connection.Close();
        }
    }
    catch(System.Data.Entity.Infrastructure.DbUpdateException ex)
    {
        log.Info(string.Format("Failure with database update {0}.", ex.Message));        
    }  
    

Ok lets walk through this.

        using (var context = new DbContext(XXXX))
        {
            context.Database.Connection.Open();

We create our context from DbContext and pass the connection string. Then we open the connection.
Note: You can setup the connection string as you want. Through an appsettings if you want I have avoided getting into details here just so I can create clear samples.

 context.Database.ExecuteSqlCommand(string.Format("INSERT INTO [dbo].[SubscriberDemo] ([FirstName_VC],[LastName_VC], [Email_VC],[Created_DT])" +  
                    "VALUES ('{0}', '{1}', '{2}',GETDATE()) ", e.firstname,e.lastname, e.email));

Next we create a SQL command and execute it from our context.

Take our json payload and send it into the Azure function

{
 "firstname": "serversncode",
 "lastname": "Azure function",
 "email": "test@test.com"
}

That's it. We've now used Entity framework in an Azure function and loaded data into our database.

This is just part 1. In the next post we will get into the details of using Entity models.

Getting an Azure function to do something like write to a database is kind of handy and it's really straight forward.

In this post I'll be focusing on the SQL so I've assumed your up to speed on Azure functions. Check out my series of posts Click here for more on getting started with Azure functions.

We'll build on the other functions we have been building so check out my other posts if your unsure. We'll use an Azure HTTP trigger function to take our person payload and load it into the a SQL database. See Azure HTTP Trigger for more information on creating an Azure HTTP function

For example we have a webhook and when someone signs up on our website we want to load them into the database.

Our current function looks something like this

#r "System.Data"

using System.Net;
using System.Data;
using System.Data.SqlClient;
using Newtonsoft.Json;

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

    string name = "";

    
    dynamic body = await req.Content.ReadAsStringAsync();
    var e = JsonConvert.DeserializeObject<Person>(body as string);

    name = e.firstname + " " + e.lastname;

    return name == " "
        ? req.CreateResponse(HttpStatusCode.BadRequest, "Please pass a name on the query string or in the request body")
        : req.CreateResponse(HttpStatusCode.OK, "Hello " + name);
}

public class Person{
    public string firstname {get;set;}
    public string lastname {get;set;}
}

Last time around we added a custom route to the binding Azure Functions HTTP Routes

So instead of returning the "hello .." we want to return a status of "Ok". You can turn it off and have no response if you want but for us we're keeping the response.

So we change the return line of our code from

    return name == " "
        ? req.CreateResponse(HttpStatusCode.BadRequest, "Please pass a name on the query string or in the request body")
        : req.CreateResponse(HttpStatusCode.OK, "Hello " + name);

to return "ok"

    return req.CreateResponse(HttpStatusCode.OK, "Ok");

Side note: you could return different HttpStatusCodes if you wanted for say a failre to update or if something wasn't right in the data. But for our purpose and in order to keep it clean we're going with a HttpStatusCode.Ok and a single message everytime.

What our code does now is to take a json payload

{
    "firstname": "Azure",
    "lastname": "Functions"
}

It then converts that payload into a person object

    dynamic body = await req.Content.ReadAsStringAsync();
    var e = JsonConvert.DeserializeObject<Person>(body as string);

And we now have an object e that has our data in.

So we want to write that to a database.

Azure Function SQL Insert

The simple way to insert data into a SQL database is as follows. This is the most common way according to the docs on MSDN as well.

     var cnnString  = "XXX";
        
    using (SqlConnection conn = new SqlConnection(cnnString))
    {
        conn.Open();

        // Insert Signup        
        var signupInsert = "INSERT INTO [dbo].[SubscriberDemo] ([FirstName],[LastName],[SignedUp])" +  
        "VALUES ('" + e.firstname + "','" + e.lastname + "',GETDATE())";
                   
        // Execute and load data into database.
        using (SqlCommand cmd = new SqlCommand(signupInsert, conn))
        {
            var rows = cmd.ExecuteNonQuery();
        }
        
        
    }

Let's break down what we're doing before we talk about it because there is a bit of a problem doing it this way.

     var cnnString  = "XXX";
        
    using (SqlConnection conn = new SqlConnection(cnnString))
    {
        conn.Open();

First step we get our connection string all setup, then open the connection to our database.

        // Insert Signup        
        var signupInsert = "INSERT INTO [dbo].[Signup] ([FirstName],[LastName],[SignedUp])" +  
        "VALUES ('" + e.firstname + "','" + e.lastname + "',GETDATE())";

Next we setup our SQL string. We're inserting the information into the signup table, and we're looking for the firstname and lastname from the person object and we have a GetDate() to create todays date.

   using (SqlCommand cmd = new SqlCommand(signupInsert, conn))
        {
            var rows = cmd.ExecuteNonQuery();
        }

We now go ahead and execute our SQL script against out database. We use the cmdExecuteNonQuery() method because we are not getting anything back.

This will insert our data into our database for us.

But heres the problem, this code doing it this way could leave you open to a SQL injection attack.

The code I've used is ok as I have parameterised the values. But your just 1 simple mistake away from a real problem.

You can protect yourself of course, do the code like I have with only strings. Validate the inputs, etc. But you don't have to there are other options out there.

So lets look at another way to load data into SQL Server.

Azure Function Stored Procedures

One way to load data into SQL is through Store Procedures.

I have a Stored Procedure called "Subscriber_Insert" in the database.

It does the same as the above insert script. But we can do more checks on the data we are sending in.

#r "System.Data"

using System.Net;
using System.Data;
using System.Data.SqlClient;
using Newtonsoft.Json;

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

    string name = "";
    
    dynamic body = await req.Content.ReadAsStringAsync();
    var e = JsonConvert.DeserializeObject<Person>(body as string);

    name = e.firstname + " " + e.lastname;

    //Connect to SQL
    var cnnString  = "XXXXX";
        
    using (SqlConnection conn = new SqlConnection(cnnString))
    {
        conn.Open();
        
        SqlCommand cmd = new SqlCommand("Subscriber_Insert", conn);
        cmd.CommandType = CommandType.StoredProcedure;
        
        cmd.Parameters.Add(new SqlParameter("@FirstName", e.firstname));
        cmd.Parameters.Add(new SqlParameter("@LastName", e.lastname));

        var reader = cmd.ExecuteReader();
    }

    return req.CreateResponse(HttpStatusCode.OK, "Ok");
}

public class Person{
    public string firstname {get;set;}
    public string lastname {get;set;}
}

For the most part it's the same code. The big difference you can see is with our SqlCommand

        SqlCommand cmd = new SqlCommand("Subscriber_Insert", conn);
        cmd.CommandType = CommandType.StoredProcedure;

Our SqlCommand we set it to the name of the Stored Procedure.
CommandType we set to CommandType.StoredProcedure.

        cmd.Parameters.Add(new SqlParameter("@FirstName", e.firstname));
        cmd.Parameters.Add(new SqlParameter("@LastName", e.lastname));

        var reader = cmd.ExecuteReader();

We set our parameters and then with cmd.ExecuteReader we call our Stored procedure and execute. Any response from the Stored procedure is in our reader.

That's it this is two different ways to get data into SQL from an Azure function.

We can build our script in the function or we can use a Stored procedure.

With azure functions we can customise the end point that our Azure function will respond to.

By default the HTTP trigger will use the name of the function as the URL. So if we want to customise that we have to create a route.

Last time out we created a HTTP function that took a JSON payload and responded for more see here Azure Functions HTTP Triggers

Azure Functions HTTP Routes

Our Default url looks like this.

http://<yourapp>.azurewebsites.net/api/<funcname>?code=<functionkey>

For a default httptrigger function. The function name is the default route "HTTPTriggerCSharp1" and not very useful

https://serversncodefunctiondemo.azurewebsites.net/api/HttpTriggerCSharp1?code=VZ75yCKFgPmMyBrfMQzQxrSu/BJwwKQzK7/yK0vQfG4fcMumwjrAuA==

We send a sample payload

{
    "firstname": "Azure",
    "lastname": "Functions"
}

We call this Function using a POST method and content type set to application/json

We get a response back of "Hello Azure Functions"

Our current route of is not very useful really.

https://serversncodefunctiondemo.azurewebsites.net/api/HttpTriggerCSharp1 

So lets look at changing that.

In our function open the "function.json" file

{
  "disabled": false,
  "bindings": [
    {
      "authLevel": "function",
      "name": "req",
      "type": "httpTrigger",
      "direction": "in"
    },
    {
      "name": "$return",
      "type": "http",
      "direction": "out"
    }
  ]
}

function.json is the file that holds all the configuration information about our function.

The bindings section of the JSON holds the information on the type of trigger used in this case it's a "HttpTrigger" you can see from the "direction: in" that this is the trigger for the function. If we have a Service bus trigger we have information on the service bus here liks this

  "bindings": [
    {
      "name": "myMessage",
      "type": "serviceBusTrigger",
      "direction": "in",
      "queueName": "serversncodedemo",
      "connection": "contentappmessage_RootManageSharedAccessKey_SERVICEBUS",
      "accessRights": "Manage"
    }
  ]

That's a sample of the binding section from our Service bus demo.

With a HTTP trigger we have a response section to the binding aswell.

    {
      "name": "$return",
      "type": "http",
      "direction": "out"
    }

For routing we're interested in the trigger part of the binding.

We want to add a "Route" attribute to the binding that will hold our new name.

So under the "Direction": "in" attribute we add our new route attribute.

"route": "demo"

So that our function.json looks something like this

{
  "disabled": false,
  "bindings": [
    {
      "authLevel": "function",
      "name": "req",
      "type": "httpTrigger",
      "direction": "in",
      "route": "demo"
    },
    {
      "name": "$return",
      "type": "http",
      "direction": "out"
    }
  ]
}

Now when we run our test. If we use the orginal URL we get no response

https://serversncodefunctiondemo.azurewebsites.net/api/HttpTriggerCSharp1?code=VZ75yCKFgPmMyBrfMQzQxrSu/BJwwKQzK7/yK0vQfG4fcMumwjrAuA==

But if we replace the name of our function with our new custom route we will get the response we are expecting.

https://serversncodefunctiondemo.azurewebsites.net/api/demo?code=VZ75yCKFgPmMyBrfMQzQxrSu/BJwwKQzK7/yK0vQfG4fcMumwjrAuA==

Azure function Key

The part of our URL on the ?code=XXXXXXXXXXXXX

This is our function key.

HTTP Triggers by default have Function keys turned on this is for security. The key applies only to the specific functions under which they are set. When used as an API key, these only allow access to that function.

The key can be included in a query string variable named code, as above, or it can be included in an x-functions-key HTTP header. The value of the key can be any function key defined for the function, or any host key.

Manage Keys

You can manage the keys from the manage option on the function.

Here you can add new keys, revoke or change them.

You can choose to allow requests without keys or specify that the master key must be used by changing the authLevel property in the function.json in the trigger binding section.

{
  "disabled": false,
  "bindings": [
    {
      "authLevel": "function",
      "name": "req",
      "type": "httpTrigger",
      "direction": "in",
      "route": "demo"
    },
    {
      "name": "$return",
      "type": "http",
      "direction": "out"
    }
  ]
}

AuthLevel

The AuthLevel property determines what keys, if any, need to be present on the request in order to invoke the function. See Working with keys below. The value can be one of the following:

• anonymous: No API key is required.
• function: A function-specific API key is required. This is the default value if none is provided.
• admin : The master key is required.

To turn off the requirement for a key we change the AuthLevel to "anonymous" like this

{
  "disabled": false,
  "bindings": [
    {
      "authLevel": "anonymous",
      "name": "req",
      "type": "httpTrigger",
      "direction": "in",
      "route": "demo"
    },
    {
      "name": "$return",
      "type": "http",
      "direction": "out"
    }
  ]
}

Now when we call the function we use the URL

https://serversncodefunctiondemo.azurewebsites.net/api/demo

We have a route api/demo setup and no key required our function will respond on this url and return "hello ...."