locked
Weird, weird EF data retrieval problem RRS feed

  • Question

  • I have the following 2 records on a SQL server table:

     

    CXAG01, 160, 1, -1, total_os, >=, 1,
    CXAG01, 160, 1, -1, total_os, <=, 5000

     

    When I execute the following Linq query to retrieve the data (the 160 value is the VIEW_ID field):

     

    var viewDT = from perm in LogDB.TBL_VIEW_FILTER_DEFINITION

                             where perm.VIEW_ID == viewID

                             select perm;

     

     

    What I get returned is the first record twice, this is absolutely bizarre behaviour. The table I'm querying has no keys defined.

    Wednesday, March 2, 2011 4:42 PM

Answers

  • Hi!

    I'm actually never worked with tables in EF without primary keys (since PK is a part of a good table design), anyway since I was curious I had to try this and retrieved the same result.

    EF requires a primary key column to work as expected, so your table(s) without primary keys is also marked read-only because of the lacking primary key.

    The reason why you get two records with the same info is that when the data is returned from the server it filters on the entity key defined in the model to see if the record already exists, if it does it simply uses this record.

    I'm a bit unsure what my opinion is of this behaviour. But I think that EF should handle tables without PK too, since there is circumstances that a primary key isn't neccessary like a log table (Even if it isn't good database design).

    What you could do is to mark all columns primary key on the entity TBL_VIEW_FILTER_DEFINITION, that would make your code work, but that again requires that none of the columns in the resultset returns NULL from the database. It's safe to do this kind of modification on the entity since it won't modify already parsed columns when updating the model.

    I'm not sure what you mean with having no control of the table, but I would strongly recommend you to request from those who actually have control of it to add a primary key to the table, it's safe to do and wouldn't impact anything as long as the primary key column is defined correctly. (Eg. Id int identity(1,1) not null in SQL Server).

    I hope this helps,


    --Rune
    • Proposed as answer by Rune Gulbrandsen Wednesday, March 9, 2011 9:09 AM
    • Marked as answer by Jackie-Sun Friday, March 11, 2011 2:29 AM
    Thursday, March 3, 2011 8:34 AM

All replies

  • I see it generated it's own key based on incorrect DB columns. Is there any way to force it to not do this? I have no control over the DB model so cannot change that. I don't to make any manual changes that will be lost the next time I generate the model.
    Wednesday, March 2, 2011 4:52 PM
  • Hi!

    I'm actually never worked with tables in EF without primary keys (since PK is a part of a good table design), anyway since I was curious I had to try this and retrieved the same result.

    EF requires a primary key column to work as expected, so your table(s) without primary keys is also marked read-only because of the lacking primary key.

    The reason why you get two records with the same info is that when the data is returned from the server it filters on the entity key defined in the model to see if the record already exists, if it does it simply uses this record.

    I'm a bit unsure what my opinion is of this behaviour. But I think that EF should handle tables without PK too, since there is circumstances that a primary key isn't neccessary like a log table (Even if it isn't good database design).

    What you could do is to mark all columns primary key on the entity TBL_VIEW_FILTER_DEFINITION, that would make your code work, but that again requires that none of the columns in the resultset returns NULL from the database. It's safe to do this kind of modification on the entity since it won't modify already parsed columns when updating the model.

    I'm not sure what you mean with having no control of the table, but I would strongly recommend you to request from those who actually have control of it to add a primary key to the table, it's safe to do and wouldn't impact anything as long as the primary key column is defined correctly. (Eg. Id int identity(1,1) not null in SQL Server).

    I hope this helps,


    --Rune
    • Proposed as answer by Rune Gulbrandsen Wednesday, March 9, 2011 9:09 AM
    • Marked as answer by Jackie-Sun Friday, March 11, 2011 2:29 AM
    Thursday, March 3, 2011 8:34 AM
  • Hi keith,

    Just wondered if my comment gave you the answer on your question? If it did, please mark it as answered, otherwise feel free to post additional questions!


    --Rune
    Wednesday, March 9, 2011 9:09 AM