none
object references in LINQ to SQL are incorrectly saved or loaded RRS feed

  • Question

  • I tried to store and reload objects and their references between each other. The problem is when I reload the data from the database the references are not set correctly.

    Here is an example code which describes the problem. I comment the the expcted state and the real state of the output in the code.


    [Table(Name = "ClassA")]
    public class ClassA
    {
        public ClassA()
        {
            LinksToClassB = new EntitySet<ClassB>();
        }
    
        [Column(IsPrimaryKey = true, IsDbGenerated = true, AutoSync = AutoSync.OnInsert, DbType = "Int NOT NULL IDENTITY")]
        public int ID { get; set; }
    
        [Association]
        public EntitySet<ClassB> LinksToClassB { get; set; } //=> 1 to n cardinality
    
        [Association]
        public ClassB OneLinkToClassB { get; set; }//=> 1 to 1 cardinality
    }
    
    
    [Table(Name = "ClassB")]
    public class ClassB
    {
        [Column(IsPrimaryKey = true, IsDbGenerated = true, AutoSync = AutoSync.OnInsert, DbType = "Int NOT NULL IDENTITY")]
        public int ID { get; set; }
    
        [Column(CanBeNull = true)]
        public string Name { get; set; }
    }
    
    public class DatabaseContext : DataContext
    {
        public Table<ClassA> ClassATable;
        public Table<ClassB> ClassBTable;
    
        public DatabaseContext(string connection) : base(connection) { }
    }
    
    
    
    [TestClass]
    public class Test
    {
        string path = @"F:\Temp\Testspace - Forum Database\database.mdf";//path to database
    
        [TestMethod]
        public void TestMethod()
        {
            //creates Database
            DatabaseContext context = new DatabaseContext(path);
    
            if (context.DatabaseExists())//Delete if exists
            {
                context.DeleteDatabase();
            }
    
            context.CreateDatabase();
    
            ClassB b1 = new ClassB(); b1.Name = "name 1";
            ClassB b2 = new ClassB(); b2.Name = "name 2";
            ClassB b3 = new ClassB(); b3.Name = "name 3";
    
            ClassA a = new ClassA();
    
            //now the references will be added to the object a
    
            //in 1-n references
            a.LinksToClassB.Add(b1);
            a.LinksToClassB.Add(b2);
            a.LinksToClassB.Add(b3);
    
            //and the has-one reference (OneLinkToClassB)
            a.OneLinkToClassB = b1;
    
    
            context.ClassATable.InsertOnSubmit(a);
    
            context.SubmitChanges(); //store in database
    
    
            //now the database will be reloaded
            context = new DatabaseContext(path);
    
    
            //Check if all ClassB objects were correctly stored and reloaded
            foreach (ClassB x in context.ClassBTable)
            {
                Console.WriteLine(x.ID + "; " + x.Name);
                /*    
                    -> expected output:
                        1; name 1
                        2; name 2
                        3; name 3
                    -> real output
                        1; name 1
                        2; name 2
                        3; name 3
    
                    -> check!
                */
            }
    
            //check if all ClassA objects were correctly stored and reloaded
            foreach (ClassA x in context.ClassATable)//context.ClassATable has only one entry
            {
                Console.WriteLine("check of entitys set");
    
                //check of 1-n references
                foreach (ClassB b in x.LinksToClassB)
                {
                    Console.WriteLine(x.ID + " has a link to " + b.ID + ", " + b.Name);
                    /*
                        -> expected output:
                            1 has a link to 1, name 1
                            1 has a link to 2, name 2
                            1 has a link to 3, name 3
    
                        -> real output
                            1 has a link to 1, name 1
    
                        -> doesn't match...
                    */
                }
    
    
                Console.WriteLine("check of single link");
    
                //check of 1-1 reference
                Console.WriteLine(x.ID + " has a link to " + x.OneLinkToClassB.ID + ", " + x.OneLinkToClassB.Name);
                /*
                        -> expected output:
                            1 has a link to 1, name 1
    
                        -> real output
                            this line throws an NullReferenceException
                */
            }
        }
    }
    I hope anyone can give me a hint how to solve this bug in my code :)



    Wednesday, March 9, 2016 2:13 AM

Answers

  • The associations weren't set correctly. Here is a changed code:

    [Table(Name = "ClassA")]
        public class ClassA
        {
            public ClassA()
            {
                LinksToClassB = new EntitySet<ClassB>();
            }
    
            [Column(IsPrimaryKey = true, IsDbGenerated = true)]
            private int ID;
    
            [Association(ThisKey = "ID", OtherKey = "ClassAId")]
            private EntitySet<ClassB> LinksToClassB;//=> 1 to n cardinality
            
            public void addLink(ClassB x)
            {
                LinksToClassB.Add(x);
                x.setLink(this);
            }
    
            public HashSet<ClassB> getLinks()
            {
                HashSet<ClassB> collection = new HashSet<ClassB>();
                foreach(ClassB x in LinksToClassB)
                {
                    collection.Add(x);
                }
                return collection;
            }
        }
    
    
        [Table(Name = "ClassB")]
        public class ClassB
        {
            private EntityRef<ClassA> _classA = default(EntityRef<ClassA>);
    
            [Column(IsPrimaryKey = true, IsDbGenerated = true, AutoSync = AutoSync.OnInsert, DbType = "Int NOT NULL IDENTITY")]
            public int ID { get; set; }
    
            [Column(CanBeNull = true)]
            public string Name { get; set; }
    
            [Column(CanBeNull = false)]
            private int ClassAId { get; set; }
    
            [Association(ThisKey = "ClassAId", OtherKey = "ID", IsForeignKey = true)]
            private ClassA ClassA
            {
                get
                {
                    return _classA.Entity;
                }
                set
                {
                    _classA.Entity = value;
                }
            }
    
            private ClassA getLink()
            {
                return _classA.Entity;
            }
    
            public void setLink(ClassA x)
            {
                _classA.Entity = x;
            }
        }
    
        public class DatabaseContext : DataContext
        {
            public Table<ClassA> ClassATable;
            public Table<ClassB> ClassBTable;
    
            public DatabaseContext(string connection) : base(connection) { }
        }
    
    
    
        [TestClass]
        public class Test
        {
            string path = @"F:\Temp\Testspace - Forum Database\database.mdf";//path to database
    
            [TestMethod]
            public void TestMethod()
            {
                //creates Database
                DatabaseContext context = new DatabaseContext(path);
    
                if (context.DatabaseExists())//Delete if exists
                {
                    context.DeleteDatabase();
                }
    
                context.CreateDatabase();
    
                ClassB b1 = new ClassB(); b1.Name = "name 1";
                ClassB b2 = new ClassB(); b2.Name = "name 2";
                ClassB b3 = new ClassB(); b3.Name = "name 3";
    
                ClassA a = new ClassA();
    
                //now the references will be added to the object a
    
                //in 1-n references
                a.addLink(b1);
                a.addLink(b2);
                a.addLink(b3);
    
                context.ClassATable.InsertOnSubmit(a);
    
                context.SubmitChanges(); //store in database
    
    
                //now the database will be reloaded
                context = new DatabaseContext(path);
    
    
                //Check if all ClassB objects were correctly stored and reloaded
    
    
                foreach (ClassB x in context.ClassBTable)
                {
                    Console.WriteLine(x.ID + "; " + x.Name);
                    /*    
                        -> expected output:
                            1; name 1
                            2; name 2
                            3; name 3
                        -> real output
                            1; name 1
                            2; name 2
                            3; name 3
    
                        -> check!
                    */
                }
    
                //check if all ClassA objects were correctly stored and reloaded
                foreach (ClassA x in context.ClassATable)//context.ClassATable has only one entry
                {
                    Console.WriteLine("check of entitys set");
    
                    //check of 1-n references
                    foreach (ClassB b in x.getLinks())
                    {
                        Console.WriteLine(b.ID + " has a link to " + b.ID + ", " + b.Name);
                        /*
                            -> expected output:
                                1 has a link to 1, name 1
                                1 has a link to 2, name 2
                                1 has a link to 3, name 3
    
                            -> real output
                                1 has a link to 1, name 1
                                1 has a link to 2, name 2
                                1 has a link to 3, name 3
    
                            -> check!
                        */
                    }
                }
            }
        }

    • Marked as answer by FloFire112 Wednesday, March 9, 2016 11:05 PM
    Wednesday, March 9, 2016 11:04 PM