CODEWITHSUNDEEP
c#linq-to-sql
flaudre
flaudre

Reputation: 2298

Linq to SQL: Exclude a to be inserted column from data context

I am stuck with this topic for a while now and I would appreciate a hint or an explanation on how to approach my problem.

I am trying to implement ORM to map my objects with a database. I have a mother object with several child and subchild elements. When I add the mother element (Position) to my DataContext, all the child elements are also being added. Inserting a new object to the database works fine. 

class Position{
    public BE be { get; set; }; // Child element

    // and so on...
}

class BE{
    [XmlIgnore, Column]
    public Nullable<int> Rueckstrom1Id { get; set; }
    [XmlElement(typeof(Rueckstrom)), Association(Storage = "rueckstrom1", ThisKey = "Rueckstrom1Id", IsForeignKey = true)]
    public Rueckstrom Rueckstrom1 {
        get { return this.rueckstrom1.Entity; }
        set { this.rueckstrom1.Entity = value; }
    }

    [XmlIgnore, Column]
    public Nullable<int> Rueckstrom2Id { get; set; }
    [XmlElement(typeof(Rueckstrom)), Association(Storage = "rueckstrom2", ThisKey = "Rueckstrom2Id", IsForeignKey = true)]
    public Rueckstrom Rueckstrom2 {
        get { return this.rueckstrom2.Entity; }
        set { this.rueckstrom2.Entity = value; }
    }

    // and so on...
}

class Rueckstrom{
    [Column(IsPrimaryKey = true, IsDbGenerated = true, DbType = "Int NOT NULL IDENTITY(1,1)")]
    public int Id {
        get { return this.id; }
        set { this.id = value; }
    }

    [Column(DbType = "Decimal(8,2)")]
    public decimal Sek {
        get { return this.sek; }
        set {  this.sek = value; }
    }

    // and so on...
}

Here's the thing: When I add my "Position" element to the data context like

  dataContext.Position.InsertOnSubmit(position);

All the child elements such as "Rueckstrom1", "Rueckstrom2", ... are also added to the context and are finally being inserted to the database on db.SubmitChanges();

I want to prevent this insertion, in case that one of my totally 7 "Rueckstrom" elements have their "Sek" property of value 0.

Since the Id has not been generated yet by the database (thus is 0) because it is an all new object, I cannot figure out how to avoid a certain child element (of type "Rueckstrom") to be inserted on SubmitChanges().

I know I could set the whole "Rueckstrom" object to NULL if its "Sek" property is 0, but due to my implementation in my program, all 7 "Rueckstrom" childs are always non-NULL.

Any ideas?

Upvotes: 0

Views: 1435

Answers (1)

user978139
user978139

Reputation: 579

One of my pet hates about Linq 2 SQL is that it automatically marks entities for insertion when either:

  1. You assign a new entity with an associated object from the data context WITHOUT explicitly calling InsertOnSubmit
  2. Or like you have experienced, when you set that individual object for insertion it automatically traverses the tree of entities and marks those for insertion too

Placing a constraint on the DB doesn't really resolve the problem as the data context will still try and insert the entity and throw the exception like you have experienced. It would be better to prevent the Linq 2 SQL context from inserting those objects.

Todo this I have created an extension method InsertOnSubmitExplicitly for the Table class which you may find useful as you explicitly set those entities for which you want to insert. For those you don't want to insert (i.e. Sek=0), you simply don't call this method or the default InsertOnSubmit method.

Solution:

    public static void InsertOnSubmitExplicitly<TEntity>(this Table<TEntity> table, TEntity obj)
        where TEntity : class
    {
        ChangeSet preSet = table.Context.GetChangeSet();
        if (preSet == null)
            throw new Exception("Unable to retrieve change set on data context before insert");

        table.InsertOnSubmit(obj);

        ChangeSet postSet = table.Context.GetChangeSet();
        if (postSet == null)
            throw new Exception("Unable to retrieve change set on data context after insert");

        var markAsDeleted = (from post in postSet.Inserts.Where(n => !ReferenceEquals(n, obj))
                             join pre in preSet.Inserts on post equals pre into temp1
                             from t1 in temp1.DefaultIfEmpty()
                             where t1 == null
                             select post);

        foreach (var entity in markAsDeleted)
            table.Context.GetTable(entity.GetType()).DeleteOnSubmit(entity);
    }

Usage:

    dataContext.Position.InsertOnSubmitExplicitly(position);

Caveat:

As stated in point 1 above, if you create a new entity and assign an associated entity to it from the data context it will automatically mark it for insertion. However, like I have implemented in my solution you could create an extension method for the data context which explicitly discards all insertions.

Upvotes: 1

Related Questions