Archive

Posts Tagged ‘Linq2Entities’

WCF RIA Services – An entity with the same identity already exists in this EntitySet.

July 16, 2010 3 comments

WCF RIA Services is pretty powerful and does a lot for you when it comes to quickly building business applications. I came across something interesting today when I was working with a client.

For this post, I am going to be focusing on WCF RIA Services in conjunction with the EntityFramework. It turns out that if you have designed your tables with identities and you have set your seed for the table to be zero (0), then you will have problems right after your first record has been created and saved to the database.

The following is a screen shot of a given table in SQL Server with the identity seed set at zero (0):

Identity Seed

The moment you try to create a subsequent record, you will get the following error:

InValidOperationException was unhandled by user code: An entity with the same identity already exists in this EntitySet.

The reason for this is that RIA Services tries to created a new record that has an identity of zero (0) and as soon as you try and add it to the underlying collection it throws an exception since a record with that value already exists.

In WCF RIA Services, I don’t believe there is a way to change the default behavior for when a new record is inserted. It looks like it follows whatever datatype is defined as the identity column that in the table. So if the column is defined as an integer than the default value would be zero (0).

I believe I remember being able to bypass this issue in ADO.NET with DataSets with the ability to define what the default value would be for adding a new record.

More than anything else, I wanted you to be aware of a possible exception that may not intuitively makes sense at first but then becomes clear once you see the data. You may get tricked into thinking that your system is working if you have an empty table and insert your first record. This will work perfectly fine since it will insert a record with the identity value being zero (0). The moment you try to add an additional record you will get the exception mentioned above.

I would like to stress that for most scenarios you should not need be concerned unless your team has designed your tables with the seed of your identity columns to be zero (0) instead of one (1). In SQL Server 200X, the default value for the seed of an integer identity is one (1).

Hope this helps to prevent any confusion. Also, if anyone knows how to rememdy this problem please let me know.

Advertisements

Modifying a column with the Identity pattern is not supported.

In doing some testing today, I came across a wierd scenario with using WCF RIA Services on top of the Entity Framework. I was testing a Site table that had the following data definition:

CREATE TABLE Site
(
	  SiteIdent int identity NOT NULL
	, SiteKey varchar(4) primary key NOT NULL
	, MinimumPasswordLength int NOT NULL
	, RequirePasswordAuthentication bit NOT NULL
)

When I tried to make a modification to an existing record, I received the message in the title of my post:

“Modifying a column with the Identity pattern is not supported.”

One thing to note, I saw several other posts stating that this works with Entity Framework outside of WCF RIA Services. I verified that the StoreGeneratedPattern was set to “Identity” but it did not help. I also saw an entry in the msdn forums talking about creating a custom attribute.

Because I have control of the database, I made the following change to the data model to correct the issue:

CREATE TABLE Site
(
	  SiteIdent int identity primary key NOT NULL
	, SiteKey varchar(4) NOT NULL
	, MinimumPasswordLength int NOT NULL
	, RequirePasswordAuthentication bit NOT NULL
) ON [PRIMARY]
GO
CREATE UNIQUE NONCLUSTERED INDEX [IX_Site0] ON [dbo].[Site]
(
	[SiteKey] ASC
) ON [PRIMARY]

It is curious since I did not encounter this problem when I had previously used RIA Services on top of Linq2Sql. I am sure that there are several ways to resolve this issue but I wanted to show one way to getting past this issue.

Hope this helps….

Load operation failed for query ‘Getcustomer’. The metadata stored by the ObjectContext is different than the metadata stored by the ObjectContext’s connection. This can happen if the connection string is changed after the ObjectContext is created.

May 25, 2010 1 comment

This exception goes hand in hand with my previous blog posts on dynamically changing the connection string in Entity Framework. If you override CreateObjectContext(), you need to be sure that you don’t first create an instance of your entities and then try and pass in the connection string by setting the property.

The following is an example of what will cause the exception:

protected override CustomerEntities CreateObjectContext()
{
    string connectionString = ...
    ...
    ctx = new GrantsManagementEntities();
    ctx.Connection.ConnectionString = connectionString;

    return ctx;
}

Assuming we had a valid connection string, you would get the exception as listed in the title of this post.

The way to correct this issue is to do the following:

protected override CustomerEntities CreateObjectContext()
{
    string connectionString = ...
    ...
    ctx = new GrantsManagementEntities(connectionString);

    return ctx;
}

This allows for having dynamic connection strings on your WCF RIA Service.

Hope this helps…

The specified named connection is either not found in the configuration, not intended to be used with the EntityClient provider, or not valid.

May 25, 2010 15 comments

The specified named connection is either not found in the configuration, not intended to be used with the EntityClient provider, or not valid.

I wanted to share some bad with the good. I typically have a WCF RIA Services Class Library for every logical line of business in my enterprise application. With that said, I tend to have several libraries that are dedicated to different modules in my application.

When you create a new WCF RIA Services class library, it creates two projects. You get a project for the client and one for hosting the data access. One of the common mistakes I can make is to forget to copy the connection string information from the web project to the underlying hosting web application.

The title of this blog is the message from the exception that gets thrown if you forget to add the connection string entry in the underlying hosting web application web.config file.

This may not solve all of your problems with this exception but it is one to thing to remember.

Dynamically changing the connection string for WCF RIA Services Linq2Entities

May 20, 2010 39 comments

One of the business requirements I get a lot is the ability to switch which database they use when they authenticate. I have just recently ported my applications over from Linq2Sql to Linq2Entties and I have noticed just enough difference that I figured I would blog about how to do this. I will also publish a subsequent post that shows how this is accomplished in Linq2Sql.

Okay, so you want to provide the ability to change the underlying connection string for a given WCF RIA Services request? The following is an example of creating a WCF RIA Services Class Library. I have also added an ADO.NET Entity Data Model as well a DomainService Class.

Data Access Project

One thing that you will notice, is that I also manually created the DataAccessDomainService.partial.cs. It is in this class that I implement the ability to switch connection strings. I have found that since the other files are code generated, that by having my logic in a partial class, I can bypass the headache when I need to update or change my model and domain service.

Note that I had to add the partial keyword to the main class in order to get my partial class to work. Also the main class derives from LinqToEntitiesDomainService where T is the model we are building our DomainService. In this case it would be DataAccessContext.

Let’s take a look at partial class now:

public partial class DataAccessDomainService
{
    /// <summary>
    /// We need override this function since we are dynamically changing the
    /// connection string based on the user's session information.
    /// </summary>
    /// <returns></returns>
    protected override DataAccessContext CreateObjectContext()
    {
        DataAccessContext ctx = null;
        if (!string.IsNullOrEmpty(ServiceContext.User.Identity.Name))
        {
            SecureLoginEntities sctx = new SecureLoginEntities();
            var userSession = (from c in sctx.sl_us_UserSession where
                c.sl_us_Token.HasValue.Equals(true) &&
                c.sl_us_Token.Value.Equals(new G
                    uid(ServiceContext.User.Identity.Name)) select
                c).FirstOrDefault();
            if (userSession != null)
            {
                sl_d_Database db = (from c in sctx.sl_d_Database where
                    c.sl_d_DatabaseIdent == userSession.sl_us_DatabaseId
                    select c).FirstOrDefault();
                if (db != null)
                {
                    string connString = EntityConnectionStringHelper.Build(
                        db.sl_d_ServerName,
                        db.sl_d_DatabaseName,
                        db.sl_d_UserName,
                        db.sl_d_Password,
                        "DataAccess");
                    ctx = new DataAccessContext(connString);
                }
            }
        }

        return ctx;
    }

};

Basically what I want to happen is that whenever there is a request to this service, I want to override the CreateContext method and change the connection string accordingly. I have some authentication logic that I am using that helps me determine what database the user wants to use but once I get past that I basically create a new instance of my context passing in the newly created connection string.

Note: The string “DataAccess” that I am passing into the EntityConnectionStringHelper.Build method above is actually from the Web.Config connection string. It represents the *.csdl, *.ssdl, *.msl names that are represented in the style that the Entity Framework requires.

In order to help me and get some code reuse, I created a helper class that helps me with buildng my connection string. This is one of the main differences between Linq2Sql and Linq2Entities.

Let’s look at this code.

public class EntityConnectionStringHelper
{
    public static string Build(
        string serverName,
        string databaseName,
        string userName,
        string password,
        string metaData)
    {
        // Specify the provider name, server and database.
        string providerName = "System.Data.SqlClient";
        return EntityConnectionStringHelper.Build(
            providerName,
            serverName,
            databaseName,
            userName,
            password,
            metaData);
    }
    public static string Build(
        string providerName,
        string serverName,
        string databaseName,
        string userName,
        string password,
        string metaData)
    {
        // Initialize the connection string builder for the
        // underlying provider.
        SqlConnectionStringBuilder sqlBuilder =
            new SqlConnectionStringBuilder();

        // Set the properties for the data source.
        sqlBuilder.DataSource = serverName;
        sqlBuilder.InitialCatalog = databaseName;
        sqlBuilder.IntegratedSecurity = false;
        sqlBuilder.UserID = userName;
        sqlBuilder.Password = password;

        // Build the SqlConnection connection string.
        string providerString = sqlBuilder.ToString();

        // Initialize the EntityConnectionStringBuilder.
        EntityConnectionStringBuilder entityBuilder =
            new EntityConnectionStringBuilder();

        //Set the provider name.
        entityBuilder.Provider = providerName;

        // Set the provider-specific connection string.
        entityBuilder.ProviderConnectionString = providerString;

        // Set the Metadata location.
        entityBuilder.Metadata = string.Format(@"res://*/{0}.csdl|res://*/{0}.ssdl|res://*/{0}.msl",
            metaData);

        return entityBuilder.ToString();
    }
};

Basically this class has two static methods called Build. One calls into the other passing in the providername. In order to correctly build a Linq2Entities connection string you have to also include the Metadata. This is the main difference between Linq2Sql and Linq2Entities. For this example, I am building connection strings for SQL Server. I am using the SqlConnectionStringBuilder class in conjunction with the EntityConnectionStringBuilder class.

When all is said and done, you will get a connection string that looks like the following example taken from my Web.config:

<configuration>
  <connectionStrings>

    <add name="DataAccessContext"
      connectionString="metadata=res://*/DataAccess.csdl|res://*/DataAccess.ssdl|
        res://*/DataAccess.msl;provider=System.Data.SqlClient;
        provider connection string=&quot;Data Source=bunkerhill;
        Initial Catalog=emgov_data;Integrated Security=False;
        User Id=emgovuser;Password=xxxx;
        MultipleActiveResultSets=True&quot;"
        providerName="System.Data.EntityClient" />

  </connectionStrings>
  ...
<configuration>

(NOTE: I put break carriage return in this so that you could read it better but it would not be like this in your Web.config file.)

By following this example, you can now provide the ability to switch between connection string dynamically. You can even go as far as storing the connection information in a database and build the connection string on the fly as well.

Hope this helps…