title: LINQ to SQL optional Column
body: <p>I need a functionality to have optional columns in a LINQ to SQL definition. So that LINQ to SQL normally ignores this column within selects and updates etc.
  But if a select contains a value for this column it should use this value.</p>
  <p>Long version:</p>
  <h2>The Scenario</h2>
  <p>I've the following tables:<br />
  <img src="https://i.stack.imgur.com/i1PhY.png" alt="alt text" /></p>
  <p>If Field.FieldViews.Count() greater than 0 than should this field be visible.</p>
  <h3>The Problem</h3>
  <p>If I check the visibility as mentioned above with:</p>
  <pre><code>Field.FieldViews.Count()
  </code></pre>
  <p>Than it makes a single query to the database for every field.
  So in my project sometimes up to 1000x</p>
  <h2>My Solution</h2>
  <p>I wrote a stored procedure:</p>
  <pre><code> SELECT
     f.*,
     (SELECT COUNT(*) FROM [fieldViews] v WHERE v.fieldId = f.fieldId) AS Visible
    FROM [fields] f
    WHERE
     f.X BETWEEN @xFrom AND @xTo AND
     f.Y BETWEEN @yFrom AND @yTo
  </code></pre>
  <p>To use this additional column I added the following code:</p>
  <pre><code> public partial class Field
    {
     private bool visible = false;
   
     [Column(Storage = &quot;Visible&quot;, DbType = &quot;INT&quot;)]
     public bool Visible
     {
      get
      {
       return visible;
      }
      set
      {
       visible = value;
      }
     }
    }
  </code></pre>
  <p><strong>This works just fine.</strong></p>
  <p>But ...</p>
  <h3>The Problem</h3>
  <p>If I fetch entries from Fields table without the stored procedure:</p>
  <pre><code>from d in DataContext.Fields select d;
  </code></pre>
  <p>I got the following error:</p>
  <blockquote>
  <p><code>Bad Storage property: 'Visible' on member 'Models.Field.Visible'.</code></p>
  </blockquote>
  <p>So I added the column &quot;Visible&quot; to the database table:</p>
  <pre><code>ALTER TABLE dbo.Fields ADD
   Visible int NOT NULL CONSTRAINT DF_Fields_Visible DEFAULT 0
  </code></pre>
  <p><strong>With this I could solve the error mentioned above.</strong></p>
  <p>But …</p>
  <h3>Next problem</h3>
  <p>I have fetched some Field objects using the stored procedure.
  Now I make some changes to some of these objects.
  If I now try to submit these changes it doesn't work. Looking at the generated query unveils the reason:</p>
  <pre><code> UPDATE [dbo].[Fields]
   SET [X] = @p3
   WHERE ([FieldId] = @p0) AND ([X] = @p1) AND ([Y] = @p2) AND ([Visible] = 3)
  </code></pre>
  <p>The problem here is, that it uses the &quot;Visible&quot; column in the where statement. But the &quot;Visible&quot; column is always 0.
  Visible is only greater than 0 if I fetch data using the stored procedure...</p>
  <h2>What I need</h2>
  <p>Something like the ColumnAttribute where the column is not required</p>
  <p>or</p>
  <p>a way to remove a column from the where statement when updating.</p>