EntityFrameworkCore FromSql method call throws System.NotSupportedException

asp.net-mvc entity-framework entity-framework-core

Question

So am using AspNetCore 1.0 with EFCore 1.0, both latest releases as far as I am aware.

Executing a query to delete an object using the FromSql method on a DbSet throws an exception. Both the code and exception are below.

public void DeleteColumn(int p_ColumnID)
{
    int temp = p_ColumnID;
    string query = "DELETE FROM Columns WHERE ID = {0}";
    var columnsList = m_context.Columns.FromSql(query, p_ColumnID).ToList();
    foreach (Columns c in columnsList)
    {
        m_context.Columns.Remove(c);
    }
     m_context.SaveChanges();
}

After executing the FromSql call, I get the following exception

An exception of type 'System.NotSupportedException' occurred in Remotion.Linq.dll but was not handled in user code

Additional information: Could not parse expression 'value(Microsoft.EntityFrameworkCore.Query.Internal.EntityQueryable`1[ASPNET5_Scrum_Tool.Models.Columns]).FromSql("DELETE FROM Columns WHERE ID = {0}", __p_0)': This overload of the method 'Microsoft.EntityFrameworkCore.RelationalQueryableExtensions.FromSql' is currently not supported.

I have no clue how to fix this error and from Googling I have come across no similar problems.

I am also wondering, if the query/code was successful it would return an 'IQueryable object. Would that solely contain the results of the query, in this case the specific Column object to delete?

1
1
9/7/2016 3:13:50 PM

Accepted Answer

FromSql is intended to allow you to compose a custom SQL SELECT statement that will return entities. Using it with a DELETE statement is not appropriate here, since your goal is to load the records you want to delete and then delete them using the default Entity Framework mechanism. A Delete statement generally does not return the records deleted (though there are ways to accomplish that). Even if they did, the records will already be deleted and so you won't want to iterate over them and do a Remove on them.

The most straightforward way to do what you want might be to use the RemoveRange method in combination with a Where query.

public void DeleteColumn(int p_ColumnID)
{
   m_context.Columns.RemoveRange(m_context.Columns.Where(x => x.ID == p_ColumnID))     
   m_context.SaveChanges();
}

Alternately, if you want to load your entities and iterate manually through them to

public void DeleteColumn(int p_ColumnID)
{
    columnList = m_context.Columns.Where(x => x.ID == p_ColumnID);
    foreach (Columns c in columnsList)
    {
        m_context.Columns.Remove(c);
    }
     m_context.SaveChanges();
}

If you really want to issue the Delete statement manually, as suggested by Mike Brind, use an ExecuteSqlCommand method similar to:

public void DeleteColumn(int p_ColumnID)
{
    string sqlStatement = "DELETE FROM Columns WHERE ID = {0}";
    m_context.Database.ExecuteSqlCommand(sqlStatement, p_ColumnID);
}
4
5/23/2017 10:30:21 AM

Popular Answer

I had the same exception in a case where I did not use delete statement. Turns out I was using the In-Memory Database. Since it is not a real database you can't use FromSQL.



Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow