Is LINQ to SQL too simple-minded?

15 Comments

• Why shouldn't adding the column be the right thing to do? Are you dealing with a schema that can't be changed?
  

• Because a column that's never used is a waste of space and processing. It's like putting wings on a bicycle. :)
  

• Not the right thing to do? According to who?
  

• Perhaps I need to be more clear. I know that I can designate several columns as a primary key, but I have no idea what the performance implications of that are. And again, it creates something that is never used.
  
  I've been chatting with some of my DBA and DB code monkey friends, and they agree with me. LINQ to SQL seems too clever for someone not to have thought of this, and I think there must be a solution.
  

• In this case I select groups of records by two columns, and those two have a clustered index. So the level of "unqiueness" that I actually need is only that a dozen or so records share those two values.
  
  That's why a primary key doesn't seem appropriate. The values in all of the other columns will surely make the row unique, but I don't need the database to do the work of tracking that "uniqueness" ever.
  

• The only thing I can think of is a case like this:
  
  Table: [abc]
  idA | idB
  ---------
  5 5
  5 5
  5 7
  9 2
  
  How would you generate the update statement for the following:
  
  abc xyz = db.abcs.Where( w => idA == 5 ).First();
  xyz.idB = 10;
  db.SubmitChanges();
  

• Well, I'm not sure I agree with your comment about wings on a bicycle. I would say more like adding a light with no battery. Unnecessary, I agree, but small, out of the way, and unobtrusive for the most part. Just add an int PK as an identity - it couldn't be easier.
  
  Keeping the table the way it is, if you already have a clustered index, then the server is already doing the work of physically arranging your data according to the index - for the most part, that's the buggest penalty associated with a PK. Ensuring the uniqueness of the values takes little overhead in comparison because the values are already physically sorted. I will caveat this (for the nay-sayers) and say, do a test to see if there really are any differences.
  

• Denny: This is data that will never be updated, and selected or deleted only in the manner I described.
  
  mhildreth: I get what you're saying, and actually you lead me to another thought, that since this data is mostly read, not written, perhaps my concerns are inconsequential. Thanks for getting me on that track.
  
  But taking the database concerns back out of the picture, this still seems like an odd, forced requirement for LINQ to SQL, because it assumes that all data requires full CRUD functionality.
  

• 1. Yes, it's a limitation among the many.
  2. Unless this table has no connection with any other table (i.e. will never be part of a join), like an island, it should absolutely have a primary key or at least an indexed field.
  

• You may have already tried this, but the solution I might take is that in my Linq to SQL file is create the "primary key" there rather the database. I have done this before on database tables that had no primary key. Of course this is simply done on the dbml code so you are not really creating a primary key per-se. The negative is that you would still need to have that unique combination of fields with no duplicates.
  
  Of course some would argue that if you create a "fake" primary key in the dbml or you use a combination of fields for the uniqueness then you should have primary key set in the database as those fields.
  

• I hate to say it, but your best solution is to create a primary key on the table. All of the new database products (especially SQL Server) are geared to this. LINQ to SQL expects this because it has become a generally accepted best practice for database tables. It's not difficult to understand why. Insisting on a primary key greatly reduces many of the complexities involving database development, especially concurrency and locking issues.
  

• If you're only inserting, there's nothing stopping you from coding:
  
  db.ExecuteCommand("INSERT INTO Table (Field) values (@p0)", p0_value)
  
  

• First, this goes away if you adhere to putting all SQL inside the database - where it belongs. I've witnessed too many systems built where the SQL was in the app and then another system needed to perform those operations and couldn't. If you create an INSERT stored procedure for that table, then anything can call that sproc and accomplish an insert. Your app, the next app, a webpage, another database, whatever.
  
  I already know the reply of app developers... but their vision has a very limited horizon. Look beyond that to the next thing... only one choice makes sense.
  

• I totally disagree. There are many choices, and yours sounds like the limited vision of a db dev. In a world of mature ORM's (nHibernate, for example), there's no reason to have SQL in the db. Ever. It's one more thing that's hard to version and evolve. No thanks!
  

• I ran into the same problem. My DB table keeps measurements and is basically rows of (timestamp, measurement-type, value).
  The only operations are insert and delete old messages.
  
  Adding a PK is easy enough, but why should I?
  
  @Mark B: As a software developer, I disagree. If sprocs are used, suddenly my application depends on code I cannot easily see and debug. The sprocs are not part of the source-control system and I cannot compare to earlier version or roll-back. And if the application is deployed to another system, all the sprocs must be deployed seperately.
  
  Yes, this can all be done using textfiles and maintaining discipline. But that is not very convenient. Remember: "inconvenient" means it gets forgotten or mistakes are made.