Delete table record from entity framework driven gridview with view datasource

Question

I'm running into an issue that appears to perhaps be either my misunderstanding or a bug with Entity Framwork.

I have a .NET 4.0 ASP.NET web project that simply uses a GridView with a datasource of an Entity Framework view object with only some of the columns being selected.

The Entity Framework is based of a database model in SQL Server 2008 R2; table design is mostly in third normal form.

A quick db schema of the relevant objects:

[t_User] UserID, bigint, PK FirstName, varchar(50) LastName, varchar(50)

[t_Manager] MgrID, int, PK UserID, bigint, FK

[v_Manager] MgrID, int UserID, bigint FirstName, varchar(50) LastName, varchar(50)

When I imported the tables and view into my EF model it assigned MgrID from v_Manager the PK, which is fine I think.

v_Manager is what drives the ASPX GridView control with the datasource set as the EF model using the v_Manager entity.

The GridView works fine how I have it, with the footer CreateNew row commands working.

The problem comes when I delete a record using the GridView RowDeleting trigger, code looks like this:

protected void MgrsGrid_RowDeleting(object sender, GridViewDeleteEventArgs e)
{
    // parse userId
    long mgrSelected = long.Parse(e.Values[0].ToString());

    using (DBEntity db = new DBEntity())
    {
        // select mgr object based on userid, (we don't select mgrid for the gridview bind)
        var mgrToDelete = db.t_Manager.Single(m => m.UserID == mgrSelected);

        if (mgrToDelete != null)
        {
            db.DeleteObject(mgrToDelete);
            db.SaveChanges();
            MgrGrid.DataBind();
        }
    }
}

I've stepped through all of the code for RowDeleting() and it works and actually deletes the record, (verified in SSMS), but I get the following error upon page render once EF code runs:

Unable to update the EntitySet 'v_Manager' because it has a DefiningQuery and no element exists in the element to support the current operation.

I'm not trying to update v_Manager nor delete anything from it, I'm working with t_Manager. I've done stack and google searches on the error that I received and I updated my edmx file manually to remove the DefiningQuery but then I get the error message directly from SQL Server 2008 saying that it can't delete record from v_Manager because it affects multiple tables.

Am I missing something here? I would think that removing a record from the t_Manager entity and calling SaveChanges() would just cause SQL Server to run a Refresh() on the v_Manager entity no?

I need to use the v_Manager entity to drive the GridView but I have a sneaking suspicion that if I changed the GridView to Bind against t_Manager that I wouldn't have any problems.

It seems that the issue comes from having the GridView driven by v_Manager triggering the RowDeleting event causes it to run a deletion against the GridViews datasource.

Is there a way to override that behavior or is this by design and if so how to people find Entity Framework useful when working with views (which most databases will utilize)?

I appreciate any assistance that anyone can provide, thanks!

EDIT: I should mention that I tried setting the EnableDelete flag = false and that only resulted in the web form telling me the datasource doesn't allow deletion when clicking my delete link in the GridView.

Brian Brian · Accepted Answer · 2012-07-06T05:43:41

TobiasFunkeMD on reddit deserves all the credit for this one (thanks again!) Reddit Link

Simply add e.Cancel = true; after the GridView.DataBind();

Example:

db.DeleteObject(mgrToDelete);
db.SaveChanges();
MgrGrid.DataBind();

e.Cancel = true;

This works the same as in javascript when you use e.CancelBubbling, essentially it stops the event from continuing on up the chain.

Entity Framework was acting fine, the issue was the GridViews own RowDeleting event always goes against the GridViews own DataSource no matter what is being done in the RowDeleting function.

Delete table record from entity framework driven gridview with view datasource

1 Answers