How to reference the SQL column description field through the DBML?

Question

I would like the SQL column description property to hold the friendly name of the column to display to the users. Is there a way that I can reference this column property through the DBML?

Update: We ended up writing a c# method that injects a space into a camel case string and renaming the DB columns to be more friendly.

Answer 1

No, as far as I know neither sqlmetal nor the dbml designer reads the extended properties where the SQL column description is kept. It might be worth looking for third party code generators for linq-to-sql that provide more functionality.

Answer 2

You could write a stored procedure that returned this data and expose that on your datacontext class.

Answer 3

I don't know of anything to do it in the DBML, but it is possible to extract this information yourself, and join it in somehow:

SELECT  C.TABLE_SCHEMA
       ,C.TABLE_NAME
       ,C.COLUMN_NAME
       ,COALESCE(xp.value, C.COLUMN_NAME) AS FriendlyName
FROM    INFORMATION_SCHEMA.COLUMNS C
INNER JOIN INFORMATION_SCHEMA.TABLES T
        ON T.TABLE_CATALOG = C.TABLE_CATALOG
           AND T.TABLE_SCHEMA = C.TABLE_SCHEMA
           AND T.TABLE_NAME = C.TABLE_NAME
           AND T.TABLE_TYPE = 'BASE TABLE'
OUTER APPLY FN_LISTEXTENDEDPROPERTY('MS_Description'
                                    ,'SCHEMA', C.TABLE_SCHEMA
                                    ,'TABLE', T.TABLE_NAME
                                    ,'COLUMN', C.COLUMN_NAME) AS xp

Extended properties can only be attached to base table columns.