Quick way to detect if a DataContext table or view exists

Question

Currently, I'm developing an application that depends on (and thus connects to) various databases via LINQ-to-SQL. For one of the databases, the connection string may vary and is thus configurable - however, the schema of this database is identical for all connection strings.

Because of the configurable connection string, I want to validate the DataContext during the startup of my application, to make sure that all tables and views my application uses, are available.

The Table<T> objects in the DataContext object are always initialized - even if the corresponding SQL table or view doesn't have any records.

So then. Currently, the validation check is performed as follows:

        bool valid = _dataContext.Articles.Count() > 0
            && _dataContext.Customers.Count() > 0
            && _dataContext.Orders.Count() > 0;

While this does work, the determination of the value of valid takes quite some time (every record of each Table is touched), which ultimately results in a time out. So, is there a faster, more reliable way to determine whether or not a Table<T> of a certain DataContext really exists as a table in the corresponding database?

Answer 1

Here is an (untested) idea:

Grab the name of your table. You can hard code it in, or you can grab it programmatically via

TableAttribute attribute = (TableAttribute)typeof(MyTableObject)
                           .GetCustomAttributes(typeof(TableAttribute), true)
                           .Single();
string name = attribute.Name;

MyTableObject is the LINQ-to-SQL generated object contained in your Table, i.e., the generic parameter T in Table<T>.

(TableAttribute is in System.Data.Linq.Mapping.)

Use the DataContext.ExecuteQuery method as in

var db = new MyDataContext();
var results = db.ExecuteQuery<string>("SELECT name FROM dbo.sysobjects WHERE xtype = 'U'");
bool hasTable = results.Any(s => "dbo." + s == name);    

Answer 2

A slight change on Jason's answer (I gave him an upvote :))

public bool TableExistsInDatabase<T>()
{
  TableAttribute attribute = (TableAttribute)typeof(T)
                             .GetCustomAttributes(typeof(TableAttribute), true)
                             .Single();

  var result = ExecuteQuery<bool>(
                String.Format(
                  "IF OBJECT_ID('{0}', 'U') IS NOT NULL
                   SELECT CAST(1 AS BIT) ELSE 
                   SELECT CAST(0 AS BIT)", attribute.Name));

  return result.First();
}