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’.
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.
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.
Show all assemblies references for an assembly “Foo”
(Show all assemblies that "Foo" assembly depends upon)
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”
WHERE AR.Name = 'System.Data.Entity'
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.
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
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
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’
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.
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'
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.
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
INNER JOIN [Repository].System_Runtime.Methods AS M ON CM.Callee = M.Id
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
INNER JOIN [Repository].System_Runtime.Methods AS MCallee ON CM.Callee = MCallee.Id
INNER JOIN [Repository].System_Runtime.Types AS T ON M.DeclaringType = T.Id
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
WHERE M.Id NOT IN
(SELECT Callee FROM [Repository].System_Runtime.CalledMethods)
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.