This project is read-only.
2
Vote

Wrong reference attributes

description

Hi, I just want to guide to some errors I found.
My english isn't very good, so sorry for simple explaination
Here is example of case scenario:
namespace TestOpenNetCFReferences
{
    using System;
    using System.Collections.Generic;
    using System.Linq;
    using System.Text;
    using OpenNETCF.ORM;
    using System.Reflection;
    using System.Diagnostics;


    [Entity(NameInStore = "Users", KeyScheme = KeyScheme.Identity)]
    public class User
    {
        [Field(IsPrimaryKey = true)]
        public int IdUser { get; set; }

        [Field(RequireUniqueValue = true)]
        public string Username { get; set; }

        [Field]
        public string Fullname { get; set; }

        [Reference(typeof(Asset), "UserId", LocalReferenceField = "IdUser", Autofill = true, ReferenceType = ReferenceType.OneToMany)]
        public Asset[] Assets { get; set; }

    }

    [Entity(NameInStore = "Assets", KeyScheme = KeyScheme.Identity)]
    public class Asset
    {
        [Field(IsPrimaryKey = true)]
        public int IdAsset { get; set; }

        [Field]
        public string Name { get; set; }

        [Field]
        public int UserId { get; set; }

        [Reference(typeof(User), "IdUser", LocalReferenceField = "UserId", Autofill = true, ReferenceType = ReferenceType.ManyToOne)]
        public User Owner { get; set; }
    }

    

    public class TestOpenNetCFReferences
    {

        public static void Test01()
        {
            try
            {
                // Load the store.
                SqlCeDataStore Store = new SqlCeDataStore("TestReferences.sdf");
                //SQLiteDataStore Store = new SQLiteDataStore("TestReferences.sqlite");

                if (!Store.StoreExists)
                {
                    Store.CreateStore();
                    Store.AddType<Asset>();
                    Store.AddType<User>();
                }
                else
                {
                    Store.DiscoverTypes(Assembly.GetExecutingAssembly());
                }
                Store.TruncateTable("Users");
                Store.TruncateTable("Assets");


                User user = new User { Username = "User1", Fullname = "User 1" };
                Asset asset1 = new Asset { Name = "Item1", Owner = user };
                Asset asset2 = new Asset { Name = "Item2", Owner = user };

                Store.Insert(asset1, true);
                Store.Insert(asset2, true);

                user = new User { Username = "User2", Fullname = "User 2" };
                asset1 = new Asset { Name = "Item3", Owner = user };
                asset2 = new Asset { Name = "Item4", Owner = user };

                Store.Insert(asset1, true);
                Store.Insert(asset2, true);

                Debug.WriteLine("");

                var assets = Store.Select<Asset>(true);
                foreach (var a in assets)
                {
                    if (a.Owner == null)
                        Debug.WriteLine("Asset doesn't have owner: ID=" + a.IdAsset.ToString() + " Name=" + a.Name + " UserId=" + a.UserId.ToString());
                }

                var users = Store.Select<User>(true);
                foreach (var u in users)
                {
                    if (u.Assets == null || u.Assets.Length == 0)
                        Debug.WriteLine("User doesn't have assets: ID=" + u.IdUser.ToString() + " Name=" + u.Fullname);
                }
            }
            catch (Exception ex)
            {
                Debug.WriteLine(ex);
            }
        }

        public static void Test02()
        {
            try
            {
                // Load the store.
                SqlCeDataStore Store = new SqlCeDataStore("TestReferences.sdf");
                //SQLiteDataStore Store = new SQLiteDataStore("TestReferences.sqlite");

                if (!Store.StoreExists)
                {
                    Store.CreateStore();
                    Store.AddType<Asset>();
                    Store.AddType<User>();
                }
                else
                {
                    Store.DiscoverTypes(Assembly.GetExecutingAssembly());
                }
                Store.TruncateTable("Users");
                Store.TruncateTable("Assets");

                Asset asset1 = new Asset { Name = "Item1" };
                Asset asset2 = new Asset { Name = "Item2" };
                List<Asset> lasset = new List<Asset>();
                lasset.Add(asset1);
                lasset.Add(asset2);
                User user = new User { Username = "User1", Fullname = "User 1", Assets = lasset.ToArray() };

                Store.Insert(user, true);                

                lasset.Clear();
                asset1 = new Asset { Name = "Item3" };
                asset2 = new Asset { Name = "Item4" };
                lasset.Add(asset1);
                lasset.Add(asset2);
                user = new User { Username = "User2", Fullname = "User 2", Assets = lasset.ToArray() };

                //scenario when one child is already inserted (!isNew)
                Store.Insert(asset1, true);
                Store.Insert(user, true);

                Debug.WriteLine("");

                var assets = Store.Select<Asset>(true);
                foreach (var a in assets)
                {
                    if (a.Owner == null)
                        Debug.WriteLine("Asset doesn't have owner: ID=" + a.IdAsset.ToString() + " Name=" + a.Name + " UserId=" + a.UserId.ToString());
                }

                var users = Store.Select<User>(true);
                foreach (var u in users)
                {
                    if (u.Assets == null || u.Assets.Length == 0)
                        Debug.WriteLine("User doesn't have assets: ID=" + u.IdUser.ToString() + " Name=" + u.Fullname);
                }
            }
            catch (Exception ex)
            {
                Debug.WriteLine(ex);
            }
        }
    }
}
In "Test01" we insert children and their's parent is inserted as reference automatically.
In "Test02" we insert parent and its childern collection is inserted as reference automatically.
Both of this test gave wrong outcome. After debuging I narrowed few errors:

File SQLStoreBase.cs :
from line 978 (method DoInsertReferences, ReferenceType.ManyToOne)
if (existing == null)
                    {
                        Insert(referenceEntity);

                        // we then copy the PK of the reference item into the "local" FK field - need to re-query the key
                        refPK = Entities[referenceEntityName].Fields.KeyField.PropertyInfo.GetValue(referenceEntity, null);

                        // set the item key
                        // we already inserted, so we have to do an update
                        // TODO: in the future, we should move this up and do reference inserts first, then back=propagate references
                        Entities[entityName].Fields[reference.ForeignReferenceField].PropertyInfo.SetValue(item, refPK, null);                        
                    }
                    else
                    {
                        // TODO: should we look for reference entity updates?  That's complex and probably out of scope for the purposes of ORM
                    }
...changed to:
if (existing == null)
                    {
                        Insert(referenceEntity);
                    }                    
                    // we then copy the PK of the reference item into the "local" FK field - need to re-query the key
                    refPK = Entities[referenceEntityName].Fields.KeyField.PropertyInfo.GetValue(referenceEntity, null);
                    // set the item key
                    // we already inserted, so we have to do an update
                    // TODO: in the future, we should move this up and do reference inserts first, then back=propagate references
                    //THIS LINE IS CHANGED TOO
                    //Entities[entityName].Fields[reference.ForeignReferenceField].PropertyInfo.SetValue(item, refPK, null);
                    Entities[entityName].Fields[reference.LocalReferenceField].PropertyInfo.SetValue(item, refPK, null);
Attribute ForeignReferenceField has to be LocalReferenceField here(debug my examle, you'll figure it out why :)). Also for second insert from my examle in Test01 user1 will exist so you won't update reference UserId on item2(it will stay 0 instead of IdUser value).


Same method, but other reference type (method DoInsertReferences, ReferenceType.OneToMany)
var fk = Entities[entityName].Fields[reference.ForeignReferenceField].PropertyInfo.GetValue(item, null);
has to be
var fk = Entities[entityName].Fields[reference.LocalReferenceField].PropertyInfo.GetValue(item, null);
and also this:
                        if (isNew)
                        {
                            Entities[et].Fields[reference.ForeignReferenceField].PropertyInfo.SetValue(element, fk, null);
                            Insert(element);
                        }
has to be:
Entities[et].Fields[reference.ForeignReferenceField].PropertyInfo.SetValue(element, fk, null);
                        if (isNew)
                        {                            
                            Insert(element);
                        }
                        else
                        {
                            Update(element);
                        }
These changes are for scenario in "Test02". First change just debug it and you will figure it out why. Second change is when asset1 is already inserted without its parent, and after that parent is inserted and his children are inserted as references, child's parent reference needs to be updated(!isNew).

I'll continue in next post.

comments

walker666 wrote Mar 7, 2014 at 10:05 AM

Also, Select of references needs to be changed:
method FillReferences(object instance, object keyValue, ReferenceAttribute[] fieldsToFill, bool cacheReferenceTable), ReferenceType.ManyToOne:
keyValue = m_entities[entityName].Fields[reference.ForeignReferenceField].PropertyInfo.GetValue(instance, null);
changed to:
keyValue = m_entities[entityName].Fields[reference.LocalReferenceField].PropertyInfo.GetValue(instance, null);
Just debug it and you'll figure it out why :).
Reason why primary keys are named like that (not simple "Id"-s) is so you can figure out which Id is referenced in debuging.

I haven't debugged cascade delete yet, but probably there will have to be made some minor changes.

Thanks for very helpfull library!

phobeous wrote May 15, 2015 at 10:25 AM

I was close to give up and change the ORM for my project when I decided to debug the library and found the exact same issue and solution than walker666.

Maybe the library should check fields when it register the types testing LocalReferenceField exists in the type.

I've also found 2 more issues:
  • In SqlServerDataStore.cs, method GetInsertCommands(string entityName) it's necessary to check if a field is row version.
  • In SqlServerDataStore.cs, method VerifyIndex fails as information_schema.indexes view doesn't exist. To fix this i've used a little variation in the query used in method UpdateIndexCacheForType in the same file and also changed the cast (long)command.ExecuteScalar() to Convert.ToInt64(command.ExecuteScalar())
Now I can use SearchOrder in my entities' fields without any exception.

wrote May 15, 2015 at 2:05 PM

ctacke wrote May 15, 2015 at 2:06 PM

If either of you can do a pull request, or even send me fixed code, I'm happy to incorporate the changes.

phobeous wrote May 15, 2015 at 3:25 PM

Of course Chris. Congrats for your libraries. I'm mainly an user of SDF as I'm Motorola ISV Partner, but I wanted to test your ORM and a personal project using SQLServer 2008 has been a good chance. May I send you the code directly or may I post here?

ctacke wrote May 15, 2015 at 3:53 PM

Whatever is easiest for you. If you go to "source code" there's an "Upload Patch" link that is easy, or feel free to email it directly to me.

phobeous wrote May 25, 2015 at 4:30 PM

Hi again. I think I've found another bug in SQLStoreBase.cs. In line 1275, when a ManyToOne reference is processed before a OneToMany one, the original keyValue value is lost, so no reference is filled anymore. To fix this we must reset the keyValue in each iteration of foreach loop, adding (for example) the following line just before if statement in line 1275:

keyValue = m_entities[entityName].Fields.KeyField.PropertyInfo.GetValue(instance, null);