Entity Framework persisted computed column woes

Question

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.

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

That would require more information to understand your problem. You are referring to child records but we don't know what is child and what is parent and what changes you did. — Ladislav Mrnka, Jun 06 '11 at 11:10
It is not always true that Company is not updated: http://stackoverflow.com/questions/6232185/ef4-update-table-set-p-0-where First of all check what happens in database layer by using SQL Profiler. — Ladislav Mrnka, Jun 06 '11 at 12:12
tried all that. looking for suggestions re: the difference of it working when the computed column is persisted or not — jenson-button-event, Jun 06 '11 at 12:47

score 0 · Answer 1 · answered Mar 15 '12 at 22:33

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);
        }

Entity Framework persisted computed column woes

1 Answers1