Codefluent Load All Unreferenced Record

Question

I'm trying to load all "tag" records that's not referenced in the "FileObjectMetaData" table with CodeFluent.Basically converting the query from this post to CodeFluent:

Elegant way to delete rows which are not referenced by other table

I have this table relationship:

How might I be able to achieve this effect?

Edit One:

It's a many to many relationship in the CodeFluent model, so in the actual generated SQL tables there's a middle linking table that contains:

• Tag_ID
• FileObjectMetaData_ID

The Tag and FileObjectMetaData table itself doesn't have a foreign key to each other directly.

Edit Two:

In the end, I chose to take the C# approach to this, as there doesn't seem to be a solid way using CodeFluent CFQL.

public static TagCollection LoadUnusedTags()
{
    TagCollection unusedTags = new TagCollection();
    TagCollection allTags = TagCollection.LoadAll();
    foreach (Tag aTag in allTags)
    {
        FileObjectMetaDataCollection fileObjectMetaDatas = FileObjectMetaDataCollection._LoadByTag(aTag.Name);
        if (fileObjectMetaDatas.Count < 1)
            unusedTags.Add(aTag);
    }
    if (unusedTags.Count < 1)
        return null;
    return unusedTags;
}

Answer 1

Solution 1: using a CFQL method

LOAD() RAW

SELECT $Tag{Columns}$
FROM $Tag$
WHERE $Tag::Id$ NOT IN 
      (
      SELECT [Tag_Id] 
      FROM [FileObjectMetadata_Tag_Tag]
      WHERE [Tag_Id] IS NOT NULL
      )

This will produce the following stored procedure:

CREATE PROCEDURE [dbo].[Tag_LoadUnused]()
AS
SET NOCOUNT ON
SELECT [Tag].[Tag_Id],[Tag].[Tag_Name],[Tag].[_trackLastWriteTime],[Tag].[_trackCreationTime],[Tag].[_trackLastWriteUser],[Tag].[_trackCreationUser],[Tag].[_rowVersion]
FROM Tag
WHERE [Tag].[Tag_Id] NOT IN 
      (
      SELECT [Tag_Id] 
      FROM [FileObjectMetadata_Tag_Tag]
      WHERE [Tag_Id] IS NOT NULL
      )
RETURN
GO

Solution 2: Using a view

You can create a view that do the same as the above CFQL method:

Then you can use this view from a CFQL method:

load() from UsusedTag

The generated SQL view and stored procedure:

CREATE VIEW [dbo].[vTagUsusedTag]
AS
SELECT [Tag].[Tag_Id],[Tag].[Tag_Name],[Tag].[_trackLastWriteTime],[Tag].[_trackCreationTime],[Tag].[_trackLastWriteUser],[Tag].[_trackCreationUser],[Tag].[_rowVersion]
FROM Tag
WHERE [Tag].[Tag_Id] NOT IN 
      (
      SELECT [Tag_Id] 
      FROM [FileObjectMetadata_Tag_Tag]
      WHERE [Tag_Id] IS NOT NULL
      )
GO

CREATE PROCEDURE [dbo].[Tag_LoadFromView]()
AS
SET NOCOUNT ON
SELECT DISTINCT [vTagUsusedTag].[Tag_Id], [vTagUsusedTag].[Tag_Name] 
    FROM [vTagUsusedTag]    
RETURN
GO