Crash Code-11 (Sync Blob Data to EF Core)

Problem Description

Building a reliable SaaS-based order management service often requires background workers to sync file data into a relational database. The objective is to implement a .NET Core Azure Function that triggers every 15 minutes to ingest order data.

The function must read a JSON file from Azure Blob Storage in read-only mode, deserialize the payload, and perform an upsert (update or insert) operation on a SQL database using Entity Framework Core. Additionally, the system must maintain strict observability by tracking every created or updated order as a custom event in Azure Application Insights using dependency injection.

Sample flow

1. The Azure Function is triggered by the timer chron schedule (TimerTrigger).
2. The function checks the incoming string payload. If the string is not null or whitespace, it deserializes the payload into a list of orders. Otherwise, it exits early.
3. The function iterates through the list and queries the database for existing records: 3.1. If the order exists, it updates the record and tracks an update event in Application Insights. 3.2. If the order does not exist, it creates a new record and tracks a creation event in Application Insights.
4. The database changes are committed as a single transaction.

Input data

The JSON file containing the list of orders is injected directly into the function via the string blobString parameter using Azure Blob input bindings. The function assumes the incoming JSON string loaded from Azure Blob Storage is always well-formed JSON, an empty string, or null.

Sample content

[
    {
        "OrderId": 1,
        "Amount": 12.34,
        "Currency": "USD"
    },
    {
        "OrderId": 2,
        "Amount": 98.76,
        "Currency": "EUR"
    }
]

Approach

To adhere to enterprise standards and maximize performance, the solution avoids the “N+1 query problem” (executing a separate database read for every order in the loop). Instead, it reads the IDs of the incoming orders and fetches all matching existing records from the database in a single query. These existing records are mapped to a dictionary for O(1) in-memory lookups during the upsert loop.

The solution requires two main components:

1. Startup Configuration: Registers the Entity Framework TaskDbContext with a Scoped lifetime for proper dependency injection.
2. Function Execution: Uses constructor injection for the database context, telemetry client, and a helper interface to handle the business logic cleanly.

Code Implementation

Dependency Injection Configuration (Startup.cs)

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18

using Microsoft.Azure.Functions.Extensions.DependencyInjection;
using Microsoft.EntityFrameworkCore;
using Microsoft.Extensions.DependencyInjection;

[assembly: FunctionsStartup(typeof(Startup))]

public class Startup : FunctionsStartup
{
    public override void Configure(IFunctionsHostBuilder builder)
    {
        // Add TaskDbContext as Scoped
        builder.Services.AddDbContext<TaskDbContext>(options =>
            options.UseInMemoryDatabase("OrdersDb"),
            ServiceLifetime.Scoped);
            
        // Note: ITaskHelper and TelemetryClient are assumed to be registered here as well.
    }
}

Azure Function Implementation (ImportOrdersFunction.cs)

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79

using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Threading.Tasks;
using Microsoft.ApplicationInsights;
using Microsoft.ApplicationInsights.DataContracts;
using Microsoft.Azure.WebJobs;

public class ImportOrdersFunction
{
    private readonly TaskDbContext _dbContext;
    private readonly ITaskHelper _taskHelper;
    private readonly TelemetryClient _telemetryClient;
    private const string BlobPath = "import/orders.json";

    public ImportOrdersFunction(
        TaskDbContext dbContext, 
        ITaskHelper taskHelper, 
        TelemetryClient telemetryClient)
    {
        _dbContext = dbContext;
        _taskHelper = taskHelper;
        _telemetryClient = telemetryClient;
    }

    [FunctionName("ImportOrdersFunction")]
    public async Task Run(
        [TimerTrigger("0 */15 * * * *")] TimerInfo myTimer,
        [Blob(BlobPath, FileAccess.Read)] string blobString)
    {
        if (string.IsNullOrWhiteSpace(blobString))
        {
            return;
        }

        var incomingOrders = _taskHelper.Deserialize<List<Order>>(blobString);
        
        if (incomingOrders == null || !incomingOrders.Any())
        {
            return;
        }

        // Optimization: Fetch existing records in one query to avoid N+1 DB calls
        var incomingOrderIds = incomingOrders.Select(o => o.OrderId).ToList();
        var existingOrders = _dbContext.Orders
            .Where(o => incomingOrderIds.Contains(o.OrderId))
            .ToDictionary(o => o.OrderId);

        foreach (var order in incomingOrders)
        {
            if (existingOrders.TryGetValue(order.OrderId, out var existingOrder))
            {
                // Update
                existingOrder.Amount = order.Amount;
                existingOrder.Currency = order.Currency;
                _dbContext.Orders.Update(existingOrder);
                
                TrackTelemetry("OrderUpdated", order.OrderId);
            }
            else
            {
                // Create
                _dbContext.Orders.Add(order);
                
                TrackTelemetry("OrderCreated", order.OrderId);
            }
        }

        // Commit all changes in a single transaction
        await _dbContext.SaveChangesAsync();
    }

    private void TrackTelemetry(string eventName, int orderId)
    {
        var telemetry = new EventTelemetry(eventName);
        telemetry.Properties.Add("OrderId", orderId.ToString());
        _telemetryClient.TrackEvent(telemetry);
    }
}

Additional Information

• Timer Trigger Format: The cron expression 0 */15 * * * * translates to “run at second 0, every 15 minutes, of every hour, every day”.
• State Management: While the code utilizes Microsoft.EntityFrameworkCore.InMemory for testing parameters, switching to SQL Server or PostgreSQL in production only requires updating the Startup builder options. The upsert logic inside the function remains unchanged.
• Telemetry Setup: The TelemetryClient automatically batches custom tracking events and transmits them asynchronously to Azure App Insights, preventing network bottlenecks during the execution loop.

Complexity Analysis

• Time Complexity: O(N) where N is the number of orders in the parsed JSON. Retrieving existing records using .Contains() scales highly efficiently in SQL. The dictionary lookups execute in O(1) time per record. Saving all records using .SaveChangesAsync() reduces the network roundtrip overhead to a constant time factor compared to saving incrementally inside the loop.
• Space/Memory Complexity: O(N) where N is the number of incoming records. The function holds the deserialized list, the list of identifiers, and the dictionary of database-matched items in memory concurrently before garbage collection safely drops them at the end of the invocation.