(MVC) MVC (2018)

Entity Framework missing FAQ (Part 1 , Part 2, Part 3)

Entity Framework missing FAQ (Part 4). From EF Code First class definition to WebAPI2 on VB.NET




4.1.1. Create MVC project.





4.1.2 Install-Package EntityFramework -Version 6.2.0


This operation only add reference to project and change web.config.




4.1.3 Add the Connection String


In this project I use MsSQL server, but if you want to change SQL server to MySQL, please install additional provider firstly, go to the page Project for refactoring ODBC access to MySQL up to EF6 Code First with MySQL. to learn how its doing.


   1:    <connectionStrings>
   2:      <add name="MyArticlesConnectionString"
   3:        connectionString="Data Source=(LocalDb)\MSSQLLocalDB;Initial Catalog=MyArticles;Integrated Security=SSPI;AttachDBFilename=|DataDirectory|\MyArticles.mdf"
   4:        providerName="System.Data.SqlClient"  />
   5:    </connectionStrings>



4.1.4 Create DB record definition


   1:    Public Class MyArticlesRecord
   2:      <Key()>
   3:      Public Property I As Integer
   4:   
   5:      Public Property ID As Guid
   6:   
   7:      Public Property CrDate As DateTime
   8:   
   9:      <MaxLength(50)>
  10:      <Required(AllowEmptyStrings:=False)>
  11:      Public Property Type As String
  12:   
  13:      <MaxLength(250)>
  14:      <Required(AllowEmptyStrings:=False)>
  15:      Public Property URL As String
  16:   
  17:      <MaxLength(250)>
  18:      <Required(AllowEmptyStrings:=False)>
  19:      Public Property TXT As String
  20:   
  21:      <MaxLength(1000)>
  22:      Public Property Descr As String
  23:  End Class



4.1.5. Create ApplicationDbContext


   1:  Public Class ApplicationDbContext
   2:      Inherits Entity.DbContext
   3:   
   4:      Public Sub New()
   5:          MyBase.New("MyArticlesConnectionString")
   6:      End Sub
   7:   
   8:      Public Shared Function Create() As ApplicationDbContext
   9:          Return New ApplicationDbContext()
  10:      End Function
  11:   
  12:      Public Property MyArticles As Entity.DbSet(Of MyArticlesRecord)
  13:  End Class


After this step my vbproj became such as this - WebApi-1-1.vbproj


4.1.6. Enable-Migrations


   1:  Namespace Migrations
   2:   
   3:      Friend NotInheritable Class Configuration 
   4:          Inherits DbMigrationsConfiguration(Of ApplicationDbContext)
   5:   
   6:          Public Sub New()
   7:              AutomaticMigrationsEnabled = False
   8:          End Sub
   9:   
  10:          Protected Overrides Sub Seed(context As ApplicationDbContext)
  11:              '  This method will be called after migrating to the latest version.
  12:   
  13:              '  You can use the DbSet(Of T).AddOrUpdate() helper extension method 
  14:              '  to avoid creating duplicate seed data.
  15:          End Sub
  16:   
  17:      End Class
  18:   
  19:  End Namespace



4.1.7. Add-Migration Initial


   1:  Namespace Migrations
   2:      Public Partial Class Initial
   3:          Inherits DbMigration
   4:      
   5:          Public Overrides Sub Up()
   6:              CreateTable(
   7:                  "dbo.MyArticlesRecords",
   8:                  Function(c) New With
   9:                      {
  10:                          .I = c.Int(nullable := False, identity := True),
  11:                          .ID = c.Guid(nullable := False),
  12:                          .CrDate = c.DateTime(nullable := False),
  13:                          .Type = c.String(nullable := False, maxLength := 50),
  14:                          .URL = c.String(nullable := False, maxLength := 250),
  15:                          .TXT = c.String(nullable := False, maxLength := 250),
  16:                          .Descr = c.String(maxLength := 1000)
  17:                      }) _
  18:                  .PrimaryKey(Function(t) t.I)
  19:              
  20:          End Sub
  21:          
  22:          Public Overrides Sub Down()
  23:              DropTable("dbo.MyArticlesRecords")
  24:          End Sub
  25:      End Class
  26:  End Namespace



4.1.8. Update-Database -Verbose


This step create database structure in SQL server (without data in this case, because seed method is absent).




4.1.9. Seed the Database


   1:  Imports System
   2:  Imports System.Data.Entity
   3:  Imports System.Data.Entity.Migrations
   4:  Imports System.Linq
   5:   
   6:  Namespace Migrations
   7:   
   8:      Friend NotInheritable Class Configuration 
   9:          Inherits DbMigrationsConfiguration(Of ApplicationDbContext)
  10:   
  11:          Public Sub New()
  12:              AutomaticMigrationsEnabled = False
  13:          End Sub
  14:   
  15:          Protected Overrides Sub Seed(context As ApplicationDbContext)
  16:   
  17:              context.MyArticles.AddOrUpdate(
  18:  New MyArticlesRecord With {.I = 1, .ID = Guid.Parse("225C83FF-9456-433B-833D-9977966F92BA"), .CrDate = Date.ParseExact("1998-01-01", "yyyy-MM-dd", System.Globalization.CultureInfo.InvariantCulture), .Type = "SQL (1998 год)", .URL = "//www.vb-net.com/sql/MySQL.htm", .TXT = "MySQL->SQL"},
  19:  New MyArticlesRecord With {.I = 2, .ID = Guid.Parse("58FB0DDE-C809-4B3F-9354-8ED516EBAFA1"), .CrDate = Date.ParseExact("1999-01-01", "yyyy-MM-dd", System.Globalization.CultureInfo.InvariantCulture), .Type = "SQL (1999 год)", .URL = "//www.vb-net.com/sql/access/index.htm", .TXT = "Access->SQL"},
 ...   
 561:  New MyArticlesRecord With {.I = 544, .ID = Guid.Parse("DDE0D380-11A7-4424-B671-E84242E4EF83"), .CrDate = Date.ParseExact("2018-12-25", "yyyy-MM-dd", System.Globalization.CultureInfo.InvariantCulture), .Type = "Soft (2018)", .URL = "//www.vb-net.com/Articles/Index.htm", .TXT = "All my articles ordered by datetime."}
 562:  )
 563:   
 564:   
 565:   
 566:          End Sub
 567:   
 568:      End Class
 569:   
 570:  End Namespace

Main problem of this step is incorrect data. For example in middle in the middle screen below you can see an empty mandatory filed TXT. So, main point of this step is adding a error log function to ApplicationDbContext, because without error handler function seed abandon silently.


   1:      Public Overrides Function SaveChanges() As Integer
   2:          Try
   3:              Return MyBase.SaveChanges()
   4:          Catch ex As Data.Entity.Validation.DbEntityValidationException
   5:              Dim sb = New StringBuilder()
   6:   
   7:              For Each failure In ex.EntityValidationErrors
   8:                  sb.AppendFormat("{0} failed validation" & vbLf, failure.Entry.Entity.[GetType]())
   9:   
  10:                  For Each [error] In failure.ValidationErrors
  11:                      sb.AppendFormat("- {0} : {1}", [error].PropertyName, [error].ErrorMessage)
  12:                      sb.AppendLine()
  13:                  Next
  14:              Next
  15:   
  16:              Throw New Data.Entity.Validation.DbEntityValidationException("Entity Validation Failed - errors follow:" & vbLf & sb.ToString(), ex)
  17:          End Try
  18:      End Function



4.1.10. Show DB structure





4.2 - Set Up ASP.NET API Endpoints to open database on internet


WebAPi controller used practically in each project, look for example to this project Use Ajax/WebApi Syns/Async request/response., but below I will show tune step by step.


4.2.1. Add WebAPI Controller


   1:  Public Class ArticlesController
   2:      Inherits Http.ApiController
   3:   
   4:      Private db As ApplicationDbContext = New ApplicationDbContext()
   5:   
   6:      Public Function GetMyArticles() As IQueryable(Of MyArticlesRecord)
   7:          Return db.MyArticles
   8:      End Function
   9:   
  10:      <Http.Description.ResponseType(GetType(MyArticlesRecord))>
  11:      Public Async Function GetMyArticles(ByVal id As Integer) As Threading.Tasks.Task(Of Http.IHttpActionResult)
  12:          Dim OneRec As MyArticlesRecord = Await db.MyArticles.FindAsync(id)
  13:   
  14:          If OneRec Is Nothing Then
  15:              Return NotFound()
  16:          End If
  17:   
  18:          Return Ok(OneRec)
  19:      End Function
  20:   
  21:  End Class


VS2017 show support text about next step to configure WebApi.


   1:  Visual Studio has added the full set of dependencies for ASP.NET Web API 2 to project 'WebApi-1'. 
   2:   
   3:  The Global.asax.vb file in the project may require additional changes to enable ASP.NET Web API.
   4:   
   5:  1. Add the following namespace references:
   6:   
   7:      Imports System.Web.Http
   8:      Imports System.Web.Routing
   9:   
  10:  2. If the code does not already define an Application_Start method, add the following method:
  11:   
  12:      protected Sub Application_Start()
  13:      
  14:      End Sub
  15:   

4.2.2. Add Json formater to WebApiConfig


  18:          Dim jsonFormatter = config.Formatters.JsonFormatter
  19:          jsonFormatter.SerializerSettings.ContractResolver = New CamelCasePropertyNamesContractResolver()
  20:          config.Formatters.Remove(config.Formatters.XmlFormatter)
  21:          jsonFormatter.SerializerSettings.DateTimeZoneHandling = Newtonsoft.Json.DateTimeZoneHandling.Utc



4.2.3. Add callback to execute WebApiConfig at the beginning of the Application_Start.


   1:  GlobalConfiguration.Configure(AddressOf WebApiConfig.Register)



4.2.4. Check result





4.2.5. Is WebApi a best choice to flash data on internet?


I'm not sure for WebAPI is best choice to transfer data from DB to internet, because there are some big and simplest alternatives:



Comments ( )
Link to this page: //www.vb-net.com/EF-missing-FAQ/index4.htm
< THANKS ME>