Getting space used by tables using Entity Framework Core

Question

I've been using the following T-SQL to get the space used (in megabytes) of the tables in my database:

SELECT 
    t.NAME AS TableName,
    CAST(ROUND(((SUM(a.total_pages) * 8) / 1024.00), 2) AS NUMERIC(36, 2)) AS TotalSpaceMB
FROM 
    sys.tables t
INNER JOIN      
    sys.indexes i ON t.OBJECT_ID = i.object_id
INNER JOIN 
    sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
INNER JOIN 
    sys.allocation_units a ON p.partition_id = a.container_id
LEFT OUTER JOIN 
    sys.schemas s ON t.schema_id = s.schema_id
WHERE 
    t.NAME NOT LIKE 'dt%' 
    AND t.is_ms_shipped = 0
    AND i.OBJECT_ID > 255 
GROUP BY 
    t.Name, s.Name, p.Rows
ORDER BY 
    t.Name

The result looks as follows:

Is there a good way to perform the equivalent using EF Core?

I looked through the Raw SQL Queries docs but the examples there didn't seem to be applicable as they mostly pertain to retrieving entities.

Answer 1

We can definitely make this work, using a Raw SQL Query.

First things first, when we run this query, it would be better to get the results back in a type of class that Entity Framework can work with. I have tried retrieving arbitrary query results back and found the results to tricky to parse. My advice is to make a class to describe the results you'll get back from a RawSQL Query.

So wherever you are storing your models for this project (or in a new file even) we'll make a new class called SpaceCalculations and decorate it with the [NotMapped] attribute to prevent EF Core from trying to make this into a table.

[NotMapped]
    public class SpaceCalculations
    {
        public string TableName { get; set; }
        public decimal TotalSpaceMB { get; set; }
    }

Next, we need to register this class in our DBContext. Where you're definitng your various Dbset<Type> records in your DBContext, add the following:

public DbQuery<SpaceCalculations> SpaceCalculations { get; set; }

Finally, to retrieve the data itself, I just place this lazily into my Home\Index controller action.

public IActionResult Index()
        {
            List<SpaceCalculations> SpaceData = _context.SpaceCalculations.
                 FromSql(@"YOUR LONG SQL STATEMENT HERE").ToList();
            ViewBag.SpaceInfo = SpaceData;
            return View();
        }

Then, to render the info, I created the following in my Index.cshtml file.

<h2>Table Space for table ....</h2>
<table>
    <tr>
        <th>
            TableName
        </th>
        <th>
            TableSpace
        </th>
        </tr>
        @foreach (SpaceCalculations SpaceRecord in ViewBag.SpaceInfo)
        {
        <tr>
            <td>
                @SpaceRecord.TableName
            </td>
            <td>
                @SpaceRecord.TotalSpaceMB
            </td>
    </tr>
    }
</table>

And the finished result!

You can read more about this here.

Answer 2

You could use QueryTypes and Raw SQL Queries together.

First, create a class to represent your results and to encapsulate the SQL query. You can organize the following code however you like, but this should get the idea across.

    public class DatabaseTableSize
    {
        public class View
        {
            public string TableName { get; private set; }
            public decimal TableSizeMb { get; private set; }
        }

        public static async Task<List<View>> Query(YourDbContextType dbContext)
        {
            return await dbContext.DatabaseTableSizeView
                .FromSql(@"SELECT
                            t.NAME AS TableName,
                            CAST(ROUND(((SUM(a.total_pages) * 8) / 1024.00), 2) AS NUMERIC(36, 2)) AS TableSizeMb
                        FROM sys.tables t
                        INNER JOIN sys.indexes i ON t.OBJECT_ID = i.object_id
                        INNER JOIN sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
                        INNER JOIN sys.allocation_units a ON p.partition_id = a.container_id
                        LEFT OUTER JOIN sys.schemas s ON t.schema_id = s.schema_id
                        WHERE
                            t.NAME NOT LIKE 'dt%'
                            AND t.is_ms_shipped = 0
                            AND i.OBJECT_ID > 255
                        GROUP BY
                            t.Name, s.Name, p.Rows
                        ORDER BY
                            t.Name")
                .ToListAsync();
        }
    }

Then add the QueryType definition to your DbContext

public DbQuery<DatabaseTableSize.View> DatabaseTableSizeView { get; set; }

Then wherever you want the data... call it

var results = await DatabaseTableSize.Query(dbContext);