EFcore3.1 get values from sys.master_files and SERVERPROPERTY

Question

I need my (console) app to get some general SQLserver DB info.

In SQLserver management studio I run this query on the master system database:

SELECT @@VERSION, name, physical_name, size, SERVERPROPERTY('Edition'), SERVERPROPERTY('ProductVersion'), SERVERPROPERTY('MachineName')
FROM sys.master_files
WHERE name = 'mydb'

I get back a table with a single row and the values I need in 7 columns

How do I do this from EFcore ?

I understand I need to use ExecuteSqlRaw as I don't have and don't want a DBContext, however I am not sure
- how to execute it on the master system table ? and
- how to get the results ? (as strings is fine)

Any help appreciated

Answer 1

I ended up solving this like this:

var server = "localhost";
var username = "user";
var password = "password";
var conString = "Server=" + server + ";Database=master;User Id=" + username +";Password=" + password;

string version="", machineName="", physicalName="";
float mydbDataFileSizeGB=-1, maxSizeGB=-1;

try {
    using SqlConnection con = new SqlConnection(conString);
    con.Open();
    var query = @"
        SELECT  
            @@VERSION AS Version, 
            SERVERPROPERTY('MachineName') AS MachineName, 
            physical_name AS PhysicalName,
            CAST(CASE WHEN type = 0 THEN size * 8. / 1024.0  / 1024.0 ELSE 0 END AS DECIMAL(8,2)) AS MydbDataFileSizeGB,
            CAST(CASE WHEN serverproperty('EngineEdition') = 4 THEN  CASE WHEN SERVERPROPERTY('productversion') between '10.50' and '5' THEN 10 ELSE 4 END ELSE -1 END  AS DECIMAL(8,2)) AS MaxSizeGB
        FROM sys.master_files
        WHERE name = 'Mydb_Data'
    ";

    using SqlCommand cmd = new SqlCommand(query, con);
    SqlDataReader rdr = cmd.ExecuteReader();

    if (rdr.Read()) {
        version = rdr.GetString(0);
        machineName = rdr.GetString(1);
        physicalName = rdr.GetString(2);
        mydbDataFileSizeGB = decimal.ToSingle(rdr.GetDecimal(3));
        maxSizeGB = decimal.ToSingle(rdr.GetDecimal(4));
    }

} catch (Exception) {
    ...
}

Answer 2

To do this in EFCore 3.1 you'd create a model representing the data you're trying to retrieve then use FromSqlRaw or FromSqlInterpolated to populate the model.

You will need to use a DbContext to do this. So if that is your hard requirement then resorting to ADO.NET as you've done is one way of doing it.

ExecuteSqlRaw/ExecuteSqlInterpolated will only return an integer result.