Quick way to detect if a DataContext table or view exists

c#, datacontext, linq-to-sql

Solution

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);

Problem

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?

Original source