Post entity script

We will create a Post table and a non-clustered index for better performance, later alter them to add a primary key and foreign key constraints, and finally, insert some dummy data into the table. The complete script is available in the GitHub repository at https://github.com/PacktPublishing/Mastering-Entity-Framework-Core/blob/master/Chapter%202/Final/MasteringEFCore.DatabaseFirst.Final/dbo.Post.sql.

The script required for creating the Post table and inserting the data is displayed below:

    // Code removed for brevity
    CREATE TABLE [dbo].[Post] (
      [Id]                INT            IDENTITY (1, 1) NOT NULL,
      [BlogId]            INT            NOT NULL,
      [Content]           NVARCHAR (MAX) NULL,
      [PublishedDateTime] DATETIME2 (7)  NOT NULL,
      [Title]             NVARCHAR (MAX) NOT NULL
    ); 
    GO
    // Code removed for brevity
    INSERT INTO [Post] ([BlogId], [Title], [Content], 
        [PublishedDateTime]) VALUES
    (1, 'Dotnet 4.7 Released', 'Dotnet 4.7 Released Contents',
        '20170424'),
    (2, '.NET Core 1.1 Released', '.NET Core 1.1 Released Contents',
        '20170424'),
    (2, 'EF Core 1.1 Released', 'EF Core 1.1 Released Contents',
        '20170424')
    GO

While inserting data time values, we should use the YYYYMMDD format, such as 20170424, and if we are tuned with the DD-MM-YYYY hh:mm:ss xm format, then we need to perform an explicit conversion, such as convert(datetime,'24-04-2017 01:34:09 PM',5), otherwise we would get the message, the conversion of a varchar data type to a datetime data type resulted in an out-of-range value.
 

We need to figure out how to execute the previous script using LocalDB:

1. Open the SQL Server Object Explorer from the View menu.
2. Expand SQL Server and (localdb)\MSSQLLocalDB.
3. Right-click on Databases and select Add New Database.
4. In the Create Database dialog box, provide the Database Name as MasteringEFCoreDbFirst and Database Location as your project path, and click OK.
5. Expand Databases, right-click on the MasteringEFCoreDbFirst database, and select New Query.

1. Copy the Blog.sql content and paste it into the New Query window, and click the execute icon or Ctrl+Shift+E:

The script execution of the Blog script is shown as follows:

Execute Blog.sql on the New Query window

1. Copy the Post.sql content and paste it in the New Query window, and click the execute icon or Ctrl+Shift+E:

The script execution of the Post script is shown here:

Execute Post.sql on New Query window

We have prepared the database for the Database-First approach; now let's see how we could leverage Entity Framework on the existing database.