Breeze is trying to update a computed database column

Question

A friend reported a problem with a computed column, Entity Framework, and Breeze

We have a table with a "FullName" column computed by the database. When creating a new Person, Breeze sends the FullName property value to the server, even though it’s not being set at all, and that triggers an error when trying to insert the new Person instance. The database throws this exception:

The column "FullName" cannot be modified because it is either a computed column or is the result of a UNION operator.

Here is the relevant portion of the SQL Table definition:

CREATE TABLE [dbo].[Person](
      [ID] [bigint] IDENTITY(1,1) NOT NULL,
      [FirstName] [varchar](100) NULL,
      [MiddleName] [varchar](100) NULL,
      [LastName] [varchar](100) NOT NULL,
      [FullName]  AS ((([Patient].[LastName]+',') + isnull(' '+[Patient].[FirstName],'')) + isnull(' '+[Patient].[MiddleName],'')),
      ...

My friend tells me the corresponding "Code First" class looks something like this:

public class Person {
      public int ID {get; set;}
      public string FirstName {get; set;}
      public string MiddleName {get; set;}
      public string LastName {get; set;}
      public string FullName {get; set;}
      ...
}

The answer to this question explains the problem and offers a solution.

Ward Ward · Accepted Answer · 2013-04-06T03:56:36

Design issues

Everyone looking at this wonders why there is a computed column for FullName and, secondarily, why this property is exposed to the client.

Let's just assume there is a good reason for the computed column, a good reason for the model to get the value from the table instead of calculating the value itself, and a good reason to send it to the client rather than have the client calculate it. Here's what he told me about that;

"We need to include the FullName in queries"

Life works out this way sometimes.

Consequences

Notice that the FullName property has a public setter. The EF metadata generator for the Person class cannot tell that this is a read-only property. FullName looks just like LastName. The metadata say "this is normal read/write property."

Breeze doesn't see a difference either. The client app may not touch this property, but Breeze has to send a value for it when creating a new Person. Back on the server, the Breeze EFContextProvider thinks it should pass that value along when creating the EF entity. The stage is set for disaster.

What can you do if (a) you can't change the table and (b) you can't change the model's FullName property definition?

A Solution

EF needs your help. You should tell EF that this is actually a database computed property. You could use the EF fluent interface or use the attribute as shown here:

[DatabaseGenerated(DatabaseGeneratedOption.Computed)]
public String FullName { get; set; }

Add this attribute and EF knows this property is read-only. It will generate the appropriate metadata and you can save a new Person cleanly. Omit it and you'll get the exception.

Note that this is only necessary for Code First. If he'd generated the model Database First, EF knows that the column is computed and doesn’t try to set it.

Be aware of a similar issue with store-generated keys. The default for an integer key is "store-generated" but the default for a Guid key is "client generated". If, in your table, the database actually sets the Guid, you must mark the ID property with [DatabaseGenerated(DatabaseGeneratedOption.Identity)]

Breeze is trying to update a computed database column

1 Answers

Design issues

Consequences

A Solution