Boostig Entity Framework Loops
public class Product {
public int ID { get; set; }
public string ShortDescription { get; set; }
}
public class OldVariation {
public int ID { get; set; }
public int ProductID { get; set; }
public virtual ProductModel Product { get; set; }
}
public class Variation {
public int ID { get; set; }
public int ProductID { get; set; }
public virtual Product Product { get; set; }
}
var db = new AppContext();
var oldVariations= db.OldVariations.ToList();
foreach (var item in oldVariations )
{
var variation = new VariationModel
{
ProductID = item.ProductID ,
Description = item.Product.Description, //because of lazy loading, entity framework is querying the database!
ShortDescription = item.Product.ShortDescription , //because of lazy loading, entity framework is querying the database!//because of lazy loading, entity framework is querying the database!
};
//if validations pass
db.Variations.Add(variation);
db.SaveChanges();
}
This scenario takes ages if the number of OldVariations is very very large. Improvements:
eager load product
save and dispose db context every n loops
context.Configuration.AutoDetectChangesEnabled = false
var db = new AppContext();
var oldVariations= db.OldVariations.Include(o=>o.Product).ToList();
var counter = 0;
var threshold = 50;
db.Configuration.AutoDetectChangesEnabled = false;
foreach (var item in oldVariations )
{
var variation = new VariationModel
{
ProductID = item.ProductID ,
Description = item.Product.Description, //because of eager loading, entity framework is NOT querying the database
ShortDescription = item.Product.ShortDescription , /because of eager loading, entity framework is NOT querying the database
};
//if validations pass
db.Variations.Add(variation);
counter++;
if (counter >= threshold)
{
db.SaveChanges();
db.Dispose();
db = new AppContext();
db.Configuration.AutoDetectChangesEnabled = false;
counter = 0;
}
}
db.SaveChanges();
Create Unique Index with Column that Accepts Null Values in Entity Framework Migration
Sql("CREATE UNIQUE NONCLUSTERED INDEX [IX_ColumnWithNulls] ON [dbo].[Table]([ColumnWithNulls] ASC) WHERE ([ColumnWithNulls] IS NOT NULL");
Sql("CREATE UNIQUE NONCLUSTERED INDEX IX_Number on dbo.Orders(Number,ID) where Number IS NOT NULL");
Resetting Entity Framework Migrations to a clean Slate
By Rick Strahl
public string Description { get; set; }
public string ShortDescription { get; set; }
public string Description { get; set; }
Windows Service with Code First Migrations Error
Getting an error of the type "System.InvalidOperationException: The model backing the 'Context' context has changed since the database was created. Consider using Code First Migrations to update the database " and the migrations are up to date and handled by another project.
In fact, the error is because the user running the windows service does not have sufficient privileges! Try setting up a different Windows service login account from the service properties.
EF, SQL Spatial and ClassLibrary Project for Models and Web App Project Referencing the ClassLibrary Project
ClassLibrary Project contains context and models linked to database by Entity Framework.
In order to use DBGeography I needed:
install in WebApp Project Nuget package Microsoft.SqlServer.Types
in method Application_Start of Global.asax add
SqlServerTypes.Utilities.LoadNativeAssemblies(Server.MapPath("~/bin"));
SqlProviderServices.SqlServerTypesAssemblyName =
"Microsoft.SqlServer.Types, Version=14.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91";
In order to re-copy assemblies to bin folder, you first need to stop IIS!!!
Error Migrating on AddOrUpdate(p => new { p.KeyID, p.ValID }... Where ValID is Nullable Int
Make sure Entity framework is above 6.2.0
EF, SQL Spatial and ClassLibrary Project for Models and Windows Service Referencing the ClassLibrary Project
ClassLibrary Project contains context and models linked to database by Entity Framework with DBGeography.
Initially:
ClassLibrary with nuget Microsoft.SqlServer.Types 14.0.1016.290
SqlServerTypes.Utilities.LoadNativeAssemblies(AppDomain.CurrentDomain.BaseDirectory); in ClassLibrary
Solution projects with EntityFramework 6.2.0 and 6.3.0
Windows Service with nuget Microsoft.SqlServer.Types 14.0.1016.290
When trying to write DB from Windows Service I get error "Exception: System.InvalidOperationException: Spatial types and functions are not available for this provider because the assembly 'Microsoft.SqlServer.Types' version 10 or higher could not be found. en System.Data.Entity.SqlServer.SqlTypesAssemblyLoader.GetSqlTypesAssembly() en System.Data.Entity.SqlServer.SqlSpatialServices.GeometryFromText(String wellKnownText) en System.Data.Entity.Spatial.DbGeometry.FromText(String wellKnownText) en System.Data.Entity.Core.Mapping.Update.Internal.Propagator.ExtentPlaceholderCreator.InitializeSpatialTypeDefaultMap()..."
In order to use DBGeography in Windows Service I needed:
Remove nuget Microsoft.SqlServer.Types from ClassLibrary
Remove any reference to SqlServerTypes.Utilities.LoadNativeAssemblies whatsoever
Upgrade entire solution to EntityFramework 6.4.4. Then I got error "No se puede cargar el archivo DLL 'SqlServerSpatial150.dll': No se puede encontrar el módulo especificado" solved by:
Upgrade nuget Microsoft.SqlServer.Types to 160.1000.6 in Windows Service
Windows Service Connects To LocalDB Database But Retrieves No Data and Shows No Connection Error!
Change account used to start service.
The type or namespace name 'Models' does not exist in the namespace 'X' (are you missing an assembly reference?)
Check out the views, there's a reference to X.Models.... and there's no namespace X.Models.
In view you may have:
@model X.Models.Y
Cannot Migrate a Remote Database
Need to publish site in the same server as the database to migrate the database.