Entity Framework Code First connection to a database
15-Jan-1313 Leave a comment
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