I currently have a very annoying issue with updating a NopCommerce database from Entity Framework.
BACKGROUND:
I am writing a small tool to migrate one type of outdated ecommerce app to nopCommerce.
THE ERROR:
The conversion of a datetime2 data type to a datetime data type resulted in an out-of-range value.
The statement has been terminated.
WHAT I HAVE DONE TO TRY RESOLVE IT
In the past, this sort of error came up when I had a DateTime property that was uninitialized (year 0001).. but in this case, that is not the issue. I looked and made sure all the DateTime properties were being set. As that didn't work, I also tried making the db column datetime2(7) but no luck. Finally I set it to NULLABLE but it still gave me the same error. So, there's obviously something else going on here and EF is throwing some unhelpful error. I had wondered if there was perhaps an insert trigger (or any trigger for that matter) on the table which was trying to perhaps create an entry in the product variant table or another table where the datetime error was then coming from, but that is not the case.
THE CODE:
foreach (var piloProduct in piloProducts)
{
var product = new Models.Nop.Product
{
Name = piloProduct.Name,
ShortDescription = piloProduct.ShortDescription,
FullDescription = piloProduct.FullDescription,
ProductTemplateId = 1,
ShowOnHomePage = false,
MetaKeywords = piloProduct.MetaKeywords,
MetaDescription = piloProduct.MetaDescription,
MetaTitle = piloProduct.MetaTitle,
SeName = piloProduct.SeName,
AllowCustomerReviews = false,
ApprovedRatingSum = 0,
NotApprovedRatingSum = 0,
ApprovedTotalReviews = 0,
NotApprovedTotalReviews = 0,
Published = true,
Deleted = false,
CreatedOnUtc = DateTime.UtcNow,
UpdatedOnUtc = DateTime.UtcNow
};
nopDb.Products.AddObject(product);
}
nopDb.SaveChanges();
I'm at a complete loss with this one, as the above code is really simple and straightforward. Any help much appreciated.
UPDATE
I have just tried using Linq-To-SQL classes instead of Entity Framework EDMX, but had the exact same problem. The strange thing is, I ran an "INSERT INTO" statment on the table directly from SQL Management Studio but it worked.. go figure... is there any way to see exactly what "INSERT" statements are being generated by either EF or Linq-To-SQL?
And here's the EDMX generated model requested by Mark Oreta:
[EdmEntityTypeAttribute(NamespaceName="eSalesModel", Name="Product")]
[Serializable()]
[DataContractAttribute(IsReference=true)]
public partial class Product : EntityObject
{
public static Product CreateProduct(global::System.Int32 id, global::System.String name, global::System.Int32 productTemplateId, global::System.Boolean showOnHomePage, global::System.Boolean allowCustomerReviews, global::System.Int32 approvedRatingSum, global::System.Int32 notApprovedRatingSum, global::System.Int32 approvedTotalReviews, global::System.Int32 notApprovedTotalReviews, global::System.Boolean published, global::System.Boolean deleted)
{
Product product = new Product();
product.Id = id;
product.Name = name;
product.ProductTemplateId = productTemplateId;
product.ShowOnHomePage = showOnHomePage;
product.AllowCustomerReviews = allowCustomerReviews;
product.ApprovedRatingSum = approvedRatingSum;
product.NotApprovedRatingSum = notApprovedRatingSum;
product.ApprovedTotalReviews = approvedTotalReviews;
product.NotApprovedTotalReviews = notApprovedTotalReviews;
product.Published = published;
product.Deleted = deleted;
return product;
}
[EdmScalarPropertyAttribute(EntityKeyProperty=true, IsNullable=false)]
[DataMemberAttribute()]
public global::System.Int32 Id
{
get
{
return _Id;
}
set
{
if (_Id != value)
{
OnIdChanging(value);
ReportPropertyChanging("Id");
_Id = StructuralObject.SetValidValue(value);
ReportPropertyChanged("Id");
OnIdChanged();
}
}
}
private global::System.Int32 _Id;
partial void OnIdChanging(global::System.Int32 value);
partial void OnIdChanged();
[EdmScalarPropertyAttribute(EntityKeyProperty=false, IsNullable=true)]
[DataMemberAttribute()]
public Nullable<global::System.Int32> PiloId
{
get
{
return _PiloId;
}
set
{
OnPiloIdChanging(value);
ReportPropertyChanging("PiloId");
_PiloId = StructuralObject.SetValidValue(value);
ReportPropertyChanged("PiloId");
OnPiloIdChanged();
}
}
private Nullable<global::System.Int32> _PiloId;
partial void OnPiloIdChanging(Nullable<global::System.Int32> value);
partial void OnPiloIdChanged();
[EdmScalarPropertyAttribute(EntityKeyProperty=false, IsNullable=false)]
[DataMemberAttribute()]
public global::System.String Name
{
get
{
return _Name;
}
set
{
OnNameChanging(value);
ReportPropertyChanging("Name");
_Name = StructuralObject.SetValidValue(value, false);
ReportPropertyChanged("Name");
OnNameChanged();
}
}
private global::System.String _Name;
partial void OnNameChanging(global::System.String value);
partial void OnNameChanged();
[EdmScalarPropertyAttribute(EntityKeyProperty=false, IsNullable=true)]
[DataMemberAttribute()]
public global::System.String ShortDescription
{
get
{
return _ShortDescription;
}
set
{
OnShortDescriptionChanging(value);
ReportPropertyChanging("ShortDescription");
_ShortDescription = StructuralObject.SetValidValue(value, true);
ReportPropertyChanged("ShortDescription");
OnShortDescriptionChanged();
}
}
private global::System.String _ShortDescription;
partial void OnShortDescriptionChanging(global::System.String value);
partial void OnShortDescriptionChanged();
[EdmScalarPropertyAttribute(EntityKeyProperty=false, IsNullable=true)]
[DataMemberAttribute()]
public global::System.String FullDescription
{
get
{
return _FullDescription;
}
set
{
OnFullDescriptionChanging(value);
ReportPropertyChanging("FullDescription");
_FullDescription = StructuralObject.SetValidValue(value, true);
ReportPropertyChanged("FullDescription");
OnFullDescriptionChanged();
}
}
private global::System.String _FullDescription;
partial void OnFullDescriptionChanging(global::System.String value);
partial void OnFullDescriptionChanged();
[EdmScalarPropertyAttribute(EntityKeyProperty=false, IsNullable=true)]
[DataMemberAttribute()]
public global::System.String AdminComment
{
get
{
return _AdminComment;
}
set
{
OnAdminCommentChanging(value);
ReportPropertyChanging("AdminComment");
_AdminComment = StructuralObject.SetValidValue(value, true);
ReportPropertyChanged("AdminComment");
OnAdminCommentChanged();
}
}
private global::System.String _AdminComment;
partial void OnAdminCommentChanging(global::System.String value);
partial void OnAdminCommentChanged();
[EdmScalarPropertyAttribute(EntityKeyProperty=false, IsNullable=false)]
[DataMemberAttribute()]
public global::System.Int32 ProductTemplateId
{
get
{
return _ProductTemplateId;
}
set
{
OnProductTemplateIdChanging(value);
ReportPropertyChanging("ProductTemplateId");
_ProductTemplateId = StructuralObject.SetValidValue(value);
ReportPropertyChanged("ProductTemplateId");
OnProductTemplateIdChanged();
}
}
private global::System.Int32 _ProductTemplateId;
partial void OnProductTemplateIdChanging(global::System.Int32 value);
partial void OnProductTemplateIdChanged();
[EdmScalarPropertyAttribute(EntityKeyProperty=false, IsNullable=false)]
[DataMemberAttribute()]
public global::System.Boolean ShowOnHomePage
{
get
{
return _ShowOnHomePage;
}
set
{
OnShowOnHomePageChanging(value);
ReportPropertyChanging("ShowOnHomePage");
_ShowOnHomePage = StructuralObject.SetValidValue(value);
ReportPropertyChanged("ShowOnHomePage");
OnShowOnHomePageChanged();
}
}
private global::System.Boolean _ShowOnHomePage;
partial void OnShowOnHomePageChanging(global::System.Boolean value);
partial void OnShowOnHomePageChanged();
[EdmScalarPropertyAttribute(EntityKeyProperty=false, IsNullable=true)]
[DataMemberAttribute()]
public global::System.String MetaKeywords
{
get
{
return _MetaKeywords;
}
set
{
OnMetaKeywordsChanging(value);
ReportPropertyChanging("MetaKeywords");
_MetaKeywords = StructuralObject.SetValidValue(value, true);
ReportPropertyChanged("MetaKeywords");
OnMetaKeywordsChanged();
}
}
private global::System.String _MetaKeywords;
partial void OnMetaKeywordsChanging(global::System.String value);
partial void OnMetaKeywordsChanged();
[EdmScalarPropertyAttribute(EntityKeyProperty=false, IsNullable=true)]
[DataMemberAttribute()]
public global::System.String MetaDescription
{
get
{
return _MetaDescription;
}
set
{
OnMetaDescriptionChanging(value);
ReportPropertyChanging("MetaDescription");
_MetaDescription = StructuralObject.SetValidValue(value, true);
ReportPropertyChanged("MetaDescription");
OnMetaDescriptionChanged();
}
}
private global::System.String _MetaDescription;
partial void OnMetaDescriptionChanging(global::System.String value);
partial void OnMetaDescriptionChanged();
[EdmScalarPropertyAttribute(EntityKeyProperty=false, IsNullable=true)]
[DataMemberAttribute()]
public global::System.String MetaTitle
{
get
{
return _MetaTitle;
}
set
{
OnMetaTitleChanging(value);
ReportPropertyChanging("MetaTitle");
_MetaTitle = StructuralObject.SetValidValue(value, true);
ReportPropertyChanged("MetaTitle");
OnMetaTitleChanged();
}
}
private global::System.String _MetaTitle;
partial void OnMetaTitleChanging(global::System.String value);
partial void OnMetaTitleChanged();
[EdmScalarPropertyAttribute(EntityKeyProperty=false, IsNullable=true)]
[DataMemberAttribute()]
public global::System.String SeName
{
get
{
return _SeName;
}
set
{
OnSeNameChanging(value);
ReportPropertyChanging("SeName");
_SeName = StructuralObject.SetValidValue(value, true);
ReportPropertyChanged("SeName");
OnSeNameChanged();
}
}
private global::System.String _SeName;
partial void OnSeNameChanging(global::System.String value);
partial void OnSeNameChanged();
[EdmScalarPropertyAttribute(EntityKeyProperty=false, IsNullable=false)]
[DataMemberAttribute()]
public global::System.Boolean AllowCustomerReviews
{
get
{
return _AllowCustomerReviews;
}
set
{
OnAllowCustomerReviewsChanging(value);
ReportPropertyChanging("AllowCustomerReviews");
_AllowCustomerReviews = StructuralObject.SetValidValue(value);
ReportPropertyChanged("AllowCustomerReviews");
OnAllowCustomerReviewsChanged();
}
}
private global::System.Boolean _AllowCustomerReviews;
partial void OnAllowCustomerReviewsChanging(global::System.Boolean value);
partial void OnAllowCustomerReviewsChanged();
[EdmScalarPropertyAttribute(EntityKeyProperty=false, IsNullable=false)]
[DataMemberAttribute()]
public global::System.Int32 ApprovedRatingSum
{
get
{
return _ApprovedRatingSum;
}
set
{
OnApprovedRatingSumChanging(value);
ReportPropertyChanging("ApprovedRatingSum");
_ApprovedRatingSum = StructuralObject.SetValidValue(value);
ReportPropertyChanged("ApprovedRatingSum");
OnApprovedRatingSumChanged();
}
}
private global::System.Int32 _ApprovedRatingSum;
partial void OnApprovedRatingSumChanging(global::System.Int32 value);
partial void OnApprovedRatingSumChanged();
[EdmScalarPropertyAttribute(EntityKeyProperty=false, IsNullable=false)]
[DataMemberAttribute()]
public global::System.Int32 NotApprovedRatingSum
{
get
{
return _NotApprovedRatingSum;
}
set
{
OnNotApprovedRatingSumChanging(value);
ReportPropertyChanging("NotApprovedRatingSum");
_NotApprovedRatingSum = StructuralObject.SetValidValue(value);
ReportPropertyChanged("NotApprovedRatingSum");
OnNotApprovedRatingSumChanged();
}
}
private global::System.Int32 _NotApprovedRatingSum;
partial void OnNotApprovedRatingSumChanging(global::System.Int32 value);
partial void OnNotApprovedRatingSumChanged();
[EdmScalarPropertyAttribute(EntityKeyProperty=false, IsNullable=false)]
[DataMemberAttribute()]
public global::System.Int32 ApprovedTotalReviews
{
get
{
return _ApprovedTotalReviews;
}
set
{
OnApprovedTotalReviewsChanging(value);
ReportPropertyChanging("ApprovedTotalReviews");
_ApprovedTotalReviews = StructuralObject.SetValidValue(value);
ReportPropertyChanged("ApprovedTotalReviews");
OnApprovedTotalReviewsChanged();
}
}
private global::System.Int32 _ApprovedTotalReviews;
partial void OnApprovedTotalReviewsChanging(global::System.Int32 value);
partial void OnApprovedTotalReviewsChanged();
[EdmScalarPropertyAttribute(EntityKeyProperty=false, IsNullable=false)]
[DataMemberAttribute()]
public global::System.Int32 NotApprovedTotalReviews
{
get
{
return _NotApprovedTotalReviews;
}
set
{
OnNotApprovedTotalReviewsChanging(value);
ReportPropertyChanging("NotApprovedTotalReviews");
_NotApprovedTotalReviews = StructuralObject.SetValidValue(value);
ReportPropertyChanged("NotApprovedTotalReviews");
OnNotApprovedTotalReviewsChanged();
}
}
private global::System.Int32 _NotApprovedTotalReviews;
partial void OnNotApprovedTotalReviewsChanging(global::System.Int32 value);
partial void OnNotApprovedTotalReviewsChanged();
[EdmScalarPropertyAttribute(EntityKeyProperty=false, IsNullable=false)]
[DataMemberAttribute()]
public global::System.Boolean Published
{
get
{
return _Published;
}
set
{
OnPublishedChanging(value);
ReportPropertyChanging("Published");
_Published = StructuralObject.SetValidValue(value);
ReportPropertyChanged("Published");
OnPublishedChanged();
}
}
private global::System.Boolean _Published;
partial void OnPublishedChanging(global::System.Boolean value);
partial void OnPublishedChanged();
[EdmScalarPropertyAttribute(EntityKeyProperty=false, IsNullable=false)]
[DataMemberAttribute()]
public global::System.Boolean Deleted
{
get
{
return _Deleted;
}
set
{
OnDeletedChanging(value);
ReportPropertyChanging("Deleted");
_Deleted = StructuralObject.SetValidValue(value);
ReportPropertyChanged("Deleted");
OnDeletedChanged();
}
}
private global::System.Boolean _Deleted;
partial void OnDeletedChanging(global::System.Boolean value);
partial void OnDeletedChanged();
[EdmScalarPropertyAttribute(EntityKeyProperty=false, IsNullable=true)]
[DataMemberAttribute()]
public Nullable<global::System.DateTime> CreatedOnUtc
{
get
{
return _CreatedOnUtc;
}
set
{
OnCreatedOnUtcChanging(value);
ReportPropertyChanging("CreatedOnUtc");
_CreatedOnUtc = StructuralObject.SetValidValue(value);
ReportPropertyChanged("CreatedOnUtc");
OnCreatedOnUtcChanged();
}
}
private Nullable<global::System.DateTime> _CreatedOnUtc;
partial void OnCreatedOnUtcChanging(Nullable<global::System.DateTime> value);
partial void OnCreatedOnUtcChanged();
[EdmScalarPropertyAttribute(EntityKeyProperty=false, IsNullable=true)]
[DataMemberAttribute()]
public Nullable<global::System.DateTime> UpdatedOnUtc
{
get
{
return _UpdatedOnUtc;
}
set
{
OnUpdatedOnUtcChanging(value);
ReportPropertyChanging("UpdatedOnUtc");
_UpdatedOnUtc = StructuralObject.SetValidValue(value);
ReportPropertyChanged("UpdatedOnUtc");
OnUpdatedOnUtcChanged();
}
}
private Nullable<global::System.DateTime> _UpdatedOnUtc;
partial void OnUpdatedOnUtcChanging(Nullable<global::System.DateTime> value);
partial void OnUpdatedOnUtcChanged();
[XmlIgnoreAttribute()]
[SoapIgnoreAttribute()]
[DataMemberAttribute()]
[EdmRelationshipNavigationPropertyAttribute("eSalesModel", "ProductCategory_Product", "Product_Category_Mapping")]
public EntityCollection<Product_Category_Mapping> Product_Category_Mapping
{
get
{
return ((IEntityWithRelationships)this).RelationshipManager.GetRelatedCollection<Product_Category_Mapping>("eSalesModel.ProductCategory_Product", "Product_Category_Mapping");
}
set
{
if ((value != null))
{
((IEntityWithRelationships)this).RelationshipManager.InitializeRelatedCollection<Product_Category_Mapping>("eSalesModel.ProductCategory_Product", "Product_Category_Mapping", value);
}
}
}
[XmlIgnoreAttribute()]
[SoapIgnoreAttribute()]
[DataMemberAttribute()]
[EdmRelationshipNavigationPropertyAttribute("eSalesModel", "ProductManufacturer_Product", "Product_Manufacturer_Mapping")]
public EntityCollection<Product_Manufacturer_Mapping> Product_Manufacturer_Mapping
{
get
{
return ((IEntityWithRelationships)this).RelationshipManager.GetRelatedCollection<Product_Manufacturer_Mapping>("eSalesModel.ProductManufacturer_Product", "Product_Manufacturer_Mapping");
}
set
{
if ((value != null))
{
((IEntityWithRelationships)this).RelationshipManager.InitializeRelatedCollection<Product_Manufacturer_Mapping>("eSalesModel.ProductManufacturer_Product", "Product_Manufacturer_Mapping", value);
}
}
}
[XmlIgnoreAttribute()]
[SoapIgnoreAttribute()]
[DataMemberAttribute()]
[EdmRelationshipNavigationPropertyAttribute("eSalesModel", "ProductPicture_Product", "Product_Picture_Mapping")]
public EntityCollection<Product_Picture_Mapping> Product_Picture_Mapping
{
get
{
return ((IEntityWithRelationships)this).RelationshipManager.GetRelatedCollection<Product_Picture_Mapping>("eSalesModel.ProductPicture_Product", "Product_Picture_Mapping");
}
set
{
if ((value != null))
{
((IEntityWithRelationships)this).RelationshipManager.InitializeRelatedCollection<Product_Picture_Mapping>("eSalesModel.ProductPicture_Product", "Product_Picture_Mapping", value);
}
}
}
[XmlIgnoreAttribute()]
[SoapIgnoreAttribute()]
[DataMemberAttribute()]
[EdmRelationshipNavigationPropertyAttribute("eSalesModel", "ProductReview_Product1", "ProductReview")]
public EntityCollection<ProductReview> ProductReviews
{
get
{
return ((IEntityWithRelationships)this).RelationshipManager.GetRelatedCollection<ProductReview>("eSalesModel.ProductReview_Product1", "ProductReview");
}
set
{
if ((value != null))
{
((IEntityWithRelationships)this).RelationshipManager.InitializeRelatedCollection<ProductReview>("eSalesModel.ProductReview_Product1", "ProductReview", value);
}
}
}
[XmlIgnoreAttribute()]
[SoapIgnoreAttribute()]
[DataMemberAttribute()]
[EdmRelationshipNavigationPropertyAttribute("eSalesModel", "ProductSpecificationAttribute_Product", "Product_SpecificationAttribute_Mapping")]
public EntityCollection<Product_SpecificationAttribute_Mapping> Product_SpecificationAttribute_Mapping
{
get
{
return ((IEntityWithRelationships)this).RelationshipManager.GetRelatedCollection<Product_SpecificationAttribute_Mapping>("eSalesModel.ProductSpecificationAttribute_Product", "Product_SpecificationAttribute_Mapping");
}
set
{
if ((value != null))
{
((IEntityWithRelationships)this).RelationshipManager.InitializeRelatedCollection<Product_SpecificationAttribute_Mapping>("eSalesModel.ProductSpecificationAttribute_Product", "Product_SpecificationAttribute_Mapping", value);
}
}
}
[XmlIgnoreAttribute()]
[SoapIgnoreAttribute()]
[DataMemberAttribute()]
[EdmRelationshipNavigationPropertyAttribute("eSalesModel", "ProductVariant_Product", "ProductVariant")]
public EntityCollection<ProductVariant> ProductVariants
{
get
{
return ((IEntityWithRelationships)this).RelationshipManager.GetRelatedCollection<ProductVariant>("eSalesModel.ProductVariant_Product", "ProductVariant");
}
set
{
if ((value != null))
{
((IEntityWithRelationships)this).RelationshipManager.InitializeRelatedCollection<ProductVariant>("eSalesModel.ProductVariant_Product", "ProductVariant", value);
}
}
}
[XmlIgnoreAttribute()]
[SoapIgnoreAttribute()]
[DataMemberAttribute()]
[EdmRelationshipNavigationPropertyAttribute("eSalesModel", "Product_ProductTag_Mapping", "ProductTag")]
public EntityCollection<ProductTag> ProductTags
{
get
{
return ((IEntityWithRelationships)this).RelationshipManager.GetRelatedCollection<ProductTag>("eSalesModel.Product_ProductTag_Mapping", "ProductTag");
}
set
{
if ((value != null))
{
((IEntityWithRelationships)this).RelationshipManager.InitializeRelatedCollection<ProductTag>("eSalesModel.Product_ProductTag_Mapping", "ProductTag", value);
}
}
}
}
