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.
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.
Show all distinct namespaces scoped by folder ‘/Build/220.127.116.11’
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/18.104.22.168')
Show all classes in the repository
SELECT T.*, TD.*
WHERE Kind = 1
Show all interfaces in the repository
WHERE Kind = 5
Show all enumeration types in the repository
WHERE Kind = 3
Show all abstract types (classes and interfaces) in the repository
WHERE TD.IsAbstract = 1
Show all interfaces that have no methods
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
GROUP BY T.Id, T.QualifiedName
Show the interfaces and count of methods in each interface
Show all types that contain other types
SELECT T.Name AS Type, TD.Kind, T2.Name AS ContainingType
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
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/22.214.171.124”
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/126.96.36.199')
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.
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
AssemblyId => assembly.Id,
AssemblyName => assembly.Name,
FolderName => folder.Name
Show all assemblies in repository that require interface by name “IFoo”
SELECT * FROM [Repository].[System.Runtime.Analysis].[GetAssembliesThatRequireInterfaceByName]('IFoo')
GetAssembliesThatRequireInterfaceByName(interfaceName : Text)
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
“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.
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
(0,0): message : Installing: CLRAnalysisQueries;Version=1.0;Locale=neutral...
(0,0): message : Installed: CLRAnalysisQueries;Version=1.0;Locale=neutral
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.