Dhanushka's Web Blog

We all know that data manipulation using tables have been made very easy by LINQ to SQL right! For an example a sample data retrieval and update query using LINQ will look something like this.

I'll just note down the steps involved so rookies will have an idea of what I'm talking about

1. Add a new LINQ to SQL class file (.dbml) to your project
   
2. Then drag and drop a table you want to query using the server explorer of VS (lets name the new file as Demo.dbml)
   

Here is some sample code to get you started with

var context = new DemoDataContext(); //get the data context you want to query
//retreive data from the table
var records = from tableTest in dc.DemoTable
              where tableTest.condition == -1
              select tableTest;

Now let's play with them a bit

foreach (var record in records){
  try
   {
    var rowInfo = context.DemoTable.Single(ob => ob.Id == rec.Id);
    var someValue=GetValue();
    rowInfo.condition = someValue > 0 ? 1 : 0; //Change a value of a property
    context.SubmitChanges();//Save the changed data to the table
   }
  catch (Exception ex)
  {
    Console.WriteLine(ex.Message);
    continue;
  }
}

That's really straight forward right! We can use the same set of steps to manipulate data using SQL Views. But there seems to be some sort of a bug in VS2010 when using LINQ to SQL class files. When we drag and drop a table to a dbml file the primary key of the table is identified and is set correctly. But if we use a VIEW that incorporates a set of tables using UNIONS this does not happen. Making it impossible to save or update data in the underlying table because of the missing primary key. You will run the same code for a view but no data will get saved.
 To avoid such a bizarre situation you need to manually set the primary key of the view using properties. To do that select the view that you needs to work on and in its attributes set click on its primary key column and go to its properties. And set the Primary Key property to true.

Now you will be able to work with your views without any problems.