query on master database throws UnintentionalCodeFirstException in DatabaseFirst

Question

Having huge problems with creating model for sql servers master db. I selected database first, didn't include any objects, so I've an empty model:

masterModel.cs:

*//------------------------------------------------------------------------------
// <auto-generated>
//     This code was generated from a template.
//
//     Manual changes to this file may cause unexpected behavior in your application.
//     Manual changes to this file will be overwritten if the code is regenerated.
// </auto-generated>
//------------------------------------------------------------------------------*

masterEntities.cs:

  public partial class masterEntities
  {

    static masterEntities()
    {
      // don't let EF modify the database schema...
      Database.SetInitializer<masterEntities>(null);
    }

    public masterEntities(string connectionString)
        : base(connectionString)
    {

    }
}

The goal is to fire a query against the master database to find out, if current user has serveradmin privileges (to create new database later on):

   public bool UserHasAdminPrivilegesAtDatabaseServer(DatabaseServer databaseServer)
    {

      var sqlBuilder = new SqlConnectionStringBuilder
      {
        DataSource = databaseServer.Server,
        InitialCatalog = "master",
        PersistSecurityInfo = true,
        IntegratedSecurity = true,
        MultipleActiveResultSets = false,


      };

      //assumes a connectionString name in .config of MyDbEntities
      var entityConnectionStringBuilder = new EntityConnectionStringBuilder
      {
        Provider = "System.Data.SqlClient",
        ProviderConnectionString = sqlBuilder.ConnectionString,
        Metadata = "res://*/masterModel.csdl|res://*/masterModel.ssdl|res://*/masterModel.msl",

      };

      var connstring = entityConnectionStringBuilder.ProviderConnectionString;

      using (var context = new masterEntities(connstring))
      {
        const string sql = "SELECT IS_SRVROLEMEMBER('sysadmin')";//"SELECT IS_SRVROLEMEMBER('sysadmin')";

        var a = context.Database.SqlQuery<int>(sql).FirstOrDefault();
        return a == 1;

      }
    }

The problem is now, that EF fires the event OnModelCreating, which is implemented as following in DatabaseFirst mode:

    using System;
    using System.Data.Entity;
    using System.Data.Entity.Infrastructure;

    public partial class masterEntities : DbContext
    {
        public masterEntities()
            : base("name=masterEntities")
        {
        }

        protected override void OnModelCreating(DbModelBuilder modelBuilder)
        {
            throw new UnintentionalCodeFirstException();
        }

    }

and here we are: throw new UnintentionalCodeFirstException(); Somehow EF wants to create some stuff in the master-db; even when we set the global static property, that it shouldn't and the model itself is empty: Database.SetInitializer<masterEntities>(null);

score 0 · Accepted Answer · edited May 23 '17 at 12:19

found the answer here: https://stackoverflow.com/a/26120762/5172266

the MetaData must be filled correctly:

    private static string GetConnectionString(string model, string providerConnectionString)
{

  var efConnection = new EntityConnectionStringBuilder();
  // or the config file based connection without provider connection string
  // var efConnection = new EntityConnectionStringBuilder(@"metadata=res://*/model1.csdl|res://*/model1.ssdl|res://*/model1.msl;provider=System.Data.SqlClient;");
  efConnection.Provider = "System.Data.SqlClient";
  efConnection.ProviderConnectionString = providerConnectionString;
  // based on whether you choose to supply the app.config connection string to the constructor
  efConnection.Metadata = string.Format("res://*/correctModel.{0}.csdl|res://*/correctModel.{0}.ssdl|res://*/correctModel.{0}.msl", model);
  // Make sure the "res://*/..." matches what's already in your config file.
  return efConnection.ToString();
}

Explanation (credit goes to reckface)

The exception you get is because when you pass a pure SQL connection string, it assumes you are working with Code first, so it calls the OnModelCreation event. When you include the MetaData section as shown above, that tells EF it's a complete EF connection string.

query on master database throws UnintentionalCodeFirstException in DatabaseFirst

1 Answers1