Entity-Framework 5.0 with PostgreSQL

Question

I would like to use the Entity-Framework 5.0 as an O/R mapper with a PostgreSql database. I already read many HowTos but I'm still stuck. Maybe I don't get the whole idea behind the EF.

What I want:

• I want to write the code by my own. Then put some attributes to the properties and classes to tell the EF how to map the objects (like I would do with NHibernate).
• I don't want to generate a database from my model - I just want to use an existing one.

Example Model:

[Table("device")]
public class Device
{
    [Key]
    public int Id { get; set; }

    [StringLength(255)]
    public string Identifier { get; set; }
}

[Table("customer")]
public class Customer
{
    public Customer()
    {
        this.Devices = new List<Device>();
    }

    [Key]
    public int Id { get; set; }

    [StringLength(255)]
    public string Name { get; set; }

    // attribute required?
    public List<Device> Devices { get; set; }
}

What I did:

• Wrote the code above
• Created a DatabaseContext class
• Added DbProviderFactories and connectionStrings in the app.config
• Generated ssdl, msl, csdl files with the EdmGen.exe tool and copied them in the project (these files are also available in the bin folder)

DatabaseContext:

public class DatabaseContext : DbContext
{
    static DatabaseContext()
    {
        Database.SetInitializer<DatabaseContext>(null);
    }

    public DatabaseContext()
        : base("PostgreSQL")
    {

    }

    public DbSet<Customer> Customers { get; set; }
    public DbSet<Device> Devices { get; set; }
}

app.config:

<?xml version="1.0" encoding="utf-8"?>
<configuration>
  <configSections>
    <!-- For more information on Entity Framework configuration, visit http://go.microsoft.com/fwlink/?LinkID=237468 -->
    <section name="entityFramework" type="System.Data.Entity.Internal.ConfigFile.EntityFrameworkSection, EntityFramework, Version=5.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" requirePermission="false" />
  </configSections>
  <startup>
    <supportedRuntime version="v4.0" sku=".NETFramework,Version=v4.5" />
  </startup>

  <system.data>
    <DbProviderFactories>
      <remove invariant="Npgsql" />
      <add name="Npgsql Data Provider" invariant="Npgsql" support="FF" description=".Net Framework Data Provider for Postgresql Server" type="Npgsql.NpgsqlFactory, Npgsql, Version=2.0.12.0, Culture=neutral, PublicKeyToken=5d8b90d52f46fda7" />
    </DbProviderFactories>
  </system.data>

  <connectionStrings>
    <add name="PostGreSQL" 
         providerName="System.Data.EntityClient" 
         connectionString="metadata=.;provider=Npgsql;provider connection string=&quot;Server=asdf;Port=5432;User Id=asdf;Password=asdf;Database=asdf;enlist=true&quot;" />
  </connectionStrings>

</configuration>

Test:

using (DatabaseContext context = new DatabaseContext())
{
    if (context.Database.Exists())
    {
        Console.WriteLine("yep");
    }
    else
    {
        Console.WriteLine("nope");
    }

    foreach (Customer c in context.Customers)
    {
        Console.WriteLine("Name={0}", c.Name);
    }
}

Console.ReadLine();

Output & Error:

"yep" .. which means the database exist. But when accessing the context.Customers property I get the following Exception:

The entity type Customer is not part of the model for the current context.

Stacktrace:

at System.Data.Entity.Internal.InternalContext.UpdateEntitySetMappingsForType(Type entityType)
   at System.Data.Entity.Internal.InternalContext.GetEntitySetAndBaseTypeForType(Type entityType)
   at System.Data.Entity.Internal.Linq.InternalSet`1.Initialize()
   at System.Data.Entity.Internal.Linq.InternalSet`1.GetEnumerator()
   at System.Data.Entity.Infrastructure.DbQuery`1.System.Collections.Generic.IEnumerable<TResult>.GetEnumerator()
   at EF5.Program.Main(String[] args) in c:\Users\tba\Documents\Visual Studio 2012\Projects\EF5\EF5\Program.cs:line 26
   at System.AppDomain._nExecuteAssembly(RuntimeAssembly assembly, String[] args)
   at System.AppDomain.ExecuteAssembly(String assemblyFile, Evidence assemblySecurity, String[] args)
   at Microsoft.VisualStudio.HostingProcess.HostProc.RunUsersAssembly()
   at System.Threading.ThreadHelper.ThreadStart_Context(Object state)
   at System.Threading.ExecutionContext.RunInternal(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean preserveSyncCtx)
   at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean preserveSyncCtx)
   at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state)
   at System.Threading.ThreadHelper.ThreadStart()

What I don't understand:

• What are the ssdl, msl, csdl files good for, if DataAnnoations are available?
• Why is the Customer class not part of the model for the current context?

When using the EdmGen.exe Tool it generates also a ObjectLayer.cs file which contains an ObjectContext and entity classes. The problem with that is, the entity classes derive from EntityObject. If I want to use the entity classes as business classes this is a serious problem, because the classes can't derive from any other classes.

Moreover I don't understand why it generates an ObjectContext.. every single HowTo I found uses the DbContext class.

Entity Customer class generated by the EdmGen tool:

/// <summary>
/// No Metadata Documentation available.
/// </summary>
[EdmEntityTypeAttribute(NamespaceName="EF5", Name="Customer")]
[Serializable()]
[DataContractAttribute(IsReference=true)]
public partial class Customer : EntityObject

I am confused :-)

Edit:

• I am not using the ObjectLayer.cs file! And I also don't want to use it, due to the inheritance problems I stated above. The partial Customer class is just an example to show what the EdmGen tool generates.

• When I remove the csdl, msl and ssdl files I get the following error:

  At least one SSDL artifact is required for creating StoreItemCollection.
  

• When I remove the metadata keyword from the connectionString property in the app.config I get the following error:

  Some required information is missing from the connection string. The 'metadata' keyword is always required.
  

Solution:

I finally figured out how it works. The connectionString in the app.config is wrong. The correct connectionString:

<connectionStrings>
    <add name="PostgreSQL"
        connectionString="Server=asdf;Port=5432;User Id=asdf;Password=asdf;Database=asdf;"
         providerName="Npgsql" />
</connectionStrings>

I removed the metadata, provider and the provider connection string property from the connectionString. Like Brice said, the metadata files aren't necessary, so I just removed them.

Also the Table-Attribute for the Device and the Customer class needs to specify the schema. Like this:

[Table("customer", Schema="public")]
public class Customer

Answer 1

You shouldn't be generating the ssdl, msl, csdl files. These files are only required if you want to use the designer. Delete these files and the generated ObjectLayer.cs.

You should also not be using an EntityClient connection string. Update your connection string to simply be the following.

<connectionStrings>
  <add name="PostGreSQL"
       providerName="Npgsql"
       connectionString="Server=asdf;Port=5432;User Id=asdf;Password=asdf;Database=asdf;enlist=true" />
</connectionStrings>