none
Why am I getting an error on DeleteAllOnSubmit while trying to delete a record from a database? RRS feed

  • Question

  • Hi,

     

    I have two tables with the following fields:

     

    Assets

    HistoricalPrices

    AssetID

    Date

    Ticker

    AssetID

    Name

    OpenPrice

     

    I have a 1-to-many relationship between the table Assets and the table HistoricalPrices. Because of this, in order to delete an asset I first need to delete all the historical prices of that asset.

     

    I get the following InvalidOperationException error “Cannot remove an entity that has not been attached.”  on the DeleteAllOnSubmit line of code. This is my code:

     

    TTSDatabaseDataContext db = new TTSDatabaseDataContext();

    Asset asset = new Asset();

    // Get asset information from the ComboBox which contains asset items

    asset = (Asset)ticker.SelectedItem;     

    // Delete the historical prices of the asset to delete

    db.HistoricalPrices.DeleteAllOnSubmit(asset.HistoricalPrices);

    // Delete the asset

    db.Assets.DeleteOnSubmit(asset);

    db.SubmitChanges();

     

    What is incorrect in my code? I’m using an example very similar to one that I found on the book “Pro LINQ Language Integrated Query in C# 2008”.

     

    I’m developing an application using Visual C# 2008 Express Edition and SQL Server Express Edition.

     

    Thanks.

     

    Best regards,

    Miguel.

     

    Thursday, November 19, 2009 8:11 PM

Answers

  • Hi Miguel,

     

    There are several points for your issue,

    1) Make the datasource of combobox global.

    Or there might be some problem when get the valuemember and display member.

    Also the datacontext in the datasource is global too.

     

    2) The most security way to get the selected asset is get by query.

    By this way, you will not encounter any attach problem since it is done by LINQ to SQL.

     

    I write a sample for your issue,

    Sample tables: Articles    1—M      Feedback

            IQueryable<Article> articles;

            DataClasses1DataContext context;

     

            private void Form1_Load(object sender, EventArgs e)

            {

                context = new DataClasses1DataContext();

                articles = context.Articles;

                comboBox1.DataSource = articles;

                comboBox1.DisplayMember = "ArticleName";

                comboBox1.ValueMember = "ArticleID";

            }

     

            private void button1_Click(object sender, EventArgs e)

            {

                if (comboBox1.SelectedValue != null)

                {

                    try

                    {

                        Article article =

                            context.Articles.Where(r => r.ArticleID == (int)comboBox1.SelectedValue).FirstOrDefault();

                        article.Feedbacks.Load();

                        context.Feedbacks.DeleteAllOnSubmit(article.Feedbacks);

                        context.Articles.DeleteOnSubmit(article);

                        context.SubmitChanges();

                    }

                    catch (Exception ex)

                    {

                        MessageBox.Show(ex.ToString());

                    }

                }

            }

    Does this work for you? If you have any questions or concerns, please update the thread and we will have a further discussion.

     

     

    Best Regards

    Yichun Feng


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    • Proposed as answer by Yichun_Feng Thursday, November 26, 2009 2:24 AM
    • Marked as answer by Miguel T. _ Thursday, November 26, 2009 6:07 AM
    Wednesday, November 25, 2009 4:48 AM

All replies

  • Change the code as below

    // Delete the historical prices of the asset to delete

    db.HistoricalPrices.DeleteAllOnSubmit(asset.HistoricalPrices);
    // Delete the asset

    db.SubmitChanges();

     

    db.Assets.DeleteOnSubmit(asset);

    db.SubmitChanges();


    Hope this works

    Jbasingh

    Friday, November 20, 2009 11:58 AM
  • Hi Jbasingh,

    It would still not work. The question is that I get the InvalidOperationException error "Cannot remove an entity that has not been attached." on the DeleteAllOnSubmit line of code which would been before addint the new line of code that you proposed.

    I just test addint the following line of code just before the DeleteAllOnSubmit:

    db.HistoricalPrices.AttachAll(asset.HistoricalPrices);

    but I get an NotSupportedException error "An attempt has been made to Attach or Add an entity that is not new, perhaps having been loaded from another DataContext.  This is not supported.".

    Why can't I delete all the related historical prices for the asset selected?

    Thanks.

    Best,
    Miguel.
    Friday, November 20, 2009 2:15 PM
  • Hi,

    In my code when I'm referring to the Datacontext I always use:


    TTSDatabaseDataContext db = new TTSDatabaseDataContext();
    ..... (code)

    instead of:

    using (TTSDatabaseDataContext db = new TTSDatabaseDataContext())
    {
    ..... (code)
    }

    Could my errors be related to this?

    Best,
    Miguel. 

    Friday, November 20, 2009 2:20 PM

  • Nothing make difference as it is used to dispose the TTSDatabaseDataContext() object

    Cannot remove an entity that has not been attached - This message indicates that HistoricalPrices data class is not atatched to Asset data class


    Could you check whether both Data classes have proper mapping?.I meant Entityset and EntiryRef Mapping are set

    Also Does the combo box return correct value which can be explicitly converted to Assets?


    jbasingh


    Friday, November 20, 2009 3:01 PM
  • Hi Jbasingh,

    If I correctly understood it, I believe that both classes (Asset and HistoricalPrice) are correctly mapped on the .dbml file. I didn't have any problem inserting data in the database. When I debug the application I can see that all the fields of the asset variable that is selected in the ComboBox are fill with the correct information.

    On the MainForm_Load event I fill the ComboBox with all the assets that are in the table Assets of the database. The question is that I only show the ticker field. This is the code that I use to accomplish this:

       // Updates the ComboBox with all the assets of the database (show only the tickers)
       private void UpdateTickerOfAsset()

       {

          IQueryable<Asset> assets = QueryEngine.GetAllAssetsInDB();

          uxTickerOfAsset.DataSource = assets;

          uxTickerOfAsset.DisplayMember = "Ticker";

          uxTickerOfAsset.ValueMember = "AssetID";

        }


    The function Query.GetAllAssetsInDB() uses a query in LINQ to SQL to obtain that information.

    Could these errors instead be related to the fact that my database is not included in my solution? I decided not to include my database in my solution in order to see the changes in the tables of my database within the IDE of Visual Studio. By doing this, I can, for example, insert a new asset in the database and in the Database Explorer of Visual Studio IDE when I right click on the "Show table data" of the table Assets I can see the new asset that was just inserted. When I created the database I saw a dialog with the following question (I choose "NO"):

    "The connection you selected uses a local data file that is not in the current projec. Would you like to copy the file to your project and modify the connection? If you copy the data file to your project, it will be copied to the project's output directory each time you run the application. Press F1 for information on controlling this behavior".

    Best,
    Miguel.
    Friday, November 20, 2009 4:07 PM
  • Try data load option to load the historical data table as well:

    DataLoadOptions

     

    ddlOptions = new DataLoadOptions();
    ddlOptions.LoadWith<Assetsoe => oe.HistoricalPrices);
    db.LoadOptions = ddlOptions;

    Then try delete on submit, or you can create something like this:

    public
    static void Delete<T>(Func<T, bool> match) where T : class
    {
    using (TTSDatabaseDataContext context = new TTSDatabaseDataContext())
    {
    Table<T> gTable = context.GetTable<T>();
    var records = gTable.Where(match);
    gTable.DeleteAllOnSubmit(records);
    }
    }

    Then you can call delete like this:

    Delete<HistoricalPrices>(hp => hp.AssetID == 1);

    Hope this helps.


    kashif
    Tuesday, November 24, 2009 8:45 PM
  • Hi Miguel,

     

    There are several points for your issue,

    1) Make the datasource of combobox global.

    Or there might be some problem when get the valuemember and display member.

    Also the datacontext in the datasource is global too.

     

    2) The most security way to get the selected asset is get by query.

    By this way, you will not encounter any attach problem since it is done by LINQ to SQL.

     

    I write a sample for your issue,

    Sample tables: Articles    1—M      Feedback

            IQueryable<Article> articles;

            DataClasses1DataContext context;

     

            private void Form1_Load(object sender, EventArgs e)

            {

                context = new DataClasses1DataContext();

                articles = context.Articles;

                comboBox1.DataSource = articles;

                comboBox1.DisplayMember = "ArticleName";

                comboBox1.ValueMember = "ArticleID";

            }

     

            private void button1_Click(object sender, EventArgs e)

            {

                if (comboBox1.SelectedValue != null)

                {

                    try

                    {

                        Article article =

                            context.Articles.Where(r => r.ArticleID == (int)comboBox1.SelectedValue).FirstOrDefault();

                        article.Feedbacks.Load();

                        context.Feedbacks.DeleteAllOnSubmit(article.Feedbacks);

                        context.Articles.DeleteOnSubmit(article);

                        context.SubmitChanges();

                    }

                    catch (Exception ex)

                    {

                        MessageBox.Show(ex.ToString());

                    }

                }

            }

    Does this work for you? If you have any questions or concerns, please update the thread and we will have a further discussion.

     

     

    Best Regards

    Yichun Feng


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    • Proposed as answer by Yichun_Feng Thursday, November 26, 2009 2:24 AM
    • Marked as answer by Miguel T. _ Thursday, November 26, 2009 6:07 AM
    Wednesday, November 25, 2009 4:48 AM
  • Hi Kpbutt,

     

    In fact your code worked even without using the Load options. Thanks for the tips.

     

    Your solution queries all the historical prices of the asset selected in the ComboBox directly in the table “HistoricalPrices”. For that reason, I believe that your code doesn’t require using the Load options. I believe that if instead you would have queried the asset selected in the table “Assets” it would have been necessary to Load the corresponding historical prices of that asset.

     

    However, I still don’t understand why my code doesn’t work. I try it again by adding the following line of code but it still hasn’t worked:

     

    asset.HistoricalPrices.Load();

     

    If the items contained in the ComboBox are of type Asset I believe that my new version of my code should have worked.

     

    Thanks for your help.

     

    Sorry for my late reply.

     

    Best,

    Miguel.

    Thursday, November 26, 2009 6:05 AM
  • Hi Yichun_Feng,

     

    Your code works fine. In fact, it works even without declaring DataContext and IQueryable<T> as global.  In addition, I have done several tests with different combinations of code in order to see if I understand why my code doesn’t work. The conclusion is that the problem is related to the information that is contained in the ComboBox. The ComboBox apparently contains “Asset” entities but it looks that I can’t use them to get the corresponding historical prices. I don’t fully understand this but from now on I will start using your proposed solution.

     

    However, and since it worked the way I test it, I will try to avoid declaring DataContext and IQueryable<T> as global in my form’s code. For example, I created a class “QueryEngine” in which I have static methods that contains most of my queries that return IQueryable<T>. I have done this to create a separation between the presentation layer and the business and data layers. Am I not breaking this rule if I declare the DataContext and the IQueryable<T> as global in my form code?

     

    In addition, in my class “QueryEngine” each of the methods that contain the queries creates a new DataContext (TTSDatabaseDataContext in the case of my application). Should I avoid doing this? What is the alternative? I don’t want to have the code to perform my queries in the code of my form.

     

    Thanks for your tips.

     

    Sorry for my late reply.

     

    Best,

    Miguel.

    Thursday, November 26, 2009 6:07 AM