Using SQL Server OpenJson with Code First Entity Framework

SQL Server’s OpenJson function is a really nice way to expose data hidden in a SQL column as JSON. Without going into the ethical arguments I have found it very useful. Today I was faced with combining it with Entity Framework Code First, or to put it another way, how do you expose the OpenJson functionality to IQuerable Linq? Here is a quick workaround (read hack), use a view.

1. Create a View to wrap your JSON e.g.;

CREATE VIEW MyJsonView AS
SELECT M.*, J.*
FROM MySqlTable M  
CROSS APPLY OPENJSON(M.MyJson)   
     WITH (Id int '$.Id', 	 
	 Name nvarchar(255) '$.Name'
	 )  
     AS J

2. Jump through a few CF hoops; USING SQL VIEWS WITH ENTITY FRAMEWORK CODE FIRST
3. You can now access the JSON that’s exposed via the View.

Honda Civic 2008 – Inertia Switch

A non-tech post, today our 2008 Honda Civic wouldn’t start and the dash lit up with VSA and Engine Warning lights. Whilst getting in the car the dash got a bang with a knee. The car decided it was in an accident and activated the Inertia Switch to stop the petrol flowing. Locating the switch was a pain in the rear. For a 2008 UK Honda Civic, you have to open the driver door, remove the vertical dash panel that sits next to the door (the other side of the wing-mirror) but on the main dash. With that removed you can reset the Inertia Switch. Hopefully the car will then start and you won’t have broken any plastic panel retainers.

Simple animated carousel in CSS3 via keyframes

I had a little delve into CSS3 Animations to see if I could create a simple carousel. My first idea is to use keyframes. I’ll start with showing the result;
AnimExample

The idea is to infinitely fade between 3 elements.

<body>
    <div class="a isanim">AAAA</div>
    <div class="b isanim">BBBB</div>
    <div class="c isanim">CCCC</div>
</body>

The core style is;

 div.isanim {
            width:  250px;
            height: 250px;
            border: 3px solid black;
            position: absolute;            
            animation-duration: 18s;
            animation-direction: normal;
            animation-iteration-count: infinite;            
            display: block;
            opacity: 0
        }

What that says is we’re going to run an animation for a total of 18 seconds and then repeat it infinite times. Easy enough. Now for the bit that I didn’t think was too obvious;

        div.a {            
            animation-name: ani1;
            animation-delay: 0s;         
        }

        div.b {
            animation-name: ani2;
            animation-delay: 6s;            
        }

        div.c {
            animation-name: ani3;
            animation-delay: 12s;            
        }

We overwrite the core animation by providing specific details. The one I think is strange is that animation-delay is within the overall animation time. I.e. we’ve defined three animations all taking 18 seconds in total INCLUDING the delay. Is that correct? Is it, honestly I’m asking (?). Anyway with that in mind we can set up the keyframes;

        @keyframes ani1 {
            33% {
                background: green;
                opacity: 1;               
            }

            66% {
                display: none;
                opacity: 0
            }
        }

        @keyframes ani2 {
            0% {
                display: none;
                opacity: 0
            }

            33% {
                display: block;
                opacity: 1;
                background: red;
            }

            66% {
                display: none;
                opacity: 0;
            }           
        }

        @keyframes ani3 {
            0% {
                display: none;
                opacity: 0                
            }

            33% {
                display: block;
                opacity: 1;
                background: yellow;
            }

            66% {
                display: none;
                opacity: 0
            }           
        }

Since each animation has a proportional delay, the keyframes can all use the same % start. So after 33% they show themselves and after 66% they should have hidden themselves. I found these proportions worked better than 100% as that created too much of a clash of colours. One gotcha seems to be the 0% keyframe rule. If you don’t set that up you can get a strange ‘jumping’ effect.

Event Grid – Filters with Custom Events

In End-To-End Custom Events I posted about how to create a .net publisher and a c# Azure Function to listen to the post. However, Event Grid comes with some additional tricks, one of these is Filters. To test this I created two additional Azure Functions, HttpTriggerCSharpOranges and HttpTriggerCSharpApples. For this example they are exactly the same bar the log text, here is the Orange variant, you can guess what the Apple one looks like 😉

using System.Net;

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

   // Get request body
   dynamic data = await req.Content.ReadAsAsync<object>();

   // Set name to query string or body data
   string subject = data[0].subject;
   log.Info(subject);

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

The next step is to create the filtered subscriptions. The REST API pointed the way but it wasn’t that clear what I needed to define in AZ. So I entered;

az eventgrid topic event-subscription  create -h

That led to the following two filtered subscription definitions (remember to grab the Function URL by selecting the function ‘Copy Function URL’;

az eventgrid topic event-subscription create --name functionlisteneroranges --endpoint <function URL to oranges> -g gridResourceGroup --topic-name <your topic> --subject-begins-with oranges

az eventgrid topic event-subscription create --name functionlistenerapples --endpoint <function URL for apples> -g gridResourceGroup --topic-name <your topic> --subject-begins-with apples

All that’s left to do is to change the subject in the calling code to start with either apples or oranges;

new PaulsTopic
{
  Id = Guid.NewGuid().ToString(),
  EventTime = DateTime.UtcNow,
  EventType = EventType.recordInserted,
  Subject = "oranges\\paulstopics\\mytest",
  Data = new SomeData
  {
    Name = "Paul",
    Rate = 99.9
   }
}

Now when you run, the subscription pushes the correct message to the correct listener. Nice 🙂

Event Grid – End to End Custom events in .net

Event Grid is a new Azure offering that allows you to listen to a number of events from different Azure sources. One source that interested my is ‘Custom Event’. How can you get this working in .net? This is one way;

First I followed the steps in this;
Quick Start – Create and route custom events with Azure Event Grid

That provides you with the custom topic you need. In my case ‘pmtopic’. A quick note, make sure you create/have a resource group in “West US2”. I found that parts of it worked in other resource groups but other parts failed with strange errors.

Next up, create an app to ‘publish’ a custom event. I chose a Console app, but it’s just plain .net.

        public enum EventType
        {
            recordInserted
        }

        public abstract class EventTopic
        {
            
            [JsonProperty(PropertyName = "id")]
            public string Id { get; set; }

            [JsonProperty(PropertyName = "subject")]
            public string Subject { get; set; }

            [JsonProperty(PropertyName = "eventtype")]
            [JsonConverter(typeof(StringEnumConverter))]
            public EventType EventType { get; set; }

            [JsonProperty(PropertyName = "eventtime")]
            public DateTime EventTime { get; set; }

        }

        public class SomeData
        {
            public string Name { get; set; }
            public double Rate { get; set; }

        }
        public class PaulsTopic : EventTopic
        {
            [JsonProperty(PropertyName = "data")]
            public SomeData Data { get; set; }
        }

        static void Main(string[] args)
        {

            PaulsTopic[] paulsTopic = new PaulsTopic[] 
            {
                new PaulsTopic
                {
                    Id = Guid.NewGuid().ToString(),
                    EventTime = DateTime.UtcNow,
                    EventType = EventType.recordInserted,
                    Subject = "paulstopics\\mytest",
                    Data = new SomeData
                    {
                        Name = "Paul",
                        Rate = 99.9
                    }
                }
            };

            

            var messageBody = JsonConvert.SerializeObject(paulsTopic);

            PostMessage(messageBody).Wait();           
        }

        private static async Task PostMessage(string messageBody)
        {            
            using (var httpClient = new HttpClient())
            {
                StringContent content = new StringContent(messageBody);
                var request = new HttpRequestMessage()
                {
                    RequestUri = new Uri("https://<your topic endpoint here>"),
                    Method = HttpMethod.Post,
                    Content = content
                };

                request.Headers.Add("aeg-sas-key", "<your key here>");


                var response = await httpClient.SendAsync(request);
                if (!response.IsSuccessStatusCode)
                {
                    Console.WriteLine(response.ReasonPhrase);
                    var body = response.Content.ReadAsStringAsync().Result;
                }
            }
        }

The key parts appear to be that you need to set the properties in EventTopic, they don’t have to be anything specific but they need to be there. If you are following the Quick Start guide then you should be able to run the above and a new message will appear at RequestBin. I also wanted to use .net to listen to the event. For this I chose to create an Azure Function. For this I followed;
Create Serviceless Function. My function looked list this;

using System.Net;

public static async Task<HttpResponseMessage> Run(HttpRequestMessage req, TraceWriter log)
{
    log.Info("C# HTTP trigger function processed a request.");
      
   // Get request body
   dynamic data = await req.Content.ReadAsAsync<object>();

   // Set name to query string or body data
   string subject = data[0].subject;
   log.Info(subject);

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

I also switched diagnostics to ‘Info’ so I could see the output in the Function monitoring system. Note that you are sending in an array of messages so remember to get an array from the message. One missing bit of the jigsaw is you now have create the subscription from your topic to the function. To do that select the Function and ‘Get Function URL’. Insert that into the following CLI/Powershell script;

az eventgrid topic event-subscription create --name functionlistener --endpoint <function endpoint URL>  -g gridResourceGroup --topic-name <your topic name>

Now when you run your publishing app you should see an invocation in the Function monitoring system, nice 🙂

Next stop, I need to start experimenting with case sensitivity and security, but in truth it wasn’t that hard to get here and it looks like a interesting event mechanism.

Edit – see also this great post; Azure Custom Events

CreateDocument in DocumentDB (Cosmos) says created but it’s not there?

Had a bit of head-scratcher today. I was using CreateDocumentAsync to write a new document to the store and the return value was ‘created’ but when I searched for the document it wasn’t there. The problem was that I was using a string for the partition Id when it should have been an INT. Doh.

DeleteDocumentAsync – PartitionKey value must be supplied

I was trying to delete a document in DocumentDB / CosmoDB and I was surprised to be faced with the error message of, ‘PartitionKey value must be supplied’. There doesn’t appear to be a way of passing the partition key to DeleteDocumentAsync. The trick is to use the request options. For example, if your collection is partitioned by ProductID;

await this.documentClient.DeleteDocumentAsync(productDocument._self, new RequestOptions { PartitionKey = new Microsoft.Azure.Documents.PartitionKey(productDocument.ProductID) });

The target “MSDeployPublish” does not exist in the project

Had a very strange issue this morning, create a new Azure Web project as usual, went to publish it and it refused to saying, ‘The target “MSDeployPublish” does not exist in the project’. So I compared the project with one of the ones that worked and for some weird reason it was missing the following line. Once added it published without issue.

<Import Project="..\packages\Microsoft.Web.WebJobs.Publish.1.0.12\tools\webjobs.targets" Condition="Exists('..\packages\Microsoft.Web.WebJobs.Publish.1.0.12\tools\webjobs.targets')" />

A method to compare blobs in different Azure blob storage accounts

I was recently asked to run a comparison of the blobs in two Azure Storage Accounts, where they should contain the same blob entries. However, some of the entries had been deleted from one account. Since it requires scanning the whole account it is expensive in cost and time. The fastest mechanism I found (and I’m sure there are better ways, please comment) was;

var blobsInBackupContainer = backupContainer.ListBlobs(useFlatBlobListing: true).OfType<CloudBlockBlob>().Select(b => b.Name);
var blobsInPrimaryContainer = primaryContainer.ListBlobs(useFlatBlobListing: true).OfType<CloudBlockBlob>().Select(b => b.Name);                

var missing = blobsInBackupContainer.Except(blobsInPrimaryContainer);
Console.WriteLine($"Missing count {missing.Count()}");
foreach (var item in missing)
{
    Console.WriteLine($"Missing item = {item}");
}