Stephen Shakespeare

Entity Framework Code First connection to a database

Connection String

  <connectionStrings>
    <add name="<my>Entities"
connectionString="Data Source=<servername>;
initial catalog=<dbname>;
integrated security=True;
multipleactiveresultsets=True;
App=EntityFramework;"
providerName="System.Data.SQLClient" />
  </connectionStrings>

Database Class

Imports System.Data.Entity

Public Class myDatabase
    Inherits Entity.DbContext

    Property Products() As DbSet(Of Products)
    Property Logs() As DbSet(Of myLog)

    Sub New()
        MyBase.New("EFIEntities")
    End Sub

    Protected Overrides Sub OnModelCreating(ByVal modelBuilder As DbModelBuilder)

        modelBuilder.Entity(Of Products).Map(Sub(mc) mc.ToTable("Product.Products"))

        modelBuilder.Entity(Of myLog).Map(Sub(mc)mc.ToTable("dbo.Logs"))

        modelBuilder.Entity(Of IHead)().Property(Function(f) f.OrderCreated).
                   HasColumnName("ih_date")

        modelBuilder.Entity(Of Film).Ignore(Function(f) f.ColumnNotInDB)

	'For Composite Key
	modelBuilder.Entity(Of RouteCardOpsProduct).
		HasKey(Function(f) New With {f.ProductID, f.OpOrder})

        modelBuilder.Entity(Of ComponentCF).Property(Function(f) f.ProductID)
		.HasDatabaseGeneratedOption(ComponentModel.DataAnnotations.Schema.DatabaseGeneratedOption.None)

'Creating Foreign Keys with thanks Julie Lerman
Note: this is tricky and easily broken with the wrong syntax.
Instead before doing the below consider a LINQ statement which would use navigation properties which work surprisingly well
Dim result = From c In context.Customers Where c.ID = 14 Select New With {c, c.Quotes}  'Anonymous type
To return this as a customer do this:
        Function FindCustomerIncludeQuotes(CustomerIDValue As Integer) As Customer
            Dim query = (From c In context.Customers
                         Where c.ID = CustomerIDValue
                         Select New With {c, c.Quotes}).FirstOrDefault

            Dim result = query.c
            result.Quotes = query.Quotes
            Return result
        End Function

'http://msdn.microsoft.com/en-us/data/gg194009
'http://msdn.microsoft.com/en-us/data/hh134698.aspx
	modelBuilder.Entity(Of Post)().HasRequired(Function(p) p.Blog).WithMany(Function(b) b.Posts).HasForeignKey(Function(p) p.FKBlogId)

	modelBuilder.Entity(Of Post)().HasMany(Function(p) p.Tags).WithMany(Function(t) t.Posts).Map(Sub(mc)
				mc.ToTable("PostJoinTag")
				mc.MapLeftKey("PostId")
				mc.MapRightKey("TagId")
			End Sub)

     End Sub End Class

Classes

Public Class Products
    <key> Public Property ID As Integer
    Public Property Code As String
End Class

Public Class RouteCardOps
	'Composite key do mapping in OnModelCreating. Column may not work yet in EF v4.x
	<key> Property ID As Integer
	<key> Property OpOrder As Integer
	Property OpCode As String

	Overridable Property Product As Product
End Class

Public Class PadCost
    <key, DatabaseGenerated(DatabaseGeneratedOption.None)> Property PadID As Integer
    <DatabaseGenerated(DatabaseGeneratedOption.Computed)>Property Total SMV As Decimal
End Class

Public Class Blog
 	<key> Property ID As Integer
	Property Title As String
End Class

Public Class Posts
	<key> Property ID As Integer
	Property Title As String
	Property BlogID As Integer

	Overidable Property Blog As Blog
End Class

Program

Module Module1
Sub Main()

Dim db As New myDatabase

Dim resultProduct = From a In db.Products
Where a.Code = "abc"
Select a

Dim result = From a In db.Logs Select a Take 10

Console.WriteLine(String.Concat("Log ID: ", result.First.ID))
Console.WriteLine(String.Concat("Product ID: ", resultProduct.First.ID))
Console.WriteLine()
Console.WriteLine("Press a key to continue/end")
Console.ReadKey()

'To Execute SQL directly against the database
context.Database.ExecuteSqlCommand("DELETE FROM Product.Products WHERE ID = ", 1)

'Then you may need to instantiate a new copy or clear an existing copy use context.Set(Of <entity>)
contextCF.Products = contextCF.Set(Of Products)()
End Sub

End Module

Windows Forms

http://msdn.microsoft.com/en-us/data/jj682076.aspx

Imports System.Data.Entity

now
	context.MyEntity.Load()
	MyBindingSource.DataSource = context.MyEntity.Local.ToBindingList

was
    'context.Currency.Load()
    DbExtensions.Load(context.Currency)
    Me.CurrencyBindingSource.DataSource = DbExtensions.ToBindingList(context.Currency.Local)

Errors and solutions:

Error Unable to load the specified metadata resource
Solution: For code first the metadata=res is unnecessary. Just use an ordinary connection string as above.

Error: ‘Load’ is not a member of System.Data.Entity.DbSet(Of …)
Solution:
Use Imports System.Data.Entity
Also ensure there is a reference to EntityFramework in the project. Especially if this is an N-Tier solution

Error: With Inherited class even it is the parent class that is being collected. Then get Invalid column name ‘Discriminator’
Solution Add <NotMapped> to child class
modelBuilder.Ignore<classname>();

Error: INSERT does not insert key if this is not an IDENTITY
Solution: Add attribute as <key, DatabaseGenerated(DatabaseGeneratedOption.None)>
Or modelBuilder.Entity<Address>().Property(p => p.Id).HasDatabaseGeneratedOption(DatabaseGeneratedOption.None);

Error: After a delete using context.Database.ExecuteSQLCommand(“DELETE…”) the context still have copies of the entities in any DBSet which needs to be cleared
Solution: context.Products = context.Set(Of Products)()

Error: The type initializer for ‘System.Data.Entity.Internal.AppConfig’ threw an exception
Solution:
If Entity Framework is installed by NuGet then you should not have a problem. However after a version update, or manual changes you may get this fault. One problem may be that in the App.Config file the section name may be missing as below:

<configuration>
    <configSections>
    <!-- For more information on Entity Framework configuration, visit http://go.microsoft.com/fwlink/?LinkID=237468 -->
      <section name="entityFramework" type="System.Data.Entity.Internal.ConfigFile.EntityFrameworkSection, EntityFramework, Version=6.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" requirePermission="false" />
    </configSections>

Error:
The Entity Framework provider type ‘System.Data.Entity.SqlServer.SqlProviderServices, EntityFramework.SqlServer’ registered in the application config file for the ADO.NET provider with invariant name ‘System.Data.SqlClient’ could not be loaded. Make sure that the assembly-qualified name is used and that the assembly is available to the running application. See http://go.microsoft.com/fwlink/?LinkId=260882 for more information.

Solution:
Add reference to EntityFramework.SqlServer, which may have not been installed via NuGet for some reason
Or if new then it may not have been built properly, so try building project again.
See link in error or: http://msdn.microsoft.com/en-us/data/jj730568

End