Developers Club geek daily blog

2 years, 10 months ago
LinqTestable is the library helping to overcome with tests the conceptual gap between OOP and a relational DB arising because of a difference of behavior NULL-and in these two paradigms. For example, comparison of NULL == returns to NULL truth in object languages, and lie in a relational model. In addition, NULL.SomeField will return NULL in a relational model and will throw out NullReferenceException in C#. LinqTestable is intended for a solution of this problem.

There was a new LinqTestable version — libraries for testing of requests to a DB through ORM


At once I will tell that the library is not up to the end ready, but in the principle it is already possible to use. At the moment I work on correct processing of OrderBy.

For demonstration of the opportunities of library ready at the moment, I will give examples of a unit tests at which it is also possible to look in source codes of library.

Elimination of NullRefenceException



The following example shows elimination of a problem of ejection of NullRefenceException at the time of the appeal to null.DOOR_ID; instead null correctly returns.
Test code
    [TestFixture]
    public class TwoLeftJoins
    {
        void ExecuteTwoLeftJoins(bool isSmart)
        {
            var dataModel = new TestDataModel {Settings = {IsSmart = isSmart}};

            const int carId = 100;
            dataModel.CAR.AddObject(new CAR{CAR_ID = carId});
            dataModel.CAR.AddObject(new CAR{CAR_ID = carId + 1});

            var cars =
                (from car in dataModel.CAR
                join door in dataModel.DOOR on car.CAR_ID equals door.CAR_ID 
                    into joinedDoor from door in joinedDoor.DefaultIfEmpty()
                join doorHandle in dataModel.DOOR_HANDLE on door.DOOR_ID equals doorHandle.DOOR_ID 
                    into joinedDoorHandle from doorHandle in joinedDoorHandle.DefaultIfEmpty()
                select car).ToList();

            Assert.AreEqual(2, cars.Count);
            Assert.AreEqual(carId, cars.First().CAR_ID);
        }

        [Test]
        public void TwoLeftJoinsShouldThrow()
        {
            Assert.Throws<NullReferenceException>(() => ExecuteTwoLeftJoins(false));
        }

        [Test]
        public void SmartTwoLeftJoinsShouldNotThrow()
        {
            ExecuteTwoLeftJoins(true);
        }
    }



For prevention of NullReferenceException it would be possible to add manually to a code check on null, but in that case you risk to receive other SQL and the plan of execution of request differing from initial. I wrote about it in the last article devoted to this library.

The case similar on previous, however NullReferenceException is prevented this time at the time of the appeal to Value at Nullable:
Test code
    [TestFixture]
    public class Contains
    {
        public void ExecuteContains(bool isSmart)
        {
            var dataModel = new TestDataModel { Settings = { IsSmart = isSmart } };

            new[]
            {
                new DOOR_HANDLE {DOOR_HANDLE_ID = 1, MATERIAL_ID = 1},
                new DOOR_HANDLE {DOOR_HANDLE_ID = 2, MATERIAL_ID = 2},
                new DOOR_HANDLE {DOOR_HANDLE_ID = 3, MATERIAL_ID = null}
            }
                .ForEach(dataModel.DOOR_HANDLE.AddObject);
           
            var doorHandleIds = new List<int>{1,2};

            var doorHandles =
                (from doorHandle in dataModel.DOOR_HANDLE
                where doorHandleIds.Contains(doorHandle.MATERIAL_ID.Value)
                select doorHandle).ToList();

            Assert.AreEqual(2, doorHandles.Count);
        }

        [Test]
        public void ContainsShouldFail()
        {
            Assert.Throws<InvalidOperationException>(() => ExecuteContains(false));
        }

        [Test]
        public void SmartContainsShouldSuccess()
        {
            ExecuteContains(true);
        }
    }



Correct behavior of Sum



The Sum method behaves differently in the database and in C# over the list from data. In case of the sum from empty selection, the database returns NULL, and in С# 0 returns. If the field to which the result of the sum is transferred was not Nullable<>, then ORM throws out an exception. When using LinqTestable, Sum either will return NULL, or will throw out an exception just as if you used the database:
Test code
    [TestFixture]
    public class SumFromEmptyTable
    {
        void ExecuteSumFromEmptyTable(bool isSmart)
        {
            var dataModel = new TestDataModel {Settings = {IsSmart = isSmart}};
            int sum = dataModel.CAR.Sum(x => x.CAR_ID);
        }

        [Test]
        public void SmartSumShouldThrow()
        {
            Assert.Throws<InvalidOperationException>(() => ExecuteSumFromEmptyTable(true));
        }

        [Test]
        public void SumShouldNotThrow()
        {
            ExecuteSumFromEmptyTable(false);
        }

        void ExecuteNullableSumFromEmptyTable(bool isSmart)
        {
            var dataModel = new TestDataModel { Settings = { IsSmart = isSmart } };
            int? sum = dataModel.DOOR_HANDLE.Sum(x => x.MATERIAL_ID);
            Assert.AreEqual(null, sum);
        }

        [Test]
        public void NullableSumShouldFail()
        {
            Assert.Throws<AssertionException>(() => ExecuteNullableSumFromEmptyTable(false));
        }

        [Test]
        public void NullableSmartSumShouldSuccess()
        {
            ExecuteNullableSumFromEmptyTable(true);
        }
    }



Comparison of null == null



When using LinqTestable, null == null will mean false if only you obviously in request do not do comparison of what that with null just as if you made database request:
Test code
    [TestFixture]
    public class NullComparison
    {
        void ExecuteNullComparison(bool isSmart)
        {
            var dataModel = new TestDataModel { Settings = { IsSmart = isSmart } };

            new[]
            {
                new DOOR_HANDLE {DOOR_HANDLE_ID = 1, MATERIAL_ID = 1, MANUFACTURER_ID = 1}, // <----
                new DOOR_HANDLE {DOOR_HANDLE_ID = 2, MATERIAL_ID = 2, MANUFACTURER_ID = 2}, //      |-- this is only pair
                new DOOR_HANDLE {DOOR_HANDLE_ID = 3, MATERIAL_ID = 1, MANUFACTURER_ID = 1}, // <----
                new DOOR_HANDLE {DOOR_HANDLE_ID = 4, MATERIAL_ID = 5, MANUFACTURER_ID = null},
                new DOOR_HANDLE {DOOR_HANDLE_ID = 5, MATERIAL_ID = 5, MANUFACTURER_ID = null},
                new DOOR_HANDLE {DOOR_HANDLE_ID = 6, MATERIAL_ID = null, MANUFACTURER_ID = null},
                new DOOR_HANDLE {DOOR_HANDLE_ID = 7, MATERIAL_ID = null, MANUFACTURER_ID = null}
            }
            .ForEach(x => dataModel.DOOR_HANDLE.AddObject(x));

            var handlePairsWithSameMaterialAndManufacturer =
               (from handle in dataModel.DOOR_HANDLE
                join anotherHandle in dataModel.DOOR_HANDLE on handle.MATERIAL_ID equals anotherHandle.MATERIAL_ID
                where handle.MANUFACTURER_ID == anotherHandle.MANUFACTURER_ID &&handle.DOOR_HANDLE_ID < anotherHandle.DOOR_HANDLE_ID
                select new {handle, anotherHandle}).ToList();

            Assert.AreEqual(1, handlePairsWithSameMaterialAndManufacturer.Count);
            var pair = handlePairsWithSameMaterialAndManufacturer.First();
            Assert.AreEqual(1, pair.handle.MATERIAL_ID);
            Assert.AreEqual(pair.handle.MATERIAL_ID, pair.anotherHandle.MATERIAL_ID);
            Assert.AreEqual(1, pair.handle.MANUFACTURER_ID);
            Assert.AreEqual(pair.handle.MANUFACTURER_ID, pair.anotherHandle.MANUFACTURER_ID);
        }

        [Test]
        public void NullComparisonShouldFail()
        {
            Assert.Throws<AssertionException>(() => ExecuteNullComparison(false));
        }

        [Test]
        public void SmartNullComparisonShouldSuccess()
        {
            ExecuteNullComparison(true);
        }
    }



How to begin to use



The library treats free software and is delivered "as is" (as is, no warranty). It is possible to download through Nuget.

There was a new LinqTestable version — libraries for testing of requests to a DB through ORM

After connection of library, replace in your test ObjectSet implementation of Expression and Provider properties on:

        public System.Linq.Expressions.Expression Expression
        {
            get { return _collection.AsQueryable<T>().ToTestable().Expression; }
        }

        public IQueryProvider Provider
        {
            get { return _collection.AsQueryable<T>().ToTestable().Provider; }
        }


It is possible to add the switch for inclusion \shutdown of LinqTestable
        public Expression Expression
        {
            get
            {
                return 
                    _settings.IsSmart ?
                    _collection.AsQueryable().ToTestable().Expression : 
                    _collection.AsQueryable().Expression;
            }
        }

        public IQueryProvider Provider
        {
            get { 
                    return
                        _settings.IsSmart ?
                        _collection.AsQueryable().ToTestable().Provider : 
                        _collection.AsQueryable().Provider;  
            }
        }



You can look at an example as the test database is implemented and LinqTestable in tests of the library is connected to it. There are source codes.

Just in case, here article about how to implement the test database on the example of Entity Framework.

At the moment the library is not able to work with sorting (OrderBy), there are several other small defects which are going to be corrected in the near future. Also I am going to porefachit a code a little.

If you find any bugs or cases when the behavior in a DB and in C# differs which are not processed by library — will be grateful if you send the problem unit-test for the LinqTestable@mail.ru mail

This article is a translation of the original post at habrahabr.ru/post/273801/
If you have any questions regarding the material covered in the article above, please, contact the original author of the post.
If you have any complaints about this article or you want this article to be deleted, please, drop an email here: sysmagazine.com@gmail.com.

We believe that the knowledge, which is available at the most popular Russian IT blog habrahabr.ru, should be accessed by everyone, even though it is poorly translated.
Shared knowledge makes the world better.
Best wishes.

comments powered by Disqus