Linq : Why this approach don't seem to work? What should be the standard approach?

Question

Why this approach don't seem to work? What should be the standard approach?

[Database(Name = "Test")]
[Table(Name = "Order")]
public class Order
{
    [Column(Name = "ID", IsPrimaryKey = true)]
    public int ID { get; set; }

    [Column(Name = "OrderDate")]
    public DateTime OrderDate { get; set; }


    public static Order Get(int id)
    {
        Order item = null;

        try
        {
            DataContext dc = new DataContext(@"Data Source=.\sqlexpress;Initial Catalog=Test;Integrated Security=True");

            var items = from order
                        in dc.GetTable<Order>()
                        where order.ID == id
                        select order;

            item = items.FirstOrDefault<Order>();

            dc.Dispose();
        }
        catch (Exception ex)
        {
            item = null;

            throw ex;
        }

        return item;
    }

    public bool Delete()
    {
        bool success = false;

        try
        {
            DataContext dc = new DataContext(@"Data Source=.\sqlexpress;Initial Catalog=Test;Integrated Security=True");

            dc.GetTable<Order>().DeleteOnSubmit(this);

            success = true;
        }
        catch (Exception ex)
        {
            success = false;

            throw ex;
        }

        return success;
    }
}

class Program
{
    static void Main(string[] args)
    {            
        Order order = Order.Get(1);

        order.Delete();

        Console.ReadLine();
    }
}

This code generates the following exception:

InvalidOperationException : "Cannot remove an entity that has not been attached."

I have also tried the following:

DataContext dc = new DataContext(@"Data Source=(local)\sqlexpress;Initial Catalog=Relationships_Test;user=;password=;Integrated Security=True");

dc.GetTable<Order>().Attach(this);
dc.GetTable<Order>().DeleteOnSubmit(this);

It didn't work either.

Answer 1

You are creating a new DataContext in the Delete() method that does not "know" the Order object you're trying to delete. ("entity is not attached")

Attach the Order object to the DataContext first.

EDIT
You may have to call item.Detach() in your Get(...) method to successfully call dc.Orders.Attach(...) in the Delete method.

EDIT 2
Oh, and by the way: Your catch handlers are somewhat useless - they basically do nothing, as the return value will not be used when the exception is rethrown. Catch exceptions in the calling code - no need to return a success flag or set item to null...

EDIT 3
Of course, as Chris is saying, you must call SubmitChanges to actually do something. However, I'd presume you're doing this already and just asked because of the exception you were getting.

Answer 2

Even if you get it attached to the context, you must call DataContext.SubmitChanges() for the commands to actually be executed.

If you are going to call DataContext.Dispose(), you should be doing it from the finally block of the try/catch/finally clause. Instead of calling it by hand, you probably should use a using statement.

Answer 3

I think it's more standard to have your business objects be unaware of the persistence mechanism as much as possible. Look at the way the LINQ to SQL designer creates its entities. The objects themselves are unaware of the DataContext. They have hooks to allow the data context to add notification mechanisms for changes and other hooks to allow the entity to add code for specific events that occur in its lifetime (creation,validation,...). They're also created as partial class implementations so you can add more code as needed and implement the partial method hooks provided.

A better, more standard way would be to use the designer and designer-generated classes as your ORM, adding more code via partial class implementations. Alternatively, you could layer a Repository pattern (or here) on top of the generated data context or even choose a different ORM (nHibernate, for example).

Answer 4

If you have an object (Order item in this case) that is created in the context of a DataContext, once you Dispose the DataContext, that object is orphaned.

To delete an orphaned object from the database, you need to either not dispose the original DataContext or somehow attach the object to a new DataContext (which can be tricky).

The easiest way to deal with this is to new the DataContext in your Main method and then Dispose the DataContext at the end of the main method (that is a standard pattern).