Insert New Data using Linq -- WCF Data Services (formerly ADO.NET Data Services)

Question

I am really struggling to insert some data into 2 database tables.

I am using Linq over WCF Data Services using Entity Framework 4.

The 2 tables look like this:

CREATE TABLE [dbo].[Accounts] (
    [Id] int IDENTITY(1,1) NOT NULL,
    [Email] nvarchar(max)  NOT NULL,
    [Password] nvarchar(max)  NOT NULL
);

CREATE TABLE [dbo].[Employees] (
    [Id] int IDENTITY(1,1) NOT NULL,
    [Name] nvarchar(max)  NOT NULL,
    [Role] nvarchar(max)  NOT NULL,
    [Account_Id] int  NOT NULL
);

ALTER TABLE [dbo].[Employees]
ADD CONSTRAINT [FK_EmployeeAccount]
    FOREIGN KEY ([Account_Id])
    REFERENCES [dbo].[Accounts]
    ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

Each Employee must have just 1 Account. However, an Account may not have any Employees at all.

I have generated the entities, and exposed a DataService which is setup with relaxed access rules:

config.SetEntitySetAccessRule( "Accounts" , EntitySetRights.All );
config.SetEntitySetAccessRule( "Employees" , EntitySetRights.All );

Using LinqPad I can successfully insert an single Account row like this:

var context = this;
Account account = Account.CreateAccount(1, "[email protected]", "p@ssword");
context.AddToAccounts(account);
context.SaveChanges();

I can see from SQL Server that the row has been inserted, and LinqPad reports no error.

The problem is when I try to insert both a related Account and Employee row together.

I am sure the code below should work?

var context = this;
Account account = Account.CreateAccount(1, "[email protected]", "password");
context.AddToAccounts(account);
Employee employee = Employee.CreateEmployee(1, "Foo", "Developer");
employee.Account = account;
context.AddToEmployees(employee);
context.SaveChanges();

The response I get back from the server (with verbose errors enabled) basically says that:

Account row inserted successfully (HTTP 201)
Employee row did not insert (HTTP 500)

The full error is:

Entities in 'DbContainer.Employees' participate in the 'EmployeeAccount' relationship. 0 related 'Account' were found. 1 'Account' is expected.

Does anybody have an example that works against WCF Data Services?

Answer 1

For anybody reading this the solution was to add the following line before SaveChanges()

context.SetLink(employee, "Account", account);

Simply assigning the account to the employee was not enough, a Link also needed to be established.