locked
Create an entity model without primary key RRS feed

  • Question

  • Hello,

    I'm using Visual Studio 2010 and WCF RIA Services for Silverlight 4. I would like to query a SQL database from a silverlight app and I follow these instructions: http://www.silverlightshow.net/items/WCF-RIA-Services-Part-1-Getting-Started.aspx

    But I have this error message when I create the Domain Model Entities:

    The table/view 'WebProxyLog' does not have a primary key defined and no valid primary key could be inferred. This table/view has been excluded. To use the entity, you will need to review your schema, add the correct keys, and uncomment it.

    The table WebProxyLog cannot have a primary key because it is a log from ISA/TMG firewall. I cannot add a new identity column because the firewall cannot write in this table after that. This table have a clustered index only.

    How can I query a table without primary key from silverlight?

    Thanks.

    Thursday, August 19, 2010 4:16 AM

Answers

  • The error message doesn't mean that you need to create the key on the database, you can provide the key yourself within the data model. If you open the EDMX file you can modify the storage model to say what the key is (the clustered index may work). You may find more explicit help on this in an Entity Framework forum.

    You say you can't modify the table, can you add a view to the database? In a view you can add in a ROW_NUMBER() field that you can use as the key.

    Another possibility would be to not use EF. Just load the data up yourself into POCOs. Then you can provide you own key (just throw a Guid into the POCO). 

    Tuesday, August 24, 2010 8:30 AM

All replies

  • Hi dsy , iam sorry to tell you that you must have property marked with [Key] attribute so you can get your objects back to silverlight and you must ensure to be unique coz if you have 2 entity with the same key you'll get only one of them

    Hope that help

    best regards

    yasser

    Thursday, August 19, 2010 4:23 AM
  • Hello Yasser,

    Thanks for your reply. Unfortunately, I cannot set a primary key or add a new column because the table is managed by another application (TMG).

    Is there another way to query a SQL database from silverlight or WPF web app? I only need the SELECT query and fill a datagrid.


    Thursday, August 19, 2010 4:50 AM
  • Hi,

    Yes, you have several ways to query a sql database, for example, you can write a WCF service to do that, or you can take a look at wcf data service:

    http://msdn.microsoft.com/en-us/library/cc838234(VS.95).aspx

    • Proposed as answer by CarlHH Tuesday, March 26, 2013 10:28 PM
    Monday, August 23, 2010 3:04 AM
  • The error message doesn't mean that you need to create the key on the database, you can provide the key yourself within the data model. If you open the EDMX file you can modify the storage model to say what the key is (the clustered index may work). You may find more explicit help on this in an Entity Framework forum.

    You say you can't modify the table, can you add a view to the database? In a view you can add in a ROW_NUMBER() field that you can use as the key.

    Another possibility would be to not use EF. Just load the data up yourself into POCOs. Then you can provide you own key (just throw a Guid into the POCO). 

    Tuesday, August 24, 2010 8:30 AM
  • Yes, I can create a view, that an idea. I will try that after holidays :)

    Anyway you give me some good ways to follow-up. Thanks you.

    Wednesday, August 25, 2010 7:02 PM
  • Thanks for the thoughts on this Colin,

    Generally confused why EF requires a key on a view, when TSQL does not permit a PK on a view ?

    I tried your suggestion of using ROW_NUMBER() to create a field (to be used as a key), but that field is created as bigint null(able).  EF auto-assigns a PK to NOT NULL fields.  I have not figured out how to force the row_number() result to be 'not null', in order to allow EF to properly assign a PK to the row_number() generated field.

    Thanks for the many excellent posts over the years...

    Monday, March 14, 2011 12:47 PM
  • TSQL doesn't permit a PK on a view because a view doesn't actually exist. Once you have retrieved that data from the database then it is an actual thing, an Entity, and it needs a primary key so that we can keep track of it.

    To use an analogy, if you are standing outside looking at the sunset, there is no primary key for what you are looking at. At any given moment, the sun is moving and birds are flying by so your view is constantly changing. If you take a picture with your phone, that view has been turned into a picture and has a filename. The filename is the primary key and the view which was previously ever changing is now a thing.

    Monday, March 14, 2011 3:58 PM
  • To use an anaology ... 

    hah, nice one

    Monday, March 14, 2011 5:05 PM
  • I see, thanks that is a good analogy.  How about the second part ?

    SELECT

    [ViewID] = row_number() over(order by VendorContacts.ContactID

    )

     

     

     

     

    )

    Monday, March 14, 2011 5:59 PM
  • If you do an explicit conversion in the view (convert(int, row_number() over(order by VendorContracts.ContactId)) that might force it to be non-null. Personally, I used the NewID() method to create a GUID. I use GUIDs for all of my keys so the exta bandwidth needed is normal for my app.

    Monday, March 14, 2011 9:26 PM