ServiceStack Ormlite Join Wrapper

Question

I've created a wrapper in my data access for joins in OrmLite.

I'm now getting the exception:

System.Exception : Expression should have only one column

All of my entities have a base class of BaseEntity. JoinType is just a facade to contain the column, selection and where of a join.

My wrapper is as follows:

public IEnumerable<TResultEntity> Join<TResultEntity>(IList<JoinType<BaseEntity, BaseEntity>> joins)
        {
            var result = new List<TResultEntity>();

            if (joins != null && joins.Any())
            {
                var joinBuilder = new JoinSqlBuilder<T, BaseEntity>();

                foreach (var join in joins)
                {
                    joinBuilder = joinBuilder.Join(join.LeftColumn, join.RightColumn, join.LeftSelection, join.RightSelection, join.LeftWhere, join.RightWhere);
                }

                var connection = this.DataConnection;
                using (connection)
                {
                    var joinSql = joinBuilder.SelectDistinct().ToSql();
                    result = connection.SqlList<TResultEntity>(joinSql);
                }
            }

            return result;
        }

Doing the same thing, without the list seems to work:

public IEnumerable<TResultEntity> Join<TLeftTable1, TRightTable1, TLeftTable2, TRightTable2, TResultEntity>(
            JoinType<TLeftTable1, TRightTable1> join1,
            JoinType<TLeftTable2, TRightTable2> join2) 
            where TLeftTable1 : BaseEntity 
            where TRightTable1 : BaseEntity 
            where TLeftTable2 : BaseEntity 
            where TRightTable2 : BaseEntity

EDIT - I'm testing using the below call:

// Act
                var join1 = new JoinType<AnswerEntity, UserSurveyStateEntity>(
                l => l.OwnerId,
                r => r.UserId,
                x => new { UserId = x.OwnerId, x.QuestionId, AnswerId = x.Id, x.AnswerValue });

                var join2 = new JoinType<SurveyEntity, UserSurveyStateEntity>(
                l => l.Id,
                r => r.SurveyInstanceId,
                x => new { SurveyId = x.Id, SurveyName = x.Name, x.StatusValue },
                null,
                null,
                x => x.StatusValue == (int)UserSurveyStatus.Complete);

                var joins = new List<JoinType<BaseEntity, BaseEntity>>();
                joins.Add(join1.As<JoinType<BaseEntity, BaseEntity>>());
                joins.Add(join2.As<JoinType<BaseEntity, BaseEntity>>());

                var result = dataAccess.Join<AnswerEntity>(joins).ToList();

Answer 1

EDIT - Now seeing the use case, the error is related to casting to the base type and the builder storing more than one column selector for the concrete BaseEntity. Consider adding an abstract JoinType class, and modifying the JoinType class so it will apply the join for the builder.

For example:

    public class Entity
    {
        public string Id { get; set; }
    }
    public class Foo
        : Entity
    {
        public string Value { get; set; }
    }

    public class Bar
        : Entity
    {
        public string FooId { get; set; }
        public string Value { get; set; }
    }

    public abstract class JoinType
    {
        public abstract JoinSqlBuilder<TNew, TBase> ApplyJoin<TNew, TBase>(
            JoinSqlBuilder<TNew, TBase> bldr);
    }

    public class JoinType<TSource, TTarget>
        : JoinType
    {
        private Expression<Func<TSource, object>> _sourceColumn;
        private Expression<Func<TTarget, object>> _destinationColumn;
        private Expression<Func<TSource, object>> _sourceTableColumnSelection;
        private Expression<Func<TTarget, object>> _destinationTableColumnSelection;
        private Expression<Func<TSource, bool>> _sourceWhere;
        private Expression<Func<TTarget, bool>> _destinationWhere;

        public JoinType(Expression<Func<TSource, object>> sourceColumn,
            Expression<Func<TTarget, object>> destinationColumn,
            Expression<Func<TSource, object>>
                sourceTableColumnSelection = null,
            Expression<Func<TTarget, object>>
                destinationTableColumnSelection = null,
            Expression<Func<TSource, bool>> sourceWhere = null,
            Expression<Func<TTarget, bool>> destinationWhere =
                null)
        {
            this._sourceColumn = sourceColumn;
            this._destinationColumn = destinationColumn;
            this._sourceTableColumnSelection = sourceTableColumnSelection;
            this._destinationTableColumnSelection =
                destinationTableColumnSelection;
            this._sourceWhere = sourceWhere;
            this._destinationWhere = destinationWhere;
        }

        public override JoinSqlBuilder<TNew, TBase> ApplyJoin<TNew, TBase>(
            JoinSqlBuilder<TNew, TBase> bldr)
        {
            bldr.Join(_sourceColumn,
                _destinationColumn,
                _sourceTableColumnSelection,
                _destinationTableColumnSelection,
                _sourceWhere,
                _destinationWhere);

            return bldr;
        }
    }

    public class FooBar
    {
        [References(typeof(Foo))]
        public string FooId { get; set; }
        [References(typeof(Bar))]
        public string BarId { get; set; }
        [References(typeof(Foo))]
        public string FooValue { get; set; }
        [References(typeof(Bar))]
        public string BarValue { get; set; }
    }

    /*
    This join accomplishes the same thing, but just returns the SQL as a string.
    */
    public string Join<TResultEntity,TBase>(IList<JoinType>joins)
    {
        var result = new List<TResultEntity>();

        if (joins != null && joins.Any())
        {
            var joinBuilder = new JoinSqlBuilder<TResultEntity, TBase>();

            foreach (var joinType in joins)
            {
                //call the apply join, and the join type will know the valid types
                joinBuilder = joinType.ApplyJoin(joinBuilder);
            }

            return joinBuilder.SelectDistinct().ToSql();
        }

        return null;
    }

    [TestMethod]
    public void TestMethod1()
    {
        OrmLiteConfig.DialectProvider = SqlServerDialect.Provider;

        var joins = new List<JoinType>();

        var jointype1 = new JoinType<Bar, FooBar>(
            bar => bar.Id,
            bar => bar.BarId,
            bar => new { BarId = bar.Id, BarValue = bar.Value }
            );
        joins.Add(jointype1);
        var joinType2 = new JoinType<Foo, FooBar>(
            foo => foo.Id,
            bar => bar.FooId,
            foo => new { FooId = foo.Id, FooValue = foo.Value}
            );
        joins.Add(joinType2);

        var str = Join<FooBar, Bar>(joins);
    }

Old Answer - still relevant to the error

This error is caused by your selector join.LeftColumn or your join.RightColumn containing two selectors. Make sure they only contain a single one.

I was able to reproduce the error with the following test:

    public class Entity
    {
        public string Id { get; set; }
    }
    public class Foo
        : Entity
    {
        public string Value { get; set; }
    }

    public class Bar
        : Entity
    {
        public string FooId { get; set; }
        public string Value { get; set; }
    }

    public class FooBar
    {
        [References(typeof(Foo))]
        public string FooId { get; set; }
        [References(typeof(Bar))]
        public string BarId { get; set; }
        [References(typeof(Foo))]
        public string FooValue { get; set; }
        [References(typeof(Bar))]
        public string BarValue { get; set; }
    }

    [TestMethod]
    public void TestMethod1()
    {
        OrmLiteConfig.DialectProvider = SqlServerDialect.Provider;
        var bldr = new JoinSqlBuilder<FooBar,Bar>();
        bldr = bldr.Join<FooBar, Bar>(
            bar => bar.BarId, 
            bar => new { Id1 = bar.Id, Id2 = bar.Id},//<-- this should only contain a single member
            bar => new { BarId =bar.BarId },
            bar => new { BarId = bar.Id, BarValue = bar.Value},
            bar => bar.BarId != null,
            bar => bar.Id != null
            );

        var str = bldr.SelectDistinct().ToSql();
    }