Create mapping for inheritance (TPT)

[Toni Wenzel]

In our business application we're using entities which are derived from other entities (inheritance TPT like http://weblogs.asp.net/manavi/inheritance-mapping-strategies-with-entity-framework-code-first-ctp5-part-2-table-per-type-tpt),

We've a custom mapping resolver implementing IMappingResolver.

Is there any possibility to create a mapping that the join to the base table will be created? I already thought of creating a custom class implementing IResolvedTableInfo.

What do you think?

[Michael Ketting]

Ah, yes. That one :)

The cheapest solution would be to create a view that joins the two tables and then just resolve to the view instead of the table. We had to tackle a similar problem with TPC (http://weblogs.asp.net/manavi/inheritance-mapping-strategies-with-entity-framework-code-first-ctp5-part-3-table-per-concrete-type-tpc-and-choosing-strategy-guidelines) when we want to select across all types derived from the abstract base class and been generating union views for this since day one. The only downside is when you have really wide tables and hit SQL Server's max-column limit.

True multi-table TableInfos...that's on my wishlist but I haven't even gotten around to designing a concept for this. But IResolvedTableInfo...yeah...could work, provided you can extend all the relevant visitors. You might also want to try ResolvedSubStatementTableInfo via MappingResolver.ResolveTableInfo(...) instead and generate the relevant join-query as a substatement. This would basically be the same as the view, only on the fly. From a purely cost-benefit point of view, I'd recommend trying to generate the correct views first since that way SQL Server might also help with statistcis and stuff. If views don't work because adapting the mapping generation is too much effort/not practical in you case, the ResolvedSubStatementTableInfo would be my next step.

True native support, that's going to be an involved task to do from the outside. An involved task when adapting the sources directly, too, but definitely harder from the outside, and I'm not sure there's going to be a true benefit except that you can remove unnecesary joins again if you do not need any of the columns from the conditions or a custom result set.

Best regards, Michael

[Toni Wenzel]

I've tried following stuff.

Public Function ResolveTableInfo(ByVal tableInfo As UnresolvedTableInfo, ByVal generator As UniqueIdentifierGenerator) As IResolvedTableInfo Implements IMappingResolver.ResolveTableInfo

ArgumentUtility.CheckNotNull("tableInfo", tableInfo)

ArgumentUtility.CheckNotNull("generator", generator)

Dim objectInfo As BusinessObjectDefinition = GetMetaType(tableInfo.ItemType)

Dim tableName = If(objectInfo.TableName.StartsWith("dbo."), objectInfo.TableName.Substring(4), objectInfo.TableName)

If (objectInfo.IsHierachicalObject()) Then

dim parentInfo = Buffers.Instance.MetaInfo.GetObjectInfoByObjectId(objectInfo.GetParentObjectID())

Dim expression As Expression = Nothing

Dim parentAlias As String = generator.GetUniqueIdentifier("j")

Dim tableAlias As String  = generator.GetUniqueIdentifier("t")

Dim primaryColumn = new SqlColumnDefinitionExpression(PrimaryKeyField.PropertyInfo.PropertyType, parentAlias, PKTableFieldName, PrimaryKeyField.IsPartOfPrimaryKey)

Dim foreignColumn = new SqlColumnDefinitionExpression(ForeignKeyField.PropertyInfo.PropertyType, tableAlias, FKTableFieldName, ForeignKeyField.IsPartOfPrimaryKey)

Dim itemExpression = Expression.Equal(primaryColumn, foreignColumn)

Dim builder As SqlStatementBuilder = New SqlStatementBuilder()

builder.SqlTables.Add(New SqlTable(New ResolvedSimpleTableInfo(tableInfo.ItemType, tableName, tableAlias), JoinSemantics.Inner))

Dim joinInfo As ResolvedJoinInfo = New ResolvedJoinInfo(New ResolvedSimpleTableInfo(parentInfo.GetBusinessObjectType(), parentInfo.TableName, parentAlias), expression)

Dim jointable As SqlJoinedTable = New SqlJoinedTable(joinInfo, JoinSemantics.Left)

Dim parentTable As SqlTable = New SqlTable(jointable, JoinSemantics.Left)

builder.SqlTables.Add(parentTable)

builder.DataInfo = new Clauses.StreamedData.StreamedSequenceInfo(GetType(IQueryable(Of )).MakeGenericType(tableInfo.ItemType), expression.[New](tableInfo.ItemType))

builder.SelectProjection = ???

Return New ResolvedSubStatementTableInfo(tableAlias, builder.GetSqlStatement())

Else

Return New ResolvedSimpleTableInfo(tableInfo.ItemType, tableName, generator.GetUniqueIdentifier("t"))

End If

End Function

The problem is I don't know how to set the SelectProjection property.

Is my way correct or have you a better example?

[Toni Wenzel]

I've also tried to use a join (which I would prefer over a subselect), but I got stucked here as well.

Dim joinInfo As ResolvedJoinInfo = New ResolvedJoinInfo(New ResolvedSimpleTableInfo(parentInfo.GetBusinessObjectType(), parentInfo.TableName, parentAlias), expression)

Dim joinTable = mainTable.GetOrAddLeftJoin(joinInfo, relationItemsResult.Response.Items.First().ForeignKeyField.PropertyInfo)


 
Return mainTable.GetResolvedTableInfo()

[Toni Wenzel]

Found a way which works. May be it's not that nice... I appreciate comments.

using Microsoft.VisualBasic;

using System;

using System.Collections;

using System.Collections.Generic;

using System.Data;

using System.Diagnostics;

using System.Linq.Expressions;

using CHG.iCLASSFW.Base.Objects;

using CHG.iCLASSFW.Base.Shared;

using Remotion.Linq;

using Remotion.Linq.SqlBackend.MappingResolution;

using Remotion.Linq.SqlBackend.SqlStatementModel;

using Remotion.Linq.SqlBackend.SqlStatementModel.Resolved;

/// <summary>

/// <see cref="ResolvedHierarchicalTableInfo" /> represents the data source defined by multiple tables in a relational database.

/// </summary>

public class ResolvedHierarchicalTableInfo : ResolvedSimpleTableInfo, IResolvedTableInfo

{

#region "Members"

private ResolvedSimpleTableInfo m_MainTableInfo;

private ResolvedSimpleTableInfo m_JoinedTableInfo;

private SqlTable m_MainTable;

private SqlJoinedTable m_JoinedTable;

#endregion

private ResolvedJoinInfo m_JoinInfo;

#region "Constructors / Destructors"

/// <summary>

/// Initializes a new instance

/// </summary>

/// <param name="itemType">The BO item type</param>

/// <param name="tableAlias">The table alias</param>

/// <param name="objectInfo">The bo definition</param>

public ResolvedHierarchicalTableInfo(Type itemType, string tableAlias, string parentAlias, BusinessObjectDefinition objectInfo, BusinessObjectDefinition parentObjectInfo) : base(itemType, objectInfo.TableName, tableAlias)

{

ArgumentUtility.CheckNotNull("objectInfo", objectInfo);

ArgumentUtility.CheckNotNull("parentObjectInfo", parentObjectInfo);

ArgumentUtility.CheckNotNull("parentAlias", parentAlias);

// retrieve the relation items to the base object

dynamic relationItemsResult = Buffers.Instance.MetaInfo.GetRelation("select statement for joining hierachical objects", parentObjectInfo, objectInfo, null, null);

if ((!relationItemsResult.IsValid))

{

throw relationItemsResult.ToServiceException(null);

}

Expression expression = null;

foreach (void item_loopVariable in relationItemsResult.Response.Items)

{

item = item_loopVariable;

dynamic primaryColumn = new SqlColumnDefinitionExpression(item.PrimaryKeyField.PropertyInfo.PropertyType, parentAlias, item.PKTableFieldName, item.PrimaryKeyField.IsPartOfPrimaryKey);

dynamic foreignColumn = new SqlColumnDefinitionExpression(item.ForeignKeyField.PropertyInfo.PropertyType, tableAlias, item.FKTableFieldName, item.ForeignKeyField.IsPartOfPrimaryKey);

dynamic itemExpression = expression.Equal(primaryColumn, foreignColumn);

if ((expression == null))

{

expression = itemExpression;

} else

{

expression = expression.AndAlso(expression, itemExpression);

}

}

m_MainTableInfo = new ResolvedSimpleTableInfo(itemType, objectInfo.TableName, tableAlias);

m_JoinedTableInfo = new ResolvedSimpleTableInfo(parentObjectInfo.GetBusinessObjectType(), parentObjectInfo.TableName, parentAlias);

m_MainTable = new SqlTable(m_MainTableInfo, JoinSemantics.Inner);

m_JoinInfo = new ResolvedJoinInfo(m_JoinedTableInfo, expression);

m_JoinedTable = m_MainTable.GetOrAddLeftJoin(m_JoinInfo, relationItemsResult.Response.Items.First().ForeignKeyField.PropertyInfo);

}

#endregion

#region "Public methods"

public Expression ResolveReference(SqlTableBase sqlTable, IMappingResolver mappingResolver, IMappingResolutionContext context, UniqueIdentifierGenerator generator)

{

ArgumentUtility.CheckNotNull("sqlTable", sqlTable);

ArgumentUtility.CheckNotNull("mappingResolver", mappingResolver);

ArgumentUtility.CheckNotNull("context", context);

ArgumentUtility.CheckNotNull("generator", generator);

SqlEntityExpression sqlEntityExpression = mappingResolver.ResolveSimpleTableInfo(m_MainTableInfo, generator);

context.AddSqlEntityMapping(sqlEntityExpression, m_MainTable);

dynamic joinSqlEntityExpression = mappingResolver.ResolveSimpleTableInfo(m_JoinedTableInfo, generator);

context.AddSqlEntityMapping(joinSqlEntityExpression, m_JoinedTable);

return sqlEntityExpression;

}

public override ITableInfo Accept(ITableInfoVisitor visitor)

{

ArgumentUtility.CheckNotNull("visitor", visitor);

dynamic result = visitor.VisitSimpleTableInfo(this);

// required to get "GetResolvedTableInfo" called

SqlBackend.SqlGeneration.SqlTableAndJoinTextGenerator textbuilder = visitor as SqlBackend.SqlGeneration.SqlTableAndJoinTextGenerator;

if ((textbuilder != null))

{

textbuilder.CommandBuilder.Append(" {0} JOIN ".Frmt(m_MainTable.JoinSemantics));

}

IJoinInfoVisitor joinVisitor = visitor as IJoinInfoVisitor;

if ((joinVisitor != null))

{

joinVisitor.VisitResolvedJoinInfo(m_JoinInfo);

} else

{

visitor.VisitSqlJoinedTable(m_JoinedTable);

}

return result;

}

public override IResolvedTableInfo GetResolvedTableInfo()

{

return this;

// now "ResolveReference" get called

}

public override string ToString()

{

return m_MainTable.ToString();

}

#endregion

#region "Properties"

/// <summary>

/// Gets the used table alias

/// </summary>

/// <returns></returns>

public string TableAlias {

get { return base.TableAlias; }

}

/// <summary>

/// Gets the underlying item type

/// </summary>

/// <returns></returns>

public override Type ItemType {

get { return base.ItemType; }

}

#endregion

}

[Michael Ketting]

Hi Toni!

Sorry, didn't notice update till today.

The first one with the SqlStatementBuilder and the SelectProjection:
You can check out the https://github.com/re-motion/Relinq-SqlBackend/blob/develop/UnitTests/SqlStatementModel/SqlStatementModelObjectMother.cs#L278 for a sample of a select expression that's useable at this point. Basically, you need a SqlEntityDefinitionExpression and fill with with metadata (table name, etc) and columns to select from your tables.

The working solution, I have to admit I didn't check it out in detail but the gist of it looks like "it's working so it's good enough". But it probably isn't doing things in the most efficient / duplication-free manner so if you have a couple of minutes to try it out, I'd recommend giving the original idea another spin.

Best regards, Michael

[Toni Wenzel]

I've tried again the SubStatement idea but it's not working. as expected. 

 - It will create a (unpretty) OUTER APPLY instead of an inner join.

 - The "join condition" will not be applied

 - The colums of the base table have the wrong table alias.

''' <summary>
    ''' Retrieves the table information
    ''' </summary>
    ''' <param name="tableInfo"> The Remotion.Linq.SqlBackend.SqlStatementModel.Unresolved.UnresolvedTableInfo which is to be resolved.</param>
    ''' <param name="generator">A Remotion.Linq.UniqueIdentifierGenerator that can be used to generate unique identifiers such as table aliases.</param>
    ''' <returns>An Remotion.Linq.SqlBackend.SqlStatementModel.Resolved.IResolvedTableInfo instance representing the tableInfo in the database.</returns>

    Public Function ResolveTableInfo(ByVal tableInfo As UnresolvedTableInfo, ByVal generator As UniqueIdentifierGenerator) As IResolvedTableInfo Implements IMappingResolver.ResolveTableInfo
        ArgumentUtility.CheckNotNull("tableInfo", tableInfo)
        ArgumentUtility.CheckNotNull("generator", generator)


        Dim objectInfo As BusinessObjectDefinition = GetMetaType(tableInfo.ItemType)

       


        If (objectInfo.IsHierachicalObject()) Then                                              
            'Return New ResolvedHierarchicalTableInfo(tableInfo.ItemType, generator.GetUniqueIdentifier("t"), objectInfo, generator)

            dim parentInfo = Buffers.Instance.MetaInfo.GetObjectInfoByObjectId(objectInfo.GetParentObjectID())
           


            Dim expression As Expression = Nothing
            Dim parentAlias As String = generator.GetUniqueIdentifier("j")
            Dim tableAlias As String  = generator.GetUniqueIdentifier("t")

            ' retrieve the relation items to the base object
            Dim relationItemsResult = Buffers.Instance.MetaInfo.GetRelation("select statement for joining hierachical objects", parentInfo, objectInfo, Nothing, Nothing)

            If (Not relationItemsResult.IsValid) Then
                Throw relationItemsResult.ToServiceException(Nothing)
            End If
           
            Dim joinedTableInfo = New ResolvedSimpleTableInfo(parentInfo.GetBusinessObjectType(), parentInfo.TableName, generator.GetUniqueIdentifier("j"))


            For Each item In relationItemsResult.Response.Items
                Dim primaryColumn = new SqlColumnDefinitionExpression(item.PrimaryKeyField.PropertyInfo.PropertyType, joinedTableInfo.TableAlias, item.PKTableFieldName, item.PrimaryKeyField.IsPartOfPrimaryKey)
                Dim foreignColumn = new SqlColumnDefinitionExpression(item.ForeignKeyField.PropertyInfo.PropertyType, tableAlias, item.FKTableFieldName, item.ForeignKeyField.IsPartOfPrimaryKey)

                Dim itemExpression as Expression = Nothing
                If (primaryColumn.Type = foreignColumn.Type) Then
                    itemExpression = Expression.Equal(primaryColumn, foreignColumn)
                Else
                    itemExpression = Expression.Equal(primaryColumn, expression.Convert(foreignColumn, primaryColumn.Type))
                End If

                If (expression Is Nothing) Then
                    expression = itemExpression
                Else
                    expression = expression.AndAlso(expression, itemExpression)
                End If
            Next

            Dim builder As SqlStatementBuilder = New SqlStatementBuilder()

           
            builder.SqlTables.Add(New SqlTable(New ResolvedSimpleTableInfo(tableInfo.ItemType, objectInfo.TableName, tableAlias), JoinSemantics.Inner))

            Dim joinInfo As ResolvedJoinInfo = New ResolvedJoinInfo(New ResolvedSimpleTableInfo(parentInfo.GetBusinessObjectType(), parentInfo.TableName, parentAlias), expression)
            Dim jointable As SqlJoinedTable = New SqlJoinedTable(joinInfo, JoinSemantics.Left)
            Dim parentTable As SqlTable = New SqlTable(jointable, JoinSemantics.Left)
            builder.SqlTables.Add(parentTable)
            builder.DataInfo = new Clauses.StreamedData.StreamedSequenceInfo(GetType(IQueryable(Of )).MakeGenericType(tableInfo.ItemType), expression.[New](tableInfo.ItemType))

            builder.SelectProjection = CreateSqlEntityDefinitionExpression(tableInfo.ItemType, tableAlias)

            Return New ResolvedSubStatementTableInfo(tableAlias, builder.GetSqlStatement())
        Else

                   
            Return New ResolvedSimpleTableInfo(tableInfo.ItemType, objectInfo.TableName, generator.GetUniqueIdentifier("t"))
        End If
    End Function

At the moment I'll continue using my prefered way using joins. 

Maybe you'll provide a supported way in any further release or we can retry after issue RMLNQSQL-2 will be released.

[Michael Ketting]

Hi Toni!

Okay thanks for trying it, but I'm happy you got a working solution. That table mapping scenario is definitely something that will pop up again in the future and I'm thinking of also writing a sample test for this since I did hope that it would work.

Best regards, Michael

PS: Also thanks for letting us know you're using the sql backend. That's great to know :)

[Toni Wenzel]

Please let me know when you've got a working sample for this scenario. I might change my implementation then.