DEV Community: Saumya-Ranjan-Mishra

Fetching column data from a column database

Saumya-Ranjan-Mishra — Wed, 10 Dec 2025 05:01:57 +0000

BigQuery Columnar Storage & Efficient JSON Fetching (C#)

Using bigquery?

Yes — BigQuery is a columnar, distributed, serverless analytical database.
It stores data in a columnar format (Capacitor), which makes it extremely fast for analytical queries, aggregations, and large scans.

Problem

If you fetch BigQuery data row-by-row in C#, and manually build arrays, it becomes:

Slow for large datasets
CPU-heavy
Memory-heavy
Inefficient for high-volume tables

Example of what you want:

export interface DealData {
  Season: number[],
  countryOfProduction: string[],
  productionGroup: string[],
  supplierName: string[],
  dealTypes: string[]
}

Best Practice: Let BigQuery Build Arrays (Using ARRAY_AGG)

Instead of retrieving many rows and aggregating in C#, BigQuery can directly return one row with arrays for each column using ARRAY_AGG.

Optimized BigQuery SQL

SELECT
  ARRAY_AGG(Season) AS Season,
  ARRAY_AGG(countryOfProduction) AS countryOfProduction,
  ARRAY_AGG(productionGroup) AS productionGroup,
  ARRAY_AGG(supplierName) AS supplierName,
  ARRAY_AGG(dealTypes) AS dealTypes
FROM your_dataset.your_table;

Benefits

Only 1 row returned
BigQuery handles the parallel aggregation
Faster and more memory-efficient
Minimal work in C#

C# Example: Reading Aggregated Arrays from BigQuery

using Google.Cloud.BigQuery.V2;
using Newtonsoft.Json;

public class DealData
{
public List<int> Season { get; set; } = new();
public List<string> countryOfProduction { get; set; } = new();
public List<string> productionGroup { get; set; } = new();
public List<string> supplierName { get; set; } = new();
public List<string> dealTypes { get; set; } = new();
}

public async Task<string> GetDealDataAsync()
{
  BigQueryClient client = BigQueryClient.Create("your-project-id");

      string query = @"
          SELECT
            ARRAY_AGG(Season) AS Season,
            ARRAY_AGG(countryOfProduction) AS countryOfProduction,
            ARRAY_AGG(productionGroup) AS productionGroup,
            ARRAY_AGG(supplierName) AS supplierName,
            ARRAY_AGG(dealTypes) AS dealTypes
          FROM your_dataset.your_table
      ";

    var result = client.ExecuteQuery(query, null);
    var row = result.First();

    DealData dealData = new DealData
    {
        Season = row["Season"].ToList<int>(),
        countryOfProduction = row["countryOfProduction"].ToList<string>(),
        productionGroup = row["productionGroup"].ToList<string>(),
        supplierName = row["supplierName"].ToList<string>(),
        dealTypes = row["dealTypes"].ToList<string>()
    };

    return JsonConvert.SerializeObject(dealData);

}

Summary

BigQuery is columnar, highly optimized for analytical workloads.
Avoid looping through BigQuery rows in C#.
Use ARRAY_AGG to let BigQuery perform column-wise aggregation.
C# then reads a single row with arrays → fast, clean, efficient.

Azure Service Bus

Saumya-Ranjan-Mishra — Mon, 29 Sep 2025 11:07:02 +0000

Azure Service Bus Queue Types

1) Queues: One to one communication. Message is put into queue and the receiver application receives it.
2) topics and subscriptions: One to Many communication. Once the message is in queue in particular topic, the message will be sent to all the subscribers of the topic. There can be multiple sender as well.

Service Bus SDKs:

1) Service Bus Client: Is a primary iterface for developers interacting with the Service Bus Client library. It serves as a gateway from which all interaction with the library will occur.

2) Service Bus Sender: is scoped to a partcular queue/topic, and is created using the Service Bus Client. The sender allows us to send messages to a queue or topic. It also allows for scheduling messages to be available for delivery at a specified date.

3) Service Bus receiver: scoped to a particular queue or subscription and is created using the service bus client.Receiver allows us to receive messages from a queue or subscription.

4) Service Bus Processor: is scoped to a a particular queue or subscription and is created using client. The Procesr can be thought of as an abstraction around the set of RECEIVERS.

// sending the message to the servic bus

car cleint = new ServiceBusClient("Connection string");
ServiceBusSender sender = client.CreateSender("QueueName");
await sender.SendMessageAsync(new ServiceBusMessage("Hello Saumya"));

// Receiving the message

ServiceBusReceiver receiver = client.createREceiver("QueueName");
ServiceBusReceivedMessage msg = await receiver.ReceiverMessageAsync();
Console.WriteLine(msg.Body.ToString());

Allowed Operations on messages received

1) complete: when a message is received from queue or sbscription, the receiver can call the "Complete" operation to indicate that the message has been successfully processed and can be removed from the queue or subscription. This ensures that other receiver will not be receiving the message

receiver.CompleteMessageAsync(message);

2) Abandon: IF the receiver of the message is not able to process the message, then it can call the abandon operation to indicate that the message should be made available for another receiver to process. This returns the message to the queue or subscription and allows another receiver to receive and process it.

receiver.AbandonMessageAsync(message); // this will also increase the delivery count of the message.

3) Defer: If a message cannot tbe processed immidiately, the receiver can call the Defer operation and store it separately untill it is ready to be processed. This allows the receiver to prioritize other messages in the queue or subscription and come back to deferred message later. (may be because the service that will process the message may be down or something, mainly if the issue is not with the message).

receiver.AbandonMessageAsync(message); // this will also increase the delivery count of the message.
ServiceBusReceivedMessage defMsg = await receiver.ReceiveDefferedMessageAsync(message.SequenceMessage);

4) DeadLetter: If a message cannot be processed due to some errir that cannt be resolved, the receiver can call the "Deadletter" operation to inicate that the message should be moved to dead-letter ueue to topic for further analysis or processing. Dead-lettering is useful for handling messages that cannot be processsed due to issues with the message content, receiver code, or configuration.

receiver.DeadLetterMessageAsync(message, "reason", "description"); // push the message to DeadLetter

// retrieve the deadLetter message
ServiceBusReceiver dlqrcv = client.CreateReceiver("firstQueue", new BusReceiverOptions{
    SubQueue = SubQueue.DeadLetter
});

ServiceBusREceiverMessage msg = await dlqrcv.ReceiverMessageAsync();

Filtering the messages to put in the queue.

For filtering the message we have to put some rules against that will be matched. The filter is usually used if we have to send the message wih particular tags to specific Queues or topics.
To apply the filters we have to first create the Administrator Client, like beow:

ServiceBusAdministrationClient administrationClient = new ServiceBus AdministrationClient("<Connection string>");

1) Applying SQL Filter

Below code create a ubscription that subscribe to "mytopic" topic in Azure Service Bus and specifically contains the message that has the tag as "name=saumya"

using Azure.Messaging.ServiceBus.Administration;

var adminClient = new ServiceBusAdministrationClient("<connection-string>");
string topicName = "customers-topic";
string subscriptionName = "prime-customers";

// Create subscription with a filter
if (!await adminClient.SubscriptionExistsAsync(topicName, subscriptionName))
{
    var options = new CreateSubscriptionOptions(topicName, subscriptionName);

    var ruleOptions = new CreateRuleOptions
    {
        Name = "PrimeMemberRule",
        Filter = new SqlRuleFilter("isPrimeMember = true")
    };

    await adminClient.CreateSubscriptionAsync(options, ruleOptions);
}


sending message:

class Customers
{
    string name {get; set;}
    int userId {get; set;}
    bool isPrimeMember{get; set;}
}

var sender  = client.CreateSender("mytopic");
Customer obj = new Customer {name="Saumya", userId = 1, isPrimeMember = false};

var message = new ServiceBusMessage()
{
    ApplicationProperties = {{ "name",obj.name} , {"userId",obj.userID} , {"isPrimeMember",false}};

    await sender.sendMessageAsync(message);
}

2) Correlation Filter

using Azure.Messaging.ServiceBus.Administration;

var adminClient = new ServiceBusAdministrationClient("<connection-string>");
string topicName = "customers-topic";
string subscriptionName = "prime-customers";

// Create subscription with Correlation Filter
if (!await adminClient.SubscriptionExistsAsync(topicName, subscriptionName))
{
    var options = new CreateSubscriptionOptions(topicName, subscriptionName);

    var ruleOptions = new CreateRuleOptions
    {
        Name = "PrimeMemberRule",
        Filter = new CorrelationRuleFilter()
    };

    // Boolean property match
    ruleOptions.Filter.Properties.Add("isPrimeMember", true);

    await adminClient.CreateSubscriptionAsync(options, ruleOptions);
}

Reading the object properties

using Azure.Messaging.ServiceBus;

// client
var client = new ServiceBusClient("<connection-string>");

// queue receiver
var receiver = client.CreateReceiver("customers-queue");

// receive one message
ServiceBusReceivedMessage received = await receiver.ReceiveMessageAsync();

if (received != null)
{
    // Deserialize body into Customers object
    var customer = received.Body.ToObjectFromJson<Customers>();

    Console.WriteLine($"Customer Name: {customer.name}");
    Console.WriteLine($"Customer Id: {customer.userId}");
    Console.WriteLine($"Prime Member: {customer.isPrimeMember}");

    // Read ApplicationProperties (metadata)
    if (received.ApplicationProperties.TryGetValue("isPrimeMember", out var primeFlag))
    {
        Console.WriteLine($"(From Properties) Prime Flag: {primeFlag}");
    }

    // complete the message
    await receiver.CompleteMessageAsync(received);
}

Background Services in ASP.Net core

Saumya-Ranjan-Mishra — Fri, 26 Sep 2025 08:38:14 +0000

What is BackgroundService in .NET?

BackgroundService is an abstract base class in .NET for implementing long-running tasks in the background of your application.

It is part of the Microsoft.Extensions.Hosting namespace and is usually used in ASP.NET Core or Worker Services.

It implements the IHostedService interface, which the .NET generic host uses to manage the lifetime of hosted background tasks.

Key points about BackgroundService:

It provides a ExecuteAsync(CancellationToken stoppingToken) method you override to write your background logic. This runs in a separate thread managed by the host.
The host automatically passes a CancellationToken so you can gracefully stop when the app shuts down.
It starts when your app starts, and stops when the host shuts down.
It is commonly used for Processing messages from a queue (Kafka, RabbitMQ, Azure Service Bus, etc.) or Periodic background tasks (cron-like jobs) or Monitoring external resources or services.

Basic Implementation

Step 1: Create a Background Service

using Microsoft.Extensions.Hosting;
using Microsoft.Extensions.Logging;

public class MyBackgroundService : BackgroundService
{
    private readonly ILogger<MyBackgroundService> _logger;

    public MyBackgroundService(ILogger<MyBackgroundService> logger)
    {
        _logger = logger;
    }

    protected override async Task ExecuteAsync(CancellationToken stoppingToken)
    {
        _logger.LogInformation("Background Service is starting.");

        while (!stoppingToken.IsCancellationRequested)
        {
            _logger.LogInformation("Background Service is doing work at: {time}", DateTimeOffset.Now);

            // Simulate work
            await Task.Delay(5000, stoppingToken);
        }

        _logger.LogInformation("Background Service is stopping.");
    }
}

Step 2: Register it in Program.cs

var builder = WebApplication.CreateBuilder(args);

// Register background service
builder.Services.AddHostedService<MyBackgroundService>();

var app = builder.Build();
app.MapGet("/", () => "Hello World!");
app.Run();

How it works

When the application starts, the host creates and starts the background service.

The ExecuteAsync loop keeps running until the app is shutting down.

When shutdown happens (e.g., Ctrl+C or app stop in cloud), the stoppingToken is triggered → loop ends → cleanup happens gracefully.

But that is not all. This a simple implementation, but in actual production system you might be using the background service for consuming messages from Queue services or using some database and for both of them, we will be using DI for getting the required services.

How to get the dependency services when required without any http request, Follow this link to read about that.

Understanding `IServiceScopeFactory.CreateScope()` in .NET and When to Use It

Saumya-Ranjan-Mishra — Fri, 26 Sep 2025 08:37:06 +0000

When working with dependency injection (DI) in ASP.NET Core, developers often stumble upon a tricky problem:

How do I resolve a scoped service (like DbContext) outside the normal HTTP request pipeline?

That’s where IServiceScopeFactory.CreateScope() comes into play.

The Problem

ASP.NET Core services have three main lifetimes:

Singleton → Created once for the entire application.
Scoped → Created once per HTTP request.
Transient → Created every time it’s requested.

Normally, in controllers or middleware, you don’t worry about scopes. ASP.NET Core automatically creates a scope for each request.

But what if you’re outside of a request? For example:

Running a background service (IHostedService, BackgroundService)
Executing a scheduled job
Writing a console app with the Generic Host

If you try to inject a DbContext (scoped service) directly into a singleton service like BackgroundService, it fails. The DI container doesn’t know how to manage its lifetime.

The Solution: `IServiceScopeFactory.CreateScope()`

IServiceScopeFactory allows you to manually create a DI scope.

Inside that scope, you can safely resolve scoped services (like DbContext) and they’ll be disposed automatically when the scope ends.

Example: Using in a Background Service

public class MyBackgroundService : BackgroundService
{
    private readonly IServiceScopeFactory _scopeFactory;

    public MyBackgroundService(IServiceScopeFactory scopeFactory)
    {
        _scopeFactory = scopeFactory;
    }

    protected override async Task ExecuteAsync(CancellationToken stoppingToken)
    {
        while (!stoppingToken.IsCancellationRequested)
        {
            using (var scope = _scopeFactory.CreateScope())
            {
                var dbContext = scope.ServiceProvider.GetRequiredService<ApplicationDbContext>();

                // Use DbContext safely here
                var users = await dbContext.Users.ToListAsync();
            }

            await Task.Delay(10000, stoppingToken);
        }
    }
}

Congratulations on creating your first production ready background job.
Keep learning, keep progressing..

Unit Of Work In ASP.Net Core

Saumya-Ranjan-Mishra — Tue, 23 Sep 2025 07:18:38 +0000

Unit Of Work.. What is it ?

The Unit of Work pattern is a design pattern used to group multiple operations (usually repository calls) into a single transaction. It ensures that either all operations succeed or none are applied, maintaining data consistency.

It usually works alongside the Repository pattern, which abstracts data access logic.

As I said it can work with repository patten, I will keep the code fairly simple rather than over-complicating it with generic repository.

Setting up the repositories first...

entities

// Models/Product.cs
public class Product
{
    public int Id { get; set; }
    public string Name { get; set; }
    public decimal Price { get; set; }
}

// Models/Order.cs
public class Order
{
    public int Id { get; set; }
    public int ProductId { get; set; }
    public DateTime OrderDate { get; set; }
}

// Models/Log.cs
public class Log
{
    public int Id { get; set; }
    public string Message { get; set; }
    public DateTime Timestamp { get; set; }
}

set the DbContext

public class AppDbContext : DbContext
{
    public AppDbContext(DbContextOptions<AppDbContext> options) : base(options) {}

    public DbSet<Product> Products { get; set; }
    public DbSet<Order> Orders { get; set; }
    public DbSet<Log> Logs { get; set; }
}

Define the repositories

public interface IProductRepository
{
    Task<Product> GetByIdAsync(int id);
    Task AddAsync(Product product);
    void Update(Product product);
    void Delete(Product product);
}

public interface IOrderRepository
{
    Task AddAsync(Order order);
}

public interface ILogRepository
{
    Task AddAsync(Log log);
}

Implement the repositories

// Product repositories
public class ProductRepository : IProductRepository
{
    private readonly AppDbContext _context;
    public ProductRepository(AppDbContext context)
    {
        _context = context;
    }

    public async Task<Product> GetByIdAsync(int id)
    {
        return await _context.Products.FindAsync(id);
    }

    public async Task AddAsync(Product product)
    {
        await _context.Products.AddAsync(product);
    }

    public void Update(Product product)
    {
        _context.Products.Update(product);
    }

    public void Delete(Product product)
    {
        _context.Products.Remove(product);
    }
}

public class OrderRepository : IOrderRepository
{
    private readonly AppDbContext _context;
    public OrderRepository(AppDbContext context)
    {
        _context = context;
    }

    public async Task AddAsync(Order order)
    {
        await _context.Orders.AddAsync(order);
    }
}

public class LogRepository : ILogRepository
{
    private readonly AppDbContext _context;
    public LogRepository(AppDbContext context)
    {
        _context = context;
    }

    public async Task AddAsync(Log log)
    {
        await _context.Logs.AddAsync(log);
    }
}

Unit of Work Interface & Implementation

public interface IUnitOfWork : IDisposable
{
    IProductRepository Products { get; }
    IOrderRepository Orders { get; }
    ILogRepository Logs { get; }
    Task<int> CompleteAsync();
}

Implementation of IUnitOfWork

public class UnitOfWork : IUnitOfWork
{
    private readonly AppDbContext _context;

    public IProductRepository Products { get; }
    public IOrderRepository Orders { get; }
    public ILogRepository Logs { get; }

    public UnitOfWork(
        AppDbContext context,
        IProductRepository productRepository,
        IOrderRepository orderRepository,
        ILogRepository logRepository)
    {
        _context = context;
        Products = productRepository;
        Orders = orderRepository;
        Logs = logRepository;
    }

    public async Task<int> CompleteAsync()
    {
        return await _context.SaveChangesAsync();
    }

    public void Dispose()
    {
        _context.Dispose();
    }
}

Inject the dependencies

Make sure you register the dependencies as AddScoped, so that all the repositories will share the same instance of DbContext in a particular http call.

services.AddDbContext<AppDbContext>(options =>
    options.UseSqlServer(Configuration.GetConnectionString("DefaultConnection")));

services.AddScoped<IProductRepository, ProductRepository>();
services.AddScoped<IOrderRepository, OrderRepository>();
services.AddScoped<ILogRepository, LogRepository>();
services.AddScoped<IUnitOfWork, UnitOfWork>();

Operations in controller

[ApiController]
[Route("api/[controller]")]
public class CheckoutController : ControllerBase
{
    private readonly IUnitOfWork _unitOfWork;

    public CheckoutController(IUnitOfWork unitOfWork)
    {
        _unitOfWork = unitOfWork;
    }

    [HttpPost]
    public async Task<IActionResult> Checkout([FromBody] ProductOrderDto dto)
    {
        var product = new Product { Name = dto.ProductName, Price = dto.Price };
        var order = new Order { ProductId = product.Id, OrderDate = DateTime.UtcNow };
        var log = new Log { Message = "New order placed", Timestamp = DateTime.UtcNow };

        await _unitOfWork.Products.AddAsync(product);
        await _unitOfWork.Orders.AddAsync(order);
        await _unitOfWork.Logs.AddAsync(log);

        // Single transaction
        await _unitOfWork.CompleteAsync();

        return Ok("Order placed successfully.");
    }
}

Congratulation! You have successfully implemented unit of work there by saving your DB from a possible inconsistent state.

Feel free to ask any question. Or ping in linkedin