Types are cornerstone of application programming.  A .NET developer works with the CLR type system by programming the managed types.  Some of the examples of CLR managed types are: Class, Structure, Enumeration, Delegate and Interface.     A namespace is a user-defined scope in which managed types are defined. Most of the CLR built-in types are defined within the System namespace, such as System.Object, System.Int32, and System.String.

The following ER diagram shows the relationship between different entities related to the definition of a CLR Type.  

Type Definitions

The TypeDefinitions entity is used to represent details of a type that belongs to a particular assembly.  It “extends” from Types entity, just like TypeReferences does. It provides metadata about a type definition including access modifier (public, private, etc.), kind (class, interface, etc.), base type, containing type, etc.  Let's take an example of a type definition for class Person: the class can be abstract and can have properties, methods and derive from other classes.  A reference to the class Person defined in some external assembly, will only have its metadata stored in TypeReferences entity.

 

The following table shows some of the queries you can perform over these set of entities.

Note: You can scope these queries to an individual assembly or repository folder by doing joins over Types, Modules, Assemblies and Folders.

Description

T-SQL Query

Show all distinct namespaces scoped by folder ‘/Build/1.0.2.4’

SELECT distinct TD.Namespace, N.Name as NamespaceName, MO.Name as ModuleName

FROM [Repository].System_Runtime.TypeDefinitions AS TD

LEFT JOIN [Repository].System_Runtime.Types AS T ON TD.Id = T.Id

LEFT JOIN [Repository].System_Runtime.Modules AS MO ON T.Module = MO.Id

LEFT JOIN [Repository].System_Runtime.Namespaces AS N ON TD.Namespace = N.Id

WHERE MO.Folder = [Repository].[Repository.Item].PathsFolder('/Build/1.0.2.4')

Show all classes in the repository

SELECT T.*, TD.*

FROM [Repository].System_Runtime.TypeDefinitions AS TD

LEFT JOIN [Repository].System_Runtime.Types AS T ON TD.Id = T.Id

WHERE Kind = 1

Show all interfaces in the repository

SELECT T.*, TD.*

FROM [Repository].System_Runtime.TypeDefinitions AS TD

LEFT JOIN [Repository].System_Runtime.Types AS T ON TD.Id = T.Id

WHERE Kind = 5

Show all enumeration types in the repository

SELECT T.*, TD.*

FROM [Repository].System_Runtime.TypeDefinitions AS TD

LEFT JOIN [Repository].System_Runtime.Types AS T ON TD.Id = T.Id

WHERE Kind = 3

Show all abstract types (classes and interfaces) in the repository

SELECT T.*, TD.*

FROM [Repository].System_Runtime.TypeDefinitions AS TD

LEFT JOIN [Repository].System_Runtime.Types AS T ON TD.Id = T.Id

WHERE TD.IsAbstract = 1

Show all interfaces that have no methods

SELECT *

FROM [Repository].System_Runtime.Types AS T

LEFT JOIN [Repository].System_Runtime.TypeDefinitions AS TD ON TD.Id = T.Id

WHERE TD.Kind = 5 AND

TD.Id NOT IN

(

SELECT distinct T.Id

FROM [Repository].System_Runtime.MethodDefinitions AS MD

LEFT JOIN [Repository].System_Runtime.Methods AS M ON MD.Id = M.Id

LEFT JOIN [Repository].System_Runtime.Types AS T ON T.Id = M.DeclaringType

LEFT JOIN [Repository].System_Runtime.TypeDefinitions AS TD ON T.Id = TD.Id

WHERE TD.Kind = 5

)

Show the types and count of methods in each type

SELECT T.Id, T.QualifiedName, COUNT(M.Id) AS MethodCount

FROM [Repository].System_Runtime.MethodDefinitions AS MD

LEFT JOIN [Repository].System_Runtime.Methods AS M ON MD.Id = M.Id

LEFT JOIN [Repository].System_Runtime.Types AS T ON T.Id = M.DeclaringType

GROUP BY T.Id, T.QualifiedName

Show the interfaces and count of methods in each interface

SELECT T.Id, T.QualifiedName, COUNT(M.Id) AS MethodCount

FROM [Repository].System_Runtime.MethodDefinitions AS MD

LEFT JOIN [Repository].System_Runtime.Methods AS M ON MD.Id = M.Id

LEFT JOIN [Repository].System_Runtime.Types AS T ON T.Id = M.DeclaringType

LEFT JOIN [Repository].System_Runtime.TypeDefinitions AS TD ON T.Id = TD.Id

WHERE TD.Kind = 5

GROUP BY T.Id, T.QualifiedName

Show all types that contain other types

SELECT T.Name AS Type, TD.Kind, T2.Name AS ContainingType

FROM [Repository].System_Runtime.TypeDefinitions AS TD

LEFT JOIN [Repository].System_Runtime.Types AS T ON T.Id = TD.Id

LEFT JOIN [Repository].System_Runtime.Types AS T2 ON T2.Id = TD.ContainingType

WHERE TD.ContainingType IS NOT NULL

Show all types that have a base type

SELECT T.Name AS Type, TD.Kind, TD.IsAbstract, T2.Name AS BaseType

FROM [Repository].System_Runtime.TypeDefinitions AS TD

LEFT JOIN [Repository].System_Runtime.Types AS T ON T.Id = TD.Id

LEFT JOIN [Repository].System_Runtime.Types AS T2 ON T2.Id = TD.BaseType

WHERE TD.BaseType IS NOT NULL

Show all methods for interface “IFoo” in folder “/Build/2.0.0.0”

SELECT T.Name, M.Name, *

FROM [Repository].[System_Runtime].[Types] as T

LEFT JOIN [Repository].[System_Runtime].[Methods] as M ON M.DeclaringType = T.Id

WHERE T.Name = 'IFoo' And

T.Folder = [Repository].[Repository.Item].PathsFolder('/Build/2.0.0.0')

 

Implemented Interfaces       

The ImplementedInterfaces entity keeps record of types that implement a particular interface. 

I am going to use “M” query language and define a SQL Server table-valued function to demonstrate the examples in this section.   Once the table-valued functions are available in the database, you can call these functions in SQL Server Management Studio (SSMS) similar to queries over tables and views. 

Description

T-SQL Query

Show all assemblies in repository that provide interface by name “IFoo”

SELECT * FROM [Repository].[System.Runtime.Analysis].[GetAssembliesThatProvideInterfaceByName]('IFoo')

 

    “M” Query Implementation

    GetAssembliesThatProvideInterfaceByName(interfaceName : Text)

    {

        (from interface in Types where interface.Name == interfaceName

        from interfaceSignature in TypeSignatures where interfaceSignature.TypeDefinition == interface

        from ii in ImplementedInterfaces where ii.Interface == interfaceSignature

        from class in TypeDefinitions where ii.Type == class

        from classType in Types where class.Id == classType

        from _module in Modules where classType.Module == _module

        from assembly in Assemblies where _module.Assembly == assembly

        from folder in FoldersTable where assembly.Folder == folder

        select

        {

            AssemblyId => assembly.Id,

            AssemblyName => assembly.Name,

            FolderName => folder.Name

        }).Distinct   

    }

Show all assemblies in repository that require interface by name “IFoo”

SELECT * FROM [Repository].[System.Runtime.Analysis].[GetAssembliesThatRequireInterfaceByName]('IFoo')

 

    “M” Query Implementation

    GetAssembliesThatRequireInterfaceByName(interfaceName : Text)

    {

        (from interface in Types where interface.Name == interfaceName

        from iMethod in Methods where iMethod.DeclaringType == interface

        from calledMethod in CalledMethods where calledMethod.Callee == iMethod

        from classMethod in Methods where calledMethod.Caller == classMethod

        from classType in Types where classMethod.DeclaringType == classType

        from _module in Modules where classType.Module == _module

        from assembly in Assemblies where _module.Assembly == assembly

        from folder in FoldersTable where assembly.Folder == folder

        select

        {

            AssemblyId => assembly.Id,

            AssemblyName => assembly.Name,

            FolderName => folder.Name

        }).Distinct   

    }

 

“M” alternative to writing Queries

Here are the steps involved in making these functions available in the database.

Step 1: Author “M” file. 

Provide a module name and then add the “M” functions within this module.  Import any modules as necessary.  See attached file.

module System.Runtime.Analysis

{

    import System_Runtime;

    import Repository.Item;

 

    function1(…) ...

 

    function2(…) ...

}

 

Step 2: Compile the “M” file to produce a database compliant package

Use the “M” compiler to compile the “M” source into a SQL package.  (It will be nice if “M” compiler could produce a DAC package.)

m.exe ClrAnalysisQueries.m /r:System.Runtime.mx,repository.mx /o:CLRAnalysisQueries.mx

Microsoft (R) Codename "M" Compiler version 1.0.1949.0

Copyright (c) Microsoft Corporation. All rights reserved.

 

Step 3: Load the data package into a database

Install the data package into the target database. 

mx.exe install ClrAnalysisQueries.mx /d:Repository

Microsoft (R) Codename "M" Command-line Utility version 1.0.1949.0

Copyright (c) Microsoft Corporation. All rights reserved.

 

 

(0,0): message : Installing: CLRAnalysisQueries;Version=1.0;Locale=neutral...

(0,0): message : Installed: CLRAnalysisQueries;Version=1.0;Locale=neutral

 

Summary

Use the TypeDefinitions and related entities to understand details about a particular class, interface or enumeration implementation in an assembly or folder.   Besides using the “M” language for defining the schema (Tables and Views), you can also use it to define utility functions over one or more SSMoS schemas.