Checking if table exists into database / Permission denied when acessing system tables

[PedroGGaspar]

Even though I've been following this project for some time (congratulations, it's awesome!), I only now had the courage to start playing with it and try to start a new project using it. We have some programs divided into modules that are developed with VB6 using mostly DAO (and ADO at some points) to access Jet databases (I know, it's oooold!). I have long wanted to convert the projects to .NET using some technology that would facilitate the migration to SQL Server in the future, and it seemed to me that this project was the bridge I needed.

Well, introduction finished, now to the problem. Currently, to know whether a module is available or not in the database, we have a function that checks the existence of key tables in the database. Does EF Core already has some bult-in functionality like this? If it doesn't have, would the best place to put something like this be in the DbContext itself?

I did it this way, as my DbContext is automatically generated by the scaffolding tool, I created a second file named 'MyDbContext.Custom.cs' (the name still doesn't completely please me as a standard to be followed in these cases, do you have any suggestions regarding this?), which "continues" the implemention of my DbContext partial class:

[ MyDbContext.Custom.cs ]

namespace MyDb;

public partial class MyDbContext : DbContext
{
   private readonly string mJetDbPath;

   public MyDbContext(string jetDbPath)
   {
      // This was the way I found to inform which database I want to open.
      mJetDbPath = jetDbPath;
   }

   protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
   {
      if (!optionsBuilder.IsConfigured)
      {
         optionsBuilder
             // The reason for calling this UseModel() method is that I used "dotnet ef dbcontext optimize"
             // command to create CompiledModels, trying to speed up initial loading of MyDbContext object.
             .UseModel(MyDb.CompiledModels.MyDbContextModel.Instance)
             .UseJet($"Provider=Microsoft.Jet.OLEDB.4.0;Data Source={mJetDbPath};");
      }
   }

   public bool TablesExists(string tableName)
   {
      bool exists = this.Database
                     .SqlQuery<int>(
                        $@"SELECT Count(Name)
                           FROM MSysObjects
                           WHERE Type = 1
                           AND Name = '{tableName}'")
                     .SingleOrDefault() > 0;
      return exists;
   }
}

But when I do this, I get an exception saying that permission for 'MSysObjects' has been denied. Do you have any clue how to solve this?

[ChrisJollyAU]

That exception is probably due to permissions or something. Might work if opened in exclusive mode.

However I do have an alternative and this is in use with various tests. There is a pseudo command SQL that should do what you are looking for

SELECT * FROM `INFORMATION_SCHEMA.TABLES` WHERE `TABLE_NAME` = 'Learn'

Note that it has to be run from a command. Executing it as a raw sql will bypass where this is intercepted. As Jet/MS Access doesn't specifically have anything like this, it is intercepted, and ADOX/DAO is used to get the info and return.

ModelContext context = new ModelContext();
context.Database.OpenConnection();
var command = context.Database.GetDbConnection().CreateCommand();
command.CommandText = "SELECT * FROM `INFORMATION_SCHEMA.TABLES` WHERE `TABLE_NAME` = 'Learn'";
var aa = command.ExecuteScalar();

It isn't full featured. While it looks like it should return all potential fields from the info it gets, it actually only returns the first column. For the tables, this is the TABLE_NAME property. So the above example, aa will bu NULL if the table does not exist or Learn if it does.

https://github.com/CirrusRedOrg/EntityFrameworkCore.Jet/blob/master/src/EFCore.Jet.Data/JetStoreSchemaDefinition/JetInformationSchema.cs shows the full available syntax.

https://github.com/CirrusRedOrg/EntityFrameworkCore.Jet/blob/master/src/EFCore.Jet.Data/JetStoreSchemaDefinition/SchemaTables.cs Can have a look at this to see the table defintions. Can do a WHERE on them and ORDER

SELECT * FROM `INFORMATION_SCHEMA.TABLES` WHERE TABLE_TYPE IN ('BASE TABLE', 'VIEW')

[PedroGGaspar]

Thanks @ChrisJollyAU, it worked like this:

   public bool TablesExists(string tableName)
   {
      this.Database.OpenConnection();
      using (var command = this.Database.GetDbConnection().CreateCommand())
      {
         command.CommandText =
            $@"SELECT *
            FROM `INFORMATION_SCHEMA.TABLES`
            WHERE `TABLE_TYPE` IN ('BASE TABLE', 'VIEW')
            AND `TABLE_NAME` = '{tableName}'";
         var result = command.ExecuteScalar();
         string tableNameColumn = (result == DBNull.Value) ? null : result.ToString();
         return !string.IsNullOrEmpty(tableNameColumn);
      }
   }

I noticed in your example that you created an instance of a DbContext (I think so) in the fisrt line of code: ModelContext context = new ModelContext(); So, you wouldn't put that function into DbContext as a method, right? Is it a bad practice?

[ChrisJollyAU]

Yes, the ModelContext is derived from DbContext. It is the name the scaffolding gave to it. Your example it would be the MyDbContext class.

My test program was just an empty project with a Program.cs and the scaffolded classes so I ended up doing it all in the main function. So that was just creating the context and getting it set up. Depending on your workflow, your context would already be created and the connection may be open.

Also you probably don't need the TABLE_TYPE part in the where. It won't affect much, just that it will filter out any system and internal tables. So MSysObjects will not be found if you include that clause, but will be found without it.

You could optimize things a bit If you know the connection will always be open at that stage you won't need the OpenConnection call For the result you can probably just do a simple != DBNull or similar. No need to convert it to a string. If it returns anything other than DBNull it is found