Execute custom SQL with Entity Framework?

Question

I need to execute a custom query which will be saved somewhere in the database, and I need it to return in a DataTable, or DataSet and bind it to a GridView which will have autogenerate columns set to true.

All my Data Access Layer works perfectly with Entity Framework, but for some specific scenario I need to do this, and I wonder if I should combine ADO.NET with Entity Framework, or if EF can do it somehow.

Biel

Answer 1

If your goal is to return ADO.NET structures (DataTable or DataSet), then just use classic ADO.NET. You'll find it easier than trying to bind data to an Entity set and then populating a DataTable or DataSet yourself.

However, if you're really and truly interested running a custom query through EntityFramework, take a look at ExecuteQuery. It allows you to execute a SQL Query and maps the result back to entities in your model. It would then be an exercise on your part to take the IEnumerable result and map it to a DataTable or DataSet. Hence my original answer of "just do it with good ol' fashioned ADO.NET methods."

Hyperbaric

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;
    }

For Return a 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();


}

Disconformity

If you're not sure you've sanitized all the inputs, use SQL parameters to prevent SQL injection attacks. – Pandowdy

No MARS support if calling a stored procedure that returns multiple result sets? SqlQuery<T> implies it can only handle a single type of result set per call. – Glossography

Answer 3

Here's another dimension and easier approach. Get SQL Connection using your 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 DataAdapter or any other classic method to execute queries using the EF connection.

This will be very useful when we do something dynamically and when we can't map to a Entity. We can get things in a DataTable for example.

The above Syntax is for EF 5.0.

Printmaker

Answer 4

I use EF6 and one day I needed way to execute dynamic SQL string and get DataTable. Firstly I merely cast DbContext.Database.Connection to SqlConnection and did whole job. It worked for tests, but application were broken, because Glimpse, that we use, injects self implementation of DbConnection with type Glimpse.Ado.AlternateType.GlimpseDbConnection. I need approach that works independently of what DbConnection is. Finally I end up with 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 case: for tests where DbContext.Database.Connection is SqlConnection and for Glimpse.Ado.AlternateType.GlimpseDbConnection

Beanery

Answer 5

Update for EF Core 8 (thanks to Peru for the original idea). Microsoft.Data.SqlClient is the new library which replaced the System.Data.SqlClient. Most of the data can be fetched by EF Core, so I cant imagine the need of other tools for that, but I found this approach useful if I want to fetch other tables like SQL system ones.

using (SqlConnection srcConn = new SqlConnection(_ctx.Database.GetConnectionString()))
using (SqlCommand srcCmd = new SqlCommand("select stopword from sys.fulltext_system_stopwords", srcConn))
{
    srcConn.Open();
    using (DbDataReader reader = srcCmd.ExecuteReader())
    {
        while (reader.Read())
        {
            _stopWords.Add(reader.GetFieldValue<string>(0));
        }
    }
}

Leanneleanor