Question

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:
alt text

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 works just 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 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:

 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

Something like the ColumnAttribute where the column is not required

or

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

Answer 1

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!

Answer 2

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 3

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; }
}

The Scenario

The Problem

My Solution

The Problem

Next problem

What I need

友情链接