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
