Computed field in Servicestack ormlite error

Question

I couldn't make it work, I added the data annotation for a computed field using ServiceStack ormlite Sql server:

[Compute, Ignore]
public string FullName { get; set; }

The problem is that my LoadSelect<Employee>() method doesn't load the colunm FullName from a computed field. Why?

if I remove the [Ignore] it loads, but then when I use the.create() method to create a new record, it returns an error, probably because it tries to add a value for the FullName field.

Table

CREATE TABLE [dbo].[Employee](
    [EmployeeId] [int] IDENTITY(1,1) NOT NULL,
    [FullName]  AS (concat(ltrim(rtrim([FirstName])),' ',ltrim(rtrim([LastName])))) PERSISTED NOT NULL,
    [FirstName] [nvarchar](55) NOT NULL,
    [LastName] [nvarchar](55) NULL,
    [Username] [nvarchar](55) NOT NULL,
    [Password] [nvarchar](55) NULL
 CONSTRAINT [PK_Employee] PRIMARY KEY CLUSTERED 
(
    [EmployeeId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80) ON [PRIMARY]
) ON [PRIMARY]

Employee class:

[Schema("dbo")]
[Alias("Employee")]
public class Employee : IHasId<int>
{
    [PrimaryKey]
    [Alias("EmployeeId")]
    [AutoIncrement]
    [Index(Unique = true)]
    public int Id { get; set;}

    [Required]
    public string FirstName { get; set; }
    public string LastName { get; set; }

    [Required]
    [Index(true)]
    public string Username { get; set; }

    public string Password { get; set; }

    [Compute, Ignore]
    public string FullName { get; set; }
}

Get method:

    public virtual async Task<IEnumerable<T>> Get<T>() where T : IHasId<int>
    {
        using (var dbCon = DbConnectionFactory.OpenDbConnection())
        {
            return await dbCon.LoadSelectAsync<T>(x => x);
        }
    }

Create method:

    public virtual async Task<T> Create<T>(T obj) where T: IHasId<int>
    {
        using (var dbCon = DbConnectionFactory.OpenDbConnection())
        {
            // if there is an id then INSERTS otherwise UPDATES
            var id = obj.GetId().SafeToLong();

            if (id > 0)
                dbCon.Update(obj);
            else
                id = dbCon.Insert(obj, true);   

            // returns the object inserted or updated
            return await dbCon.LoadSingleByIdAsync<T>(id);
        }
    }

Could you provide more details about use of .create()? This a custom wrapper using Db.Insert<T> underneath or other? Also if you could provide full example POCOs for LoadSelect so others can reproduce issue? — Darren Reid
@RogerOliveira no it's still not runnable, it's missing the definition of Employee Table and can you also provide the CREATE TABLE definition of Employee you're using that has the Computed column so we can test it locally? — mythz
@mythz I have removed a few fields of child tables and updated the info in this post. — Roger Oliveira

mythz mythz · Accepted Answer · 2016-07-21T04:30:33

The [Ignore] attribute tells OrmLite you want it to ignore the property completely which is not what you want, you need to just use the [Compute] attribute to handle computed columns which I've just added a test for in this commit which is working as expected in the latest version of OrmLite, e.g:

db.DropTable<Employee>();
db.ExecuteSql(@"
CREATE TABLE [dbo].[Employee](
[EmployeeId] [int] IDENTITY(1,1) NOT NULL,
[FullName]  AS (concat(ltrim(rtrim([FirstName])),' ',ltrim(rtrim([LastName])))) PERSISTED NOT NULL,
[FirstName] [nvarchar](55) NOT NULL,
[LastName] [nvarchar](55) NULL,
[Username] [nvarchar](55) NOT NULL,
[Password] [nvarchar](55) NULL
CONSTRAINT [PK_Employee] PRIMARY KEY CLUSTERED ([EmployeeId] ASC)");

var item = new Employee
{
    FirstName = "FirstName",
    LastName = "LastName",
    Username = "Username",
    Password = "Password",
    FullName = "Should be ignored",
};

var id = db.Insert(item, selectIdentity: true);

var row = db.LoadSingleById<ComputeTest>(id);

Assert.That(row.FirstName, Is.EqualTo("FirstName"));
Assert.That(row.FullName, Is.EqualTo("FirstName LastName"));

row.LastName = "Updated LastName";
db.Update(row);

row = db.LoadSingleById<ComputeTest>(id);

Assert.That(row.FirstName, Is.EqualTo("FirstName"));
Assert.That(row.FullName, Is.EqualTo("FirstName Updated LastName"));

Which also works using the async API's in your Create() helper method, e.g:

var row = await Create(item);

Assert.That(row.FirstName, Is.EqualTo("FirstName"));
Assert.That(row.FullName, Is.EqualTo("FirstName LastName"));

row.LastName = "Updated LastName";
row = await Create(row);

Assert.That(row.FirstName, Is.EqualTo("FirstName"));
Assert.That(row.FullName, Is.EqualTo("FirstName Updated LastName"));

I'm assuming you're using an older version of OrmLite, if you upgrade to the latest version it should work.

Computed field in Servicestack ormlite error

1 Answers