Mar 24

Tables without a clustered index are not supported in this version of sql server

I try to do an insert in to n:n (SQL Azure based) related tables (UserSet and TenantSet) using the Entity Framework, but it fails with the error: “tables without a clustered index are not supported in this version of sql server”. I’ve used the model first approach to generate the SQL database, so the SQL Server database should be fine.

After extensive search it seems that the table UserTenant has no clustered index. The script that the Entity Framework generate contains (in my opinion) an error. The relevant part of the script is:

Creating primary key on [User_UserId], [Tenant_TenantId] in table ‘UserTenant’

ALTER
TABLE [dbo].[UserTenant]
ADD
CONSTRAINT [PK_UserTenant]
PRIMARYKEY NONCLUSTERED ([User_UserId], [Tenant_TenantId] ASC);
GO

Of course the NONCLUSTERED parameter should me CLUSTERED. Change that and run the script again.

That’s all, hope the Entity Framework team resolves this what looks like a bug.

 

Mar 22

Solving error 500 issues using the Enitity Framework with MVC 4 Web api

I have a MVC 4 applications that calls a Web Api REST service. The webservice retrieves the values from a Windows Azure database using the Entity Framewok. For some reason a call to get a user from the web api fails. After inspecting the respons with the Fiddler2, I saw this error:

 

HTTP/1.1 500 Internal Server Error

ExceptionMessage=The ‘ObjectContent`1′ type failed to serialize the response body for content type ‘application/json; charset=utf-8′.

ExceptionMessage=Error getting value from ‘Tenant’ on ‘System.Data.Entity.DynamicProxies.User_A66506F1AD16B9BC58E2CE2527D9CEDD7F1AFFBF3191ECC41B3556′.

 

The strange thing is, that sometimes the call is made correctly. I’m running the application locally on my development workstation and especially when I step through the code, the call is miraculously completed correctly.

After hours of search I read in Professional C# 2012 and .NET 4.5that there are different setting in the way the object loads from the database using the Entity Framework. The setting from your datacontext must be set to LazyLoadingEnable = false. So my code looks as follows:

var db = new BsoDataModelContainer();
db.Configuration.LazyLoadingEnabled = false;

Quite simple, once you know IT.

.