SCIMSchemaAttributeModel does not always have SchemaId column set

[bzuidgeest]

It seems that in SCIMSchemaAttributeModel, the SchemaId column is not set for subattributes with the schema Id but just null. I can use a recursive query to get the complete list of attributes in a schema. But having schemaId always filled with schemaId would make getting a complete attribute list for a schema much more efficient.

Same goes for RepresentationId in SCIMRepresentationAttributeLst. If it was filled in everywhere, I could do the query for getting all attributes without recursion, making the example query below more simple.

;WITH RepresentationAttribute 
AS
(
    SELECT * 
	FROM [SCIMRepresentationAttributeLst]
	WHERE RepresentationId IS NOT NULL
    
	UNION ALL

    SELECT child.Id,
		child.ParentId,
		child.SchemaAttributeId,
		CASE WHEN Child.RepresentationId IS NULL THEN parent.RepresentationId ELSE Child.RepresentationId END AS RepresentationId  
	FROM [SCIMRepresentationAttributeLst] AS child
		INNER JOIN RepresentationAttribute AS parent ON child.parentId = parent.id
)
SELECT 
	[SCIMRepresentationLst].Id,
	[SCIMRepresentationLst].ExternalId,
	RepresentationAttribute.*
	,SCIMRepresentationAttributeValueLst.*
	,SCIMSchemaAttributeModel.*
FROM [SCIM].[dbo].[SCIMRepresentationLst]
	inner join RepresentationAttribute on [SCIMRepresentationLst].Id = RepresentationAttribute.RepresentationId
	inner join SCIMSchemaAttributeModel on RepresentationAttribute.SchemaAttributeId = SCIMSchemaAttributeModel.Id
	left outer join SCIMRepresentationAttributeValueLst ON RepresentationAttribute.Id = SCIMRepresentationAttributeValueLst.SCIMRepresentationAttributeId

This might not mean much for the .NET side of things, but might be useful for further processing on the SQL side. Also if this fouls something on the .NET side of things just close as wont implement as this is not that important

[simpleidserver]

The SchemaId and RepresentationId columns have been set, you can take the latest version from the branch origin/release/1.1.0.
The release number has been upgrated because we made breaking changes on the database.

Note : the table SCIMSchemaAttribute must be truncated before launching the solution, otherwise the SchemaId column will not be set with the correct value.

[bzuidgeest]

I have tested it and now I can simply do

SELECT 
	[SCIMRepresentationLst].Id,
	[SCIMRepresentationLst].ExternalId,
	[SCIMRepresentationAttributeLst].*
	,SCIMRepresentationAttributeValueLst.*
	,SCIMSchemaAttributeModel.*
FROM [SCIM].[dbo].[SCIMRepresentationLst]
	inner join [SCIMRepresentationAttributeLst] on [SCIMRepresentationLst].Id = [SCIMRepresentationAttributeLst].RepresentationId
	inner join SCIMSchemaAttributeModel on [SCIMRepresentationAttributeLst].SchemaAttributeId = SCIMSchemaAttributeModel.Id
	left outer join SCIMRepresentationAttributeValueLst ON [SCIMRepresentationAttributeLst].Id = SCIMRepresentationAttributeValueLst.SCIMRepresentationAttributeId
order by ExternalId

to get all attributes for any model. Much simpler.