Frans,

I'm sure I saw a way to do this before, but I can't seem to find it on the forums...

I'm trying to create an INNER JOIN between 2 non related columns neither of which are PKs in their own right.

The JOIN I'm looking for is:

SELECT   dbo.FolderLink.ParentFolderUID
FROM         dbo.Permission INNER JOIN
                      dbo.FolderLink ON dbo.Permission.ResourceUID = dbo.FolderLink.ParentFolderUID

SQL Server doesn't let me create such a relation, I guess since they are part of a composite PK and the "Add New Custom Relation m:n Relation" feature in the Designer (which I've only just discovered ) gives me blank combo boxes stopping me using it.

Here's my failed attempt:

IEntityRelation relation = new EntityRelation(RelationType.ManyToMany);
relation.AddEntityFieldPair(EntityFieldFactory.Create(PermissionFieldIndex.GroupUID), EntityFieldFactory.Create(FolderLinkFieldIndex.ParentFolderUID));

Any ideas how to create this in LLBLGen?

Marcus

Tables for your reference:

CREATE TABLE [dbo].[FolderLink] (
    [ParentFolderUID] [uniqueidentifier] NOT NULL ,
    [ChildFolderUID] [uniqueidentifier] NOT NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[Permission] (
    [ResourceUID] [uniqueidentifier] NOT NULL ,
    [GroupUID] [uniqueidentifier] NOT NULL ,
    [SkinAccountUID] [uniqueidentifier] NOT NULL ,
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[FolderLink] ADD 
    CONSTRAINT [PK_FolderLink] PRIMARY KEY  CLUSTERED 
    (
        [ParentFolderUID],
        [ChildFolderUID]
    )  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[Permission] ADD 
    CONSTRAINT [PK_Permission] PRIMARY KEY  CLUSTERED 
    (
        [ResourceUID],
        [GroupUID],
        [SkinAccountUID]
    )  ON [PRIMARY] 
GO

Dont use the type manytomany. as a manytomany relation is always build on top of 2 m:1 relations. So define an 1:n EntityRelation object and specify one of the fields as the PK field and pass that EntityRelation object to the fetch logic

(ManytoMany type is ignored by fetch logic)

Thanks Frans, but I've been trying numerous combinations without success

The full logic I'm using is:

IEntityRelation relation = new EntityRelation(RelationType.OneToMany);
relation.AddEntityFieldPair(EntityFieldFactory.Create(PermissionFieldIndex.GroupUID), EntityFieldFactory.Create(FolderLinkFieldIndex.ParentFolderUID));
relation.StartEntityIsPkSide = true;

IRelationPredicateBucket bucket = new RelationPredicateBucket();
bucket.PredicateExpression.Add(PredicateFactory.CompareRange(FolderLinkFieldIndex.ChildFolderUID, childFolderUIDs));        
bucket.PredicateExpression.Add(PredicateFactory.CompareRange(PermissionFieldIndex.GroupUID, groupUIDs));
bucket.Relations.Add(relation);

EntityCollection folderLinkEntities = new EntityCollection(new FolderLinkEntityFactory());
adapter.FetchEntityCollection(folderLinkEntities, bucket);

Which generate the following SQL which contains a syntax error "Incorrect syntax near the keyword 'And'." (found at the start of the WHERE clause).

exec sp_executesql N'SELECT [Permission].[ResourceUID] AS [ResourceUID],[Permission].[GroupUID] AS [GroupUID],[Permission].[SkinAccountUID] AS [SkinAccountUID],[Permission].[CanCreate] AS [CanCreate],[Permission].[CanRead] AS [CanRead],[Permission].[CanUpdate] AS [CanUpdate],[Permission].[CanDelete] AS [CanDelete],[Permission].[CanChangePermissions] AS [CanChangePermissions],[Permission].[CanPurchase] AS [CanPurchase],[Permission].[CanShare] AS [CanShare],[Permission].[Protection] AS [Protection],[Permission].[OwnerUID] AS [OwnerUID],[Permission].[Timestamp] AS [Timestamp] FROM [Permission] WHERE ( And [Permission].[GroupUID] IN (@GroupUID1, @GroupUID2, @GroupUID3, @GroupUID4) And [Permission].[SkinAccountUID] = @SkinAccountUID5) ORDER BY [Permission].[ResourceUID] ASC', N'@GroupUID1 uniqueidentifier,@GroupUID2 uniqueidentifier,@GroupUID3 uniqueidentifier,@GroupUID4 uniqueidentifier,@SkinAccountUID5 uniqueidentifier', @GroupUID1 = '5D93106C-FA0A-4913-A59D-792A9D3A1C0D', @GroupUID2 = 'BA060CE1-3733-44ED-A550-F63B34C2F76B', @GroupUID3 = '94E31AA3-A0B7-46E4-A40A-F43C9266DB03', @GroupUID4 = 'FD872A62-CD27-49A7-9946-A9A361BFDFDB', @SkinAccountUID5 = 'AA5BCACF-1FDB-4FA8-BF3F-E3876B27A866'

UPDATE... and I just noticed... this is fetching the wrong table and putting in an ORDER BY????

UPDATE 2... I've also tried swapping the order of the fields in AddEntityFieldPair which makes no difference.

UPDATE 3... SORRY... I think the code above is actually working... My Bad Thanks for your help... I took the wrong SQL from the Profiler!!!

Marcus

heh Ok.

Also try the new tracing functionality in 1.0.2004.2, which gives you better feedback on the queries generated and the values specified

Otis wrote:

Also try the new tracing functionality in 1.0.2004.2, which gives you better feedback on the queries generated and the values specified

Definately... I've been dying to upgrade to 2004.2 but I have a load of custom templates stuff and task performers that need to be migrated . In the last few months I just haven't had the luxury of spare time and I was waiting until you hit RC1, (which I now see you have!!) before I ventured into the new unknown.

Will definately upgrade this week.

Cheers, Marcus

Marcus wrote:

Otis wrote:

Also try the new tracing functionality in 1.0.2004.2, which gives you better feedback on the queries generated and the values specified

Definately... I've been dying to upgrade to 2004.2 but I have a load of custom templates stuff and task performers that need to be migrated .

Task performers as in classes don't need to be upgraded, and most templates will work perfectly fine. I kept project.Catalog for backwards compatibility in templates . Though there will be some issues, most of them in the typedlist area. Though I think most issues in templates are rather small.