Obscure Problems and Gotchas

I’ve griped about this before, not sure why everything has to be so complicated with EF at times, but holy hell executing just a simple SQL String “SELECT * FROM Table” is a major feat that takes more than just 1 line of code to do. Anyhow, if you are using EF 4.0 and not EF 4.1 it is slightly more difficult to execute sql strings right from EF. EF 4.1 has some nice methods that make a bit of a difference, but not very much, I still find myself needing the following code below.

I just want to execute my sql string and get a DataTable back for pete’s sake? Is that really too much to ask for? Apparently so…

So first thing’s first, we need to get a connection string. You would think this was easy… well it isn’t unfortunately.

Getting the Connection String in EF 4.0

//You need these libraries
using System.Data.Objects;
using System.Data.EntityClient;

public static string GetConnectionString(this ObjectContext context)
{
 return ((EntityConnection)context.Connection).StoreConnection.ConnectionString;
}

If you are using EF 4.1 check out this link here to see how to do it in 4.1; I haven’t tried it so I can’t vouch for it. EF 4.1 offers a new property called “Database” which seems to help with some of this mundane code.

Getting a DataTable in EF 4.0

//You need these libraries
using System.Data;
using System.Data.Objects;
using System.Data.SqlClient;

public static DataTable ExecuteDataTable(this ObjectContext context, string sqlString)
{
 DataTable dt = new DataTable("Table1"); //The Table name MUST be set in order to pass this over WCF

 using (IDbConnection con = new SqlConnection(context.GetConnectionString()))
 {
  using (IDbCommand cmd = new SqlCommand(sqlString, (SqlConnection)con))
  {
   cmd.CommandTimeout = 0;

   using (SqlDataAdapter adapter = new SqlDataAdapter())
   {
    adapter.SelectCommand = (SqlCommand)cmd;
    adapter.Fill(dt);
   }
  }
 }

 return dt;
}

Please be aware that if you are going to return this DataTable over the pipe from a WCF service to a client application, you MUST name your DataTable or you will get a bogus WCF error that will only serve as unnecessary confusion (it will be going into my hall of shame soon). At all… Look here for a great question and answer about this.

Getting a DataSet in EF 4.0

//You need these libraries
using System.Data;
using System.Data.Objects;
using System.Data.SqlClient;

public static DataSet ExecuteDataSet(this ObjectContext context, string sqlString)
{
 DataSet ds = new DataSet();

 using (IDbConnection con = new SqlConnection(context.GetConnectionString()))
 {
  using (IDbCommand cmd = new SqlCommand(sqlString, (SqlConnection)con))
  {
   cmd.CommandTimeout = 0;

   using (SqlDataAdapter adapter = new SqlDataAdapter())
   {
    adapter.SelectCommand = (SqlCommand)cmd;
    adapter.Fill(ds);
   }
  }
 }

 return ds;
}  

I haven’t tested the DataSet code yet, but it should work since it is exactly the same as the DataTable code except that it is returning a DataSet instead of a DataTable. I tried making a generic version of this method, but it didn’t work. If you are going to return the DataSet across the pipe via WCF, you need to change it into a List<DataTable> so that it will be serializable. Just remember to name each table to avoid WCF bogus problems.

Sources
http://stackoverflow.com/questions/6587143/get-the-connection-string-for-entity-framework-using-the-poco-template/

http://stackoverflow.com/questions/10254272/execute-stored-procedure-in-entity-framework-return-listdatatable-or-dataset

http://stackoverflow.com/questions/915329/is-it-possible-to-run-native-sql-with-entity-framework

http://stackoverflow.com/questions/12702/net-returning-datatables-in-wcf