Extract SQL Column Extended Properties From LINQ in C#

Question

Hi, i have an SQL table with extended properties on each column.

Is there a way to access these from LINQ in c# using Linq2SQL?

Answer 1

Things like "MS_Description" etc? Not AFAIK; you could write an SP that talks to the store at the database (taking the database object name and column name), and query that via the data-context - but nothing built in.

It would also be pretty easy to write some code that uses Expression to get the database names (instead of the OO names) to pass in. Something like:

    public static string GetProperty<TContext, TValue>(
        this TContext ctx, Expression<Func<TContext, TValue>> selector,
        string propertyName)
        where TContext : DataContext
    {
        MemberExpression me = selector.Body as MemberExpression;
        if (me == null) throw new InvalidOperationException();
        var member = me.Member;
        var objType = me.Expression.Type;
        var metaType = ctx.Mapping.GetMetaType(objType);
        string tableName = metaType.Table.TableName;
        string columnName = metaType.GetDataMember(member).MappedName;

        return ctx.GetProperty(tableName, columnName, propertyName);
    }

(or something similar; just firing up a test db...)

Where you provide the GetProperty method via a mapped SPROC.

Update: yes, that kinda works; example:

string desc = ctx.GetProperty(x => x. DataChanges.First().Change, "MS_Description");

The First() is a pain, but less ugly than having to have two selectors; it could be re-written that way, though:

string desc = ctx.GetProperty(x => x.DataChanges, dc => dc.Change, "MS_Description");

Up to you which is less hacky. Note also that you'd need to split the owner out of tableName.

Answer 2

In what way do you want to access them? My L2S add-in for VS 2008 can turn them into xml doc comments (so they show up in VS intellisense), and description attributes [that you can access from code]. See the 'update documentation from database' feature at http://www.huagati.com/dbmltools/ ...