Foreing key conversion

Feb 23, 2016 at 5:54 PM
I'm evaluating if VITA framework can fit in an existing project I have to take over, the framework used in the past uses all Identity (int) columns for primary keys and saves 0 (zeroes) in foreing key columns when the reference is empty. Obviously there is no integral reference enforced on the db level.

I made some test and I can successfully read a referenced entity when the foreing key is set but when the foreing key is zero I get an entity with id zero that never loads.

Is it possible to add a value converter or a filter on these kind of columns to have a null entity in the reference property ?

thanks for any light you can share on these aspects
Feb 23, 2016 at 6:42 PM
Well, there's no out-of-the-box solution. But it is doable. Essentially you need to replace 2 Action delegates in EntityMemberInfo for these properties:

(in EntityMemberInfo)
public Func<EntityRecord, EntityMemberInfo, object> GetValueRef;
public Action<EntityRecord, EntityMemberInfo, object> SetValueRef;
These delegates are initialized during model construction, and take care of things like value conversions, null/not null checks, and ref entity loads.
Your replacement for GetValueRef should check the value in the record; if zero, return null; if non-zero, call default (the old action that was there) and it will load the referenced entity. Your SetValueRef should check the value (new value of column passed as parameter; if null, set the record value to zero and exit)

There are 2 ways to override these actions. One is to do it in code after App.Init (just override Init), run thru entities in app.Model.Entities, and in each entity in entity.Members find EntityMemberInfo that you need (its Kind should be EntityRef), and replace the GetValueRef and SetValueRef with your delegates
The other approach is to create a custom entity attribute that you put on property; as an example look at SecretAttribute, it replaces GetValueRef.
Feb 23, 2016 at 10:38 PM
 public partial class ApolloRefAttribute : EntityModelAttributeBase
    {
        private Func<EntityRecord, EntityMemberInfo, object> _defaultSetter;
        public override void Apply(AttributeContext context, Attribute attribute, EntityMemberInfo member)
        {
            if (member.Kind != MemberKind.EntityRef)
            {
                context.Log.Error("ApolloRef attribute may be used only on properties that are references to other entities. Property: {0}.{1}",
                    member.Entity.Name, member.MemberName);
                return;
            }

            _defaultSetter = member.GetValueRef;
            member.GetValueRef = this.GetApolloRef;
        }

        private object GetApolloRef(EntityRecord record, EntityMemberInfo member)
        {
            var value = record.ValuesOriginal[member.Index];
            if (value != null && value.IsNumericType() && (int) value == 0)
                return null;

            return _defaultSetter(record, member);
        }
    }
I made something like this but if I use memer.ValueIndex to get the value from the ValuesOriginal collection I always get the index 0 and so the key of my parent object.
If i replace member.ValueIndex with member.Index i get the correct value but feels wrong.

What have i missed ?
Feb 23, 2016 at 10:53 PM
Edited Feb 23, 2016 at 10:58 PM
ah, it's because references (Entity objects referenced) are stored in ValuesTransient, while FK value is in different column:
do like this:
  var fkMember = member.ReferenceInfo.FromKey.KeyMembers[0].Member;
  var fkValue = record.GetValueDirect(fkMember);
  if (fkValue == 0) .. ... .. and so on     


One question - why field is called _defaultSetter while it is in fact GETTER?

Edit: changed 'this.' to 'record.'
Feb 23, 2016 at 11:17 PM
Edited Feb 23, 2016 at 11:17 PM
It's a refactor error. I started trying to change the setter and found that it was the getter I had to change.

I will test tomorrow and let you know. Thanks.
Feb 24, 2016 at 7:22 AM
I always get a null fkValue with this code
var fkMember = member.ReferenceInfo.FromKey.KeyMembers[0].Member;
var fkValue = record.GetValueDirect(fkMember);
Feb 24, 2016 at 8:11 AM
ah, sorry, it should be ExpandedKeyMembers[0]
  var fkMember = member.ReferenceInfo.FromKey.ExpandedKeyMembers[0].Member;
  var fkValue = record.GetValueDirect(fkMember);
Feb 24, 2016 at 8:17 AM
now it works perfectly thanks
Mar 8, 2016 at 3:10 PM
small issue regarding this, i need to set 0 in the db when the reference is null but I can't accomplish it
       private void SetApolloRef(EntityRecord record, EntityMemberInfo member, object value)
        {
            if (value == null)
            {               
                record.SetValueDirect(member, 0);
            }
            else
                _defaultSetter(record, member, value);
        }
I've made my setter but this is not working, do you have any brilliant idea how to solve this ?
Mar 8, 2016 at 3:12 PM
nevermind, I've changed my setter into this and it's seems to work
private void SetApolloRef(EntityRecord record, EntityMemberInfo member, object value)
        {            
            if (value == null)
            {
                var fkMember = member.ReferenceInfo.FromKey.ExpandedKeyMembers[0].Member;
                record.SetValueDirect(fkMember, 0);
            }
            else
                _defaultSetter(record, member, value);
        }
Mar 9, 2016 at 8:01 AM
I had some issues with this FK conversion but I think I've finally cracked it. I'm a bit concerned about performance. First thing first, I had two issues after my last post
  1. lazy-loading a parent object worked but lazy-loading a parent of a parent was not working, my attribute was braking this
  2. setting the value 0 on FK with the SetValueDirect was not triggering an update on the database if it was the only modified field
Here's the code of getter and setter that are currently working for me, IIdentityEntity and ISoftDelete are two common interfaces I use for my entities
        private object GetApolloRef(EntityRecord record, EntityMemberInfo member)
        {
            //var fkMember = member.ReferenceInfo.FromKey.ExpandedKeyMembers[0].Member;
            //var fkValue = record.GetValueDirect(fkMember);
            //if (fkValue == null || (IsNumericType(fkValue) && (int)fkValue == 0))
            //    return null;

            //return _defaultGetter(record, member);

            //previous method breaks the lazy loading from the 2nd level row.PARENT.PARENT
            var ret = _defaultGetter(record, member);
            var entity = ret as IIdentityEntity;
            if (entity?.id == 0)
                return null;

            var softDeleteEntity = ret as ISoftDelete;
            if (softDeleteEntity != null && softDeleteEntity.canc != 0)
                return null;

            return ret;
        }

        private void SetApolloRef(EntityRecord record, EntityMemberInfo member, object value)
        {                       
            //if we only set the valuedirect no save are pushed to the database
            _defaultSetter(record, member, value);
            if (value == null)
            {
                var fkMember = member.ReferenceInfo.FromKey.ExpandedKeyMembers[0].Member;
                record.SetValueDirect(fkMember, 0);
            }
        }
If you have 5 minutes and can give me some feedback on this I would really appreciate it, thanks.
Luca
Mar 9, 2016 at 8:38 AM
what is parent of a parent? don't quite understand
try using SetValue instead of SetValue direct, it should mark Record as dirty, so it will be updated on next saveChanges
Mar 9, 2016 at 8:43 AM
These are 4 test classes I used to make the test. parent of a parent is traversing two levels of FK. If I only SetValue Vita tries to set a NULL value on my db field that doesn't allow nulls, I need 0 value when the FK is null.
public interface level1 : IIdentityEntity
    {
        [Size(100)]
         string descrizione { get; set; }
        
        IList<level2> CHILDS { get; }
    }

    public interface level2 : IIdentityEntity
    {
        [Size(100)]
        string descrizione { get; set; }
        [ApolloRef, EntityRef("level1_id")]
        level1 PARENT { get; set; }
        IList<level3> CHILDS { get; }
    }

    public interface level3 : IIdentityEntity
    {
        [Size(100)]
        string descrizione { get; set; }
        [ApolloRef, EntityRef("level2_id")]
        level2 PARENT { get; set; }
        IList<level4> CHILDS { get; }
    }

    public interface level4 : IIdentityEntity
    {
        [Size(100)]
        string descrizione { get; set; }
        [ApolloRef, EntityRef("level3_id")]
        level3 PARENT { get; set; }        
    }
Mar 10, 2016 at 5:42 AM
I see it now, we forgot to check if record is actually loaded. Your old code would work if you add the following at the beginning:
if (record.Status == EntityStatus.Stub)
 record.Reload();
for setter, we need to mark record as modified; so just put the following at the end of setter:

if (record.Status == EntityStatus.Loaded)
    record.Status = EntityStatus.Modified;
you can look at 'standard' getter/setter for reference properties, they are in MemberValuesGettersSetters.cs class/file, GetEntityRefValue, SetEntityRefValue methods; there's a lot going on there, you don't need all this stuff, but you can get some clues.

what kind of concerns you have with performance?
Mar 17, 2016 at 9:06 AM
Hi, sorry for the late reply I've been zerg rushed at the office.
Everything is working fine now, my performance concerns were about the casting and null checking, but with the right code there is no need of that.
Just for completeness of the solution in case anyone else have the same weird database logic I post the final code I'm using right now.

I added a New event on the entities to set the initial values of NULL references to 0 in the db. Otherwise on new entities without explicitly setting the value to NULL the framework was sending the NULL Value to the database instead of the 0 (int).
public class ApolloRefAttribute : EntityModelAttributeBase
    {
        EntityMemberInfo _member;
        EntityMemberInfo _fkMember;
        private Func<EntityRecord, EntityMemberInfo, object> _defaultGetter;
        private Action<EntityRecord, EntityMemberInfo, object> _defaultSetter;
        public override void Apply(AttributeContext context, Attribute attribute, EntityMemberInfo member)
        {
            _member = member;
            if (_member.Kind != MemberKind.EntityRef)
            {
                context.Log.Error("ApolloRef attribute may be used only on properties that are references to other entities. Property: {0}.{1}", _member.Entity.Name, _member.MemberName);
                return;
            }

            _fkMember = member.ReferenceInfo.FromKey.ExpandedKeyMembers[0].Member;

            var entity = _member.Entity;
            entity.Events.New += EntityEvent_InitNullRef;

            _defaultGetter = _member.GetValueRef;
            _defaultSetter = _member.SetValueRef;
            _member.GetValueRef = this.GetApolloRef;
            _member.SetValueRef = this.SetApolloRef;
        }

        void EntityEvent_InitNullRef(EntityRecord record, EventArgs args)
        {
            if (record.Status != EntityStatus.New) return;
            record.SetValueDirect(_fkMember, 0);
        }
        private object GetApolloRef(EntityRecord record, EntityMemberInfo member)
        {
            //fix for lazy loading
            if (record.Status == EntityStatus.Stub)
                record.Reload();

            //var fkMember = member.ReferenceInfo.FromKey.ExpandedKeyMembers[0].Member;
            var fkValue = record.GetValueDirect(_fkMember);
            if (fkValue == null || (IsNumericType(fkValue) && (int)fkValue == 0))
                return null;

            return _defaultGetter(record, member);           
        }

        private void SetApolloRef(EntityRecord record, EntityMemberInfo member, object value)
        {           
            if (value == null)
            {                
                record.SetValueDirect(_fkMember, 0);
                if (record.Status == EntityStatus.Loaded)
                    record.Status = EntityStatus.Modified;
            }
            else
            {
                _defaultSetter(record, member, value);
            }
        }

        private bool IsNumericType(object o)
        {
            switch (Type.GetTypeCode(o.GetType()))
            {
                case TypeCode.Byte:
                case TypeCode.SByte:
                case TypeCode.UInt16:
                case TypeCode.UInt32:
                case TypeCode.UInt64:
                case TypeCode.Int16:
                case TypeCode.Int32:
                case TypeCode.Int64:
                case TypeCode.Decimal:
                case TypeCode.Double:
                case TypeCode.Single:
                    return true;
                default:
                    return false;
            }
        }
    }
Mar 17, 2016 at 4:35 PM
Hi
Looks good. One note: I think In SetApolloRef you need the same check for Stub and reload record, just like in getter
Apr 13, 2016 at 5:50 PM
Hi,
I have a strange situation wherre my ApolloRef attribute is unable to set a reference, whan I call the _defaultSetter the value is not set in the entity.

I kno wI'm not giving you much information but at the moment is all I have. This is happening only for one single entity and it's almost a month I'm using this.

Any idea ?

thanks,
Luca
Apr 13, 2016 at 6:15 PM
Try setting ApplyOrder property explicitly in attribute constructor:
this.ApplyOrder = AttributeApplyOrder.Last;

I think it's the order, make sure your attribute is activated late in the processing cycle
Apr 13, 2016 at 6:26 PM
Edited Apr 13, 2016 at 6:26 PM
Sadly, still not working. Any other idea ?
Can this have anything to do with the way I call the default setter ?
If I remove my attribute the setter works as expected.
Apr 13, 2016 at 6:45 PM
ah, OK, so the _defaultSetter is not null, sorry I was confused, your problem is that after you execute it the value itself is not set.
Are you using the latest code? In this latest I modified some code around there, around SetValueDirect, might be an issue.
First, let's try to see what happens with values in record. Stop on call to _defaultSetter, check values of Record.ValuesTransient and of fk column values in Record.ValuesModified; then step over _defaultSetter and look again, see if values changed, any of them.
If not - can you step into _defaultSetter? I publish symbols (PDBs), so if you setup properly in VS, in 'Tools/Options/ NuGet Package Manager/Package Sources' a reference to 'symbolsource.org'; so try to set it up and step in, and see what's going on. You should get into MemberValueGettersSetters.SetEntityRefValue, see if it exits prematurely
Apr 13, 2016 at 7:04 PM
Record.ValuesTransiet contains the reference to value I'm expecting in my properties after the default setter is invoked but Record.ValuesModified has no changes.
The reference i'm setting is a new record.

i'm using 1.8.5 becuase with 1.8.6 i get a Vita_arraytotable error, or something like this.

if you have no more hints I'll try sysmbolsource later, thanks.
Apr 13, 2016 at 7:15 PM
About 1.8.6 - can you please give me more details of the error? Vita_ArrayToTable is db type, user defined table type I create on start up, so it's important to know what kind of trouble are there with this.
About _defaultSetter. It is new record, and you're using identities, right? I initially put 0 into identity column on New, so can it be it does update FK value, but it is simply 0? After SaveChanges I get the identity from db and set it into PK, and then update it in all entities referencing the inserted record, in their FK columns. Can it be your problem is actually SaveChanges() propagation of new identity value?
Apr 13, 2016 at 7:21 PM
or maybe even simpler - for identity, the FK value is initially 0, waiting to be generated when we actually insert the record. But your attr is treating it as NULL reference in GetValue?!
Apr 13, 2016 at 7:26 PM
meaning, you need to first check if actual value is set in GetApolloRef, before you get Fk member value and check it for zero
var obj = record.GetValueDirect(member);
if (obj != null) return obj;
Apr 13, 2016 at 11:58 PM
indeed the problem was in the getter, I had to add this code before checking fkValue for 0/null
var obj = record.GetValueDirect(member);
if (obj != null)
     return _defaultGetter(record, member);
because returning obj directly was casuing cast exception, obj is an EntityRecord and not the correct type.

this is the exception I got with 1.8.6
2016-04-13 18:16:05.357 +02:00 [Error] "ApolloNet.Condomini.Services.PreventiviAggiornaImportiRequest"
Vita.Entities.DataAccessException: Column, parameter, or variable @P0. : Cannot find data type dbo.Vita_ArrayAsTable. ---> System.Data.SqlClient.SqlException: Column, parameter, or variable @P0. : Cannot find data type dbo.Vita_ArrayAsTable.
   at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
   at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
   at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
   at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
   at System.Data.SqlClient.SqlDataReader.TryConsumeMetaData()
   at System.Data.SqlClient.SqlDataReader.get_MetaData()
   at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)
   at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task& task, Boolean asyncWrite, SqlDataReader ds, Boolean describeParameterEncryptionRequest)
   at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, TaskCompletionSource`1 completion, Int32 timeout, Task& task, Boolean asyncWrite)
   at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
   at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method)
   at System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior)
   at System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader()
   at Vita.Data.Driver.DbDriver.ExecuteCommand(IDbCommand command, DbExecutionType executionType)
   --- End of inner exception stack trace ---
   at Vita.Data.Database.ExecuteDbCommand(IDbCommand command, DataConnection connection, DbExecutionType executionType, Func`2 resultsReader)
   at Vita.Data.Database.ExecuteLinqSelect(LinqCommand command, EntitySession session, DataConnection conn)
   at Vita.Data.Database.ExecuteLinqCommand(LinqCommand command, EntitySession session)
   at Vita.Data.DataSource.ExecuteLinqCommand(EntitySession session, LinqCommand command)
   at Vita.Data.DataAccessService.ExecuteLinqCommand(LinqCommand command, EntitySession session)
   at Vita.Entities.Runtime.EntitySession.ExecuteLinqCommand(LinqCommand command)
   at Vita.Entities.Linq.EntityQueryProvider.System.Linq.IQueryProvider.Execute(Expression expression)
   at Vita.Entities.Linq.EntityQueryProvider.System.Linq.IQueryProvider.Execute[TResult](Expression expression)
   at Vita.Entities.Linq.EntityQuery`1.System.Collections.Generic.IEnumerable<TEntity>.GetEnumerator()
   at System.Collections.Generic.List`1..ctor(IEnumerable`1 collection)
   at System.Linq.Enumerable.ToList[TSource](IEnumerable`1 source)
   at ApolloNet.Condomini.Services.PreventiviServices.Any(PreventiviAggiornaImportiRequest dto) in C:\WORKING\llusetti\apollo\condomini\ApolloNet.Condomini\Services\PreventiviServices.cs:line 108
Keep in mind i'm not using vita to update the database and maybe I missed something
Apr 14, 2016 at 12:07 AM
aboutg the exception on the obj cast, this was happening only for record loaded from the db and not for the newly created objects.
Apr 14, 2016 at 5:49 AM
about code snippet - yeah, my bad, forgot that it is record that is returned, your code should work fine
about trouble with new version.
That's a problem for situations when you disable db schema updates. The new version uses one custom table type, for sending arrays of values (used in LINQ and in Includes).
My suggestion is to run the following manually:

CREATE TYPE [dbo].[Vita_ArrayAsTable] AS TABLE(
[Value] [sql_variant] NULL
)

it should be working then. I will think about handling this situation in more automated way...
Apr 18, 2016 at 5:37 PM
Hi, me again. I had no time to test the new version with the manual scripot, I will do the next days,
I have a small problem with my FK conversion, sometimes the FK value leads to a non existent record.
How can I trap this in the ApolloRef attribute and ? I came up with this code in the getter but I'm not sure if it's the right path in Vita.
Can you check it and give me your opinion? thanks.
            if (record.Status == EntityStatus.Stub)
                record.Reload();

            var obj = record.GetValueDirect(member);
            if (obj != null)
                return _defaultGetter(record, member);
            
            var fkValue = record.GetValueDirect(_fkMember);
            if (fkValue == null || (IsNumericType(fkValue) && Convert.ToInt32(fkValue) == 0))
                return null;

            var entityRecord = _defaultGetter(record, member);

            if (entityRecord == null)
                return null;

            var entityBase = entityRecord as EntityBase;
            if (entityBase != null && !entityBase.Record.IsAttached)
                return null;

            return entityRecord; 
Apr 18, 2016 at 5:42 PM
Ok I can tell you from more tests tha this code is not good :)
Apr 18, 2016 at 6:24 PM
Edited Apr 18, 2016 at 6:26 PM
Ok, I see. It is a bug inside, recently added code forgets to check for null, so it kinda assumes that the target is always there.
For you, it should be simply:

var entity = _defaultGetter(record, member);
return entity;

but now it blows up because of this bug. I don't see an easy fix for you without simply catching exception. So until next push, I suggest to put this there:

try {
return _defaultGetter(record, member);
} catch(NullReferenceException ex) {
record.SetValueDirect(member, null); //this will set value to DbNull, so no more exceptions; optional!
return null;
}

once I fix it, you can revert to simpler code without try/catch