Linq to sql table doesn't have Primary Key

Question

 

Hi,

 

I am working with Linq to Sql . I want to do insert,update, delete operations with the table which doesn't have primary key.

but i am unable to do this ,i am getting following error...

 

Can't perform Create, Update or Delete operations on 'Table(test)' because it has no primary key.

 

Is any one can help me to do operations for the table does't have primary key.

 

Thanks

Shiva

Answer 1

How would you perform delete/update/etc. without PK (whensome row doesn't have unique identification)? Yes you can delete/update/etc. using i.e. where x > 10, but this is only particular case. But then when updating some selected entity without identification, then saving changes back to DB will be impossible.

Answer 2

Hi,

 

But i Haven't understand how to solve the problem.Can you please explain more clear.

 

Shiva

 

Answer 3

The easiest way will be creating PK on the table.

Think about this. You have table with five rows; you select first two (whatever "first" means for this case); then you change something on both. Now it's impossible to send updates back to the database, because you *can't* say what rows you updated.

Imagine this table:
col1     col2     col3
 1         2          3
 a         b          c
 x         y          z  <<
 1         2          4
Think how you identify the row marked "<<"? No way until you add primary key (no matter that there's just one "this" row, between select and update there can be insert with same values ...).

Answer 4

A primary key defines the columns that make up the unique reference to each row and LINQ to SQL requires this so it can identify which row to update or delete when it goes back to the database.

As Jiri says the solution is to determine which columns can be used to uniquely identify the rows and to set these as the primary key.

[)amien

Answer 5

I would just add that if for some reason you cannot have a real primary key in your database table, but somehow there is such a set of columns that define a unique key for each row and that can be used for identity resolution, then you should be able to “fake” a primary key in the model by setting the IsPrimaryKey property on the corresponding columns on the designer.

Thanks,

Diego

Answer 6

Hi,

 

So I cannot modify a table which doesn't have primary key using LINQ..?

 

am I right..?

 

Shiva

 

 

Answer 7

To update a record in a table you need a unique reference to it regardless of whether you are using LINQ or not.

If you don't want to give it a primary key that would identify to clients what this unique reference is then you can mark some columns within the LINQ to SQL designer as being the primary key even though they aren't as Diego suggests which will at least let LINQ identify the record to update.

[)amien

Answer 8

But if you create "fake" unique/PK you can have some "mysterious" update if you will have more rows with this "fake keyed" values.

Answer 9

If you have more than one row with these values then it isn't a unique reference.  This problem is not unique to LINQ to SQL.

[)amien

Answer 10

Yes, I agree. Thanks for pointing this out. I always meant to recommend that you should use some mechanism to ensure that the key identifying the row in the database is unique. If it is not a PK, then a unique index should work.

 

What is not clear to me is why would you need to do this (i.e., when is not ok to have a PK but is ok to have a unique index). But since I have heard this request before, I wanted to make clear that LINQ to SQL reasons in this case about what you specify in your model, and not about the actual schema in the database.

 

Thanks,

Diego

 

Answer 11

Yes, I know. I was talking about fake unique (created just ot make linq to sql work). If there's is a really set of columns, that can be used as identification, I think it should be created as PK in database anyway.

Answer 12

Let's say that you have 3 tables like these;

Insurances                CoverOptions             CoverRelations
--------------              -----------------            -----------------
InsuranceId PK          CoverId                     InsuranceId  (Foreign Key)
BeginDate                 CoverTypeName         CoverId        (Foreign Key)
EndDate
exc.

Scenario : You are working for a company that has 4000 cars. All those cars have insurances but not all insurances are same. Some of the insurances are selected to cover everything some of them are covering only the selected disasters.

CoverRelations table has no PK as it is not necessary.

If your scenario is like this, I suggest System.Data.SqlClient so you can do whatever you want on that table.

Answer 13

As we can write Update or Delete Query without specifying WHERE clause in SQL and operation is performed on all rows. Similar is my case I don't want to create PK on table. I want to update all rows at certain time or lets say I have only one row in table and I know that there will always be only one row in table. Then how to write that kind of query in LINQ


for example: If I write in SQL

UPDATING ALL EMPLOYESS
---------------------------

UPDATE OPTION_TABLE
   SET Status = 1


So my above query is to Set the Status to 1 and there will always be one row. Create PK in this case doesn't make sense. So Whats the solution ? Can anybody help?

Answer 14

As I suggested on my latest reply, use System.Data.SqlClient   For Ex:

 

SqlConnection Cn = new SqlConnection("<Connection String Goes Here>");
SqlCommand Cmd = new SqlCommand("update option_table set status=1",Cn);
Cn.Open();
Cmd.ExecuteNonQuery();
Cn.Close();

 

 

 

Answer 15

Dear Serdar Tas, thanks for your reply.

Well, the above code isn't following LINQ. Suppose my application's whole architecture is using LINQ syntax everywhere for database operations then your above code will violate the architectural rules because you have writen in line Query in Command Object. Although I have done the same when I didn't find any solution.

Answer 16

if you have a table storing just one record, make one of the fields PK and you're done. Just for cases like having a relationship table storing only foreign keys it's ok not to have a PK.