LINQ to SQL Trick: Get all Table Names

LINQ to SQL Trick: Get all Table Names

Rate This
  • Comments 6

It's often useful to be able to list all the tables or columns in your DataContext. One example is if you want to write a report generation system that works for any DataContext class. LINQ to SQL has an API called the MetaModel which allows you to get at this information and a lot more. Here's the simplest example, it lists all the table names in the context:

        var model = new AttributeMappingSource().GetModel(typeof(Northwind));

        foreach (var mt in model.GetTables())

            Console.WriteLine(mt.TableName);

The MetaModel holds all of the information the LINQ to SQL uses to map between the CLR type system and the SQL database. The above code doesn't require an instance of the DataContext. If you happen to have an instance then you could also,

        Northwind db = ...from somewhere

        var model = db.Mapping;

        foreach (var mt in model.GetTables())

            Console.WriteLine(mt.TableName);

In a comment below, Max asks how you get the column names for a table. Here it is:

        var model = new AttributeMappingSource().GetModel(typeof(Northwind));
        foreach (var mt in model.GetTables()) {
            Console.WriteLine(mt.TableName);
            foreach (var dm in mt.RowType.DataMembers) 
                Console.WriteLine(" "+dm.MappedName);

The metamodel represents everything that LINQ to SQL knows about the .NET type system and its mapping into the database. So you can find columns, associations, SQL types, inheritance information, and others.

If you're ambitious you could even replace the MetaModel with your own implementation--for example, maybe you want to generate your mapping or the fly, or read it from a web service.

This posting is provided "AS IS" with no warranties, and confers no rights.

Leave a Comment
  • Please add 5 and 4 and type the answer here:
  • Post
  • PingBack from http://www.universityupdate.com/Technology/C-Sharp/4222002.aspx

  • Once you have that how would you go about getting all of the Columns for each of these tables?

  • Max,

    I added an example for column names above.

  • How would you get a particular table using a dropdown to select the table name?

  • hi,

    Im trying to code a webservice that gets the name of a table from a control and returns an array that fills a dropdownlist, something like this:"but something is wrong, could u help me out?

    [WebMethod]

    public ListItemDupla[] GetColumnList(string TableName)

    {

    var model = new AttributeMappingSource().GetModel(typeof(MyDataContext));

    foreach (var mt in model.GetTables())

    {

     if (mt.TableName.ToString() == TableName)

     {

      foreach(var dm in mt.RowType.DataMembers)

      {

       var fields= from c in dm.MappedName

                   select new

                   ListItemDupla(c.ToString(),

                     c.GetType());

                   return campos.ToArray();

      }

     }

    }

    }

  • Hi

    Very useful article, do you know how to or if it is possible to get the column names for a lINQ function as apposed to a table, I have tried replacing GetTables() with GetFunctions in the example above this returns my stored procedure names but than am totally stuck on how to get the column names from my stored procedure if at all possible?

    Thanks in advance!!!

Page 1 of 1 (6 items)