C#•5mo ago

ASP.NET Core Blazor + EF error

I'm programming an ASP.NET Core Blazor and I'm using Entity Framework to contact the database. Everything works fine except when I use an async function which queries the database. I get the following error:

General error: Invalid operation. The connection is closed.

General error: Invalid operation. The connection is closed.

Although I've set the Microsoft SQL Server connection timeout to none yet this still happens

var department = await PrrojectDBService.GetDepartmentByIdWithRelatedData(selectedDepartmentID);

var department = await PrrojectDBService.GetDepartmentByIdWithRelatedData(selectedDepartmentID);

and here's the code for that function:

public async Task<Project.Models.ProjectDB.Department> GetDepartmentByIdWithRelatedData(int departmentId)
    {
        try
        {
            Console.WriteLine("Starting to fetch department data for department ID: " + departmentId);

            var department = await Context.Departments
                .Include(d => d.DepartmentNotes)
                .Include(d => d.DepartmentInfrastructures)
                .Include(d => d.DeviceInstallationStatuses)
                .FirstOrDefaultAsync(d => d.departmentID == departmentId);

            Console.WriteLine("Successfully retrieved department data for department ID: " + departmentId);

            return department;
        }
        catch (DbException dbEx)
        {
            Console.WriteLine("Database-related error: " + dbEx.Message);
            throw;

        }
        catch (Exception ex)
        {
            Console.WriteLine("General error: " + ex.Message);
            throw; 
        }
    }

public async Task<Project.Models.ProjectDB.Department> GetDepartmentByIdWithRelatedData(int departmentId)
    {
        try
        {
            Console.WriteLine("Starting to fetch department data for department ID: " + departmentId);

            var department = await Context.Departments
                .Include(d => d.DepartmentNotes)
                .Include(d => d.DepartmentInfrastructures)
                .Include(d => d.DeviceInstallationStatuses)
                .FirstOrDefaultAsync(d => d.departmentID == departmentId);

            Console.WriteLine("Successfully retrieved department data for department ID: " + departmentId);

            return department;
        }
        catch (DbException dbEx)
        {
            Console.WriteLine("Database-related error: " + dbEx.Message);
            throw;

        }
        catch (Exception ex)
        {
            Console.WriteLine("General error: " + ex.Message);
            throw; 
        }
    }

40 Replies

Pobiega•5mo ago

and how is Context created here?

VRose•5mo ago

In the

program.cs

program.cs

with the following code:

builder.Services.AddDbContext<Project.Data.ProjectDBContext>(options =>
    options.UseSqlServer(builder.Configuration.GetConnectionString("AttendanceDashboardDBConnection")),
    ServiceLifetime.Scoped);

builder.Services.AddLocalization();

var app = builder.Build();

builder.Services.AddDbContext<Project.Data.ProjectDBContext>(options =>
    options.UseSqlServer(builder.Configuration.GetConnectionString("AttendanceDashboardDBConnection")),
    ServiceLifetime.Scoped);

builder.Services.AddLocalization();

var app = builder.Build();

Pobiega•5mo ago

and the injection in your service class? @VRose

VRose•5mo ago

    using System;
    using System.Data;
    using System.Linq;
    using System.Linq.Dynamic.Core;
    using System.Collections.Generic;
    using System.Threading.Tasks;
    using System.Text.Encodings.Web;
    using Microsoft.AspNetCore.Components;
    using Microsoft.Data.SqlClient;
    using Microsoft.EntityFrameworkCore;
 

    using Project.Data;

   
    using System.Data.Common;

    using System;
    using System.Data;
    using System.Linq;
    using System.Linq.Dynamic.Core;
    using System.Collections.Generic;
    using System.Threading.Tasks;
    using System.Text.Encodings.Web;
    using Microsoft.AspNetCore.Components;
    using Microsoft.Data.SqlClient;
    using Microsoft.EntityFrameworkCore;
 

    using Project.Data;

   
    using System.Data.Common;

Pobiega•5mo ago

no, how is the Context variable in your service populated? You've shown the DI setup for the database context, but not how you actually resolve the instance

VRose•5mo ago

My project in general can query the database fine, I used some templates by Radzen and they also work fine and are able to insert, edit and delete data from my database, but when I try to query the database to get specific data, I'm getting that error Hold on

Angius•5mo ago

In other words, where did you get this from, where and how is it defined?

VRose•5mo ago

@Pobiega @ZZZZZZZZZZZZZZZZZZZZZZZZZ

ProjectDBService.cs

ProjectDBService.cs

namespace Project
    {
        public partial class ProjectDBService
        {
            ProjectDBContext Context
            {
            get
            {
                return this.context;
            }
            }

            private readonly ProjectDBContext context;
            private readonly NavigationManager navigationManager;

            public ProjectDBService(ProjectDBContext context, NavigationManager navigationManager)
            {
                this.context = context;
                this.navigationManager = navigationManager;
            }

            public void Reset() => Context.ChangeTracker.Entries().Where(e => e.Entity != null).ToList().ForEach(e => e.State = EntityState.Detached);

namespace Project
    {
        public partial class ProjectDBService
        {
            ProjectDBContext Context
            {
            get
            {
                return this.context;
            }
            }

            private readonly ProjectDBContext context;
            private readonly NavigationManager navigationManager;

            public ProjectDBService(ProjectDBContext context, NavigationManager navigationManager)
            {
                this.context = context;
                this.navigationManager = navigationManager;
            }

            public void Reset() => Context.ChangeTracker.Entries().Where(e => e.Entity != null).ToList().ForEach(e => e.State = EntityState.Detached);

Pobiega•5mo ago

ok so its just injected normally (I assume).. not really sure why you'd wrap the field in a property like that thou

Angius•5mo ago

Yeah, that property is just not doing anything

Pobiega•5mo ago

can just access the private field itself however, that all seems fine and not the source of your error And this service is only accessed on the server side of blazor, right?

VRose•5mo ago

How can I know..? 😅

Pobiega•5mo ago

Well blazor has multiple operating modes.. it can run as a pure FE app, entirely serverside-rendered or a hybrid

VRose•5mo ago

It's a serverside application

Pobiega•5mo ago

hm okay the error message implies that the context has been disposed, or the underlying connection been closed for another reason my first guess was manual creation of the context, instead of letting the DI system handle it, but that seems to be done correctly You only ever inject the service too right?

VRose•5mo ago

The thing is, the connection shouldn't get force closed because I've changed the timeout to none and also, if there's any errors in handling with the database, the following block of code:

 catch (DbException dbEx)
        {
            Console.WriteLine("Database-related error: " + dbEx.Message);
            throw;
}

 catch (DbException dbEx)
        {
            Console.WriteLine("Database-related error: " + dbEx.Message);
            throw;
}

would've worked instead of

catch (Exception ex)
        {
            Console.WriteLine("General error: " + ex.Message);
throw;

catch (Exception ex)
        {
            Console.WriteLine("General error: " + ex.Message);
throw;

What do you mean...?

Pobiega•5mo ago

the ProjectDBService class you've made you never manually do ... = new ProjectDBService(...); anywhere?

VRose•5mo ago

Nope, never

Pobiega•5mo ago

well, its not a timeout, and its not a database error the connection has been closed, but your code is still trying to use it after the close and its throwing (what I guess is) an InvalidOperationException, which is NOT a DbException I'm not seeing any obvious cause for this so far.. a common cause of this is missing an await somewhere

VRose•5mo ago

Well yep and I've no idea why tbh... But just to put you in picture, here's the workflow of the page that's facing this issue: I've a dropdown menu that usesthe following method to populate the list:

            protected override async Task OnInitializedAsync()
            {
                departments = await ProjectDBService.GetDepartments();
            }

            protected override async Task OnInitializedAsync()
            {
                departments = await ProjectDBService.GetDepartments();
            }

and then when the user chooses an item (the id gets saved and queried) i get that error

Pobiega•5mo ago

curious can you show the handler for the dropdown choose event? actually, at this point, can you put this entire project on github or something?

VRose•5mo ago

Sorry but I can't because it's personal...

Pobiega•5mo ago

Then I can't offer any further help Best of luck I don't know how blazor handles context lifetimes in regards to things like the event handler etc but that seems a likely source of the problem

VRose•5mo ago

Here:

protected async Task changeTest(object args)
            {

                NotificationService.Notify(new NotificationMessage 
                { 
                    Summary = "Attempting to retrieve data", 
                    Severity = NotificationSeverity.Info 
                });

                try
                {
                    

                    var department = await ProjectDBService.GetDepartmentByIdWithRelatedData(selectedDepartmentID);
                    NotificationService.Notify(new NotificationMessage
                    {
                        Summary = $"Department: {department.departmentName} has {department.departmentID}\n{department.DepartmentInfrastructures}",
                        Severity = NotificationSeverity.Info,
                        Detail = "test",
                    });
                catch (Exception ex)
                {
                    NotificationService.Notify(new NotificationMessage
                    {
                        Summary = $"Error: {ex.Message}",
                        Severity = NotificationSeverity.Error
                    });
                }

protected async Task changeTest(object args)
            {

                NotificationService.Notify(new NotificationMessage 
                { 
                    Summary = "Attempting to retrieve data", 
                    Severity = NotificationSeverity.Info 
                });

                try
                {
                    

                    var department = await ProjectDBService.GetDepartmentByIdWithRelatedData(selectedDepartmentID);
                    NotificationService.Notify(new NotificationMessage
                    {
                        Summary = $"Department: {department.departmentName} has {department.departmentID}\n{department.DepartmentInfrastructures}",
                        Severity = NotificationSeverity.Info,
                        Detail = "test",
                    });
                catch (Exception ex)
                {
                    NotificationService.Notify(new NotificationMessage
                    {
                        Summary = $"Error: {ex.Message}",
                        Severity = NotificationSeverity.Error
                    });
                }

I'm really sorry but my project really contains some information that I can't share or else I'd have...

Pobiega•5mo ago

where are you getting selectedDepartmentID from there? and you already have all the departments in your departments var so just read it from there?

Angius•5mo ago

Well, if it is Blazor, and yoiu are injecting this code into Blazor components... I believe the guideline is to use a DbContextFactory instead of injecting the context. I wonder if that has to do anything with it

Pobiega•5mo ago

its SSR blazor, but that would make sense you'd need a new context for reactivity since the old one has been disposed

VRose•5mo ago

Well from the chosen department ID, although I've tried replacing that with a fixed number but it didn't fix the issue

Pobiega•5mo ago

well I think ZZZ just nailed the issue you are doing DB calls in reactive code

VRose•5mo ago

May you please elavorate on how to do this?

Pobiega•5mo ago

thats a no-no, unless you create a new context your service pattern is also biting you in the ass here

Angius•5mo ago

You inject a DbContextFactory instead of a DbContext and use that to get the context Never used Blazor outside of toy projects so I don't know the full details off the top of my head

Pobiega•5mo ago

https://learn.microsoft.com/en-us/aspnet/core/blazor/blazor-ef-core?view=aspnetcore-8.0

ASP.NET Core Blazor with Entity Framework Core (EF Core)

Learn how to use Entity Framework Core (EF Core) in Blazor apps.

Pobiega•5mo ago

If there's any chance that multiple threads may access the same code block, inject a factory and make a new instance per operation. Otherwise, injecting and using the context is usually sufficient. By default, consider using one context per operation. The context is designed for fast, low overhead instantiation: The recommended approach to create a new DbContext with dependencies is to use a factory. EF Core 5.0 or later provides a built-in factory for creating new contexts.

https://learn.microsoft.com/en-us/aspnet/core/blazor/blazor-ef-core?view=aspnetcore-8.0#new-dbcontext-instances this will require quite a big rewrite of your code, since you'll need to inject the factory into your components, instead of the service

VRose•5mo ago

And I need to do that in the page I'm working on? This is really a hassle ;-;

Pobiega•5mo ago

the problem is the lifetime of your service class it uses a db context, which in blazor should be more or less transient but your component injects a service for the lifetime of the component this keeps that service (and thus the underlying context) alive for longer than it should if you change up your service to get a new context for each operation, you'll be fine thats probably the easiest fix here

VRose•5mo ago

I'll try this later on today and I will update you...Thank you a lot for your time and help and I hope this would fix my issue 🙏

Jimmacle•5mo ago

a blazor "scope" lives for the entire signalr session, so scoped services are kind of pointless and won't work as expected

Pobiega•5mo ago

that would explain the killed connection 🙂

VRose•5mo ago

I just tried this now and it solveddd my isssue, THANK YOUUUUUUUUUUUUUU A LOTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTT ❤️❤️❤️❤️❤️❤️❤️❤️❤️❤️ You've no idea how much this made my day 😭

Gaming

Programming

ASP.NET Core Blazor + EF error