Entity Framework persisted computed column woes
I recently updated a computed column to be persisted due to poor performance when querying it.
However now updates to child records throw this error
System.Data.SqlClient.SqlException
Internal Query Processor Error: The query processor could not produce a query plan. For more information, contact Customer Support Services.
Having an inner exception of
An error occurred while saving entities that do not expose foreign key properties for their relationships. The EntityEntries property will return null because a single entity cannot be identified as the source of the exception. Handling of exceptions while saving can be made easier by exposing foreign key properties in your entity types开发者_如何学C.
This happens with or without an index on the column.
FYI I have a configuration for the column thus:
Property(c => c.DisplayName).HasColumnName("DISPLAY_NM").HasDatabaseGeneratedOption(DatabaseGeneratedOption.Computed);
Any workaround?
edit
The child element statement is pretty irrelevant - but here it is. Imagine entities Order, Company (an Order must have a Company). I create an Order with an un-changed Company and its the Company entity with the computed column that is causing the problem - it works fine without the persistence on the computed column (as expected there's 1 insert statement on Order and 0 update statements on Company)
I think this is the solution but unsure how to do it in EF
I solved similar problem by adding ID of related entity as property (essentialy exposed FK value in table), and when creating record, if I only assign id of related object to exposed FK property, everything works, related object is not needed.
You can see sample of entity having both related object and its ID mapped using EF fluid mapping here:
public ThingMap()
{
this.Property(t => t.Name)
.IsRequired()
.IsMaxLength()
.IsUnicode();
//// Table mappings
this.ToTable("Things", "go");
this.Property(t => t.Name).HasColumnName("Name");
this.Property(t => t.TypeId).HasColumnName("Type_Id");
//// References
this.HasRequired(t => t.Type)
.WithMany(t => t.Things)
.HasForeignKey(t => t.TypeId);
}
精彩评论