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