Hello,

I use Designer version: 2.0.0.0 and Runtime libraries version: 2.0.0.0.

I have two Sub-Types of an Entity. These Sub-Types have a 1:n Relation (Field ID - Field ParentID). Both Sub-Types inherit fields from the Main-Entity and they have new mapped fields. Now i want to fetch the Sub-Type 1 and search in a new field in the Sub-Type 2. Because the Relation is in the same Table I use the advanced filtering method and set the objectAlias. The error I have is that the Query which is generated has different objectAliases. The one in the InnerJoin section is another as the one in the Where section.

I try to show my problem in a sample:

Dim subType1Collection As New EntityCollection(Of subType1Entity)
Dim filter As New RelationPredicateBucket

filter.Relations.Add(subType1Entity.Relations.SubType2EntityUsingParentId, "SubType2")
filter.PredicateExpression.Add(subType2Fields.MappedFieldSubType2.SetObjectAlias("SubType2") = "test")

Using da As New DataAccessAdapter
   da.FetchEntityCollection(subType1Collection, filter)
End Using

'The SQL-Query looks like:

SELECT 
[server].[dbo].[Table1].[C000] AS [ID]
[server].[dbo].[Table1].[C001] AS [ParentID]
[server].[dbo].[Table1].[C002] AS [InheritField]
[server].[dbo].[Table1].[C003] AS [MappedFieldSubType1]
FROM 
[server].[dbo].[Table1] INNER JOIN [server].[dbo].[Table1] [LPA_S1] 
ON [server].[dbo].[Table1].[C000] = [SubType2].[C001]
WHERE
[LPA_S2].[C004] = "test"

Then I get an Error, because SQL could not bind [LPA_S2].

When I filter to an Inherited-Field from the MainEntity, then the ObjectAliases are the same.

I hope I discriped my problem correct. I'm happy about any answer.

Best regards andré

What happens when you don't use aliases in code? In this case would you please post the generated Query? (without aliases)

In this case I get an error from the SQL-Server, that the Tablenames are not explicit.

I show this on my really used code.

Database: CrossArchiv Table: tbl500

MainEntity: ArchivEntity SubEntity1: ArchivMappeEntity SubEntity2: ArchivDateiEntity

Relation: ArchivMappeEntity ID - ArchivDateiEntity ParentID (1:n)

This is the code without aliases:

Dim archivMappeCollection As New EntityCollection(Of ArchivMappeEntity)
Dim filter As New RelationPredicateBucket

filter.PredicateExpression.AddWithAnd(ArchivMappeFields.Datum >= Me.datVon.Value)
filter.PredicateExpression.AddWithAnd(ArchivMappeFields.Datum <= Me.datBis.Value)
filter.PredicateExpression.AddWithAnd(New FieldFullTextSearchPredicate(ArchivDateiFields.Datei, Nothing, FullTextSearchOperator.Contains, "Rechnung"))

filter.Relations.Add(ArchivMappeEntity.Relations.ArchivDateiEntityUsingParentId)

Using da As New DataAccessAdapter
   da.FetchEntityCollection(archivMappeCollection, filter)
End Using

This is the generated Query without aliases:

exec sp_executesql N'
SELECT 
[CrossArchiv].[dbo].[tbl500].[C000] AS [F0], 
[CrossArchiv].[dbo].[tbl500].[C001] AS [F1], 
[CrossArchiv].[dbo].[tbl500].[C002] AS [F2], 
[CrossArchiv].[dbo].[tbl500].[C012] AS [F3], 
[CrossArchiv].[dbo].[tbl500].[C013] AS [F4], 
[CrossArchiv].[dbo].[tbl500].[C998] AS [F5], 
[CrossArchiv].[dbo].[tbl500].[C999] AS [F6], 
[CrossArchiv].[dbo].[tbl500].[ts] AS [F7], 
[CrossArchiv].[dbo].[tbl500].[C019] AS [F8], 
[CrossArchiv].[dbo].[tbl500].[C020] AS [F9], 
[CrossArchiv].[dbo].[tbl500].[C005] AS [F10], 
[CrossArchiv].[dbo].[tbl500].[C006] AS [F11], 
[CrossArchiv].[dbo].[tbl500].[C007] AS [F12], 
[CrossArchiv].[dbo].[tbl500].[C008] AS [F13], 
[CrossArchiv].[dbo].[tbl500].[C009] AS [F14], 
[CrossArchiv].[dbo].[tbl500].[C010] AS [F15], 
[CrossArchiv].[dbo].[tbl500].[C011] AS [F16], 
[CrossArchiv].[dbo].[tbl500].[C017] AS [F17], 
[CrossArchiv].[dbo].[tbl500].[C018] AS [F18] 
FROM 
( 
    [CrossArchiv].[dbo].[tbl500]  
    INNER JOIN 
    [CrossArchiv].[dbo].[tbl500] 
    ON 
    [CrossArchiv].[dbo].[tbl500].[C000]=[CrossArchiv].[dbo].[tbl500].[C012]
) 
WHERE 
( 
    ( 
        [CrossArchiv].[dbo].[tbl500].[C005] >= @Datum1 
        AND 
        [CrossArchiv].[dbo].[tbl500].[C005] <= @Datum2 
        AND 
                                Contains([CrossArchiv].[dbo].[tbl500].[C003], @Datei11) 
    ) 
    AND 
    ( 
        [CrossArchiv].[dbo].[tbl500].[C020] = @Kz4
    )
) 
',N'
@Datum1 datetime,
@Datum2 datetime,
@ParentId3 int,
@Kz4 int',
@Datum1='30.04.2007 00:00:00:000',
@Datum2='30.07.2007 23:59:59:000',
@Kz4=2

Which LLBLGen runtime library build are you using? Please refer to this thread to know how to get the RTL build: http://www.llblgen.com/TinyForum/Messages.aspx?ThreadID=7722

I'm using the LLBLGen runtime library build 2.0.0.60928.

That's a rather old version.

Would you please download and try using the latest?

Now, I'm using the LLBLGen runtime library build 2.0.7.705. But I get the same error. Every time when I filter a field which is a new binding field (not inherted from the main entity) the generated query don't take the alias I give him.

Here the code again:

Dim archivMappeCollection As New EntityCollection(Of ArchivMappeEntity)
Dim filter As New RealtionPredicateBucket

filter.Relations.Add(ArchivMappeEntity.Relations.ArchivDateiEntityUsingParentId, "ArchivDatei")

filter.PredicateExpression.Add(New FieldFullTextSearchPredicate(ArchivDateiFields.Datei.SetObjectAlias("ArchivDatei"), Nothing, FullTextSearchOperator.Contains, "test"))
filter.PredicateExpression.AddWithAnd(ArchivMappeFields.Datum >= Me.datVon.Value)
filter.PredicateExpression.AddWithAnd(ArchivMappeFields.Datum <= Me.datBis.Value)

Using da As New DataAccessAdapter
            da.FetchEntityCollection(archivMappeCollection, filter)
End Using

The generated Query:

exec sp_executesql N'
SELECT [CrossArchiv].[dbo].[tbl500].[C000] AS [F0], 
[CrossArchiv].[dbo].[tbl500].[C001] AS [F1], 
[CrossArchiv].[dbo].[tbl500].[C002] AS [F2], 
[CrossArchiv].[dbo].[tbl500].[C012] AS [F3], 
[CrossArchiv].[dbo].[tbl500].[C013] AS [F4], 
[CrossArchiv].[dbo].[tbl500].[C998] AS [F5], 
[CrossArchiv].[dbo].[tbl500].[C999] AS [F6], 
[CrossArchiv].[dbo].[tbl500].[ts] AS [F7], 
[CrossArchiv].[dbo].[tbl500].[C019] AS [F8], 
[CrossArchiv].[dbo].[tbl500].[C020] AS [F9], 
[CrossArchiv].[dbo].[tbl500].[C005] AS [F10], 
[CrossArchiv].[dbo].[tbl500].[C006] AS [F11], 
[CrossArchiv].[dbo].[tbl500].[C007] AS [F12], 
[CrossArchiv].[dbo].[tbl500].[C008] AS [F13], 
[CrossArchiv].[dbo].[tbl500].[C009] AS [F14], 
[CrossArchiv].[dbo].[tbl500].[C010] AS [F15], 
[CrossArchiv].[dbo].[tbl500].[C011] AS [F16], 
[CrossArchiv].[dbo].[tbl500].[C017] AS [F17], 
[CrossArchiv].[dbo].[tbl500].[C018] AS [F18] 
FROM 
( 
    [CrossArchiv].[dbo].[tbl500]  
    INNER JOIN 
    [CrossArchiv].[dbo].[tbl500] [LPA_A1]  
    ON  
    [CrossArchiv].[dbo].[tbl500].[C000]=[LPA_A1].[C012]
) 
WHERE 
( 
    ( 
        Contains([LPA_A2].[C003], @Datei11) 
        AND 
        [CrossArchiv].[dbo].[tbl500].[C005] >= @Datum2 
        AND 
        [CrossArchiv].[dbo].[tbl500].[C005] <= @Datum3 
    ) 
    AND 
    ( 
        [CrossArchiv].[dbo].[tbl500].[C020] = @Kz4
    )
) 
',N'
@Datei11 nvarchar(8),
@Datum2 datetime,
@Datum3 datetime,
@Kz4 int',
@Datei11=N'Rechnung',
@Datum2='30.04.2007 00:00:00:000',
@Datum3='30.07.2007 23:59:59:000',
@Kz4=2

For example, when i filter to an inherited field, then the correct alias will generate.

Filter.Relations.Add(ArchivMappeEntity.Relations.ArchivDateiEntityUsingParentId, "ArchivDatei")

'ArchivDateiFields.Dateiname not inherited from the MainEntity (ArchivEntity)
Filter.PredicateExpression.AddWithAnd(ArchivDateiFields.Dateiname.SetObjectAlias("ArchivDatei") = "test")

'ArchivDateiFields.ParentId inherted from the MainEntity (ArchivEntity)
Filter.PredicateExpression.AddWithAnd(ArchivDateiFields.ParentId.SetObjectAlias("ArchivDatei") = 102315)

The generated Query:

[...]
FROM 
( 
    [CrossArchiv].[dbo].[tbl500]  
    INNER JOIN 
    [CrossArchiv].[dbo].[tbl500] [LPA_A1]  
    ON  
    [CrossArchiv].[dbo].[tbl500].[C000]=[LPA_A1].[C012]
) 
WHERE 
( 
    ( 
        [LPA_A2].[C014] = @Dateiname1    --Not Inherited Field
        AND 
        [LPA_A1].[C012] = @ParentId2        --Inherited Field
[...]

Do I anything wrong?

Hi,

Did you regenerate the generated code with the latest version ?

Oh sure, this should I do. I will do this at first tomorrow morning. ^^

Hi. I have re-generate the project, but I get the same error.

In v2.0 there is a known issue with inheritance where two entities are involved which have the same parent: it uses the entity the field is defined in as an index in the set of aliases to pick the artificial alias (it uses artificial aliases because inheritance can mean you have multiple tables referred by the same entity alias)

THis goes wrong when the field is defined in the SUPERtype but the field used is actually from a subtype. In your case, the ID field is in the supertype ArchivEntity, while the FK is in the subtype.

We tried to fix this in v2.5 by introducing an architectural change which we couldn't add to v2.0 because it would break code. I'll use your setup as a test on v2.0 and v2.5 to see if there's a workaround for v2.0 and to see if our code is indeed enough to fix your scenario.

(edit). Hmm... wrong inheritance hierarchy setup. NEXT time, it would be really great if you could simply provide all the info, like inheritance hierarchy type, which fields are inherited by which entity etc.

Hmm, something's odd with your inheritance definition: your relation between parent and child isn't defined in the supertype? (While the parent is defined there) ?

If not, how is it defined? From datei (file) to mappe (folder) and from mappe to mappe?

(edit) I've created the following hierarchy: (TargetPerEntityHierarchy) FolderFile (abstract, supertype). Fields: ID, Name, ParentID, Discriminator Folder (subtype) File (subtype) Fields: Filesize

Folder has a relation with self, using ParentID File has a relation with Folder, using ParentID.

With this, I can reproduce it:

[Test]
public void SubtypesWithRelationAndSameParentTest()
{
    // get all folders which have files with a size >= 200.
    EntityCollection<FolderEntity> folders = new EntityCollection<FolderEntity>();
    RelationPredicateBucket filter = new RelationPredicateBucket();
    filter.Relations.Add(FolderEntity.Relations.FileEntityUsingParentId, "File");
    filter.PredicateExpression.Add(FileFields.Filesize.SetObjectAlias("File") >= 200);

    using(DataAccessAdapter adapter = new DataAccessAdapter())
    {
        adapter.FetchEntityCollection(folders, filter);
    }

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

The sad thing is: v2.5 also fails. Looking into it.

I managed to fix it in 2.5. I'll see if I can backport it to v2.0. In v2.5 you also shouldn't have to alias this, but it still fails if you don't so that's another bug.

The other situation is if you have a subtype of ArchivDatei and filter on a field in that, that then should also pickup the alias properly which it doesn't

I fixed it in v2.0 as well. Due to an architectural change I had to make I couldn't port back all the fixes of v2.5 back to v2, but this fix will solve your problem. I'll attach the new ormsupportclasses build shortly.

(edit) Fixed ormsupportclasses dl is attached. Could you give it a spin, please?

Hello Otis,

sorry for my late answer. It works. Now it generate the right alias. Thank you very much.

I only have one question: Would the fixed ormsupportclasses become an offical ormsupportcalsses?

Best regards andré

Sure. Each new release contains all the fixes that took place since the last release.

Sorry that I have to disturb again. I don't know if I should open a new Thread, but I write my request first down.

I have a problem with the maxNumberOfItemsToReturn Parameter, when I fetch the Collection.

I want to have a "SELECT TOP 100 ... ".

da.FetchEntityCollection(archivMappeCollection, filter, 100)

When I build the filter without Relations, then the generated Query contains the Top-Clausel. When I build the filter with Relations, the Query doesn't contains the Top-Clausel.

Please start a new thread.

Thanks.