Category Archives: Database

Generating Entity-Framework classes for .NET Core projects in a Database First scenario

Microsoft provides two command line tools in situations when there is a need to generate Entity Framework classes for a .NET Core project. Within the Package Manager console of Visual Studio you can use Scaffold-DbContext and if you are using the .NET Core CLI then the command dotnet ef is available. Details on the capabilities (migration, scaffold, update, drop, and more) as well as instructions on what needs to be installed can be found on EF Core tools reference – .NET Core CLI and Entity Framework Core tools reference – Package Manager Console in Visual Studio.

When using dotnet ef command in the .NET Core CLI you will add the scaffold argument which will allow you to generate classes for an entire database, specific tables, or schemas within a database. If you are using it against a specific set of tables then you will provide each table with the –table flag preceding the table name.

In this example the command will generate EF classes in order to interact with two common Identity tables, AspNetRoleClaims and AspNetRoles, within the dbo schema.

dotnet ef dbcontext scaffold "Data Source=(localdb)\MSSQLLocalDB;Initial Catalog=IdentityServer;" Microsoft.EntityFrameworkCore.SqlServer --table dbo.AspNetRoleClaims -table dbo.AspNetRoles

Having the tool generate classes for a schema is just as simple. Instead of using the –table use –schema and provide the schema name.

If there comes a need to regenerate tables or add more then you will need to use the –force flag. This will allow the process to overwrite any files that already exist in the project. Make sure you include all tables or schemas that you want generated. Even if a table hasn’t changed since the earlier schema change you will still need to list it when regenerating the classes.

If you prefer to use the Package Manager console then the same command from above could be executed using only small changes. Instead of a flag for each table you will combine all tables in a space separated list within enclosing quotes.

Scaffold-DbContext -Provider Microsoft.EntityFrameworkCore.SqlServer -Connection "Data Source=(localdb)\MSSQLLocalDB;Initial Catalog=IdentityServer;" -Tables dbo.AspNetRoleClaims, dbo.AspNetRoles

This post went over only a small portion of the capabilities that these tools provide. If you are designing a system to be a Code First approach then you’ll become very familiar with these tools as you generate initialization and migration scripts. If you are unfamiliar with with EF migrations then take a look at Managing Database Schemas to guide you through each step.

Edited 26 April 2021 Fix Scaffold-DbContext syntax error.

Creating an #Azure #SQLSERVER database login account

A few weeks ago I posted the steps to create a login account in SQL Server which utilized SQL Server Authentication. While the steps work perfectly for local instances of SQL Server it doesn’t work on when you are operating in Azure SQL Server. I realized this quickly when I decided to use the steps at work with no success. Luckily the differences aren’t major, which this post will go over, so without further delay, here is what you need to do.

The big difference between creating an account on an Azure based SQL Server database versus a local server is under which database you run the steps. When creating the login you’ll run under the master database. Then when creating the user you’ll need to run it once under master and then again under the database the account should be associated with.

-- Execute from master database on the server
CREATE LOGIN [DbLoginUser] WITH PASSWORD = 'Password1'
GO
CREATE USER [DbLoginUser] FOR LOGIN [DbLoginUser] 
GO

The above script creates the login which you should be able to test out immediately after running them. You won’t have access to any of the databases on the server but you should be able to log into the server. The next script will associate the login with the particular database and place it in the db_owner role.

-- Open a new query window in the database you want to create the user
CREATE USER [DbLoginUser] FOR LOGIN [DbLoginUser] WITH DEFAULT_SCHEMA = [dbo]
GO
ALTER ROLE db_owner ADD MEMBER [DbLoginUser]
GO

With the steps completed you should be able to log into the server with the new DbLoginUser account. Additionally, you have full control over the database the user was created under.

Basic steps to create a new #SQLSERVER database account

Once or twice a year I need to create an account for a SQL Server database that utilizes database authentication.  And every time I have to do this I end up spending too much time searching for the correct steps.  So this post is to help keep me from forgetting what to do for the rest of my career.

In the example below, the database we want to create an account for is [TestDatabase].  The login that will be used for authentication is DbLoginAcct.  And the user that will grant the login access to [TestDatabase] and the [dbo] schema is DbUserAcct.

USE [TestDatabase]
CREATE LOGIN [DbLoginAcct] WITH PASSWORD=N'test', DEFAULT_DATABASE=[TestDatabase]
GO
CREATE USER [DbUserAcct] FOR LOGIN [DbLoginAcct]
GO
ALTER USER [DbUserAcct] WITH DEFAULT_SCHEMA=[dbo]
GO
-- We want this user to have full control of the database so make it a db_owner
ALTER ROLE [db_owner] ADD MEMBER [DbUserAcct]
GO

After running the script we can log into the database by using the DbLoginAcct credentials.  If this is the first account on the server using database authentication you may run into issues when authenticating.  To fix these issues take a look at  How to Fix Microsoft SQL Server Login Failed Error 18456?

Finally, two things worth noting are the difference between the LOGIN and USER objects created as part of the script.  As it relates to the server, the LOGIN is used for authentication and the USER is used for authorization.  In other words, the LOGIN gets you into the server while the USER gets you access to the specific database.

Update:  This is for a non-Azure SQL Server Instance.