Database not updated bug - Entity Framework

Question

I am using a Entity Framework and JQuery to update values in an SQL table column with a bit data type. This is being done by reading the values into an html table and then having a button that will change the bit value for each row.

Here you can see the html table:

    <table id="categoryList" class="table">
    <thead>
    <tr>
        <th>Category ID</th>
        <th>Category Name</th>
    </tr>
    </thead>
    <tbody>
    @foreach (var item in Model.Categories)
    {
        <tr>
            <td>@item.id</td>
            <td>@item.name</td>
            <td>
                <button class="btn btn-success categoryEnabled" data-id="@item.id">Enabled</button>
            </td>
        </tr>
    }
    </tbody>
</table>

Then here is the script I am using to start the action on click:

$(".categoryEnabled").on("click", function() {
    $(this).hide();
    $(this).next().show();
    if (!e) var e = window.event;
    e.cancelBubble = true;
    if (e.stopPropagation) {
        e.stopPropagation();
    }
    DisableRow($(this).data('id'));
});

function DisableRow(id) {
    $.post('@Url.Action("DisableRow", "Category")', { "Id": id }, function () {
        alert('Row Disabled!');
    }).fail(function () {
        alert('Error disabling row!');
    });
}

This is then linked to a controller action that should change the state bit value for the selected row and update:

public void DisableRow(int id)
    {
        var connection = new CategoryDBEntities();

        var record = connection.Categories.Find(id);

        if (record == null) return;

        record.state = StatesTypes.Disabled;

        connection.SaveChanges();
    }

Here's Category object for reference:

namespace Project.Models
{
using System;
using System.Collections.Generic;

    public enum StatesTypes
    {
        Disabled = 0,
        Enabled = 1
    }

public partial class Category
{

    public int id { get; set; }
    public string name { get; set; }
    public StatesTypes state { get; set; }
}

}

DbContext:

namespace Project.Models
{
using System;
using System.Data.Entity;
using System.Data.Entity.Infrastructure;

public partial class CategoryDBEntities : DbContext
{
    public QualityDBEntities()
        : base("name=CategoryDBEntities")
    {
    }

    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
        throw new UnintentionalCodeFirstException();
    }

    public DbSet<Category> Categories { get; set; }

}

}

Output from Diagnostics Debug:

SELECT TOP (2) 
[Extent1].[id] AS [id], 
[Extent1].[name] AS [name]
FROM [dbo].[Category] AS [Extent1]
WHERE [Extent1].[id] = @p0


-- p0: '5' (Type = Int32)

The issue is that the record.state is changed to disabled (0), but the bit value in the database does not change. I must be missing something here. Thanks for your help!

You say bit but enums usually are mapped to integers. Can you send the mapping? Also, how did you create the DB? Try to change the bit to integer — bubi
– bubi, Commented Jun 25, 2015 at 17:48
The Db was already created. I just added the state column in an existing table. I'll try to change the bit to int. If I wanted to keep it bit, what would I change the enum to? — cfly24
– cfly24, Commented Jun 25, 2015 at 17:58
Add the following code after the line where you new up a CategoryDBEntities: connection.Database.Log = s => Debug.WriteLine(s);. This will output the SQL that EF generates when you call SaveChanges(). What do you see? — sstan
– sstan, Commented Jun 25, 2015 at 17:58
Did you try marking the entity state as modified? Something along the lines of connection.Entry(record).State = EntityState.Modified; before saving? — Shawn
– Shawn, Commented Jun 25, 2015 at 18:23

sstan · Accepted Answer · 2015-06-25 18:57:41Z

2

Your state column is not being mapped at all between EF and your database.

You can see this in your SQL log:

SELECT TOP (2) 
[Extent1].[id] AS [id], 
[Extent1].[name] AS [name]
FROM [dbo].[Category] AS [Extent1]
WHERE [Extent1].[id] = @p0


-- p0: '5' (Type = Int32)

Notice that the state column is not being selected at all.

Look to see if you have configured the state property to be ignored by using an annotation [NotMapped], or fluent API configuration like entityTypeConfiguration.Ignore(p => p.state).

Once you have that fixed, I expect you'll be getting exceptions about the fact that you're mapping an enum to a bit field. As others are suggesting, you probably want to use a boolean for your property.

edited Jun 25, 2015 at 18:57

answered Jun 25, 2015 at 18:47

sstan

36.8k7 gold badges54 silver badges72 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Ron Brogan · Accepted Answer · 2015-06-25 18:32:37Z

0

It doesn't look like you're actually committing the changed item back to the database. You're only altering the copy you've made of it.

To ensure that the data is altered in the DB you want to do something like the following:

connection.Categories.Attach(record);
connection.Entry(record).State = System.Data.Entity.EntityState.Modified;
connection.SaveChanges();

answered Jun 25, 2015 at 18:32

Ron Brogan

9011 gold badge9 silver badges25 bronze badges

2 Comments

sstan Over a year ago

This is a good test to try. But it is not a correct solution, because the entity is part of the context already. Changing the property should be sufficient. Setting an entity to Modified is required when you are attaching a disconnected entity.

cfly24 Over a year ago

Hmmm...this still didn't solve the problem. Not too sure what else could be going on

Shawn · Accepted Answer · 2015-06-25 18:40:33Z

0

I will make this an answer because I am pretty sure if you are hitting the action method at some point you will need to do this before saving changes.

connection.Entry(record).State = EntityState.Modified;
connection.SaveChanges();

This allows EF to issue an update on the record passed. I still think you might run into issues not having the state be a boolean and trying to save to a bit field though.

Humor us and try the following:

public void DisableRow(int id)
{
    var connection = new CategoryDBEntities();

    var record = connection.Categories.Find(id);

    if (record == null) return;

    record.state = false; //Assume false for disabled

    connection.SaveChanges();
}

And

public partial class Category
{
    public int id { get; set; }
    public string name { get; set; }
    public bool state { get; set; }
}

I am making the assumption you are using code first to create your database, but regardless I know for a fact if you generate from an existing DB the field will be a bool if it is a bit in the db. Something else to consider it might need to be a nullable column on the DB unless you set a default some how. Simply replace:

public bool state { get; set; }
//with
public bool? state { get; set; }

edited Jun 25, 2015 at 18:40

answered Jun 25, 2015 at 18:31

Shawn

8892 gold badges9 silver badges28 bronze badges

7 Comments

sstan Over a year ago

This is a good test to try. But it is not a correct solution, because the entity is part of the context already. Changing the property should be sufficient. Setting an entity to Modified is required when you are attaching a disconnected entity.

Shawn Over a year ago

I still lean towards the bit not mapping correctly. EF in my experience has always treated bit fields as boolean in code.

cfly24 Over a year ago

I tried this and it still showed no change in the database :/

Shawn Over a year ago

See my latest update bool might need to be a nullable?

Shawn Over a year ago

Ok let's take a step back and cover some basics: 1) You hit the DisableRow method correct (in debug)? 2) I assume the connection is working (record != null)? 3) Is the database table correctly created by code first (with bit field etc)

|

Brian Tompsett - 汤莱恩 · Accepted Answer · 2015-06-25 20:12:43Z

Finally got this figured out! Thanks @Shawn for all the help!

There were actually a couple things going on.

First off, like you suggested I had to change the the StatesType enum to bool so it would map correctly:

public partial class Category
{
public int id { get; set; }
public string name { get; set; }
public bool state { get; set; }
}

Since its now bool I set the state to false:

public void DisableRow(int id)
{
var connection = new CategoryDBEntities();

var record = connection.Categories.Find(id);

if (record == null) return;

record.state = false;

connection.SaveChanges();
}

The big issue here and the main reason that it wasn't showing any change in the database was because the state column was not being mapped between EF and the database (which was also referenced in one of the answers).

This was because the state column was created in SQL Server Management; not VS. So I had to go into the .edmx and click 'Update Model from Database' and then refresh the table.

Thanks for all the help on this one!

Collectives™ on Stack Overflow

Database not updated bug - Entity Framework

4 Answers 4

Comments

2 Comments

7 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

2 Comments

7 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related