While making a website in MVC3 on ASP.NET, I got the error: “The entry ‘xyz’ has already been added.”.
I realized that somehow my data Context was already being created by EF and I was getting this error.

The fix for this was to change my ConnectionString (in web.config) to not be of this same name.

I realized that in code-first models, the connection string had to match the database being created, which should match the context class, derived from DbContext.  This was happening in this “database-first” case as well.

I made this short post because many folks are baffled by this error, and I hope this post helps them.

There is a learning process to switching to SQL Azure from your local SQL Server database, and this was a painful but learning experience for me and this post will hopefully help you to get on that road.

Let’s assume you have created a SQL Azure database, which is pretty straight-forward, and want to now connect to it from a client.

Here’s what you will need…

Tools

You can connect to SQL Azure from within SQL Manager provided you have the SQL Server 2008 R2 edition. If you do not, you can connect a query I think from some posts I read but I’m not sure, so remember to upgrade to the R2 edition if possible, or you can  download the free R2 express edition.

Network settings

To connect to Azure, you will need changes at the server and the client.

At the server side in Azure, you will need to open the firewall to allow an IP range to allow connections from. Azure makes this very easy if configured from the client machine because when you Add new rule, it will display your IP and that makes it easier to specify a range.

In the end, you might end up with a few rules like this:

sql azure firewall

The client side could end up being one of the most frustrating aspects of using Azure. You need to have port 1433 to connect to SQL Azure. Period. There is no workaround that whatsoever. If you’re in a corporate network, this might be an issue. If you’re on a wireless network, this might be an issue because many public ones only have port 80 and a few others open.

You might get the following error if your port is not accessible.

sql azure error1

You might have to work with your corporate IT or ISP to open port 1433.

To check if your SQL Azure database is accessible from your machine, telnet into it. Here’s how: Depending on your Windows installation, you might have to install telnet. Go to Control Panel – Programs and Features. Select IIS and expand that node and check Telnet Client and install it. Go to a DOS prompt and type in telnet, and you will get a telnet prompt. To attempt to connect to your SQL Azure db, enter o xyz.database.windows.net 1433 and you should get a connecting prompt.
Something like this:

sql azure telnet

Otherwise, you’ll get a meaningful error message that you could troubleshoot.
When done, enter q to quit telnet.

Once you’re able to connect to your db in the cloud, it’s time to use it in your application.

Connection strings

To point your app to the SQL Azure db, it is just a matter of replacing your (local) connection string with an Azure connection string. This is made really convenient by the Azure folks because they provide a connection string in the online admin tool that you can just copy over.

Go to the Databases tab, select your database and click the Connection Strings button and you get a useful pop-up.

One gotcha if you make a mistake in putting in your string (which I did) is that you might get an error stating “Format of the initialization string does not conform to specification starting at index NN” This indicates it’s an XML problem and so double-check your string.

I think that covers the basics of connecting to your database in the cloud. Have fun!

While using LINQ, you may want to choose unique items in a list with the DISTINCT clause. To use this clause with your objects or projections or an object that represents stored proc. results, you will need to provide the logic used to compare 2 object instances and return if they are equal or not. This is because .NET does not inherently know how to compare these objects.

Lets say you have a Person class:

public class Person {
    public long ID;
    public string firstName;
    public string lastName;
}

The comparison logic is defined in a class PersonComparer needs to implement the IEqualityComparer interface that has 2 methods as shown.

public class PersonComparer : IEqualityComparer<Person>
{
    #region IEqualityComparer<Person> Members

    public bool Equals(Person object1, Person object2)
    {

        //return true if equal
        return object1.firstName.Equals(object2.firstName)
               && object1.lastName.Equals(object2.lastName);

    }

    public int GetHashCode(Person obj)
    {
        return obj.ID.GetHashCode();
    }

    #endregion
}

Now to use this, let’s say we have a list of Person objects IEnumerable<Person> lstPerson, and we want to get unique objects in this.

Declare an instance of your Comparer object

PersonComparer myComparer = new PersonComparer();

and then use it with the Distinct clause

IEnumerable<Person> lstPersonDistinct = lstPerson.Distinct(myComparer)

 If you want to go deeper, Distinct is defined in the .NET framework as an extension on class Enumerable.

public static IEnumerable<TSource> Distinct<TSource>(this IEnumerable<TSource> source, IEqualityComparer<TSource> comparer);

So essentially, the interface (IEqualityComparer) of myComparer is used in your Distinct call.

Hope this post helps you to implement Distinct for your objects.

Many people ask about REST authentication, so here are a few possible approaches.

Simply put, a server provides access to resources by users/clients, and authentication is a mechanism used to identify users. A REST request you make or consume may require authentication in most business situations.

There is really nothing special about REST calls when it comes to authentication because they are ultimately just http requests, only the URL style is “RESTful”, and the techniques below apply to any http implementation, including ASP.NET.
Here are some scenarios that do not involve any third-parties at run-time.

1. Http authentication over SSL

This seems to be a common approach, whereby the client (webpage or application) sends a username and password (un/pw) in the http header to the server over SSL (encryption is important since the un/pw is clear text). The server retrieves this un/pw and authenticates/authorizes the request accordingly.
There are a number of ways to store a user data and authenticate the un/pw against it, from the built-in asp.NET Membership stuff to a simple match of passwords in a User table.

Your IIS website/service can be set up to use “basic authentication”, from website- properties – Directory Security (tab) – Auth and Access control – Edit and check the required box.
Servers that support basic http auth. are usually configured to “require a pre-authorization”. I don’t know how to do this in IIS.

Server code:

The incoming request has a http Request header, which you can get the auth information from.
In your incoming aspx or auth layer, you can get this from Request.Headers[“Authorization”] and will be of the format “Basic dW46cHc=”.

Here’s some aspx code you can use:
Remember to add a try/catch in there.

protected void Page_Load(object sender, EventArgs e)
{
    const string BASIC = "Basic ";

    string authHeader = Request.Headers["Authorization"];

    if (!string.IsNullOrEmpty(authHeader) && authHeader.Contains(BASIC))
    {
        string auth = authHeader.Substring(BASIC.Length);
        System.Text.UTF8Encoding encoder = new System.Text.UTF8Encoding();
        System.Text.Decoder utf8Decode = encoder.GetDecoder();

        byte[] credentialBuffer = Convert.FromBase64String(auth);
        int charCount = utf8Decode.GetCharCount(credentialBuffer, 0, credentialBuffer.Length);
        char[] decoded_char = new char[charCount];
        utf8Decode.GetChars(credentialBuffer, 0, credentialBuffer.Length, decoded_char, 0);
        string authDecoded = new String(decoded_char);

        string[] unpw = authDecoded.Split(':');
        string username = unpw[0];
        string pwd = unpw[1];
    }

…
…
…
}

At the end of this, you will have the supplied credentials in username and pwd.

Client code:

In basic auth with pre-auth required, you make an initial http request without credentials (don’t get confused by the phrase pre-auth) and the server returns a Response code of 401. This indicates that the server is looking for auth info and you resubmit the request this time with the un/pw in the http header. Setting the PreAuthenticate property of your HttpWebRequest object to TRUE, and the auth information will be sent automatically in the http header by .NET, while making any future requests to that URI.
http://msdn.microsoft.com/en-us/library/system.net.httpwebrequest.preauthenticate.aspx
If the server is not configured for pre-auth, you send the un/pw directly in the initial request.

Here is some sample code for your client to supply basic http auth.. For CRUD operations, you would need to POST some information (for an update) and that is included here.
For plain URL access, replace the method from POST to GET and remove the lines that write to the Request stream.

Add usings at the top:

using System.Net;
using System.IO;
using System.Text;

To post a string called myContent to myURL

try
{

HttpWebRequest req = (HttpWebRequest) HttpWebRequest.Create(myURL);
req.Method = "POST";
req.Timeout = 10000;        // 10 seconds timeout
req.ContentType = "application/x-www-form-urlencoded";
req.ContentLength = myContent.Length;
byte[] credentialBuffer = new UTF8Encoding().GetBytes(username + ":" + password);
req.Headers["Authorization"] = "Basic " + Convert.ToBase64String(credentialBuffer);
stream = req.GetRequestStream();
stream.Write(Encoding.Default.GetBytes(myContent), 0, myContent.Length);
}
catch (Exception ex)
{
    … //error handling

throw new Exception(ex.Message);
}
finally
{
    if (stream != null)
        stream.Close();
}

Now, to read in the response (for your request object req).

HttpWebResponse resp = (HttpWebResponse)req.GetResponse();
stream = resp.GetResponseStream();

byte[] readBuf = new byte[2048]; //whatever the expected size

int bytesRead = stream.Read(readBuf, 0, 2048);
string readData = Encoding.ASCII.GetString(readBuf);
stream.Close();

string received = readData.Trim();

2. Client certificates

This is the most secure form of identifying a particular client. I will add that is constrained because it needs certificate installation and is expensive but it has its uses in mostly B2B but maybe B2C scenarios as well.
For e.g. A financial application wants to retrieve data from a financial warehouse.

Client certificates identify the client making the request, and are issued by third-parties, like Verisign, who verify the client while issuing the certificate, and vouch for the authenticity of the client.

This URL has pretty much everything you need to know about certificates: http://msdn.microsoft.com/en-us/magazine/cc163454.aspx

Hope that helped…
In the future, I will write about oAuth and federated services.

I see a lot of questions and articles about doing CRUD operations with LINQ to SQL (DLinq), especially about Updates, DAL design, etc., and think a little guidance could help a few people…
This article only focuses on update scenarios, typically encountered with web/async apps.
Let’s start with a simple scenario without Transactions or multiple Data Contexts.

Say we want to update a Person’s name in our Employee database, from our app.

First retrieve the Person row, then Update it


dbPerson p = (from p in myContext.Persons where p.Id = myId select p).First()
p.Name = “foo”;
myContext.SubmitChanges();

Note: Do not use Attach unless your database object, in this case p, comes from another Data Context instance. If it is in a single unit of work, there is no need to use Attach, and it can get pretty confusing if used incorrectly.

In the update above, LINQ takes an optimistic concurrency approach under the covers, and generates a SQL with a complex Where clause, that essentially compares all the fields from your passed in object to the ones in the database.

This provides a concurrency check, whereby if another user snuck in and updated the Person record, the update will fail because the old value checks will detect that the underlying SQL data has changed after the fetch.
This is a good thing, but there is an overhead of the Where clause, which is proportional to the number of columns. So, if you had 30 columns in the Person table, the Where clause would include 30 comparisons.

Although this might be jarring, if the Update is not used that often a little overhead  is fine, and that’s the price you pay for the convenience of LINQ.
However, at the other extreme end, if the Update operation is on a critical path and needs to be perform like a dragster with clean pipes, use a stored procedure instead.
As an Architect, I recommend that you first understand your app’s profile because software design is always subjective.

A middle ground, without using stored procs but improving performance by avoid the various Where clause checks, is to use a Timestamp column in DLinq as follows…
 

 

update linq Let’s say we have a LastModifiedAt column in the database, to use as a timestamp.

As shown, select it in the OR/M designer and View Properties.
Set the Timestamp property to True, as shown.

Now, DLinq will use this column for concurrency checks instead of the in-efficient Where clause

 

Remember to set the default value of the timestamp column in your database to getUtcDate() or something, and don’t update this column with LINQ.

Note the Property values that are there. They can be set to improve the efficiency for different scenarios. For e.g. I set Auto-sync to Never because I don’t update an Entity multiple times, so I don’t need it to be updated in LINQ every time.

In the future, I will explore Transactions and multiple Data Contexts when it comes to DAL design.

As you may know, MVC2 is the newest version of a Model-View-Controller framework built on top of ASP.NET and .NET 3.5 SP1, and comes built-in with Visual Studio 2010.
This is an on-going series about creating simple web-sites in MVC2 and I’ll talk about some relevant Design Patterns along the way. I’ll be using C#, but the knowledge is applicable to any .NET language.

Ok, let’s get started in VS 2010. Select New Project: ASP.NET MVC2 Web Application, and Select a Name and Location for your solution. In the following step, I would highly recommend opting to Create a Project with Unit Tests.

Right off the bat, you get a Web Solution with some default folders containing Models, Views and Controllers.

mvcfolders Unlike some Java MVC frameworks and like Ruby on Rails, MVC2 follows the principle of Convention over Configuration. What this means is that the folder structure and naming of your pieces is important and should follow the required Convention, but no Configuration information is required because the Framework will automatically wire things together, which is quite convenient.

As you see, folders called Controllers, Models and Views are created. prefixes and names are important here. For e.g. an Employee controller should be called EmployeeController and be in the Controllers folder. There should be a Views folder called Employee which will contain Views for it. Default Views are named as the Action.
You can define a model (think data class) under Models.

To support a request like http://mywebsite/employee/details/12345, we need to create an Employee controller and a Details View:

Controllers
   EmployeeController.cs

Views
   Employee
      details.aspx

To create the Employee controller, right-click the Controlers folder, select Add a Controller called EmployeeController.
To create the View, right-click Views folder, Create a folder called Employee, and right Click to Add  a View called details.aspx

If you want to create a strongly-typed data class, it could be

Models
   EmployeeData.cs

So, now we have the pieces, and need to wire the http request to the Controller.
The routing is MVC2 is done in global.asax.cs with

routes.MapRoute(…)

That specifies how an http request of a given pattern will invoke a specified action (or method) on a specified Controller class with the required parameters.
You should really understand how this routing works; this is a critical part of your app, and takes a little time to wrap your head around because it is super-flexible.

e.g. To map employee/details/12345, specify:

routes.MapRoute(
    “employeeDetails”, 
    “{controller}/{action}/{id}”,
     new { controller = “Employee”, action = “Details”, id = UrlParameter.Optional } 

This means that any request in the form of employee/details/* will invoke an Action (or method) called details in the employee controller (class named EmployeeController).

Next, we’ll talk a bit about models, passing data around and the new and powerful Data Attributes…

If you have sets of data that have a visual element, and would like to show them as sets that a user can interact with, e.g. zoom-in, filter, go to linked content, etc., consider a tool called Pivot from Microsoft Labs.

You can get it and read all about it at: www.getpivot.com
A good presentation with some design principles is at: http://live.visitmix.com/MIX10/Sessions/FTL02
An older TED presentation about the technology, that shows interesting possibilities: http://www.ted.com/talks/gary_flake_is_pivot_a_turning_point_for_web_exploration.html

Application showing a sample image set:

Note: The grouping, filters, etc. are automatically generated by the tool, based on the XML used to define the data-set.

I shall write more about this technology after I play with the Silverlight Pivot control.