azure entity framework: new table is not created, internal server error when trying to insert data

inno 1 Reputation point
2022-11-28T11:38:26.807+00:00

i am stuck here for days now:

all i want is to create a table called useritems and upload data into it, this is my middleware:

[Route("tables/useritem")]  
public class tblUserController : TableController<UserItem>  
{  
    public tblUserController(AppDbContext context)  
        : base(new EntityTableRepository<UserItem>(context))  
    {  

    }  
}  

}

public class UserItem : EntityTableData
{
[Required, MinLength(1)]
public string Email { get; set; } = "";
public string Telephone { get; set; } = "";
public string Password { get; set; } = "";
}

app context:

{
public class AppDbContext : DbContext
{

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

    /// <summary>  
    /// The dataset for the UserItems.  
    /// </summary>  
    public DbSet<UserItem> UserItems => Set<UserItem>();  

    /// <summary>  
    /// Do any database initialization required.  
    /// </summary>  
    /// <returns>A task that completes when the database is initialized</returns>  
    public async Task InitializeDatabaseAsync()  
    {  
        await this.Database.EnsureCreatedAsync().ConfigureAwait(false);  
    }  
}  

program.cs

var builder = WebApplication.CreateBuilder(args);
var connectionString = builder.Configuration.GetConnectionString("C5"); // set the connection string name that you set up (caused issues before)

if (connectionString == null)
{
throw new ApplicationException("DefaultConnection is not set");
}

builder.Services.AddDbContext<AppDbContext>(options => options.UseSqlServer(connectionString));
builder.Services.AddDatasyncControllers();

var app = builder.Build();

// Initialize the database
using (var scope = app.Services.CreateScope())
{
var context = scope.ServiceProvider.GetRequiredService<AppDbContext>();
await context.InitializeDatabaseAsync().ConfigureAwait(false);
}

// Configure and run the web service.
app.MapControllers();
app.Run();

as far as my understanding goes, this should create a table. and once I try to insert data into it, it should show up in my SSMS. but it doesnt.

When I open the link to the db I get this:
![264719-grafik.png]1

So it seems to have been created and running.

Funniest part:

If I create a table via SSMS and the create table command that is of the name UserItems and has all the features of the entity table, I CAN create data from my app but not READ it. So the auto create fails and therefore the connection must have an issue.

Why arent my tables created automatically?

Entity Framework Core
Entity Framework Core
A lightweight, extensible, open-source, and cross-platform version of the Entity Framework data access technology.
726 questions
0 comments No comments
{count} votes

1 answer

Sort by: Most helpful
  1. AgaveJoe 27,656 Reputation points
    2022-11-28T14:55:37.973+00:00

    as far as my understanding goes, this should create a table. and once I try to insert data into it

    The database and tables are create when the application starts.

    I recommend reading the EnsureCreated() documentation to make sure you're understand the limitations.

    DatabaseFacade.EnsureCreatedAsync(CancellationToken) Method

    Example Pattern

    Configuration

    {  
      "Logging": {  
        "LogLevel": {  
          "Default": "Information",  
          "Microsoft.AspNetCore": "Warning"  
        }  
      },  
      "AllowedHosts": "*",  
      "ConnectionStrings": {  
        "WebApiTestDb": "Server=(localdb)\\MSSQLLocalDB;Database=WebApiTestDb;Trusted_Connection=True;MultipleActiveResultSets=true;"  
      }  
    }  
    

    Model

    namespace WebApiTestDb.Models  
    {  
        public class UserItem  
        {  
            public int UserItemId {get; set;}  
            public string? Email { get; set; } = string.Empty;  
            public string? Telephone { get; set; } = string.Empty;  
            public string? Password { get; set; } = string.Empty;  
        }  
    }  
    

    DbContext

    namespace WebApiTestDb.Data  
    {  
        public class WebApitestDbContext : DbContext  
        {  
            public WebApitestDbContext(DbContextOptions<WebApitestDbContext> options) : base(options)  
            {  
            }  
            public DbSet<UserItem> UserItems { get; set; }  
        }  
    }  
    

    Startup.cs

    using Microsoft.EntityFrameworkCore;  
    using WebApiTestDb.Data;  
      
    var builder = WebApplication.CreateBuilder(args);  
      
    // Add services to the container.  
      
    builder.Services.AddControllers();  
    builder.Services.AddDbContext<WebApitestDbContext>(options =>  
            options.UseSqlServer(builder.Configuration.GetConnectionString("WebApiTestDb")));  
      
    // Learn more about configuring Swagger/OpenAPI at https://aka.ms/aspnetcore/swashbuckle  
    builder.Services.AddEndpointsApiExplorer();  
    builder.Services.AddSwaggerGen();  
      
    var app = builder.Build();  
      
    using (var scope = app.Services.CreateScope())  
    {  
        var context = scope.ServiceProvider.GetRequiredService<WebApitestDbContext>();  
        await context.Database.EnsureDeletedAsync();  
        await context.Database.EnsureCreatedAsync();  
      
        if(!context.UserItems.Any())  
        {  
            context.UserItems.Add(new WebApiTestDb.Models.UserItem()  
            {  
                Email = "HelloWorld@mail.com",  
                Password = "password",  
                Telephone = "123 456-7890"  
            });  
            await context.SaveChangesAsync();  
        }  
    }  
      
    // Configure the HTTP request pipeline.  
    if (app.Environment.IsDevelopment())  
    {  
        app.UseSwagger();  
        app.UseSwaggerUI();  
    }  
      
    app.UseHttpsRedirection();  
      
    app.UseAuthorization();  
      
    app.MapControllers();  
      
    app.Run();  
    

    Scaffolded Controller

    using System;  
    using System.Collections.Generic;  
    using System.Linq;  
    using System.Threading.Tasks;  
    using Microsoft.AspNetCore.Http;  
    using Microsoft.AspNetCore.Mvc;  
    using Microsoft.EntityFrameworkCore;  
    using WebApiTestDb.Data;  
    using WebApiTestDb.Models;  
      
    namespace WebApiTestDb.Controllers  
    {  
        [Route("api/[controller]")]  
        [ApiController]  
        public class UserItemsController : ControllerBase  
        {  
            private readonly WebApitestDbContext _context;  
      
            public UserItemsController(WebApitestDbContext context)  
            {  
                _context = context;  
            }  
      
            // GET: api/UserItems  
            [HttpGet]  
            public async Task<ActionResult<IEnumerable<UserItem>>> GetUserItems()  
            {  
                return await _context.UserItems.ToListAsync();  
            }  
      
            // GET: api/UserItems/5  
            [HttpGet("{id}")]  
            public async Task<ActionResult<UserItem>> GetUserItem(int id)  
            {  
                var userItem = await _context.UserItems.FindAsync(id);  
      
                if (userItem == null)  
                {  
                    return NotFound();  
                }  
      
                return userItem;  
            }  
      
            // PUT: api/UserItems/5  
            // To protect from overposting attacks, see https://go.microsoft.com/fwlink/?linkid=2123754  
            [HttpPut("{id}")]  
            public async Task<IActionResult> PutUserItem(int id, UserItem userItem)  
            {  
                if (id != userItem.UserItemId)  
                {  
                    return BadRequest();  
                }  
      
                _context.Entry(userItem).State = EntityState.Modified;  
      
                try  
                {  
                    await _context.SaveChangesAsync();  
                }  
                catch (DbUpdateConcurrencyException)  
                {  
                    if (!UserItemExists(id))  
                    {  
                        return NotFound();  
                    }  
                    else  
                    {  
                        throw;  
                    }  
                }  
      
                return NoContent();  
            }  
      
            // POST: api/UserItems  
            // To protect from overposting attacks, see https://go.microsoft.com/fwlink/?linkid=2123754  
            [HttpPost]  
            public async Task<ActionResult<UserItem>> PostUserItem(UserItem userItem)  
            {  
                _context.UserItems.Add(userItem);  
                await _context.SaveChangesAsync();  
      
                return CreatedAtAction("GetUserItem", new { id = userItem.UserItemId }, userItem);  
            }  
      
            // DELETE: api/UserItems/5  
            [HttpDelete("{id}")]  
            public async Task<IActionResult> DeleteUserItem(int id)  
            {  
                var userItem = await _context.UserItems.FindAsync(id);  
                if (userItem == null)  
                {  
                    return NotFound();  
                }  
      
                _context.UserItems.Remove(userItem);  
                await _context.SaveChangesAsync();  
      
                return NoContent();  
            }  
      
            private bool UserItemExists(int id)  
            {  
                return _context.UserItems.Any(e => e.UserItemId == id);  
            }  
        }  
    }  
    
    0 comments No comments