ORMs Meet SQL – My Presentation for Tuga IT 2016

Even having been just a few hours at the Tuga IT conference, I can say that it was a huge success! So many awesome speakers, the chance to meet friends and talk about all the crazy stuff going on was great!

The Tuga IT organization and sponsors really deserve a big applause for making it possible.

image

I will highlight one of them, because I have the feeling that without his enthusiasm, vision and “madness”, this wouldn’t have happened: Niko Neugebauer (@nikoneugebauer), aka, “Columnstore guy”! Well done, man! Winking smile

As for me, thanks to the NetPonto community who selected my presentation, I had a chance to do a light talk about ORMs, which you can find in my OneDrive here. Kudos to NetPonto for having me on board! Winking smile

See you next year, I hope!

Entity Framework Pitfalls: Migrations and DbContext Construction

If you want to create or apply a migration over a DbContext-derived context and your context doesn’t have a public parameterless constructor, it will fail. This happens with both .NET Framework and .NET Core: the problem is that the migrations framework has no way of knowing how to create the context so as to get information from it, the migrations framework actually instantiates it!

There are two ways to go around it:

  • Create a public parameterless constructor on your context;
  • Create a class that implements IDbContextFactory<T>, where the generic parameter is your DbContext-derived class.

Having a parameterless constructor is not always ideal, because you may need to pass something in, like a connection string, but you can do it just for executing migrations and them remove it again. Just make sure that the connection string in use is the right one.

As for the IDbContextFactory<T> approach, it’s quite simple:

public class BlogContextFactory : IDbContextFactory<BlogContext>

{

    public BlogContext Create()

    {

        var connectionString = "get a connection string somehow";

        return new BlogContext(connectionString);

    }

}

Basically, you have to implement the Create method so as to return an instance of your context, doesn’t really matter how you instantiate it. The migrations API will find this class automatically.

If you don’t do any of this, you may get weird errors, like, “unable to find the DbContext”, which doesn’t really help much.

Another problem you may face is if you have multiple DbContexts, in that case, you have to tell migrations which one to use.

Getting the Weather Forecast Using SharePoint Workflows

Introduction

I already wrote a post on how to use SharePoint 2013 workflows. This time, I’ll get back to this topic, with a slightly more interesting (IMO) use case: a recurring workflow that runs once a day and emails users the weather forecast for the next day!

Let’s get started! This will work on both SharePoint 2013 on premises or SharePoint Online.

Weather Forecast

I needed a web service for retrieving the weather forecast that was:

  • Free (of course!)
  • REST (not SOAP!)
  • Using JSON (not XML!)

I ended up using Yahoo’s excellent weather web service. It does all this and a lot more, and I suggest you have a look at it.

image

I had to find out the WOEID for the city I was interested in – Coimbra, Portugal, of course – which was as simple as running this YQL query:

select woeid from geo.places(1) where text="coimbra, pt"

Then, all I had to do was pass the returned value (739672) to the query passed in the query string of the REST web service:

https://query.yahooapis.com/v1/public/yql?q=select%20*%20from%20weather.forecast%20where%20woeid%20%3D%20739672&format=json&env=store%3A%2F%2Fdatatables.org%2Falltableswithkeys

The result is this:

image

Or, in a formatted way, much easier to read:

{

   "query": {

      "count": 1,

      "created": "2016-05-14T10:13:08Z",

      "lang": "en-US",

      "results": {

         "channel": {

            "units": {

               "distance": "mi",

               "pressure": "in",

               "speed": "mph",

               "temperature": "F"

            },

            "title": "Yahoo! Weather - Coimbra, Coimbra, PT",

            "link": "http://us.rd.yahoo.com/dailynews/rss/weather/Country__Country/*https://weather.yahoo.com/country/state/city-739672/",

            "description": "Yahoo! Weather for Coimbra, Coimbra, PT",

            "language": "en-us",

            "lastBuildDate": "Sat, 14 May 2016 11:13 AM WEST",

            "ttl": "60",

            "location": {

               "city": "Coimbra",

               "country": "Portugal",

               "region": " Coimbra"

            },

            "wind": {

               "chill": "57",

               "direction": "325",

               "speed": "7"

            },

            "atmosphere": {

               "humidity": "90",

               "pressure": "1004.0",

               "rising": "0",

               "visibility": "16.0"

            },

            "astronomy": {

               "sunrise": "6:17 am",

               "sunset": "8:43 pm"

            },

            "image": {

               "title": "Yahoo! Weather",

               "width": "142",

               "height": "18",

               "link": "http://weather.yahoo.com",

               "url": "http://l.yimg.com/a/i/brand/purplelogo//uh/us/news-wea.gif"

            },

            "item": {

               "title": "Conditions for Coimbra, Coimbra, PT at 10:00 AM WEST",

               "lat": "40.194672",

               "long": "-8.40737",

               "link": "http://us.rd.yahoo.com/dailynews/rss/weather/Country__Country/*https://weather.yahoo.com/country/state/city-739672/",

               "pubDate": "Sat, 14 May 2016 10:00 AM WEST",

               "condition": {

                  "code": "26",

                  "date": "Sat, 14 May 2016 10:00 AM WEST",

                  "temp": "57",

                  "text": "Cloudy"

               },

               "forecast": [

                  {

                     "code": "39",

                     "date": "14 May 2016",

                     "day": "Sat",

                     "high": "61",

                     "low": "54",

                     "text": "Scattered Showers"

                  },

                  {

                     "code": "28",

                     "date": "15 May 2016",

                     "day": "Sun",

                     "high": "71",

                     "low": "54",

                     "text": "Mostly Cloudy"

                  },

                  {

                     "code": "30",

                     "date": "16 May 2016",

                     "day": "Mon",

                     "high": "76",

                     "low": "52",

                     "text": "Partly Cloudy"

                  },

                  {

                     "code": "28",

                     "date": "17 May 2016",

                     "day": "Tue",

                     "high": "77",

                     "low": "53",

                     "text": "Mostly Cloudy"

                  },

                  {

                     "code": "30",

                     "date": "18 May 2016",

                     "day": "Wed",

                     "high": "78",

                     "low": "52",

                     "text": "Partly Cloudy"

                  },

                  {

                     "code": "30",

                     "date": "19 May 2016",

                     "day": "Thu",

                     "high": "77",

                     "low": "52",

                     "text": "Partly Cloudy"

                  },

                  {

                     "code": "30",

                     "date": "20 May 2016",

                     "day": "Fri",

                     "high": "71",

                     "low": "55",

                     "text": "Partly Cloudy"

                  },

                  {

                     "code": "28",

                     "date": "21 May 2016",

                     "day": "Sat",

                     "high": "72",

                     "low": "55",

                     "text": "Mostly Cloudy"

                  },

                  {

                     "code": "30",

                     "date": "22 May 2016",

                     "day": "Sun",

                     "high": "72",

                     "low": "53",

                     "text": "Partly Cloudy"

                  },

                  {

                     "code": "30",

                     "date": "23 May 2016",

                     "day": "Mon",

                     "high": "75",

                     "low": "52",

                     "text": "Partly Cloudy"

                  }

               ],

               "description": "<![CDATA[<img src=\"http://l.yimg.com/a/i/us/we/52/26.gif\"/>\n<BR />\n<b>Current Conditions:<\/b>\n<BR />Cloudy\n<BR />\n<BR />\n<b>Forecast:<\/b>\n<BR /> Sat - Scattered Showers. High: 61Low: 54\n<BR /> Sun - Mostly Cloudy. High: 71Low: 54\n<BR /> Mon - Partly Cloudy. High: 76Low: 52\n<BR /> Tue - Mostly Cloudy. High: 77Low: 53\n<BR /> Wed - Partly Cloudy. High: 78Low: 52\n<BR />\n<BR />\n<a href=\"http://us.rd.yahoo.com/dailynews/rss/weather/Country__Country/*https://weather.yahoo.com/country/state/city-739672/\">Full Forecast at Yahoo! Weather<\/a>\n<BR />\n<BR />\n(provided by <a href=\"http://www.weather.com\" >The Weather Channel<\/a>)\n<BR />\n]]>",

               "guid": {

                  "isPermaLink": "false"

               }

            }

         }

      }

   }

}

SharePoint Workflow

Next, I wanted to use this information in a SharePoint workflow. This workflow has to run every day automatically, and this was a challenge, because it is not supported out of the box. But, first things first, let’s see how I designed it the Weather Forecast workflow.

Initialization

Open SharePoint Designer and create a new Site workflow, make sure it is using the SharePoint 2013 settings. I first added a stage called Initialization, where I created a string workflow variable named url, to which I assigned the web service URL:

image

Next, I also created a dictionary variable named requestHeaders, to which I added a string entry called Accept, with value application/json (I don’t think this is necessary, but it is always good to tell a REST web service what we’re expecting):

image

Get Weather Forecast

On a new stage named Get Weather Forecast, I add the call to the web service itself, by adding a Call HTTP Web Service action, pointing to the contents of the url variable:

image

After I do that, I access the properties of Call HTTP Web Service action and I set the request headers as coming from the requestHeaders variable I just created:

image

Make sure you set the response to go to a new dictionary variable, responseContent. The rest is really irrelevant, but if you run into problems, it may be useful to look at the responseCode (string) and responseHeaders (dictionary) variables.

Process Response

I then added a new stage named Process Response, where I added an action of type Get an Item from a Dictionary, from which I extracted value query/results/channel/item/forecast(1)/text from the responseContent variable, which contains the result of the web service invocation, into a new string variable called result:

image

This needs some explanation. The value I’m extracting from dictionary is similar to a JSONPath – the JavaScript dotted-path to traverse an object – with two differences:

  • Instead of dots (.), we use slashes (/)
  • Instead of square brackets ([]) for indexed properties, we use parenthesis

The path we are extracting is so that we can jump directly to the textual description of the forecast for the next day, according to the schema that the Yahoo web service returns. If you use another one, you will need to change this path, of course.

Send Email

The next stage I added is called Send Email, and it’s where I send the weather forecast to the interested users. In it, I add a single action of type Send an Email,

image

In the To field, we should use a group instead of explicitly named users, something like “Weather Forecast Users”. In the body of the email, I have a mix of text and the result variable, which, if you remember, contains the portion of the web service response that has the textual description of the forecast.

Sleep for a Day

The last stage I’m adding is called Sleep for a Day, and, again, only has a single activity, Pause for Duration, configured to sleep for 1 day.


Wrap Up

This is what the resulting workflow looks like:

imageIn each stage, we need to add a transition to the next one, except in Sleep for a Day, on which the transition is going to Initialization. So, I have the following stages and transitions:

Stage Transition
Initialization Get Weather Forecast
Get Weather Forecast Process Response
Process Response Send Email
Send Email Sleep for a Day
Sleep for a Day Initialization

As for variables, I have:

image

Starting the Workflow

Now, all we have to do is, after publishing the workflow, is starting it. We do that through Site contentsSite workflows, normally available at URL /_layouts/15/workflow.aspx, and clicking the Weather Forecast link.

image

This workflow, it should be noted, shouldn’t stop, because after it reaches the final stage, it jumps to the first one, don’t forget about it.

Debugging the Workflow

The debugging experience is not great. What you can do is log stuff to the workflow history, using a Log to History List action, but be warned that you cannot log large contents: for example, logging the result of the web service call won’t work. What you can do is see where the workflow stopped and see if any exception was thrown, by clicking at the Status field of the running or completed workflow and inspecting the messages produced:

image

You also have the Workflow Health page (/_layouts/15/WorkflowServiceHealth.aspx), on which you can also see some information and stop running workflows that have gone rogue:

image

It is also worth pointing out that, unless you configured it otherwise, the output history for the workflows is stored in a hidden list called Workflow History (/Lists/Workflow%20History/AllItems.aspx):

image

Conclusion

SharePoint offers some mechanisms for integrating outside data, in the form of REST web services returning JSON, into workflows. While more complex scenarios require more advanced techniques and APIs (Business Connectivity Services, for example, or custom workflow actions), it is still possible to do some interesting stuff with what is available out of the box. Variations to this example might include conditional checks, like, only sending a warning email if the weather forecast is not good, or store the results for the next days, etc. I leave that as an exercise to you, dear reader! Smile In the meantime, do let me know how it goes!

Elasticsearch @ NetPonto

(Portuguese only, sorry!)

No passado sábado participei no primeiro encontro da comunidade NetPonto em Aveiro, onde fiz uma apresentação sobre Elasticsearch (a segunda em quinze dias!).

Podem encontrar os slides e os exemplos na minha OneDrive aqui.

Obrigado a todos os que participaram! Winking smile