Azure Functions on .Net 8 with Entity Framework Core

Question

I need to create an Azure Function app using .Net 8 and EF Core where I can:

Perform CRUD operations from Az functions
Use either a DB Context or a repository object
Ensure either of these two objects are made available by dependency injections.

I've tried looking for tutorials or good working examples for the same but have come up empty handed so far. Any help here will be really appreciated.

EDIT: Adding error details.

I've tried the same approach as Vivek but I'm running into issues. For e.g., I am not able to get the data when I query using the dbContext. Here's my constructor:

public Function1(
    ILogger<Function1> logger,
    AppDbContext appDbContext)
{
    _logger = logger;
    _appDbContext = appDbContext;

    try
    {
        var test = _appDbContext.Locations.First();
    }
    catch (Exception ex)
    {
        string message = ex.Message;    
    }
}

Checking the value of _dbContext.Locations shows the following error:

'((Microsoft.EntityFrameworkCore.Internal.InternalDbSet<Models.Location>)_appDbContext.Locations).Local' threw an exception of type 'System.InvalidOperationException'

The exception that I see in code is as following:

at Microsoft.Data.SqlClient.SqlBuffer.ThrowIfNull()
   at Microsoft.Data.SqlClient.SqlBuffer.get_String()
   at Microsoft.Data.SqlClient.SqlDataReader.GetString(Int32 i)
   at Microsoft.EntityFrameworkCore.Query.Internal.SingleQueryingEnumerable`1.Enumerator.MoveNext()
   at System.Linq.Enumerable.TryGetSingle[TSource](IEnumerable`1 source, Boolean& found)
   at Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.Execute[TResult](Expression query)
   at Microsoft.EntityFrameworkCore.Query.Internal.EntityQueryProvider.Execute[TResult](Expression expression)
   at VTEst.Function1..ctor(ILogger`1 logger, AppDbContext appDbContext) in C:\Users\.....\Function1.cs:line 22

Finally, The output in the function console shows as following:

[2024-03-18T18:47:59.633Z] An exception occurred while iterating over the results of a query for context type 'VTest.AppDbContext'.
[2024-03-18T18:47:59.634Z] System.Data.SqlTypes.SqlNullValueException: Data is Null. This method or property cannot be called on Null values.
[2024-03-18T18:47:59.635Z]    at Microsoft.Data.SqlClient.SqlBuffer.ThrowIfNull()
[2024-03-18T18:47:59.635Z]    at Microsoft.Data.SqlClient.SqlBuffer.get_String()
[2024-03-18T18:47:59.636Z]    at Microsoft.Data.SqlClient.SqlDataReader.GetString(Int32 i)
[2024-03-18T18:47:59.636Z]    at lambda_method2(Closure, QueryContext, DbDataReader, ResultContext, SingleQueryResultCoordinator)
[2024-03-18T18:47:59.637Z]    at Microsoft.EntityFrameworkCore.Query.Internal.SingleQueryingEnumerable`1.Enumerator.MoveNext()
[2024-03-18T18:47:59.637Z] Result: An exception occurred while iterating over the results of a query for context type 'VTest.AppDbContext'.
[2024-03-18T18:47:59.638Z] System.Data.SqlTypes.SqlNullValueException: Data is Null. This method or property cannot be called on Null values.
[2024-03-18T18:47:59.639Z]    at Microsoft.Data.SqlClient.SqlBuffer.ThrowIfNull()
[2024-03-18T18:47:59.639Z]    at Microsoft.Data.SqlClient.SqlBuffer.get_String()
[2024-03-18T18:47:59.640Z]    at Microsoft.Data.SqlClient.SqlDataReader.GetString(Int32 i)
[2024-03-18T18:47:59.640Z]    at lambda_method2(Closure, QueryContext, DbDataReader, ResultContext, SingleQueryResultCoordinator)
[2024-03-18T18:47:59.641Z]    at Microsoft.EntityFrameworkCore.Query.Internal.SingleQueryingEnumerable`1.Enumerator.MoveNext()
Exception: System.Data.SqlTypes.SqlNullValueException: Data is Null. This method or property cannot be called on Null values.
[2024-03-18T18:47:59.641Z]    at Microsoft.Data.SqlClient.SqlBuffer.ThrowIfNull()
[2024-03-18T18:47:59.642Z]    at Microsoft.Data.SqlClient.SqlBuffer.get_String()
[2024-03-18T18:47:59.642Z]    at Microsoft.Data.SqlClient.SqlDataReader.GetString(Int32 i)
[2024-03-18T18:47:59.643Z]    at lambda_method2(Closure, QueryContext, DbDataReader, ResultContext, SingleQueryResultCoordinator)
[2024-03-18T18:47:59.644Z]    at Microsoft.EntityFrameworkCore.Query.Internal.SingleQueryingEnumerable`1.Enumerator.MoveNext()
Stack:    at Microsoft.Data.SqlClient.SqlBuffer.ThrowIfNull()
[2024-03-18T18:47:59.644Z]    at Microsoft.Data.SqlClient.SqlBuffer.get_String()
[2024-03-18T18:47:59.645Z]    at Microsoft.Data.SqlClient.SqlDataReader.GetString(Int32 i)
[2024-03-18T18:47:59.645Z]    at lambda_method2(Closure, QueryContext, DbDataReader, ResultContext, SingleQueryResultCoordinator)
[2024-03-18T18:47:59.646Z]    at Microsoft.EntityFrameworkCore.Query.Internal.SingleQueryingEnumerable`1.Enumerator.MoveNext().

EDIT 2: Issue resolved

Vivek and Gert - Thank you very much for your help!

Please share your code? what have you tried yet and what error you are getting? — Vivek Vaibhav Shandilya
– Vivek Vaibhav Shandilya, Commented Mar 18, 2024 at 5:03
First thing to check: does it work if you use the context in more straightforward code, for example in Linqpad with a local database? It seems that you simply read a null value in a string property that's expected to be not null. — Gert Arnold
– Gert Arnold, Commented Mar 19, 2024 at 13:03

Vivek Vaibhav Shandilya · Accepted Answer · 2024-03-19 12:59:09Z

This worked for me. I have used Http Trigger function.

My Code:

.csproj:

<Project Sdk="Microsoft.NET.Sdk">
  <PropertyGroup>
    <TargetFramework>net8.0</TargetFramework>
    <AzureFunctionsVersion>v4</AzureFunctionsVersion>
    <OutputType>Exe</OutputType>
    <ImplicitUsings>enable</ImplicitUsings>
    <Nullable>enable</Nullable>
  </PropertyGroup>
  <ItemGroup>
    <FrameworkReference Include="Microsoft.AspNetCore.App" />
    <PackageReference Include="Microsoft.Azure.Functions.Worker" Version="1.20.1" />
    <PackageReference Include="Microsoft.Azure.Functions.Worker.Extensions.Http" Version="3.1.0" />
    <PackageReference Include="Microsoft.Azure.Functions.Worker.Extensions.Http.AspNetCore" Version="1.2.0" />
    <PackageReference Include="Microsoft.Azure.Functions.Worker.Sdk" Version="1.16.4" />
    <PackageReference Include="Microsoft.ApplicationInsights.WorkerService" Version="2.21.0" />
    <PackageReference Include="Microsoft.Azure.Functions.Worker.ApplicationInsights" Version="1.1.0" />
    <PackageReference Include="Microsoft.EntityFrameworkCore" Version="8.0.3" />
    <PackageReference Include="Microsoft.EntityFrameworkCore.SqlServer" Version="8.0.3" />
  </ItemGroup>
  <ItemGroup>
    <None Update="host.json">
      <CopyToOutputDirectory>PreserveNewest</CopyToOutputDirectory>
    </None>
    <None Update="local.settings.json">
      <CopyToOutputDirectory>PreserveNewest</CopyToOutputDirectory>
      <CopyToPublishDirectory>Never</CopyToPublishDirectory>
    </None>
  </ItemGroup>
  <ItemGroup>
    <Using Include="System.Threading.ExecutionContext" Alias="ExecutionContext" />
  </ItemGroup>
</Project>

Program.cs:

using FunctionApp14;
using Microsoft.Azure.Functions.Worker;
using Microsoft.Extensions.DependencyInjection;
using Microsoft.Extensions.Hosting;
using Microsoft.EntityFrameworkCore;
using System;

var host = new HostBuilder()
    .ConfigureFunctionsWebApplication()
    .ConfigureServices(services =>
    {
        services.AddApplicationInsightsTelemetryWorkerService();
        services.ConfigureFunctionsApplicationInsights();
        services.AddDbContext<TodoContext>(options =>
            options.UseSqlServer(Environment.GetEnvironmentVariable("SQL_Conn")));

    })
    .Build();

host.Run();

Function1.cs:

using Microsoft.AspNetCore.Http;
using Microsoft.AspNetCore.Mvc;
using Microsoft.Azure.Functions.Worker;
using Microsoft.Extensions.Logging;
using Microsoft.EntityFrameworkCore;
using System.Text.Json;

namespace FunctionApp14
{
    public class Function1
    {
        private readonly ILogger<Function1> _logger;
        private readonly TodoContext _context;

        public Function1(ILogger<Function1> logger, TodoContext context)
        {
            _logger = logger;
            _context = context;
        }

        [Function("Function1")]
        public async Task<IActionResult> Run([HttpTrigger(AuthorizationLevel.Anonymous, "get", "post")] HttpRequest req)
        {
            _logger.LogInformation("C# HTTP trigger function processed a request.");
            string requestBody = await new StreamReader(req.Body).ReadToEndAsync();
            var input = JsonSerializer.Deserialize<TodoItem>(requestBody);

            _context.TodoItems.Add(input);
            await _context.SaveChangesAsync();
            return new OkObjectResult($"Welcome to Azure Functions!\nData: {input}");
        }
    }

    public class TodoItem
    {
        public int Id { get; set; }
        public string Name { get; set; }
        public bool IsComplete { get; set; }
    }

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

        public DbSet<TodoItem> TodoItems { get; set; }
    }


}

`INPUT`:

{
    "Id": 1,
    "Name": "Vivek",
    "IsComplete": true
}

`OUTPUT`:

Edited:

code to fetch data from table.

using Microsoft.AspNetCore.Http;
using Microsoft.AspNetCore.Mvc;
using Microsoft.Azure.Functions.Worker;
using Microsoft.Extensions.Logging;
using Microsoft.EntityFrameworkCore;
using System.Text.Json;

namespace FunctionApp14
{
    public class Function1
    {
        private readonly ILogger<Function1> _logger;
        private readonly TodoContext _context;

        public Function1(ILogger<Function1> logger, TodoContext context)
        {
            _logger = logger;
            _context = context;
        }

        [Function("Function1")]
        public async Task<IActionResult> Run([HttpTrigger(AuthorizationLevel.Anonymous, "get", "post")] HttpRequest req)
        {
            _logger.LogInformation("C# HTTP trigger function processed a request.");
            var product = _context.TodoItems.ToList();
            foreach (var item in product)
            {
                _logger.LogInformation($"id: {item.Id}\nname: {item.Name}\niscomplete:{item.IsComplete}");
            }
            return new OkObjectResult($"Welcome to Azure Functions!");
        }
    }

    public class TodoItem
    {
        public int Id { get; set; }
        public string Name { get; set; }
        public bool IsComplete { get; set; }
    }

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

        public DbSet<TodoItem> TodoItems { get; set; }
    }


}

`OUTPUT`:

Thanks a lot, Vivek. I've updated the original post with the error details.
ToList() is used to fetch all the values. if you want to get the first row value, I would suggest use _context.TodoItems.ToList().first()

Vishal · Accepted Answer · 2024-03-19 22:52:28Z

1

Following the suggestion in the following post, I turned on detailed errors for EF core: Entity Framework Core: `SqlNullValueException: Data is Null.` How to troubleshoot?

services.AddDbContext<AppDbContext>(
    options => options.UseSqlServer(connectionString)
                    .EnableDetailedErrors(true));

This led me to the specific error which being several columns in my table returning null data. Fixing that resolved my issue.

answered Mar 19, 2024 at 22:52

Vishal

2,2352 gold badges20 silver badges20 bronze badges

Collectives™ on Stack Overflow

Azure Functions on .Net 8 with Entity Framework Core

2 Answers 2

My Code:

`INPUT`:

`OUTPUT`:

Edited:

code to fetch data from table.

`OUTPUT`:

3 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

My Code:

INPUT:

OUTPUT:

Edited:

code to fetch data from table.

OUTPUT:

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related

`INPUT`:

`OUTPUT`:

`OUTPUT`: