System.Runtime Schema: Assembly, Type and Method References

As a continuation of my previous post on exploring relational schema for .NET Assemblies, let me drill-into a few more entities in the System.Runtime data model.

.NET applications are made up of multiple assemblies. Assemblies reference assemblies. Assemblies use types and methods defined in other assemblies. Each of referenced assemblies can reference other assemblies.

Let's see the structure of following entities in the System.Runtime model that provide information on ‘what’ references ‘what’.

· AssemblyReferences

· TypeReferences

· MethodReferences

I will showcase the intent and relationship of these entities (use the following ER diagram as a reference) through some sample queries. I have chosen to use T-SQL in SQL Server Management Studio (SSMS) for this post. You are welcome to use the SQL query language of your choice, including ADO.NET or “M” Query.

Assembly References

A .NET assembly can depend on other .NET assemblies. The SQL Server Modeling Services System.Runtime model supports a structure for storing dependencies across assemblies. The LoadAssembly.exe utility allows you to load dependencies of an assembly into the database. The tool also provides option to do transitive closure over the dependencies, in order to load not only the immediate dependencies but also their referenced assemblies. The AssemblyReferences entity in the model provides a way to query an assembly’s dependencies on other dependencies.

Description	Query
Show all assemblies references for an assembly “Foo”   (Show all assemblies that "Foo" assembly depends upon)	SELECT AR.* FROM [Repository].System_Runtime.Assemblies AS A INNER JOIN [Repository].System_Runtime.AssemblyReferences AS AR ON AR.Assembly = A.Id WHERE A.Name = 'Foo'
Show all assemblies that reference an assembly “System.Data.Entity”	SELECT A.* FROM [Repository].System_Runtime.Assemblies AS A INNER JOIN [Repository].System_Runtime.AssemblyReferences AS AR ON AR.Assembly = A.Id WHERE AR.Name = 'System.Data.Entity'

 

Type References

When a developer is creating a .NET application, at minimum, she refers to types such as System.Object defined in mscorlib.dll. Most software development best practices advocate factoring of code into multiple libraries. The TypeReferences entity in the model is used to determine whether a Type being used in an assembly is a referenced type. Types entity is the “base” for both TypeReferences and TypeDefinitions entities and use the table-per-type patterns. The IsReference attribute in the Types entity is used to filter on referenced and defined types.

Description	Query
Show all types in repository that are references to types defined in other assemblies	SELECT * FROM [Repository].System_Runtime.Types WHERE IsReference = 1
Show type name, type qualified name and referenced assembly name for types that are being referenced from assembly “Foo”	SELECT T.Name, T.QualifiedName, AR.Name FROM [Repository].System_Runtime.AssemblyReferences AS AR LEFT JOIN [Repository].System_Runtime.TypeReferences AS TR ON TR.AssemblyReference = AR.Id LEFT JOIN [Repository].System_Runtime.Types AS T on T.Id = TR.Id WHERE AR.Name = 'Foo'
Show all types (and assemblies they belong to) in folder “/Sample” that reference any types defined in assembly that is prefixed with “Microsoft.”	SELECT T.QualifiedName as ReferencedType, A.Name as MyApp, AR.Name as ReferencedAssembly FROM [Repository].System_Runtime.AssemblyReferences AS AR LEFT JOIN [Repository].System_Runtime.TypeReferences AS TR ON TR.AssemblyReference = AR.Id LEFT JOIN [Repository].System_Runtime.Types AS T on T.Id = TR.Id LEFT JOIN [Repository].System_Runtime.Modules AS M on T.Module = M.Id LEFT JOIN [Repository].System_Runtime.Assemblies AS A on M.Assembly = A.Id WHERE AR.Name like 'Microsoft.%' AND A.Folder = [Repository].[Repository.Item].PathsFolder('/Sample')
Show assembly name, module name and type name for all types in assembly “Foo” that are references to types defined in other assemblies.	SELECT A.Name AS AssemblyName,M.Name as ModuleName, T.Name as TypeName FROM [Repository].System_Runtime.Assemblies AS A LEFT JOIN [Repository].System_Runtime.Modules M ON M.Assembly = A.Id LEFT JOIN [Repository].System_Runtime.Types T ON T.Module = M.Id WHERE T.IsReference = 1 AND A.Name = 'Foo’

 

Method References

Just as an assembly references other assemblies, you may want to know which methods are actually referenced from other assemblies. The MethodReferences entity in the model is used to determine whether a Type being used in an assembly is a referenced type. The MethodReferences entity “extends” from Methods entity. The IsReference attribute in the Methods entity is used to filter on referenced and defined methods.

Description

Query

Show all methods in module “Foo.dll” that are references to methods in other assemblies

SELECT T.QualifiedName as TypeName, M.Name as MethodName FROM [Repository].System_Runtime.MethodReferences AS MR INNER JOIN [Repository].System_Runtime.Methods AS M ON M.Id = MR.Id INNER JOIN [Repository].System_Runtime.Types AS T ON T.Id = M.DeclaringType INNER JOIN [Repository].System_Runtime.Modules AS MO ON MO.Id = T.Module INNER JOIN [Repository].System_Runtime.Assemblies AS A ON A.Id = MO.Assembly WHERE MO.Name = 'Foo.dll'

 

Called Methods

In SSMoS System.Runtime model, we store two types of method references – called methods and method overrides. When an application in assembly “Foo” is calling a method “X” in another assembly “Bar”, an instance will be added in the MethodReferences table for the called method. Note that any call that can only be determined at runtime (such as virtual method and dynamic (reflection-based) calls) won’t appear in the CalledMethods table.

Description	Query
Show all distinct methods in repository that call a certain method > 50 times	SELECT distinct M.* FROM [Repository].[System_Runtime].CalledMethods AS CM INNER JOIN [Repository].System_Runtime.Methods AS M ON CM.Caller = M.Id WHERE CM.CallCount > 50
Show all distinct methods in repository that are called by other methods > 50 times	SELECT distinct M.* FROM [Repository].[System_Runtime].CalledMethods AS CM INNER JOIN [Repository].System_Runtime.Methods AS M ON CM.Callee = M.Id WHERE CM.CallCount > 50
Show type and method with corresponding call count where the call count > 50 times	SELECT M.Id, T.QualifiedName as TypeName, M.Name as CallerMethod, MCallee.Name as CalleeMethod, CM.CallCount, M.IsReference FROM [Repository].[System_Runtime].CalledMethods AS CM INNER JOIN [Repository].System_Runtime.Methods AS MCallee ON CM.Callee = MCallee.Id INNER JOIN [Repository].System_Runtime.Methods AS M ON CM.Caller = M.Id INNER JOIN [Repository].System_Runtime.Types AS T ON M.DeclaringType = T.Id WHERE CM.CallCount > 50
Show all types in repository that have dead methods (i.e. methods that are not called by anyone)	SELECT distinct T.* FROM [Repository].System_Runtime.Methods AS M INNER JOIN [Repository].System_Runtime.Types AS T ON M.DeclaringType = T.Id WHERE M.Id NOT IN (SELECT Callee FROM [Repository].System_Runtime.CalledMethods)

 

Summary

In this post, I showed you how to use few entities in System.Runtime model to find relationships across objects in the CLR domain. You may have observed that we have made some choices on how to model relationships between assemblies, types and methods. We put this mental effort on designing the schema once, so you don't have to do it again and again, for cases where you needed a granular CLR schema in the database. Even though I used the raw T-SQL queries in this post, we don't expect all end-users to be sophisticated T-SQL users.  The design of the schema is very domain-specific and unless the user is very familiar with the domain, doing queries over that domain can be difficult without understanding the data structure and their relationships. In the limit, we expect that only ISVs and developers have to know this level of detail so they can provide a friendlier tooling experience to the end-users.