Get FileStream size (sql server) by mvc app context asp net mvc

I would like if .net (asp.net mvc) provides possibility to get size of FileStream having DbContext. As I did research so far I can:

1) SELECT SUM(DATALENGTH('ColumnName'))FROM Table

2)

SELECT DB_NAME() AS [DBNAME], size / 128 AS [Size in MB]
FROM sys.database_files
WHERE type = 2;

My question is if I can calculate filestream using C#. I have connection to db by DbContext. Can I get size?

Please help me. BR Cenarius

---

UPDATE 10.12.2018

Point of my help was to acquire higher performance, because SUM(DATALENGTH(…) was slow. Thinking about c# I supposed that I may help to speed up query. However the best solution, after reading many articles on google, was to use any how sys.database_files.

Thanks for help.

BR

Cenarius

Answer

Many SQL Server built-in functions are available through SqlFunctions in Entity Framework up to 6.2 and DbFunctions in EF Core 2.1

In EF 6.2, SqlFunctions.DataLength can be used to return the size of any field. Eg:

var files = myContext.Select(row=>
                       new { 
                            row.Name,
                            Size=SqlFunctions.DataLength(row.File)
                           })
                     .ToArray();

The function is not availalbe in EF Core unfortunately. An alternative could be to use Query Types and raw SQL queries to execute a raw SQL query and map it to a query type:

class FileAndSize
{
    public string Name{get;set;}
    public long Size {get;set;}
}


var files = myContext.Query<FileAndSize>
                     .FromSql("select name,datalength(file) as size from ...")
                     .Where(...)
                     .ToList();

Raw SQL queries can be combined with LINQ operators to create the final SQL statement, which allows adding Where(), GroupBy and other operators.

In EF Core versions before 2.1 it’s still possible to execute raw SQL queries but they’ll have to be mapped to entity classes.