Executing raw SQL query with Entity Framework without using DbSet

In this previous post, I showed how to execute raw SQL query using EF but it requires the entity to be defined and to be accessible via DbSet. However, there might be times where you just want to execute a raw SQL query to return some arbitrary shape that is not necessarily part of your EF entities, i.e. Sequences.

One option is we can leverage ADO.NET from Context.Database property. We can use the GetDbConnection() method to create a command. From that, you have access to all the ADO.NET APIs.

The example below will return all the SQL sequences in the database.

            using var command = database.GetDbConnection().CreateCommand();

            command.CommandText = @"
                    select name          as Name,
                           minimum_value as MinValue,
                           maximum_value as MaxValue
                    from sys.sequences;";
            command.CommandType = CommandType.Text;

            database.OpenConnection();

            using var reader = command.ExecuteReader();
            var results = new List<object>();

            while (reader.Read())
            {
                var name = (string)reader["Name"];
                if (sequenceNames.Contains(name))
                {
                    results.Add(new
                    {
                        Name = name,
                        MinValue = (long)reader["MinValue"],
                        MaxValue = (long)reader["MaxValue"]
                    });
                }                
            }

            database.CloseConnection();

This solution is what I would go with if I’m restricted to using EF to query data into some arbitrary shape. However, if there’s no constraint, I would use Dapper or some other lightweight ORM to achieve the same result with a lot less code.