locked
Handle DB errors on EF object RRS feed

  • Question

  • I have a person class which has a collection (association to) emailAddresses.  When I save the person and one of the email addresses is a duplicate I get an error from the database telling me it failed but not which address caused the error.  I want to be able to return this info to the client 

    I currently use the ObjectContext.SavingChanges for validation but this event fires before sending the data to the database.  Which event can I use to inspect the object(s) after returning from the database to determine if anb error occured and display it ?

    It would make sense if could have access to the data that caused UpdateException in the HandleException handler but I don't see the data value there.

    • Edited by PapaStoke Friday, October 28, 2011 12:56 PM
    Friday, October 28, 2011 12:36 PM

Answers

  • You have to consider what is going on behind the scenes to see why it's hard to match back the exact address and entity that caused an error. 

    Let's take an example using the Northwind database.  All we're going to do is create an Order, and then add two Order_Details entities to the Order and then save it all.  The first Order_Detail will save okay, but the second one will fail because we didn't include a valid ProductID:

        class Program
        {
            static void Main(string[] args)
            {
                NorthwindEntities context = new NorthwindEntities();
                Order_Detail od;
                Order order = new Order();
                order.CustomerID = "ALFKI";
    
                
                od = new Order_Detail();
                od.Quantity = 10;
                od.ProductID = 1;
    
                order.Order_Details.Add(od);
                
                
                od = new Order_Detail();
                od.ProductID = 9999;  //invalid
                od.Quantity = 5;
    
                order.Order_Details.Add(od);
    
                context.Orders.AddObject(order);
                try
                {
                    context.SaveChanges();
                }
                catch (System.Data.UpdateException ex)
                {
                    MessageBox.Show(ex.InnerException);
                }
                
    
            }
        }
    

      Here is the batch of sql statements that get sent to SQL Server (all at one time) when we do SaveChanges:

    exec sp_executesql N'insert [dbo].[Orders]([CustomerID], [EmployeeID], [OrderDate], [RequiredDate], [ShippedDate], [ShipVia], [Freight], [ShipName], [ShipAddress], [ShipCity], [ShipRegion], [ShipPostalCode], [ShipCountry])
    values (@0, null, null, null, null, null, null, null, null, null, null, null, null)
    select [OrderID]
    from [dbo].[Orders]
    where @@ROWCOUNT > 0 and [OrderID] = scope_identity()',N'@0 nchar(5)',@0=N'ALFKI'
    go
    exec sp_executesql N'insert [dbo].[Order Details]([OrderID], [ProductID], [UnitPrice], [Quantity], [Discount])
    values (@0, @1, @2, @3, @4)
    ',N'@0 int,@1 int,@2 decimal(19,4),@3 smallint,@4 real',@0=11091,@1=1,@2=0,@3=10,@4=0
    go
    exec sp_executesql N'insert [dbo].[Order Details]([OrderID], [ProductID], [UnitPrice], [Quantity], [Discount])
    values (@0, @1, @2, @3, @4)
    ',N'@0 int,@1 int,@2 decimal(19,4),@3 smallint,@4 real',@0=11091,@1=9999,@2=0,@3=5,@4=0
    go
    


    And when this batch of SQL gets sent to SQL Server, all EF gets back when the error occurs is this:

    The INSERT statement conflicted with the FOREIGN KEY constraint "FK_Order_Details_Products". The conflict occurred in database "Northwind", table "dbo.Products", column 'ProductID'.
    The statement has been terminated.

    As you can see, there isn't enough information to determine:

    1.  Which Entity was involved that caused the error.  What is the Product entity?  No, it was the Order_Detail entity.

    2.  Even if you could figure out it was the Order_Detail entity, which specific Order_Detail entity was the one caused the error?  The first or second one?  There isn't enough information to determine that from the error.

     


    Tom Overton


    • Edited by Tom_Overton Friday, October 28, 2011 8:21 PM typo fix
    • Marked as answer by PapaStoke Friday, November 4, 2011 4:47 PM
    Friday, October 28, 2011 4:56 PM

All replies

  • Hi CoolGuy,

    I think you can grab the information from the SavingChanges event and pass it back to the client.  What I did is put a property in the ObjectContext that can be read by the client to iterate through to dermine what the entities were that were being saved before an error occured.  In my example my context is CAPSEntities and I have an entity named TOM_TABLE.

    Here is my extenion of the ObjectContext code:

    using System;
    using System.Collections.Generic;
    using System.Linq;
    using System.Text;
    using System.Data.Objects;
    using System.Data;
    using System.Data.Objects.DataClasses;
    using System.Data.EntityClient;
    using System.ComponentModel;
    using System.Xml.Serialization;
    using System.Runtime.Serialization;
    
    namespace ConsoleApplication17
    {
        public partial class CAPSEntities 
        {
            public List<ObjectStateEntry> entities { get; set; }
    
            partial void OnContextCreated()
            {
                
                this.SavingChanges
                    += new EventHandler(context_SavingChanges);
            }
            
            private void context_SavingChanges(object sender, EventArgs e)
            {
                entities = new List<ObjectStateEntry>();
                foreach (ObjectStateEntry entry in
                    ((ObjectContext)sender).ObjectStateManager.GetObjectStateEntries(
                    EntityState.Added | EntityState.Modified))
                {
                    this.entities.Add(entry);
                            
                }
            }
    
            
        }
    }
    


    Here is my client code:

     class Program
        {
            static void Main(string[] args)
            {
                CAPSEntities ctx = new CAPSEntities();
    
                TOM_TABLE t = new TOM_TABLE();
                string error_entities = "";
    
                try
                {
                    t.ID = 1;
                    t.Name = "name 1";
    
                    ctx.TOM_TABLE.AddObject(t);
    
                    ctx.SaveChanges();
                }
                catch (System.Data.UpdateException ex)
                {
    
                    foreach (ObjectStateEntry entry in ctx.entities)
                    {
                        Type type = entry.Entity.GetType();
                        error_entities += type.Name + "\r\n";
                    }
                    MessageBox.Show("Error: " + ex.InnerException + " happened when saving these entities: " + error_entities);
    
                }
    
            }
        }
    

     

     

     


    Tom Overton
    Friday, October 28, 2011 2:47 PM
  • I need to be able to determine which of the error_entities caused the database error.  It looks like your code will just tell me the error and list all of the entities involved in the insert.

    Friday, October 28, 2011 3:26 PM
  • Yeah, that's basically what it does.  I don't think there is ever going to be a way to get the exact entity that caused the error.  That's really impossible when you think about it because all EF is doing is sending a SQL statement to SQL Server.  When an error occurs on the sql server end, sql returns an error without knowing anything about the fact that enties are involved.  When EF gets the SQL error back all it can do is basically wrap the sql error inside the UpdateException for informational purposes.  EF has no way to be able to parse the SQL error and map it back to a specific entity or entities.  There are just too many potential SQL errors that could occur. 

    The closest you could get is maybe create a routine that takes the SQL Error string, and do a:

    int index = Sqlerr.IndexOf("mytable");

    And if it returns a value you know that table was involved in the sql error in some way, and then the client can figure out from that which entity (or entities) map to that table.


    Tom Overton

    • Edited by Tom_Overton Friday, October 28, 2011 4:05 PM
    Friday, October 28, 2011 3:51 PM
  • I assume there is an event to hook into EF where it is generating and sending the SQL.  I assumed that it is generating seperate insert statements for each email address that I am sending

    Friday, October 28, 2011 4:03 PM
  • Really?  No way to find what person object caused duplicate key?
    JP Cowboy Coders Unite!
    Friday, October 28, 2011 4:10 PM
  • In my example I am only submitting a single person with multiple emailAddresses, so my issue is detemining which emailAddress caused the error.
    Friday, October 28, 2011 4:13 PM
  • You have to consider what is going on behind the scenes to see why it's hard to match back the exact address and entity that caused an error. 

    Let's take an example using the Northwind database.  All we're going to do is create an Order, and then add two Order_Details entities to the Order and then save it all.  The first Order_Detail will save okay, but the second one will fail because we didn't include a valid ProductID:

        class Program
        {
            static void Main(string[] args)
            {
                NorthwindEntities context = new NorthwindEntities();
                Order_Detail od;
                Order order = new Order();
                order.CustomerID = "ALFKI";
    
                
                od = new Order_Detail();
                od.Quantity = 10;
                od.ProductID = 1;
    
                order.Order_Details.Add(od);
                
                
                od = new Order_Detail();
                od.ProductID = 9999;  //invalid
                od.Quantity = 5;
    
                order.Order_Details.Add(od);
    
                context.Orders.AddObject(order);
                try
                {
                    context.SaveChanges();
                }
                catch (System.Data.UpdateException ex)
                {
                    MessageBox.Show(ex.InnerException);
                }
                
    
            }
        }
    

      Here is the batch of sql statements that get sent to SQL Server (all at one time) when we do SaveChanges:

    exec sp_executesql N'insert [dbo].[Orders]([CustomerID], [EmployeeID], [OrderDate], [RequiredDate], [ShippedDate], [ShipVia], [Freight], [ShipName], [ShipAddress], [ShipCity], [ShipRegion], [ShipPostalCode], [ShipCountry])
    values (@0, null, null, null, null, null, null, null, null, null, null, null, null)
    select [OrderID]
    from [dbo].[Orders]
    where @@ROWCOUNT > 0 and [OrderID] = scope_identity()',N'@0 nchar(5)',@0=N'ALFKI'
    go
    exec sp_executesql N'insert [dbo].[Order Details]([OrderID], [ProductID], [UnitPrice], [Quantity], [Discount])
    values (@0, @1, @2, @3, @4)
    ',N'@0 int,@1 int,@2 decimal(19,4),@3 smallint,@4 real',@0=11091,@1=1,@2=0,@3=10,@4=0
    go
    exec sp_executesql N'insert [dbo].[Order Details]([OrderID], [ProductID], [UnitPrice], [Quantity], [Discount])
    values (@0, @1, @2, @3, @4)
    ',N'@0 int,@1 int,@2 decimal(19,4),@3 smallint,@4 real',@0=11091,@1=9999,@2=0,@3=5,@4=0
    go
    


    And when this batch of SQL gets sent to SQL Server, all EF gets back when the error occurs is this:

    The INSERT statement conflicted with the FOREIGN KEY constraint "FK_Order_Details_Products". The conflict occurred in database "Northwind", table "dbo.Products", column 'ProductID'.
    The statement has been terminated.

    As you can see, there isn't enough information to determine:

    1.  Which Entity was involved that caused the error.  What is the Product entity?  No, it was the Order_Detail entity.

    2.  Even if you could figure out it was the Order_Detail entity, which specific Order_Detail entity was the one caused the error?  The first or second one?  There isn't enough information to determine that from the error.

     


    Tom Overton


    • Edited by Tom_Overton Friday, October 28, 2011 8:21 PM typo fix
    • Marked as answer by PapaStoke Friday, November 4, 2011 4:47 PM
    Friday, October 28, 2011 4:56 PM
  • Thanks Tom for posting that...
    JP Cowboy Coders Unite!
    Friday, October 28, 2011 5:12 PM
  • Thanks for clarifying.  I think that the negative (loss of this info) is much greater than the small gain that is achieved from sending this in one statement.  Do you think that this may change in the future?

    It looks like these statements are executed seperately.  Is there a way to get EF to wrap them into a transaction so they either all or none are committed?

    Friday, October 28, 2011 6:26 PM
  • It doesn't show in any tracing, but EF does wrap those statements in a transaction.   So it will not let the Order get created if one of the Order_Detail records doesn't get written correctly.

    And I agree, it would be nice if these could be executed separately so EF could tell you in a more granular level exactly which entity insert or update failed.  Maybe in a future version, but I haven't heard anything about plans to yet. 


    Tom Overton
    Friday, October 28, 2011 6:55 PM
  • Hi,

    I am writing to check the status of the issue on your side.  Would you mind letting us know the result of the suggestions?

    If you need further assistance, please feel free to let me know.   I will be more than happy to be of assistance.

    Have a nice day.


    Alan Chen[MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Friday, November 4, 2011 6:35 AM
  • I used a change interceptor and am checking for dups and returning an error if it finds a dup.
    Friday, November 4, 2011 4:48 PM