0

I am using Entityframework 6 Database first. The application am building will allow users to change connection string. After I tested the newly entered connection string validity, I wanted to check if the tables that are on edmx models really exist on the given connection string.

So I tried this:

let say I have ExampleEntity on edmx.. to check if it exist on real database table I used..

try{
   dbContext.ExampleEntity.ToList();
   return true; // the table exist
}
catch{
   return false;
}

Is there a better way to check the existence of the database tables using Entityframework?

ash
  • 2,297
  • 3
  • 16
  • 33
  • And if the table exists, how do you know it's got the expected columns? – Gert Arnold Oct 11 '17 at 09:06
  • @Gert I thought the model properties are the reflection of the table columns. Entity framework will map table columns with the models properties. am I missing something? – ash Oct 11 '17 at 09:11
  • Yeah, but what if the database was changed without changing the names of tables or adding/deleting tables? – Gert Arnold Oct 11 '17 at 09:21
  • You can use Count() method to check if exist, and return true otherwise return false. – Abhay Dixit Oct 11 '17 at 09:24
  • @GertArnold That is why I allowed the users to change the connection string if the database name and server changed. – ash Oct 11 '17 at 09:24
  • 1
    Possible duplicate of [Entity Framework - How to check if table exists?](https://stackoverflow.com/questions/6100969/entity-framework-how-to-check-if-table-exists) – Abhay Dixit Oct 11 '17 at 09:24
  • @Aby I did check that link before and this is not duplicate question because I specifically asked for solution using Entityframework not LINQ or SqlQuery. For you answer.. still it is the same as mine. They both have performance and speed cost . – ash Oct 11 '17 at 09:29
  • @Aby Entityframework has support if the database exist or not using the method dbContext.Database.Exists() but not for the tables as far as I know. – ash Oct 11 '17 at 09:30
  • True, both have performance and speed cost – Abhay Dixit Oct 11 '17 at 09:41

2 Answers2

1

There is no built-in function in Entity Framework database-first that checks if tables in the EDMX store model do actually exist in the database. Only EF code-first, using migrations, has such a check.

What we do in database-first projects is add a little table to the database that contains a version number. This version number is incremented on each database upgrade. When the application starts, we check if the database version matches the version the application should communicate with. It's a very simple, but useful mechanism.

Gert Arnold
  • 100,019
  • 29
  • 193
  • 278
0

You can just ask the database if it has a table with the name like that. 

var tableName = "Tablename";
tableName = "'" + tableName + "'";
var count = cxt.Database.SqlQuery(typeof(int), "SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE='BASE TABLE' AND TABLE_NAME ="+ tableName).ToListAsync().FirstOrDefault();
var result = Convert.ToInt32(count); //1=true, 0=false;
Mr.Sheep
  • 311
  • 3
  • 15
  • 1
    See comments: "I specifically asked for solution using Entityframework not LINQ or SqlQuery". Also, this isn't essentially different than the other SqlQuery answer, or the proposed duplicate. – Gert Arnold Oct 11 '17 at 14:14