value from database computed property is Null in codefluent entity property

Question

My MS SQL 2014 database table has a computed property column which uses a database function. Using SQL Server Management Studio, a query against the table lists the computed property values as expected.

The Codefluent model created via the import wizard shows the Entity with the computed column as a property. The underlying .cpf file defines the property with "d3p1:compute=" and the list of parameters that are used by the database function.

When an entity or the collection of entities is loaded, the properties which are used in the computed property have values, yet the computed property has a value of nothing/null.

How do I get Codefluent to read the computed value from the database table and have the value included in the entity's properties?

score 1 · Accepted Answer · answered Sep 06 '16 at 06:55

This is a bit tricky. First of all, you should declare the property like any other property. Then you must instruct the SQL producer to declare a formula on that column. You can do that with a custom 'compute' attribute in the SQL producer namespace. You can set it with the Visual Studio modeler like this:

In this example I've created an int property that is just another column value multiplied by 2.

Optionally, you can declare the property to be 'read on save' because most of the time, you want to read the computed value after a save, not only on load operations:

Once this is all done, this sample console app should display 30:

class Program
{
    static void Main(string[] args)
    {
        var c = new Customer();
        c.Name = "killroy";
        c.Age = 15;
        c.Save();
        Console.WriteLine(c.Age2); // will display 30
    }
}

Thank you for some direction! The "optionally... read-on-save..." was set to True after I imported the table into the model. I tried changing some true/false properties of the element but to no avail. The database function is rather complicated and I'm not sure how to rewrite it to fit in the "value" cell of the attribute for "compute" I'm thinking I might write an "OnGet" snippet to match the database function. Since the underlying values get saved to the database row, other apps that use the DB will have the computed value and the CF apps will have it as well. — Greg Delaney, Sep 06 '16 at 18:28

score 1 · Answer 2 · answered Sep 06 '16 at 13:27

If Simon Mouriers solution solves your problem than that is probably the best approach. However, there are 2 other options

RAW View Method

After you create a Codefluent Entities View click on the Edit Where button and it will allow you to create a RAW View

You can than specify the advanced property "UsedForMethods".

WARNING: Related entities will use the table instead of the view. This is by design and there is an article somewhere on the knowledge center on how to get around it. http://www.softfluent.com/product/codefluent-entities/knowledge-center/

Rename SQL Tables and Create a SQL View with the Same Name as the Original Table - This method is a hack, Softfluent discourages this approach, I love it because I know exactly what is happening under the scenes. I have used it with success in a scenario in which I needed soft deletes. I have automated the process with 2 stored procedures that handle the renaming. Using this approach requires running one of the stored procedures to undo the name changing prior to building the model. The other stored procedure handles the renaming after building the model. I'll post the stored procedures and how I use them within a couple of days.

As I mentioned to Simon, I'm thinking I might write an "OnGet" snippet to match the database function. Since the underlying values get saved to the database row, other apps that use the DB will have the computed value and the CF apps will have it as well — Greg Delaney, Sep 06 '16 at 19:43

value from database computed property is Null in codefluent entity property

2 Answers2