CODEWITHSUNDEEP
asp.net-mvcentity-frameworkentity-framework-core
Foysal94
Foysal94

Reputation: 603

EntityFrameworkCore FromSql method call throws System.NotSupportedException

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?

Upvotes: 1

Views: 3407

Answers (2)

rlv-dan
rlv-dan

Reputation: 1104

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.

Upvotes: 0

steve v
steve v

Reputation: 3540

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

Upvotes: 4

Related Questions