Monday, February 11, 2008

Common Reason ADO.NET Data Services Don't Work - Reason 2 - Invalid Primary Key Name

I was helping a reader diagnose an issue with ADO.NET Data Services over the last week.  Basically the issue was that just about any table he had in his model wasn't being exposed through the REST service that ADO.NET provided when using LINQ to SQL Entities.  When using ADO.NET Entity Framework to generate his model, all was well.

As it turned out, the issue had to do with his primary key name.  Apparently, if you are using ADO.NET Data Services on top of a LINQ to SQL DataContext object, you need to make sure that your primary key is defined as "[ENTITY]ID" or "ID".  Anything else doesn't seem to work.

So, if your table looks like this:

USE [BlogExampleDB]
GO
/****** Object: Table [dbo].[TestTable]    Script Date: 02/11/2008 18:01:04 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[TestTable](
    [MyPrimaryKey] [int] IDENTITY(1,1) NOT NULL,
    [Column1] [nchar](10) NULL,
 CONSTRAINT [PK_TestTable] PRIMARY KEY CLUSTERED
(
    [MyPrimaryKey] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

Your LINQ to SQL entity (dbml) will look like this:

Linqtosql entity

If you then plug that into your ADO.NET Data Service, and then visit your service, you end up with this:

<?xml version="1.0" encoding="iso-8859-1" standalone="yes"?>
<service xml:base="http://localhost:51499/Astoria/MySimpleService.svc/"
        xmlns:atom="http://www.w3.org/2005/Atom"
        xmlns:app="http://www.w3.org/2007/app" xmlns="http://www.w3.org/2007/app">
    <workspace>
        <atom:title>Default</atom:title>
    </workspace>
</service>

Your entity is nowhere to be found.

It all has to do with the name of your primary key.  In this example, if I change the primary key to either "TestTableID" or "ID", I'll see my entity show up.

Things to note about this:

  1. You don't have to change your underlying data model.  You only need to change the LINQ to SQL Entity.
  2. This only seems to affect LINQ to SQL entities, and not ADO.NET EDM's.

6 comments:

Erik said...

Great blog Jim.

I'm having a similar issue that this post is trying to address, have my column named "ID" and set as the primary key, but I'm still seeing default values int the web service feed. My Linq to Sql code is below:

[Column(Storage="_ID", AutoSync=AutoSync.OnInsert, DbType="Int NOT NULL IDENTITY", IsPrimaryKey=true, IsDbGenerated=true)]
public int ID
{
get
{
return this._ID;
}
set
{
if ((this._ID != value))
{
this.OnIDChanging(value);
this.SendPropertyChanging();
this._ID = value;
this.SendPropertyChanged("ID");
this.OnIDChanged();
}
}
}

I'm using the data context in another class and it is filling properly. It's just the service that doesn't work. Here's my initialize code:

public static void InitializeService(IWebDataServiceConfiguration config)
{
config.SetResourceContainerAccessRule("*", ResourceContainerRights.AllRead);
}

Jim Fiorato said...

Thanks for the feedback Erik.

One bug I've seen is that the default constructor of the dbml designer file for some reason doesn't setup the connection string.

Open up your dbml designer file, and ensure that the default constructor sends the connection string to the base class.

If that doesn't work, if you want to shoot me your project, I can take a look.

Jim

Editor In Chief said...

Thanks Jim, that really helped.

Pratik said...

The other way to solve this problem would be specify the DataServiceKeyAttribute on the type and specify the key property name(s) (If you don't want to rename the property).

dogu.tumerdem said...

Hi, I experienced another problem. I have a primary key of nvarchar with values of email addresses. When I try to run a LINQ query on client with criteria of an email address, query failes. @ (and other symbols) causes this exception are there any resolution for this ?

Jim Fiorato said...

I'm not quite sure why it is doing that.

I've found www.stackoverflow.com to be a great place to post and find answers to issues like this. Try heading over there and posting this question. There's lots of experts in LINQ and ADO.NET Data Services over there.

Jim