Problem in saving in SQL file because of primary key.

See more:

C#

SQL

Hi,
There is a challenging problem with saving files is SQL.
I have a table and a list:

public Table<TableBuildResource> tableBuildResource = null;
public List<TableBuildResource> listBuildResource = null;

After I load sql data, I convert table to list.
For saving, I have to convert list to table. But, always there is problem with primary key, because although table records are deleted, adding those records again from list are assumed as error. The is no redundancy in the list due to primary key. but still there is problem with saving.

private void SaveBuildResource()
{
    var DelList = from c in tableBuildResource
                  select c;
    tableBuildResource.DeleteAllOnSubmit(DelList);
    foreach (TableBuildResource t in listBuildResource)
        tableBuildResource.InsertOnSubmit(t);
    SubmitChanges();
}

I use SQL-CE and linq-to-sql in C#.

What should I do?

Eddy Vluggen wrote:

You'd have to iterate your Table-List, updating every changed record.

It slows down the speed of program. Tables are much slower that Lists. Also it needs reflection(to work for all classes because my program has many classes that has to be saved in sql file). Also, it needs to a loop nested in another to check if a row exists or is new(too much slow for a long database. At least 10^6 checks!)

I wonder, if overwriting a row is so dispensable for SQL to be neglected?
Also I can solve the problem even if SQL consented to delete all rows and forgot them. But as if it is going to annoy people.
It is more funny when I googled and found no example of overwriting a row. In all samples the programmer just defined a new row and added manual data and then inserted the row. then saved and sometimes used complex flexibilities of linq-to-sql. But I found no simple practical sample. It looks like a joke.

The thing I am looking for is so easy. Why Linq Tables have such unfriendly manner?