none
ADO.NET Entity Framework 6: how to delete row from a table in PostqreSQL? RRS feed

  • Question

  • Our application uses Entity Framework 6 and PostgreSql database.

    we try to use following code to delete a row from table "Persons" and get a error: ERROR: 42P01: relation "persons" does not exist

    string script = "DELETE FROM Persons WHERE Name = " + name;

    Context.Database.ExecuteSqlCommand(script);

    Search online and people mention that using quote or unquote in PostqreSql. If without using quote, everything will be normalized into low case. That is reason we get complaints about "persons" not "Persons". And suggest to use everything (table names and column names) in lower case. However, we do not like the idea to make everything lower case.

    we also try the following code. however, if the entity does not load yet, the code will throw exception.

    "The object cannot be deleted because it was not found in the ObjectStateManager."

    Person person = Persons.Single(c => c.Name.Equals(name, StringComparison.InvariantCultureIgnoreCase));
    if (person != null)
    {
        Context.Persons.Remove(person );
        Context.SaveChanges();
    }

    In the book "Programming Entity Framework DbContext" by Julia Lerman & Rowan Miller, page 47, it mention following method:

    ver toDelete = new Desitniatio (DestinationId = 2};
    context.Destinations.Attach(toDelete);
    context.Desinations.Remove(toDelete);
    context.SaveChanges();

    however, the above code will not work if the entity already load into memory.

    On page 46, it mentions a way to load first and remove it from DbSet.

    using (var context = new BreakWayContext())
    {
       var bay = (from d in context.destinations
                   where d.Name == "Wine Glass Bay"
                   select d).Single();
       context.Destinations.Remove(bay);
       context.SaveChanges();
    }
    So far we only make this way work. However, this means if we need to delete a row from a database table, we have to load it into memory first and then delete it.

    Frist question, is there a way that we can know if an entity load into memory?

    Second question, what is best practice to delete a row from a table in EF6 in code? thx!


    JaneC





    • Edited by JJChen Tuesday, April 7, 2015 3:57 PM
    Tuesday, April 7, 2015 3:28 PM

Answers

  • Hello JaneC,

    >>we try to use following code to delete a row from table "Persons" and get a error: ERROR: 42P01: relation "persons" does not exist

    For this exception, since it is related with the Entity Framework 6 and PostgreSql database, I suggest that you ask it on the PostgreSql forum: http://forums.devart.com/

    >> Frist question, is there a way that we can know if an entity load into memory?

    In Entity Framework, all entities would be traced by the ObjectStateManager class, you could try to query this class to check if an entity is already in memory. Or an easier way is to check the DbSet.Local property: https://msdn.microsoft.com/en-us/library/gg696248(v=vs.113).aspx

    >> Second question, what is best practice to delete a row from a table in EF6 in code? thx!

    It is hard to say which way is the best, in your case, if you already know the primary key value, you could use the Julia Lerman & Rowan Miller provided way, just to check its local property before attaching it to the context as:

    using (DFDBEntities db = new DFDBEntities())
    
                {
    
                    Order order = new Order() { OrderID = 1 };
    
                    db.Orders.ToList();
    
                    if (!db.Orders.Local.Any(l => l.OrderID == order.OrderID))
    
                    {
    
                        db.Orders.Attach(order);
    
                    }
    
                    else
    
                    {
    
                        order = db.Orders.Local.Single(l => l.OrderID == order.OrderID);
    
                    }
    
                    db.Orders.Remove(order);
    
                    db.SaveChanges();
    
                }
    

    If you just know some specify column values, which is not a primary key, I think you could firstly query the local property, if not find an matched record, then query the database.

    Regards.


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    • Marked as answer by JJChen Friday, April 10, 2015 11:40 PM
    Wednesday, April 8, 2015 2:51 AM
    Moderator