Is it possible to run native sql with entity framework?

Question

I am trying to search an XML field within a table, This is not supported with EF.

Without using pure Ado.net is possible to have native SQL support with EF?

Answer 1

I assume you're trying to do a XPath/XQuery search on the XML field, which can be done efficiently directly on the SQL Server.

But for that, you'd need to be able to execute some "native" T-SQL against the database you're connected to.

Linq2SQL offers the ExecuteQuery on the data context classes - EF doesn't have anything like that, really, since the EF tries to shield you from a variety of possible database backends.

I think you'd be best off grabbing your XML column from the entity and query it using a XmlDocument (.NET 2.0) or Linq-to-XML (XElement style; .NET 3.0 and up).

Marc

Answer 2

BTW, I had the same problem and found a way to easily execute native SQL. Here's a code sample:

    static void ExecuteSql(ObjectContext c, string sql)
    {
        var entityConnection = (System.Data.EntityClient.EntityConnection)c.Connection;
        DbConnection conn = entityConnection.StoreConnection;

        ConnectionState initialState = conn.State;
        try
        {
            if (initialState != ConnectionState.Open)
                conn.Open();  // open connection if not already open
            using (DbCommand cmd = conn.CreateCommand())
            {
                cmd.CommandText = sql;
                cmd.ExecuteNonQuery();
            }
        }
        finally
        {
            if (initialState != ConnectionState.Open)
                conn.Close(); // only close connection if not initially open
        }
    }

Answer 3

public class RaptorRepository<T>
    where T : class
{
    public RaptorRepository()
        : this(new RaptorCoreEntities())
    {
    }

    public RaptorRepository(ObjectContext repositoryContext)
    {
        _repositoryContext = repositoryContext ?? new RaptorCoreEntities();
        _objectSet = repositoryContext.CreateObjectSet<T>();
    }

    private ObjectContext _repositoryContext;
    private ObjectSet<T> _objectSet;
    public ObjectSet<T> ObjectSet
    {
        get
        {
            return _objectSet;
        }
    }


    public void DeleteAll()
    {
        _repositoryContext
            .ExecuteStoreCommand("DELETE " + _objectSet.EntitySet.ElementType.Name);
    }

}