Home

rss

Adding a lookup field to an existing table using Entity Framework code first migrations

As you build out a site using the code first approach, at some point you will most likely need to add an additional lookup field to a class. There are a couple of steps required above what’s automatically done for you by EF code first migrations to make things work happily. If you consider the following simple class
class Client
    {
        public int ID { get; set; }
        public string ClientName { get; set; }
        public string ClientAddress { get; set; }
    }
If you add a migration at this point you’ll end up with something like
public partial class InitialSetup : DbMigration
    {
        public override void Up()
        {
            CreateTable(
                "Clients",
                c => new
                    {
                        ID = c.Int(nullable: false, identity: true),
                        ClientName = c.String(),
                        ClientAddress = c.String(),
                    })
                .PrimaryKey(t => t.ID);

        }

        public override void Down()
        {
            DropTable("Clients");
        }
    }
Once this class has been added to the database and you have existing data, adding another non nullable lookup field can be tricky. When you add your lookup and it tries to add the lookupID field to the existing Clients table, it’ll fail because the existing data will fail referential integrity due to their being no data in the lookup table. Once we added a lookup named ClientStatus to the model, it would look like
class Client
    {
        public int ID { get; set; }
        public string ClientName { get; set; }
        public string ClientAddress { get; set; }
        public int ClientStatusID { get; set; }

        public virtual ClientStatus ClientStatus { get; set; }
    }

    class ClientStatus
    {
        public int ClientStatusID { get; set; }
        public int ClientStatusText { get; set; }
    }
and our migration code will look like
public override void Up()
        {
            CreateTable(
                "ClientStatus",
                c => new
                    {
                        ClientStatusID = c.Int(nullable: false, identity: true),
                        ClientStatusText = c.Int(nullable: false),
                    })
                .PrimaryKey(t => t.ClientStatusID);

            AddColumn("Clients", "ClientStatusID", c => c.Int(nullable: false));
            AddForeignKey("Clients", "ClientStatusID", "ClientStatus", "ClientStatusID", cascadeDelete: true);
            CreateIndex("Clients", "ClientStatusID");
        }

        public override void Down()
        {
            DropIndex("Clients", new[] { "ClientStatusID" });
            DropForeignKey("Clients", "ClientStatusID", "ClientStatus");
            DropColumn("Clients", "ClientStatusID");
            DropTable("ClientStatus");
        }
The problem lies between the CreateTable and the AddColumn commands. The lookup table will be created, but the ClientStatusID non nullable field can’t be added to the Clients table as there are no records on the ClientStatus table. To get around this in my code, I am tweaking the Up function to insert a default row into the lookup table and assigning the value of 1 to the default value of the ClientStatusID field in the Clients table. I’m adding
Sql("INSERT INTO ClientStatus (ClientStatusName) VALUES ('Active')");
and updating
AddColumn("Clients", "ClientStatusID", c => c.Int(nullable: false));
to be
AddColumn("Clients", "ClientStatusID", c => c.Int(nullable: false, defaultValue:1));
As we are just creating the ClientStatus Table, we can reasonably assume that the first record created will have an CLientStatusID of 1.

Telerik ASP.NET MVC controls and jQuery

For those who are using the Telerik controls for MVC you will most likely have the following somewhere on your page or Master page

This will create issues if you then try to add any jQuery to the page including jQuery plugins.

To get around this, you can append a method to the Telerik ScriptRegistrar to disable the jQuery reference. You need to change it to

And your page should be happy again.

Getting random records from a table using LINQ to SQL

I needed to get a number of random records from a table and a quick google search led me to http://mosesyap.com/BlogWeb/post/2008/07/C-Getting-a-Random-Record-from-a-Table-inside-the-SQL-Server-Database.aspx This is a very quick and easy mechanism to use and will work on any table. To load a specific number of random records change the .First() function
DataContext db = new QuoteDataContext();
var quote = db.Quote.OrderBy(q => db.GetNewId()).First();
to be .Take(x)
DataContext db = new QuoteDataContext();
var quote = db.Quote.OrderBy(q => db.GetNewId()).Take(x);
replacing x with however many records you need.

Creating a Tag Cloud with c# and IEnumberable<>

I recently had to create a tag cloud for an application I’m writing. The tags are attached to bookmarks.

The function returns a paragraph tag with a link for each tag. Each link has a specific class attribute set depending on the weighting given to that tag.

The C# code:

public class TagCloudGenerator
    {
        public string MakeTagCloud(IQueryable<BookmarkTag> Tags)
        {
            Decimal totaltags = Tags.Count();
            Decimal tagpercent = 0;
            int tagweight = 0;

            StringBuilder TagCloud = new StringBuilder();

            var groupedtags = Tags.GroupBy(t => t.Tag);

            TagCloud.Append("

"); foreach (var tag in groupedtags) { tagpercent = (tag.Count() / totaltags) * 100; if (tagpercent >= 90) { tagweight = 1; } else if (tagpercent >= 70) { tagweight = 2; } else if (tagpercent >= 40) { tagweight = 3; } else if (tagpercent >= 20) { tagweight = 4; } else if (tagpercent >= 3) { tagweight = 5; } else { tagweight = 0; } TagCloud.Append(String.Format("{2} ", tag.Key.Replace(" ", "-"), tagweight, tag.Key)); } TagCloud.Append("

"
); return TagCloud.ToString(); } }

The CSS:

/* Tag Cloud */

.tagcloud
{
    text-align: left;
}

.tagcloud a
{
    padding: 0px 10px 0px 0px;
    text-decoration:none;
    padding:3px 4px;
    white-space:nowrap  
}

.tagcloud a:hover
{
    background-color:#fff;
    color:#012840;
}

.tagcloud .tag1
{
    font-size: 2em;
}

.tagcloud .tag2
{
    font-size: 1.7em;
}

.tagcloud .tag3
{
    font-size: 1.5em;
}

.tagcloud .tag4
{
    font-size: 1.2em;
}

.tagcloud .tag5
{
    font-size: 1em;
}

.tagcloud .tag0
{
    font-size: .8em;
}

Using Data annotations with partial classes and LINQ to SQL

If you’re using LINQ to SQL as your data provider then you will need to add a few extra steps to utilise Data annotations with ASP.NET MVC.

If you have created your own objects then you can append annotations such as [DisplayName(“This is the display name I want in the view”)] directly to the object properties as you can see below. These will then be used by UI Helper and for Model validation by MVC.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.ComponentModel;
using System.ComponentModel.DataAnnotations;

namespace MyApp.Models
{
    public class MyObject
    {
        [DisplayName("First Name")]
        public string Property1 { get; set; }

        [DisplayName("Surname")]
        [Required]
        public string Property2 { get; set; }        

    }
}

You can see in the code above that we’ve set some metadata for the DisplayName for the UI Helpers and a Required attribute for model validation.

You can create a partial class to add additional functions and overrides to your LINQ to SQL objects but you cannot add data annotation attributes directly to the LINQ object properties. To achieve this you need to use a separate class and link it to the LINQ to SQL object using the Metadatatype attribute.

Lets assume that the MyObject class is now a LINQ object. We can create a partial class to add additional functions but we cannot use the data annotations shown above (DisplayName and Required)

namespace MyApp.Models
{
    public partial class MyObject
    {
        public string MyAddedFunction()
        {
            return "Some additional data";
        }

    }
}

To add the data annotation attributes we will create another class called MyObject_Validation as shown below

namespace MyApp.Models
{
    public class MyObject_Validation
    {
        [DisplayName("First Name")]
        public string Property1 { get; set; }

        [DisplayName("Surname")]
        [Required]
        public string Property2 { get; set; }        

    }
}

Which overrides the DisplayName on our two fairly poorly named properties Property1 and Property2 to more meaningful names and we assert that Property2 is a required field.

We can now use the Metadatatype  attribute to link the data annotations in MyObject_Validation to MyObject

namespace MyApp.Models
{
    [MetadataType(typeof(MyObject_Validation))]
    public partial class MyObject
    {
        public string MyAddedFunction()
        {
            return "Some additional data";
        }

    }
}