DLINQ- Entities being inserted without .InsertOnSubmit(...)?!

Question

Hello,

I ran into an interesting problem while using DLINQ. When I instantiate an entity, calling .SubmitChanges() on the DataContext will insert a new row into the database - without having ever called .Insert[All]OnSubmit(...).

//Code sample:
Data.NetServices _netServices = new Data.NetServices(_connString);

Data.ProductOption[] test = new Data.ProductOption[]
{
        new Data.ProductOption
        {
             Name="TEST1", 
             //Notice the assignment here
             ProductOptionCategory=_netServices.ProductOptionCategory.First(poc => poc.Name == "laminate")
        }
};

    _netServices.SubmitChanges();

Running the code above will insert a new row in the database. I noticed this effect while writing an app to parse an XML file and populate some tables. I noticed there were 1000+ inserts when I was only expecting around 50 or so - then I finally isolated this behavior.

How can I prevent these objects from being persisted implicitly?

Thanks, -Charles

Answer 1

Think of the relationship as having two sides. When you set one side of the relationship the other side needs to be updated so in the case above as well as setting the ProductOptionCategory it is effectively adding the new object to the ProductOptions relationship on the laminate ProductOptionCategory side.

The work-around is as you have already discovered and to set the underlying foreign key instead so LINQ to SQL will not track the objects in the usual way and require implicit indication it should persist the object.

Of course the best solution for performance would be to determine from the source data which objects you don't want to add and never create the instance in the first place.