CODEWITHSUNDEEP
c#sqlentity-frameworkentity-framework-core
crystyxn
crystyxn

Reputation: 1601

Truncate table in entity framework core

How can I truncate a certain table with C# code, not SQL query?

I want the equivalent of TRUNCATE TABLE <table_name>

So far I've tried this:

context.Products.RemoveRange(context.Products);

however, it doesnt do anything

Upvotes: 4

Views: 14245

Answers (3)

Dipon Roy
Dipon Roy

Reputation: 406

I have done things as below, for EF Core 5.0.11 This is working for SQL Server, Oracle, PostgreSQL

public class AnnotationHelper
{
    /*
     * https://stackoverflow.com/questions/45667126/how-to-get-table-name-of-mapped-entity-in-entity-framework-core
     */
    private static string GetName(IEntityType entityType, string defaultSchemaName = "dbo")
    {
        //var schemaName = entityType.GetSchema();
        //var tableName = entityType.GetTableName();
        var schema = entityType.FindAnnotation("Relational:Schema").Value;
        string tableName = entityType.GetAnnotation("Relational:TableName").Value.ToString();
        string schemaName = schema == null ? defaultSchemaName : schema.ToString();
        string name = string.Format("[{0}].[{1}]", schemaName, tableName);
        return name;

    }

    public static string TableName<T>(DbSet<T> dbSet) where T : class
    {
        var entityType = dbSet.EntityType;
        return GetName(entityType);
    }
}
 

public static class EfHelper
{
    /*
     * need to install Microsoft.EntityFrameworkCore.Relational
     */
    public static string Truncate<T>(this DbSet<T> dbSet) where T : class
    {
        var context = dbSet.GetService<ICurrentDbContext>().Context;
        string cmd = $"TRUNCATE TABLE {AnnotationHelper.TableName(dbSet)}";        
        using (var command = context.Database.GetDbConnection().CreateCommand())
        {
            if (command.Connection.State != ConnectionState.Open)
            {
                command.Connection.Open();
            }
            command.CommandText = cmd;
            command.ExecuteNonQuery();
        }
        return cmd;
    }
}


[Test]
public void Truncate()
{
    Db.Users.Add(new User() 
    {
        Name = "Name",
        Email = "Email",
        CreatedBy = "CreatedBy",
        CreatedOn = DateTime.Now
    });
    Db.SaveChanges();
    Assert.GreaterOrEqual(Db.Users.ToList().Count, 1);

    /*Truncate table*/
    Db.Users.Truncate();
    Assert.AreEqual(0, Db.Users.ToList().Count);
}

Upvotes: 5

Sid James
Sid James

Reputation: 529

re: extensions as mentioned by CodeCaster: If you are allowed to use NUGET packages in your code, you might find this library useful; it has 'Truncate' and 'TruncateAsync' exactly as you've asked about.

https://github.com/borisdj/EFCore.BulkExtensions

Yes, behind the scenes it is still using 'ExecuteSqlRaw', but using a c# method allows better error handling. For example, in some cases database security may not allow table truncation by the executing thread, so if that's important to your application you can handle it easier with a wrapper.

Upvotes: 1

CodeCaster
CodeCaster

Reputation: 151594

You can't, that's not what an ORM is for.

An ORM helps you access the database in terms of object access. Truncating a table is a data layer operation, not an object operation. The equivalent in Entity Framework would be to load all objects from the database and delete them one by one.

You don't want that, you want to truncate the table. Then dive down into SQL and truncate that table.

Surely there are extensions on Entity Framework that allow things like this, but in the end they will generate exactly the SQL you're preventing to execute, so why not simply do that yourself?

Upvotes: 8

Related Questions