1

I tries to delete an entity - table

This is the code:

db.Sessions.SqlQuery("delete table session");
db.SaveChanges();

This isnt delete the table. Or do anything.. db is inherits from DbContext object.

I've found a solution

foreach (var item in db.Sessions)
{
    db.Sessions.Remove(item);
}

db.SaveChanges();

But I'm sure theres a better way.

Thanks.

Nir
  • 2,497
  • 9
  • 42
  • 71

2 Answers2

2

SqlQuery as the name implies only for executing queries if you want to drop the table you need something else:

With the use of the DbContext.Database property you can execute any DDL/DML query:

db.DataBase.ExecuteSqlCommand("drop table sessions");

I have also created a repro and it's working fine with EF4.3.1 with SQL Server2008:

using (var db = new EFRepros())
{
    db.Sessions.Add(new Session() { Name = "Test1" });
    db.Sessions.Add(new Session() { Name = "Test2" });
    db.Sessions.Add(new Session() { Name = "Test3" });
    db.SaveChanges();
    Console.WriteLine(db.Sessions.Count()); // Output: 3
    db.Database.ExecuteSqlCommand("delete from sessions");
    //db.Database.ExecuteSqlCommand("truncate table sessions"); //this also works
    Console.WriteLine(db.Sessions.Count()); // Output: 0
}

Note: EF by default use plurar table names so you need to use sessions.

nemesv
  • 138,284
  • 16
  • 416
  • 359
  • I dont want to drop. I want to delete/truncate. And when I tries it, it returns: "There was an error parsing the query. [ Token line number = 1,Token line offset = 1,Token in error = truncate ]" – Nir Jun 15 '12 at 18:44
  • Have you tried `db.DataBase.ExecuteSqlCommand("DELETE FROM session");`? – nemesv Jun 15 '12 at 18:53
  • @nir I've updated my answer, for me both delete and truncate works fine. – nemesv Jun 18 '12 at 09:46
1

Are you trying to drop the table? If so, you can't do that through a SqlQuery call because, as the name implies, that method deals strictly with querying.

If you're trying to delete all the rows from a table, then this question is a duplicate of this:

Linq to Sql: How to quickly clear a table

Community
  • 1
  • 1
Milimetric
  • 13,411
  • 4
  • 44
  • 56