search

markrendle / Simple.Data

A light-weight, dynamic data access component for C# 4.0
MIT License
1.33k stars 303 forks source link

SimpleRecord does not Support Select and get error if retrieving non existant record which involves a join. #265

Closed JohnEffo closed 11 years ago

JohnEffo commented 11 years ago

Hi Sorry for placing two errors into one ticket but I had already written the code.

namespace Tests.SuperDuper
{
    using System;
    using FluentAssertions;
    using NUnit.Framework;
    using Ploeh.AutoFixture;
    using Simple.Data;

    public class CurrentUser
    {
        public int CompanyId { get; set; }
        public string CompanyName { get; set; }
        public int LocationId { get; set; }
        public string LocationName { get; set; }
        public string CustomerName { get; set; }
        public int CustomerId { get; set; }

    }

    [TestFixture]
    public class SimpleData
    {

        private Fixture AutoFixture;
        private dynamic db;

        [SetUp]
        public void setup()
        {
            AutoFixture = new Fixture();
             db = PerformDbSetup();
        }

        private static dynamic PerformDbSetup()
        {
            var adapter = new InMemoryAdapter();
            Database.UseMockAdapter(adapter);
            var db = Database.Open();
            adapter.SetKeyColumn("Customer", "Id");
            adapter.Join.Master("Location", "Id").Detail("Customer", "LocationFk");
            adapter.Join.Master("Company", "Id").Detail("Customer", "CompanyFk");
            return db;
        }

        private static void CreateUser(dynamic db, CurrentUser user)
        {
            InsertUser(db, user);
            InsertLocation(db, user);
            InsertCompany(db, user);
        }

        private static void InsertCompany(dynamic db, CurrentUser user)
        {
            db.Company.Insert(new { Id = user.CompanyId, Name = user.CompanyName });
        }

        private static void InsertLocation(dynamic db, CurrentUser user)
        {
            db.Location.Insert(new
            {
                Id = user.LocationId,
                Name = user.LocationName
            });
        }

        private static void InsertUser(dynamic db, CurrentUser user)
        {
            db.Customer.Insert(new
            {
                Id = user.CustomerId,
                CompanyFk = user.CompanyId,
                LocationFk = user.LocationId,
                Name = user.CustomerName
            });
        }

        [Test]
        ///Sanity check passes
        public void can_reteive_user_successfully()
        {
            //arrange
            var db = PerformDbSetup();

            var user = AutoFixture.Create<CurrentUser>();

            CreateUser(db, user);

            //act
            var result = ReteiveUser(user.CustomerId);

            //assert
            result.CompanyId.Should().Be(user.CompanyId);
            result.CustomerId.Should().Be(user.CustomerId);
            result.LocationId.Should().Be(user.LocationId);
            result.CompanyName.Should().Be(user.CompanyName);
            result.CustomerName.Should().Be(user.CustomerName);
            result.LocationName.Should().Be(user.LocationName);
        }

        [Test]
        ///Sanity check passes
        public void can_get_value_by_id()
        {
            //arrange
            var db = PerformDbSetup();

            var user = AutoFixture.Create<CurrentUser>();

            CreateUser(db, user);

            //act
            var result = db.Customer.Get(user.CustomerId);

            //assert
            Assert.That(result.Name,Is.EqualTo(user.CustomerName));

        }

        [Test]
        /// From: http://simplefx.org/simpledata/docs/pages/Retrieve/ColumnSelection.html
        /// By default, the All, Get and Find* commands retrieve every column from each table specified in their invocation. 
        /// Use the Select method to set explicitly the data fields returned by those commands.
        public void can_project_column_of_single_value_when_using_get_to_retrieve_data()
        {
            //arrange
            var db = PerformDbSetup();

            var user = AutoFixture.Create<CurrentUser>();

            CreateUser(db, user);

            //act
            Action callToGetProjectingColumns = ()=> db.Customer.Get(user.CustomerId)
                           .Select(db.Customer.Id.As("CustomerId"),
                                   db.Customer.Name.As("CustomerName"));

            //assert
            callToGetProjectingColumns.ShouldNotThrow("documentation implies Select works on SimpleRecord");
        }

        [Test]
        /// From: http://simplefx.org/simpledata/docs/pages/Retrieve/ColumnSelection.html
        /// By default, the All, Get and Find* commands retrieve every column from each table specified in their invocation. 
        /// Use the Select method to set explicitly the data fields returned by those commands.
        public void can_project_column_of_single_value_when_using_Find_to_retrieve_data()
        {
            //arrange
            var db = PerformDbSetup();

            var user = AutoFixture.Create<CurrentUser>();

            CreateUser(db, user);

            //act
            Action callToGetProjectingColumns = () => db.Customer.FindById(user.CustomerId)
                           .Select(db.Customer.Id.As("CustomerId"),
                                   db.Customer.Name.As("CustomerName"));

            //assert
            callToGetProjectingColumns.ShouldNotThrow("documentation implies Select works on SimpleRecord");
        }

        [Test]
        ///Is this expected behavior
        public void do_not_blow_up_if_ask_for_data_which_is_not_present_when_Joining()
        {
            //arrange
            //arrange
            var db = PerformDbSetup();

            var user = AutoFixture.Create<CurrentUser>();

            CreateUser(db, user);

            //act
            Action result = ()=> ReteiveUser(user.CustomerId + 1);

            //assert
            result.ShouldNotThrow("attempting to retreive non existant data should not throw exception");

        }

       public CurrentUser ReteiveUser(int userId)
         {
             var db = Database.Open();
                var item = db.Customer.FindAllById(userId)
                        .Select(
                            db.Customer.Id.As("CustomerId"),
                            db.Customer.Name.As("CustomerName"),
                            db.Customer.Location.Id.As("LocationId"),
                            db.Customer.Location.Name.As("LocationName"),
                            db.Customer.Company.Id.As("CompanyId"),
                            db.Customer.Company.Name.As("CompanyName")
               ).Join(db.Location).On(db.Customer.LocationFk == db.Location.Id)
               .Join(db.Company).On(db.Customer.CompanyFk == db.Company.Id)
               .First();
                return item;
        }

    }
}
markrendle commented 11 years ago

Get and FindBy return single records, so Select is not possible. Recommended method is FindAllBy with Select and FirstOrDefault. On 17 Mar 2013 12:51, "JohnEffo" notifications@github.com wrote:

Hi Sorry for placing two errors into one ticket but I had already written the code.

namespace Tests.SuperDuper { using System; using FluentAssertions; using NUnit.Framework; using Ploeh.AutoFixture; using Simple.Data;

public class CurrentUser
{
    public int CompanyId { get; set; }
    public string CompanyName { get; set; }
    public int LocationId { get; set; }
    public string LocationName { get; set; }
    public string CustomerName { get; set; }
    public int CustomerId { get; set; }

}

[TestFixture]
public class SimpleData
{

    private Fixture AutoFixture;
    private dynamic db;

    [SetUp]
    public void setup()
    {
        AutoFixture = new Fixture();
         db = PerformDbSetup();
    }

    private static dynamic PerformDbSetup()
    {
        var adapter = new InMemoryAdapter();
        Database.UseMockAdapter(adapter);
        var db = Database.Open();
        adapter.SetKeyColumn("Customer", "Id");
        adapter.Join.Master("Location", "Id").Detail("Customer", "LocationFk");
        adapter.Join.Master("Company", "Id").Detail("Customer", "CompanyFk");
        return db;
    }

    private static void CreateUser(dynamic db, CurrentUser user)
    {
        InsertUser(db, user);
        InsertLocation(db, user);
        InsertCompany(db, user);
    }

    private static void InsertCompany(dynamic db, CurrentUser user)
    {
        db.Company.Insert(new { Id = user.CompanyId, Name = user.CompanyName });
    }

    private static void InsertLocation(dynamic db, CurrentUser user)
    {
        db.Location.Insert(new
        {
            Id = user.LocationId,
            Name = user.LocationName
        });
    }

    private static void InsertUser(dynamic db, CurrentUser user)
    {
        db.Customer.Insert(new
        {
            Id = user.CustomerId,
            CompanyFk = user.CompanyId,
            LocationFk = user.LocationId,
            Name = user.CustomerName
        });
    }

    [Test]
    ///Sanity check passes
    public void can_reteive_user_successfully()
    {
        //arrange
        var db = PerformDbSetup();

        var user = AutoFixture.Create<CurrentUser>();

        CreateUser(db, user);

        //act
        var result = ReteiveUser(user.CustomerId);

        //assert
        result.CompanyId.Should().Be(user.CompanyId);
        result.CustomerId.Should().Be(user.CustomerId);
        result.LocationId.Should().Be(user.LocationId);
        result.CompanyName.Should().Be(user.CompanyName);
        result.CustomerName.Should().Be(user.CustomerName);
        result.LocationName.Should().Be(user.LocationName);
    }

    [Test]
    ///Sanity check passes
    public void can_get_value_by_id()
    {
        //arrange
        var db = PerformDbSetup();

        var user = AutoFixture.Create<CurrentUser>();

        CreateUser(db, user);

        //act
        var result = db.Customer.Get(user.CustomerId);

        //assert
        Assert.That(result.Name,Is.EqualTo(user.CustomerName));

    }

    [Test]
    /// From: http://simplefx.org/simpledata/docs/pages/Retrieve/ColumnSelection.html
    /// By default, the All, Get and Find* commands retrieve every column from each table specified in their invocation.
    /// Use the Select method to set explicitly the data fields returned by those commands.
    public void can_project_column_of_single_value_when_using_get_to_retrieve_data()
    {
        //arrange
        var db = PerformDbSetup();

        var user = AutoFixture.Create<CurrentUser>();

        CreateUser(db, user);

        //act
        Action callToGetProjectingColumns = ()=> db.Customer.Get(user.CustomerId)
                       .Select(db.Customer.Id.As("CustomerId"),
                               db.Customer.Name.As("CustomerName"));

        //assert
        callToGetProjectingColumns.ShouldNotThrow("documentation implies Select works on SimpleRecord");
    }

    [Test]
    /// From: http://simplefx.org/simpledata/docs/pages/Retrieve/ColumnSelection.html
    /// By default, the All, Get and Find* commands retrieve every column from each table specified in their invocation.
    /// Use the Select method to set explicitly the data fields returned by those commands.
    public void can_project_column_of_single_value_when_using_Find_to_retrieve_data()
    {
        //arrange
        var db = PerformDbSetup();

        var user = AutoFixture.Create<CurrentUser>();

        CreateUser(db, user);

        //act
        Action callToGetProjectingColumns = () => db.Customer.FindById(user.CustomerId)
                       .Select(db.Customer.Id.As("CustomerId"),
                               db.Customer.Name.As("CustomerName"));

        //assert
        callToGetProjectingColumns.ShouldNotThrow("documentation implies Select works on SimpleRecord");
    }

    [Test]
    ///Is this expected behavior
    public void do_not_blow_up_if_ask_for_data_which_is_not_present_when_Joining()
    {
        //arrange
        //arrange
        var db = PerformDbSetup();

        var user = AutoFixture.Create<CurrentUser>();

        CreateUser(db, user);

        //act
        Action result = ()=> ReteiveUser(user.CustomerId + 1);

        //assert
        result.ShouldNotThrow("attempting to retreive non existant data should not throw exception");

    }

   public CurrentUser ReteiveUser(int userId)
     {
         var db = Database.Open();
            var item = db.Customer.FindAllById(userId)
                    .Select(
                        db.Customer.Id.As("CustomerId"),
                        db.Customer.Name.As("CustomerName"),
                        db.Customer.Location.Id.As("LocationId"),
                        db.Customer.Location.Name.As("LocationName"),
                        db.Customer.Company.Id.As("CompanyId"),
                        db.Customer.Company.Name.As("CompanyName")
           ).Join(db.Location).On(db.Customer.LocationFk == db.Location.Id)
           .Join(db.Company).On(db.Customer.CompanyFk == db.Company.Id)
           .First();
            return item;
    }

}

}

— Reply to this email directly or view it on GitHubhttps://github.com/markrendle/Simple.Data/issues/265 .

JohnEffo commented 11 years ago

Ok. Any thoughts on do_not_blow_up_if_ask_for_data_which_is_not_present_when_Joining?

Cheers John

JohnEffo commented 11 years ago

Mark going to close this as no comment on test do_not_blow_up_if_ask_for_data_which_is_not_present_when_Joining. Thank you to the quick response on Get and FindBy, is there any way I can update the documentation for you?