Linq To Sql optional Column

Question

My problem in short:

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.

Long version:

The Scenario

I've the following tables:

If Field.FieldViews.Count() greater than 0 than should this field be visible.

The Problem

If I check the visibility as mentioned above with:

Field.FieldViews.Count()

Than it makes a single query to the database for every field. So in my project sometimes up to 1000x

My Solution

I wrote a stored procedure:

 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

To use this additional column I added the following code:

 public partial class Field
  {
   private bool visible = false;

   [Column(Storage = "Visible", DbType = "INT")]
   public bool Visible
   {
    get
    {
     return visible;
    }
    set
    {
     visible = value;
    }
   }
  }

This just works fine.

But ...

The Problem

If I fetch entries from Fields table without the stored procedure:

from d in DataContext.Fields select d;

I got the following error:

Bad Storage property: 'Visible' on member 'Models.Field.Visible'.

So I added the column "Visible" to the database table:

ALTER TABLE dbo.Fields ADD

Visible int NOT NULL CONSTRAINT DF_Fields_Visible DEFAULT 0

With this I could solve the error mentioned above.

But ...

Next problem

I have fetched some Field objects using the stroed procedure. Now I make some changes to some of these objects. If I now try to submit these changes it don't work. Looking at the generated query unveils the reason:

 UPDATE [dbo].[Fields]
 SET [X] = @p3
 WHERE ([FieldId] = @p0) AND ([X] = @p1) AND ([Y] = @p2) AND ([Visible] = 3)

The problem here is, that it uses the "Visible" column in the where statement. But the "Visible" column is always 0. Visible is only greater than 0 if I fetch data using the stored procedure...

What I need

Somethink like the ColumnAttribute where the column is not required

or

a way to remove a column from the where statement when updating.

Some ideas?

Thanks, Markus

Answer 1

We've solved the original problem by querying the details table instead, something like:

FieldViewsRepository.FieldViews.Where(fv => fv.FieldViewId == Field.FieldID).Count()

This generates just one query to the database.

If you need a list of objects with the additional Visible property, you could do something like this:

FieldRepository.Fields.Select(f => new { ID=f.FieldID, X=f.X, y=f.Y, Visible=f.FieldViews.Any() }

or

FieldRepository.Fields.Select(f => new { Field=f, Visible=f.FieldViews.Any() }

Unlike the case where Visible is a property on the Filed object and executes the query for each field, in this case the fields and the Visible property are fetched in one query from th e database.

Answer 2

There's no need to store this as a column on the field table - you can calculate this in the Field class:

public bool Visible
{
    get { return this.FieldViews.Count() > 0; }
}

Answer 3

I found a work around which solves my problem:

     private bool visible = false;
 public bool Visible
 {
  get
  {
   return visible;
  }
  set
  {
   visible = value;
  }
 }

 private int fakeVisible
 {
  get
  {
   return 0;
  }
  set
  {
   visible = value > 0;
  }
 }

 [Column(Name="Visible", Storage = "fakeVisible", DbType = "Int NOT NULL")]
 public int FakeVisible { get; set; }

Thanks anyway!