none
How to reset datacontext error state after unsuccessful persistance to MS-SQL RRS feed

  • Question

  • Hi everyone,
    I'm beginer in LINQ to SQL. I'm using LINQ to SQL in ASP.NET MVC app with MS-SQL.
    I've choosed to use generic class to implement generic repository on LINQ to SQL.
    This works with no problems until any DB exception is thrown.

    Example:
    There is Orders and Products table. Orders is related to Products table 1 : MANY.

    When I, using LINQ to SQL, try to delete row from Orders which has relation to rows in products my DB throws exception about referential integrity.
    This is expected and OK.
    I catch this in try catch and perform exception handling.
    Problem is that after this incident any INSERT or UPDATE or DELETE on any other LINQ to SQL entity without any relation to tables Product and Order results in same error!?

    I assume that LINQ to SQL "remembers" in some kind of Queue that he failed in deleting of this row and any request for CRUD operation for whatsoever after wards simply pops this up, not letting requested task to get completed?

    So, how should I tell LINQ to SQL caching that he should forget about this exception from database and continue with life. Is there any dispose command on datacontext or what?

    Any link to more info would be appreciated.

    Down here are excerpts from generic class to communicate linq to sql context:

    Thanks people for any help !

    Sinbad
    public void DeleteBy(int id)
    {
    Delete(GetEntityOrDefault(id), true);
    }

    public TEntity GetEntityOrDefault(int id)
    {
    return GetEntity(id)??(new TEntity());

    }


    [System.ComponentModel.DataObjectMethod(System.ComponentModel.DataObjectMethodType.Select)]
    public TEntity GetEntity(int id)
    {
    var query = string.Format("Select * from {0} where {1} = {2}", new object[] { TableName, GetPrimaryKey().Name, id });
    return DataContext.ExecuteQuery<TEntity>(query).FirstOrDefault();
    }

    public class BaseCMSRepository<TEntity>
    where TEntity : class, new()
    {
    protected static DataContext DataContext
    {
    get
    {
    return ...//valid LINQ to SQL datacontext
    }
    }
    .....




    Sunday, June 21, 2009 11:19 AM

Answers

  • LINQ to SQL isn't remembering the exception but it does have the object that didn't get deleted/inserted/updated last time still in it's list of objects to persist. The best course of action would be to make sure you never try to do this - perhaps perform some checking in your application for the relationships/criteria that would throw this before you add it to the DeleteOnSubmit queue. If that is somehow not possible then the next best thing to do would be to throw away that DataContext and start a new one. Bear in mind though that this exception doesn't mean just 1 operation failed, the entire batch of operations would not have taken place and so you will be loosing all that. [)amien
    Sunday, June 21, 2009 3:14 PM
    Moderator

All replies

  • LINQ to SQL isn't remembering the exception but it does have the object that didn't get deleted/inserted/updated last time still in it's list of objects to persist. The best course of action would be to make sure you never try to do this - perhaps perform some checking in your application for the relationships/criteria that would throw this before you add it to the DeleteOnSubmit queue. If that is somehow not possible then the next best thing to do would be to throw away that DataContext and start a new one. Bear in mind though that this exception doesn't mean just 1 operation failed, the entire batch of operations would not have taken place and so you will be loosing all that. [)amien
    Sunday, June 21, 2009 3:14 PM
    Moderator
  • Thanks Damien !

    It was right in front of my eyes.
    I access singleton wraper instance of datacontext all the time. Can't anymore remember where I read that this should be done this way. :(
    After googling a bit  I figure that correct way should be:

    using (new NorthwindDatacontext())
    {
       ....
       Do something on Linq2SQL entites
       ...
    }

    In this way datacontext lives only for period required for that unit of work not anymore.
    Is it correct to treat Linq 2 SQL datacontext as ADO.NET SQL connection, use as short as possible and make sure you have disposed it ?

    Thanks again !

    Sinbad
    Sunday, June 21, 2009 7:42 PM