C#•6d ago

EFCore is incapable of connecting to DB despite everything else being able to

Mystifying me because - SSMS connects fine - Migration commands like Add-Migration "Add_Table" and Update-Database work fine

PM> Add-Migration "Update02"
Build started...
Build succeeded.
To undo this action, use Remove-Migration.
PM> Update-Database
Build started...
Build succeeded.
Acquiring an exclusive lock for migration application. See https://aka.ms/efcore-docs-migrations-lock for more information if this takes too long.
Applying migration '20241216052859_Update02'.
Done.

PM> Add-Migration "Update02"
Build started...
Build succeeded.
To undo this action, use Remove-Migration.
PM> Update-Database
Build started...
Build succeeded.
Acquiring an exclusive lock for migration application. See https://aka.ms/efcore-docs-migrations-lock for more information if this takes too long.
Applying migration '20241216052859_Update02'.
Done.

can also be viewed from DB side. I am always encountering this error from EFCore when I run the application:

A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: TCP Provider, error: 40 - Could not open a connection to SQL Server)'

A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: TCP Provider, error: 40 - Could not open a connection to SQL Server)'

Code will be in responses

6 Replies

surwrenOP•6d ago

AppDbContext.cs:

public class AppDbContext : DbContext
{
    public IConfiguration _config { get; set; }
    public AppDbContext(IConfiguration config)
    {
        _config = config;
    }

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        optionsBuilder.UseSqlServer(_config.GetConnectionString("SqlServer"));
    }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        base.OnModelCreating(modelBuilder);

        modelBuilder.Entity<User>()
            .HasMany(u => u.Subscribers)
            .WithMany(u => u.SubscribedTo)
            .UsingEntity(j => j
                .ToTable("UserSubscribers")
                .HasKey("SubscribedToId", "SubscribersId")
            );

        modelBuilder.Entity<UserRelationship>(entity =>
        {
            entity.HasKey(ur => new { ur.UserFirstId, ur.UserSecondId });
            //entity.HasCheckConstraint("CK_UserRelationship_Order", "UserFirstId < UserSecondId");
            entity.HasOne(ur => ur.UserFirst)
                  .WithMany()  // No navigation property on User
                  .HasForeignKey(ur => ur.UserFirstId)
                  .OnDelete(DeleteBehavior.Restrict);  // Prevent cascading delete

            entity.HasOne(ur => ur.UserSecond)
                  .WithMany()
                  .HasForeignKey(ur => ur.UserSecondId)
                  .OnDelete(DeleteBehavior.Restrict);
        });
    }

    public DbSet<User> Users { get; set; }
    public DbSet<UserRelationship> UserRelationships { get; set; }
}

public class AppDbContext : DbContext
{
    public IConfiguration _config { get; set; }
    public AppDbContext(IConfiguration config)
    {
        _config = config;
    }

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        optionsBuilder.UseSqlServer(_config.GetConnectionString("SqlServer"));
    }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        base.OnModelCreating(modelBuilder);

        modelBuilder.Entity<User>()
            .HasMany(u => u.Subscribers)
            .WithMany(u => u.SubscribedTo)
            .UsingEntity(j => j
                .ToTable("UserSubscribers")
                .HasKey("SubscribedToId", "SubscribersId")
            );

        modelBuilder.Entity<UserRelationship>(entity =>
        {
            entity.HasKey(ur => new { ur.UserFirstId, ur.UserSecondId });
            //entity.HasCheckConstraint("CK_UserRelationship_Order", "UserFirstId < UserSecondId");
            entity.HasOne(ur => ur.UserFirst)
                  .WithMany()  // No navigation property on User
                  .HasForeignKey(ur => ur.UserFirstId)
                  .OnDelete(DeleteBehavior.Restrict);  // Prevent cascading delete

            entity.HasOne(ur => ur.UserSecond)
                  .WithMany()
                  .HasForeignKey(ur => ur.UserSecondId)
                  .OnDelete(DeleteBehavior.Restrict);
        });
    }

    public DbSet<User> Users { get; set; }
    public DbSet<UserRelationship> UserRelationships { get; set; }
}

Program.cs:

var builder = WebApplication.CreateBuilder(args);

builder.Services.AddControllers();
builder.Services.AddEndpointsApiExplorer(); // Learn more about configuring Swagger/OpenAPI at https://aka.ms/aspnetcore/swashbuckle
builder.Services.AddSwaggerGen();
builder.Services.AddDbContext<AppDbContext>(options =>
    options.UseSqlServer(builder.Configuration.GetConnectionString("SqlServer")));

var app = builder.Build();

using (var scope = app.Services.CreateScope())
{
    var context = scope.ServiceProvider.GetRequiredService<AppDbContext>();
    var user1 = new User { Ona_Id = 1, UnityHair = 0, Level = 1 };
    var user2 = new User { Ona_Id = 2, UnityHair = 2, Level = 1 };
    var user3 = new User { Ona_Id = 3, UnityHair = 2, Level = 2 };

    user1.SubscribedTo.Add(user2);
    user2.Subscribers.Add(user1);

    user2.SubscribedTo.Add(user3);
    user3.Subscribers.Add(user2);

    context.Users.AddRange(user1, user2, user3);
    context.SaveChanges();
}


if (app.Environment.IsDevelopment()) { // HTTP request pipeline.
    app.UseSwagger();
    app.UseSwaggerUI();
}

app.UseHttpsRedirection();
app.UseAuthorization();
app.MapControllers();
app.Run();

var builder = WebApplication.CreateBuilder(args);

builder.Services.AddControllers();
builder.Services.AddEndpointsApiExplorer(); // Learn more about configuring Swagger/OpenAPI at https://aka.ms/aspnetcore/swashbuckle
builder.Services.AddSwaggerGen();
builder.Services.AddDbContext<AppDbContext>(options =>
    options.UseSqlServer(builder.Configuration.GetConnectionString("SqlServer")));

var app = builder.Build();

using (var scope = app.Services.CreateScope())
{
    var context = scope.ServiceProvider.GetRequiredService<AppDbContext>();
    var user1 = new User { Ona_Id = 1, UnityHair = 0, Level = 1 };
    var user2 = new User { Ona_Id = 2, UnityHair = 2, Level = 1 };
    var user3 = new User { Ona_Id = 3, UnityHair = 2, Level = 2 };

    user1.SubscribedTo.Add(user2);
    user2.Subscribers.Add(user1);

    user2.SubscribedTo.Add(user3);
    user3.Subscribers.Add(user2);

    context.Users.AddRange(user1, user2, user3);
    context.SaveChanges();
}


if (app.Environment.IsDevelopment()) { // HTTP request pipeline.
    app.UseSwagger();
    app.UseSwaggerUI();
}

app.UseHttpsRedirection();
app.UseAuthorization();
app.MapControllers();
app.Run();

surwrenOP•6d ago

Ok, I think it is bc I am debugging via dockerfile. Https works How can I update my dockerfile to allow EFCore to connect to my local machine?

Pobiega•6d ago

If your app runs in docker, you need to use a connection string that targets the host instead of localhost. iirc that's enabled by default in docker for windows, and if you Google it you'll find the right hostname host.docker.internal on docker-for-windows and docker-for-mac see top answer at https://stackoverflow.com/questions/24319662/from-inside-of-a-docker-container-how-do-i-connect-to-the-localhost-of-the-mach for a lot more detail

surwrenOP•5d ago

I just used docker-compose and accessed the db by name instead idk if copout lol

Pobiega•5d ago

Oh, so db was a container too, even beforehand?

surwrenOP•4d ago

no, it was local

Gaming

Programming

EFCore is incapable of connecting to DB despite everything else being able to