Run custom sql with entity map?

Question

I need to execute a custom query that will be stored somewhere in the database, and I need it to return to a datatable or dataset and bind it to a gridview that will have auto-generating columns in true.

My entire data access level works fine with entity infrastructure, but for some specific scenarios I need to do this and I wonder if I should combine ado.net with entity infrastructure or if EF can do it somehow

Answer 1

If your goal is to return ADO.NET structures (DataTable or DataSet), just use the classic ADO.NET. It will be easier for you than trying to bind data to the Entity set, and then fill in the DataTable or DataSet yourself.

However, if you are really interested in doing a custom query through EntityFramework, see ExecuteQuery . It allows you to execute an SQL query and maps the result back to objects in your model. Then it would be an exercise on your part to take the result of IEnumerable and map it to a DataTable or DataSet. Hence my original answer, "just do it with the good ADO.NET methods."

Answer 2

For Entity Framework 5 use

context.Database.SqlQuery

---

And for Entity Framework 4 use the following code

context.ExecuteStoreQuery

---

public string BuyerSequenceNumberMax(int buyerId) { string sequenceMaxQuery = "SELECT TOP(1) btitosal.BuyerSequenceNumber FROM BuyerTakenItemToSale btitosal " + "WHERE btitosal.BuyerID = " + buyerId + "ORDER BY CONVERT(INT,SUBSTRING(btitosal.BuyerSequenceNumber,7, LEN(btitosal.BuyerSequenceNumber))) DESC"; var sequenceQueryResult = context.Database.SqlQuery<string>(sequenceMaxQuery).FirstOrDefault(); string buyerSequenceNumber = string.Empty; if (sequenceQueryResult != null) { buyerSequenceNumber = sequenceQueryResult.ToString(); } return buyerSequenceNumber; } 

To return the list, use the following code

  public List<PanelSerialList> PanelSerialByLocationAndStock(string locationCode, byte storeLocation, string itemCategory, string itemCapacity, byte agreementType, string packageCode) { string panelSerialByLocationAndStockQuery = "SELECT isws.ItemSerialNo, im.ItemModel " + "FROM Inv_ItemMaster im " + "INNER JOIN " + "Inv_ItemStockWithSerialNoByLocation isws " + " ON im.ItemCode = isws.ItemCode " + " WHERE isws.LocationCode = '" + locationCode + "' AND " + " isws.StoreLocation = " + storeLocation + " AND " + " isws.IsAvailableInStore = 1 AND " + " im.ItemCapacity = '" + itemCapacity + "' AND " + " isws.ItemSerialNo NOT IN ( " + " Select sp.PanelSerialNo From Special_SpecialPackagePriceForResale sp " + " Where sp.PackageCode = '" + packageCode + "' )"; context.Database.SqlQuery<PanelSerialList>(panelSerialByLocationAndStockQuery).ToList(); } 

Answer 3

Here's a different size and simpler approach. Get the SQL connection using the Entity Framework Context:

 var connection = (System.Data.SqlClient.SqlConnection) _db.Database.Connection; if (connection != null && connection.State == ConnectionState.Closed) { connection.Open(); } var dt = new DataTable(); using (var com = new System.Data.SqlClient.SqlDataAdapter("Select * from Table", connection)) { com.Fill(dt); } 

And we can use the DataAdapter or any other classic method to execute queries using an EF connection.

This will be very useful when we do something dynamically and when we cannot match Entity. For example, we can get data in a DataTable.

The above syntax is for EF 5.0.

Answer 4

I am using EF6 and one day I need a way to execute a dynamic SQL string and get a DataTable. First, I just dropped the DbContext.Database.Connection in SqlConnection and did all the work. It worked for tests, but the application was broken because the Glimpse we use introduces a DbConnection self- DbConnection type Glimpse.Ado.AlternateType.GlimpseDbConnection . I need an approach that works no matter what DbConnection is. Finally, I end up with the following code:

 public class SqlDataProvider : ISqlDataProvider { private readonly DbContext _context; public SqlDataProvider(DbContext context) { _context = context; } public DataTable GetDataTable(string sqlQuery) { try { DbProviderFactory factory = DbProviderFactories.GetFactory(_context.Database.Connection); using (var cmd = factory.CreateCommand()) { cmd.CommandText = sqlQuery; cmd.CommandType = CommandType.Text; cmd.Connection = _context.Database.Connection; using (var adapter = factory.CreateDataAdapter()) { adapter.SelectCommand = cmd; var tb = new DataTable(); adapter.Fill(tb); return tb; } } } catch (Exception ex) { throw new SqlExecutionException(string.Format("Error occurred during SQL query execution {0}", sqlQuery), ex); } } 

And this works for any occasion: for tests where DbContext.Database.Connection is SqlConnection and for Glimpse.Ado.AlternateType.GlimpseDbConnection