Tag: SQL Server

Entity Framework model – uniqueidentifier primary keys

The Entity Framework has had a number of additions and fixes created over the years, and one I particularly like is the way to specify how to handle a GUID or rather a primary key in SQL Server of type uniqueidentifier. Previously you had to manually open the .edmx file in say Notepad and add the StoreGeneratedPattern attribute to your PK column and set the value to Identity. That in itself wasn’t too hard, but when you made changes to the model and saved it, those manual changes were overwritten. Now, the StoreGeneratedPattern attribute is part of the Visual Studio UI, or rather the Properties window in Visual Studio, making it easy to handle.

Let me point out, that the above scenario is really only applicable, if you do not have SQL Server handle the addition of a new row, by creating a new primary key value, by say using the newid or newsequentialid functions. If you have SQL Server handle the addition of a new row, you can set the StoreGeneratedPattern attribute to Identity, and in your partial entity class (say Country), you decorate the id column with the AllowEmptyStrings attribute and a value of false, like this:

[ScaffoldTable(true)]
[MetadataType(typeof(Country.Country_Metadata))]
public partial class Country
{
     internal class Country_Metadata
     {
        [Required(AllowEmptyStrings = false)]
        public object Id { set; get; }
        ...
If your code adds the primary key, before you attempt to save it to the database, there’s little reason to set the StoreGeneratedPattern attribute.

Advertisements

Package ‘Microsoft SQL Management Studio Package’ failed to load

I started getting this error message when opening Microsoft SQL Management Studio 2008 R2, right after installing some application and updates to Visual Studio 2010, by using the Web Platform Installer. It was fairly simple to fix though, because after running a repair of the .NET Framework 4 Client Profile and rebooting the machine, things were back to normal.

Failed to generate a user instance of SQL Server due to a failure in starting the process for the user instance. The connection will be closed.

Lately, I’ve been getting this quite a lot on my development VM. I have a SQL Server 2008 Express Edition database in the App_Code folder of my Web site, and sometimes when I get the error, closing down the browser and running the Web site again from within Visual Studio solves it. However, more often than not, that doesn’t do it. Stopping the ASP.NET Development Server doesn’t fix it either nor does closing down and reopening Visual Studio.

What does the trick, is to clear the items in this folder, %LOCALAPPDATA%\Microsoft\Microsoft SQL Server Data\SQLEXPRESS. If the files are locked, you’ll need to reboot your machine, and delete the files before you run your Web site again.

You can also open up the Task Manager and delete all sqlservr.exe processes, but I think a reboot is better…

— UPDATE —

It turns out that you don’t have to delete the files, but simply kill the sqlservr.exe process that runs under your account (the logged in identity). Much easier…

Many-To-Many Entity Modeling in Entity Framework

In my continued saga to create a new Web site based on ASP.NET 4 Dynamic Data and an Entity Data Model by using the Entity Framework 4, I needed to create or rather model a many-to-many relationships between 3 tables (3 pairs ;-)). While I have no problems doing that with an associated table in a SQL Server database, or any database, for that matter, I hadn’t done that in EF. However, I noticed that the Dynamic Data Web site, I had created, contained field templates for displaying and editing a Many-To-Many relationships. The problem was that while the database did work, I just could not model it using the Designer. Then I noticed that my association table contained three columns, the foreign keys for the two linked tables, but also a separate primary key. I generally create all my tables with an artificial primary key, and I had also done so in this case. Okay, I did a lot of other stuff first, before deciding to try and remove the artificial primary key column (of type uniqueidentifier) and create a composite primary key of the two “foreign key” columns. Then I updated the data model, but no luck. Hmm, okay, I then removed the 3 tables from the model, and subsequently updated the model from the database. In the update wizard, I added back the 3 tables and this time, 3 many-to-may relationships had been created. Great…

However, that wasn’t the best part; Dynamic Data automatically used the many-to-may field controls (ManyToMany.ascx and ManyToMany_Edit.ascx) to model the relationship in the UI. Particularly in edit view this was great, and it has saved me many hours of work. Mind you, a CheckBoxList control is sued, so if you have many rows in the linked table when editing an entity, it can become unwieldy to manage for an end-user.