1

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?

Foysal94
  • 565
  • 2
  • 7
  • 15
  • Not sure what you are trying to do here. Your `DELETE` statement does not return the rows deleted, and `FromSql` need to return columns that match your entity type (see [here](https://docs.efproject.net/en/latest/querying/raw-sql.html#limitations)). – stephen.vakil Sep 07 '16 at 14:35
  • @stephen.vakil I have edited the original quiestion to hopefully make it clearer.Basically I am trying to delete a column from the databse with a specfic ID. I expect the FromSql method to return the correct column with the specfic ID to delete into the columnsList variable. After which, I iterate through the list and delete it from the DbSet before saving. – Foysal94 Sep 07 '16 at 15:10
  • I have gone through that docunmention multiple times and I do not recieve any help from it to solve this issue. Why does my DELETE statment not return the rows? And how am I not returning a Columns entity type also? I probably should have said this is my first inteaction with SQL/LINQ using C#. – Foysal94 Sep 07 '16 at 15:12
  • Delete is a statement, not a query. Typically it will just return the # of rows affected. Is your "real code" more complex than this? You don't need to use raw sql to accomplish your code sample. You could just use `RemoveRange` as described [here](http://stackoverflow.com/questions/2519866/how-do-i-delete-multiple-rows-in-entity-framework-without-foreach) ... `m_context.Columns.RemoveRange(m_context.Columns.Where(x => x.ID == p_ColumnID))` or replace your first two lines with var `columnList = m_context.Columns.Where(x => x.ID == p_ColumnID)` – stephen.vakil Sep 07 '16 at 15:15
  • So is SELECT and WHERE statements aswell than? If it was a SELECT query I would run into the same issues? I was not aware of the solutions you provided as I am still new to C# LINQ. The solutions are more easier. However for my sake I wish to better understand why it is not working and how to make it work using raw sql to improve my skills. If you are familar with EFCore I would greatly appreciate if you could explain what FromSql does? Like if it just returns the number of rows and actully does nothing to do the database what the hell do I do to fix this using raw SQL? – Foysal94 Sep 07 '16 at 15:25
  • `Columns.FromSQL` allows you to supply a raw sql which is expected to return one or more rows that match the fields in the `Columns` table. So you could do `FromSQL("SELECT * FROM COLUMNS WHERE ID == {0}")` which would return an `IQueryable` with `Column` data, which you could then iterate through and remove. You can't pass in a `DELETE` statement because it's a statement, not a query, so it is not going to return a cursor with data in the right format. – stephen.vakil Sep 07 '16 at 15:42
  • If you want to perform an insert, update or delete, you should use the `context.Database.ExecuteSqlCommand` method, not `FromSql`. – Mike Brind Sep 07 '16 at 15:59
  • @stephen.vakil Ahhh making sense now. Thank you so much for your help. – Foysal94 Sep 07 '16 at 16:14
  • @MikeBrind Is there any reason(s) why ExecuteSqlCommand should be used instead of FromSql? – Foysal94 Sep 07 '16 at 16:15
  • The FromSql method is for returning entities. The ExecuteSqlCommand method doesn't return entities. You cannot delete items using FromSql. It doesn't work that way. – Mike Brind Sep 07 '16 at 20:31
  • @stephen.vakil You should write your comments up as an answer. – Mike Brind Sep 07 '16 at 20:31
  • Ok so from the accepted answer below and your answer suggesting ExecuteSqlCommand, which one is generally better if any difference? My Unit tests use EFCore InMemoryDatabase ability, and ExecuteSqlCommand does not work with it. The method call throws a 'System.InvalidOperationException'. Unable to resolve service for type 'Microsoft.EntityFrameworkCore.Storage.IRawSqlCommandBuilder'. This is often because no database provider has been configured for this DbContext. Is there a way to fix this? I presume no because InMemoryTesting is not designed to mimic a relational database ? – Foysal94 Sep 08 '16 at 00:01
  • There is no reason to use `ExecuteSqlCommand` unless you can't accomplish what you want to any other way. If you are going to use Entity Framework, use it as it is primarily intended and act on objects/entities. – stephen.vakil Sep 08 '16 at 14:31

2 Answers2

4

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);
}
Community
  • 1
  • 1
stephen.vakil
  • 3,492
  • 1
  • 18
  • 23
  • So I wanted to select a column from the database and change a property of it, for example its name. I used the 'Where' method on the Columns DbSet to retrieve the correct column with the ID. This normally would be done with a 'SELECT' SQL query. This might be the wrong assumption, but can the 'Where' method is really powerful and can be used for most DB actions? Is that wrong? I also noticed it had a 'Select' method on the DbSet, what does that do exactly as I can't understand what parameter it accepts or what it returns? – Foysal94 Sep 08 '16 at 16:10
  • Also I was wondering, if my Columns table contains a lot of rows of columns. So a large dataset. Is this filtering using 'Where' slow compared to executing a regular Sql command on FromSql or ExecuteSqlCommand? The EF docs use FromSql with a Select query and than build/compose onto of that using LINQ methods. I am really trying to understand the best solution for most problems. – Foysal94 Sep 08 '16 at 16:14
0

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.

rlv-dan
  • 976
  • 1
  • 10
  • 21