Extract SQL Column Extended Properties From LINQ i

问题:

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

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

回答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.

回答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/ ...

回答3:

You could create a view in your DB selecting all the info you need from sys views - starting with sys.extended_properties view (in SQL2005 and later) and joining others as needed (sys.tables and sys.all_columns in your case I'd think). Then just import that view in VS in your model.

Alternatively you could use SMO to access all your SQL Server metadata, including extended properties on tables, columns etc. That will be LINQ to Objects though I believe. By the way this is the only portable (across SQL Server versions) way to query this information.

http://msdn.microsoft.com/en-us/library/ms162557.aspx