Dynamic Table Mapping for LINQ-to-SQL

using System;
using System.Data.Linq;
using System.Linq;
using Microsoft.VisualStudio.TestTools.UnitTesting;
using Prototype.NamedTable.Data;

namespace Prototype.NamedTable
{
    /// <summary>
    /// Summary description for Verify
    /// </summary>
    [TestClass]
    public class Verify
    {
        /// <summary>
        /// Connection string
        /// </summary>
        public const string SQLTest = @"Data Source=.\SQLEXPRESS;Initial Catalog=test;Integrated Security=True;Pooling=False";

        // Record ID with special purpose
        private const int NewId = 1000;
        private const int DeleteId = 1001;

        [TestInitialize]
        public void BeforeTest()
        {
            Utility.Verbose = true;
            Utility.Clear();
        }

        #region Proof of concept
        [TestMethod]
        [Description("Whether the compositing be affected")]
        public void ProofOfConceptRead()
        {
            var context = new DataContext(SQLTest) { Log = Console.Out };
            var table = context.GetTable<Result2012>();
            var query = from r in table select (AResult)r; // Conversion
            var query2 = from r in query where r.Id == 108 select r; // Select from the converted
            var list = query2.ToList();

            Assert.AreEqual(1, list.Count);
        }

        [TestMethod]
        [Description("Whether a update can work")]
        public void ProofOfConceptUpdate()
        {
            var context = new DataContext(SQLTest) { Log = Console.Out };
            var table = context.GetTable<Result2012>();
            var query = from r in table select (AResult)r; // Conversion
            var query2 = from r in query where r.Id == 108 select r;
            var list = query2.ToList();

            list[0].Value = 300; // Make a change

            context.SubmitChanges(); // Commit
        }

        [TestMethod]
        [Description("Demonstrate the use of backing table of a base class")]
        public void ProofOfConceptOverridden()
        {
            var context = new DataContext(SQLTest);
            var table = context.GetTable<AResult, Result2012>();
            var query = from r in table where r.Id == 108 select r;
            var list = query.ToList();

            Assert.AreEqual(1, list.Count);
            Assert.AreEqual(108, list[0].Id);
            Assert.AreEqual("2012_8", list[0].Name);
        }

        [TestMethod]
        [Description("Demonstrate the use of backing table of a interface")]
        public void ProofOfConceptInterface()
        {
            var context = new DataContext(SQLTest);
            var table = context.GetTable<IResult, Result2011>();
            var query = from r in table where r.Id == 108 select r;
            var list = query.ToList();

            Assert.AreEqual(1, list.Count);
            Assert.AreEqual(108, list[0].Id);
            Assert.AreEqual("2012_8", list[0].Name);
        }
        #endregion

        #region CRUD
        [TestMethod]
        [Description("Read from named table, override mode")]
        public void ReadOverridden()
        {
            var context = new DataContext(SQLTest);
            var table = context.GetTable<AResult>("result2012");
            var query = from r in table where r.Id == 108 select r;
            var list = query.ToList();

            Assert.AreEqual(1, list.Count);
            Assert.AreEqual(108, list[0].Id);
            Assert.AreEqual("2012_8", list[0].Name);
        }

        [TestMethod]
        [Description("Read from named table, interface mode")]
        public void ReadInterface()
        {
            var context = new DataContext(SQLTest);
            var table = context.GetTable<IResult>("result2012");
            var query = from r in table where r.Id == 108 select r;
            var list = query.ToList();

            Assert.AreEqual(1, list.Count);
            Assert.AreEqual(108, list[0].Id);
            Assert.AreEqual("2012_8", list[0].Name);
        }

        [TestMethod]
        [Description("Create using named table, overridden mode")]
        public void CreateOverridden()
        {
            var context = new DataContext(SQLTest);
            var table = context.GetTable<AResult>("result2012");

            var before = (from r in table where r.Id == NewId select r).Count();
            Utility.Log("Before {0}", before);

            table.InsertOnSubmit(new AResult { Id = NewId, Name = "Newly added", Value = 230.4595 });
            context.SubmitChanges();

            var result = (from r in table where r.Id == NewId select r).ToList();
            Utility.Log("After insertion {0}", result.Count);

            Assert.AreEqual(1, result.Count - before);

            foreach (var record in result)
                Assert.AreEqual(NewId, record.Id);
        }

        [TestMethod]
        [Description("Create using named table, interface mode")]
        public void CreateInterface()
        {
            var context = new DataContext(SQLTest);
            var table = context.GetTable<IResult>("result2012");

            var before = (from r in table where r.Id == NewId select r).Count();
            Utility.Log("Before {0}", before);

            table.InsertOnSubmit(new ResultImp { Id = NewId, Name = "Newly added", Value = 230.4595 });
            context.SubmitChanges();

            var result = (from r in table where r.Id == NewId select r).ToList();
            Utility.Log("After insertion {0}", result.Count);

            Assert.AreEqual(1, result.Count - before);

            foreach (var record in result)
                Assert.AreEqual(NewId, record.Id);
        }

        [TestMethod]
        [Description("Creating using synchronized object")]
        public void CreateSynchornized()
        {
            var context = new DataContext(SQLTest);
            var table = context.GetTable<VResult>("result2010");

            var before = (from r in table select r).Count();
            Utility.Log("Before {0}", before);

            var record = new VResult { Name = "Newly added", Value = 230.4595 };
            table.InsertOnSubmit(record);

            context.SubmitChanges();

            // Make sure the Id is retrieved
            Assert.AreNotEqual(0, record.Id);
        }

        [TestMethod]
        [Description("Update using named table, override mode")]
        public void UpdateOverride()
        {
            var context = new DataContext(SQLTest);
            var table = context.GetTable<AResult>("result2012");

            var record = (from r in table where r.Id == 109 select r).ToList()[0];
            var before = record.Value;
            Utility.Log("Before {0}", before);

            record.Value = record.Value + 12.012;
            context.SubmitChanges();

            // Recreate the context and table to break any possible cache
            context = new DataContext(SQLTest);
            table = context.GetTable<AResult>("result2012");
            var after = (from r in table where r.Id == 109 select r).ToList()[0].Value;
            Utility.Log("After {0}", after);

            Assert.AreEqual(12.012, after - before);
        }

        [TestMethod]
        [Description("Update using named table, interface mode")]
        public void UpdateInterface()
        {
            var context = new DataContext(SQLTest);
            var table = context.GetTable<IResult>("result2012");

            var record = (from r in table where r.Id == 109 select r).ToList()[0];
            var before = record.Value;
            Utility.Log("Before {0}", before);

            record.Value = record.Value + 10.0;
            context.SubmitChanges();

            // Recreate the context and table to break any possible cache
            context = new DataContext(SQLTest);
            table = context.GetTable<IResult>("result2012");
            var after = (from r in table where r.Id == 109 select r).ToList()[0].Value;
            Utility.Log("After {0}", after);

            Assert.AreEqual(10.0, after - before);
        }

        [TestMethod]
        [Description("Update using named table, synchronized object")]
        public void UpdateSynchronized()
        {
            var context = new DataContext(SQLTest);
            var table = context.GetTable<VResult>("result2010");

            var record = (from r in table where r.Id == 203 select r).ToList()[0];
            var before = record.Value;
            Utility.Log("Before {0}", before);

            record.Value = record.Value + 10.0;
            context.SubmitChanges();

            // Recreate the context and table to break any possible cache
            context = new DataContext(SQLTest);
            table = context.GetTable<VResult>("result2010");
            var after = (from r in table where r.Id == 203 select r).ToList()[0].Value;
            Utility.Log("After {0}", after);

            Assert.AreEqual(10.0, after - before);
        }

        [TestMethod]
        [Description("Delete from named table, override mode")]
        public void DeleteOverride()
        {
            var context = new DataContext(SQLTest);
            var table = context.GetTable<AResult>("result2012");

            table.InsertOnSubmit(new AResult { Id = DeleteId });
            context.SubmitChanges();

            var before = (from r in table where r.Id == DeleteId select r).Count();
            Assert.IsTrue(before > 0);

            var records = (from r in table where r.Id == DeleteId select r).ToList();
            foreach (var record in records)
                table.DeleteOnSubmit(record);
            context.SubmitChanges();

            var after = (from r in table where r.Id == DeleteId select r).Count();
            Assert.AreEqual(0, after);
        }

        [TestMethod]
        [Description("Delete from named table, interface mode")]
        public void DeleteInterface()
        {
            var context = new DataContext(SQLTest);
            var table = context.GetTable<IResult>("result2012");

            table.InsertOnSubmit(new ResultImp { Id = DeleteId });
            context.SubmitChanges();

            var before = (from r in table where r.Id == DeleteId select r).Count();
            Assert.IsTrue(before > 0);

            var records = (from r in table where r.Id == DeleteId select r).ToList();
            foreach (var record in records)
                table.DeleteOnSubmit(record);
            context.SubmitChanges();

            var after = (from r in table where r.Id == DeleteId select r).Count();
            Assert.AreEqual(0, after);
        }
        #endregion

        #region Extended
        [TestMethod]
        [Description("Union three tables")]
        public void UnionThreeTables()
        {
            var context = new DataContext(SQLTest) { Log = System.Console.Out };

            var table2011 = context.GetTable<AResult>("result2011");
            var table2012 = context.GetTable<IResult>("result2012");
            var table2010 = context.GetTable<VResult>("result2010");

            var c1 = (from r in table2011 select r).Count();
            var c2 = (from r in table2012 select r).Count();
            var c3 = (from r in table2010 select r).Count();

            var query = (from r1 in table2011 select new { Id = r1.Id, Value = r1.Value })
                .Union(from r2 in table2012 select new { Id = r2.Id, Value = r2.Value })
                .Union(from r3 in table2010 select new { Id = r3.Id, Value = r3.Value });

            var list = query.ToList();

            Utility.Log("Found {0}", list.Count);

            // Because SQL Express union implies a distinct operation, 
            // which may result overall count less than sum of each table count
            Assert.IsTrue(c1 + c2 + c3 >= list.Count);
            Assert.IsTrue(0 < list.Count);
        }
        #endregion
    }
}