Execute SQL with Entity Framework 4

Working with a client that is just moving over to EF4, they wanted some examples of executing dynamic SQL against their entity connection. The example below, taken from a console application, shows two ways to do this by using ExecuteStoreQuery or by extracting the SqlConnection from the EntityConnection. I’ve included examples in C# and VB.NET.

C#

using System;
using System.Configuration;
using System.Data.EntityClient;
using System.Data.SqlClient;
using ConsoleApplication5.Entity;

namespace ConsoleApplication5
{
    class Program
    {
        static void Main(string[] args)
        {
            EntityConnection myConnection = new EntityConnection(ConfigurationManager.ConnectionStrings["SimpleDBEntities"].ConnectionString);

            //Using ExecuteStoreQuery in EF4.
            using (SimpleDBEntities se = new SimpleDBEntities(myConnection))
            {
                foreach(var tableNames in se.ExecuteStoreQuery("SELECT name FROM sys.tables"))
                {
                    Console.WriteLine(tableNames);
                }
            }

            //Extract the SqlConnection from the EntityConnection.
            SqlConnection mySqlConnection = myConnection.StoreConnection as SqlConnection;
            if (mySqlConnection.State != System.Data.ConnectionState.Open)
                mySqlConnection.Open();
            SqlCommand myCommand = new SqlCommand("SELECT name FROM sys.tables", mySqlConnection);
            using (var tableNames = myCommand.ExecuteReader())
            {
                while (tableNames.Read())
                {
                    Console.WriteLine(tableNames[0].ToString());
                }
            }

            mySqlConnection.Close();
            Console.ReadLine();
        }
    }
}

VB.NET

Imports System.Data.EntityClient
Imports System.Data.SqlClient
Imports System.Configuration

Module Module1

    Sub Main()
        Dim myConnection As New EntityConnection(ConfigurationManager.ConnectionStrings("SimpleDBEntities").ConnectionString)

        'Using ExecuteStoreQuery in EF4.
        Using se As New SimpleDBEntities(myConnection)
            For Each tableNames In se.ExecuteStoreQuery(Of String)("SELECT name FROM sys.tables")
                Console.WriteLine(tableNames)
            Next
        End Using

        'Extract the SqlConnection from the Entity Connection.
        Dim mySqlConnection As SqlConnection = TryCast(myConnection.StoreConnection, SqlConnection)
        If mySqlConnection.State <> System.Data.ConnectionState.Open Then
            mySqlConnection.Open()
        End If
        Dim myCommand As New SqlCommand("SELECT name FROM sys.tables", mySqlConnection)
        Using tableNames = myCommand.ExecuteReader()
            While tableNames.Read()
                Console.WriteLine(tableNames(0).ToString())
            End While
        End Using

        mySqlConnection.Close()
        Console.ReadLine()

    End Sub

End Module

Additional information can be found in MSDN at How to: Directly Execute Commands Against the Data Source