Archive for category Entity Framework

Encrypting the connection string for Entity Framework

I needed to encrypt the connection string that Entity Framework uses for its magic. So I encrypted the value in the “connectionString” attribute:

<connectionStrings>
   <add name="ConnString" connectionString="x4eAeGmvVTwzSOE2js7oWjQgdf" 
      providerName="System.Data.EntityClient" />
</connectionStrings>

I then updated the class in my application that is derived from the DbContext class, decrypting (using the creatively named “ScottsDecryption” (and no, that’s not the actual name)) the string in the call to “base”:

public partial class Entities : DbContext {
   public Entities()

       : base(ScottsDecryption(ConfigurationManager.ConnectionStrings["Entities"].ConnectionString)) { }
}

By the way, I received a ‘Keyword not supported: “data source”‘ error when first trying to get this working. I needed to replace the “&quot;” entries with actual single quote marks in the connection string, as described in this Stack Overflow post. So I had to change this connection string:

metadata=res://*/Models.Model1.csdl|res://*/Models.Model1.ssdl|res://*/Models.SRModel.msl;provider=Oracle.ManagedDataAccess.Client;provider connection string=&quot;Data Source=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=blah)(PORT = 1234))(CONNECT_DATA=(SERVICE_NAME=scott.no.way)));User Id=MyID;Password=nope;&quot;

To this:

metadata=res://*/Models.Model1.csdl|res://*/Models.Model1.ssdl|res://*/Models.SRModel.msl;provider=Oracle.ManagedDataAccess.Client;provider connection string='Data Source=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=blah)(PORT = 1234))(CONNECT_DATA=(SERVICE_NAME=scott.no.way)));User Id=MyID;Password=nope;'
Advertisements

Leave a comment

The Entity Framework provider type… exception when unit testing

While merrily going about my business creating an MVC project using Entity Framework 6 against an Oracle database, I decided that unit testing my data layer would be a fun, useful thing to do. So I added a reference to my MVC project into my unit test project and away I went. But not too far because I got this error:

{System.InvalidOperationException: The Entity Framework provider type ‘Oracle.ManagedDataAccess.EntityFramework.EFOracleProviderServices, Oracle.ManagedDataAccess.EntityFramework, Version=6.121.2.0, Culture=neutral, PublicKeyToken=89b483f429c47342’ registered in the application config file for the ADO.NET provider with invariant name ‘Oracle.ManagedDataAccess.Client’ could not be loaded. Make sure that the assembly-qualified name is used and that the assembly is available to the running application.

This was thankfully an easy issue to correct. In my unit test project, I added references to the Oracle.ManagedDataAccess and Oracle.ManagedDataAccess.EntityFramework DLLs, and I was testing like a madman.

Leave a comment

MetadataException: Unable to load the specified metadata resource

Using Entity Framework, in my context class, I had defined the constructor which pointed to a connection string:

public class EFTestContext : DbContext {
    public EFTestContext()
        : base("name=Entities") {
    }
}

When I attempted to run the application, I got this message:

 MetadataException: Unable to load the specified metadata resource

Turns out, the connection string was wrong. I had copied the constructor code over from a different project, but when I created the connection string in the web.config, I named it something other than “Entities”. Once I changed the connection string in the constructor, it was good:

: base("name=RightConnString") {

1 Comment

DateTime out-of-range error in Entity Framework

Trying to add a new object to my context in MVC / Entity Framework, I ran into this error:

The conversion of a datetime2 data type to a datetime data type resulted in an out-of-range value.
The statement has been terminated.

I was, in fact, just adding a minimum date to my property, so I didn’t see the problem:

using (UsersContext db = new UsersContext()) {
  db.UserProfiles.Add(new UserProfile { UserName = model.UserName, 
    DateLastLoggedIn = DateTime.MinValue });
  db.SaveChanges();
}

Through the miracle of Stack Overflow, I discovered that I needed to set a DateTime greater than the minimum date. So I picked an arbitrary date

db.UserProfiles.Add(new UserProfile { UserName = model.UserName, 
DateLastLoggedIn = new DateTime(1982, 1, 1) });

I chose 1982 because that is when “I Ran” by “Flock of Seagulls” was released. But choose the release date for your favorite song,  even if, for some reason, it’s by Miley Cyrus, and you should be OK.

Leave a comment

EntityException: The underlying provider failed on Open

The application I was debugging went wonky on me (“Out of Memory” exceptions, in case your curious), so I had to kill Visual Studio and start over. When I did this, I got the following unfamiliar error when trying to connect to a database using Entity Framework:

EntityException: The underlying provider failed on Open

A good resource appeared to be Stack Overflow, but those weren’t the issues I was dealing with.

I opened SQL Server Management Studio and looked at my database. There was a little triangle of doom icon next to it, with the words “Suspect”. That didn’t sound good. Visions of viruses and evil geniuses trying to take over the word on database at a time rushed into my brain.

Turns out it wasn’t that big a deal to fix. I found a great post at https://support.managed.com/kb/a398/how-to-repair-a-suspect-database-in-mssql.aspx, and that got me back up and running.

Leave a comment

No Entity Framework provider found for the ADO.NET provider with invariant name ‘System.Data.SqlClient’

This was the message I was getting while trying to run a unit test against my C# project:

No Entity Framework provider found for the ADO.NET provider with invariant name ‘System.Data.SqlClient’

Not pleasant. My web project did have Entity Framework 6.x installed, so what was going on?

The issue wasn’t with my web project, it was with my test project. I needed to have those Entity Framework DLLs in my test project as well. I took the easy way out and installed them via NuGet Console. At the prompt, I entered this:

Install-Package EntityFramework

 

Voila, that was all I needed. But just make sure that when the NuGet Console is open, you change the “Default project” drop down list to point to your test project, not the project with the code to be tested.

Leave a comment

“Conversion of a datetime2 data type to a datetime” error in Entity Framework

I was following the very helpful tutorial at http://msdn.microsoft.com/en-us/data/jj206878, taking my first crack at Entity Framework. Yes, I’m more than a little behind the curve on that technology. But, no time like the present to catch up.

To be honest, I was “mostly following” the tutorial. I went off and created my own User table, the script of which is:

CREATE TABLE [dbo].[User] (     
[Id]          INT           IDENTITY (1, 1) NOT NULL,
[FirstName]   VARCHAR (50)  NOT NULL,
[LastName]    VARCHAR (50)  NOT NULL,
[Email]       VARCHAR (100) NOT NULL,
[Password]    VARCHAR (50)  NOT NULL,
 [CreatedDate] DATETIME      DEFAULT (getdate()) NOT NULL,
PRIMARY KEY CLUSTERED ([Id] ASC)
);

And I continued to mostly follow along with the tutorial, generating the various EF classes, and wrote code to insert values into the database using the wonderful EF stuff:

protected void Button1_Click(object sender, EventArgs e)         
{
             using (var db = new Entries())
             {
                 var newUser = new User { FirstName = txt1.Text, LastName = txt2.Text, Email = txt3.Text, Password = txt4.Text };
                 db.Users.Add(newUser);
                 db.SaveChanges();
                var queryUsers = from u in db.Users
                                  orderby u.LastName
                                  select u;
                foreach (var item in queryUsers)
                 {
                     Response.Write("<br>User = " + item.FirstName + " " + item.LastName);
                 }
             }
 }

 

When I ran my incredibly complex code, I received this unhappy message courtesy of a System.Data.Entity.Infrastructure.DbUpdateException:

Message=The conversion of a datetime2 data type to a datetime data type resulted in an out-of-range value. The statement has been terminated.

This was because I had marked my CreatedDate field as nullable. I had thought that the database would just do its thing and create the date when the record was inserted, but EF didn’t like that. So what I did was populate the date in C# code, and that worked out:

var newUser = new User{FirstName = txt1.Text, LastName = txt2.Text, Email = txt3.Text, Password = txt4.Text
, CreatedDate = DateTime.Now};

,

1 Comment