Obtain max length for a "string" column using LINQ To SQL

Question

Is it possible to obtain the maximum column length for a VARCHAR, CHAR etc?

Answer 1

In pure T-SQL you can use this query:

select max_length from sys.columns as c inner join sys.objects o on c.object_id = o.object_id where o.name = 'myTable' and c.name = 'myColumn'

For linq-to-sql you need it rewrite into linq.

Answer 2

Here's a way which avoids touching the database:

• Using Reflection, get the property of the entity class that corresponds to the column in question.
• Then, retrieve the System.Data.Linq.Mapping.Column attribute of the property.
• Then, parse the DbType property of this attribute (eg NVarChar(255) NOT NULL) to get the column length.

Answer 3

If you want the maximum length of values in a particular column (C#):

int maxLength = dc.MyTable.Max(x => x.MyColumn.Length);

Answer 4

Answered here:

http://stackoverflow.com/questions/20684/linq-to-sql-underlying-column-length

Though I found it neater to change:

public static int GetLengthLimit(Type type, string field) //definition changed so we no longer need the object reference
//Type type = obj.GetType(); //comment this line

And call using:

int i = GetLengthLimit(typeof(Pet), "Name");

Can anybody think of a of way to strongly type the field reference?

Answer 5

Hmm, anyone find a way to use strongly typed field reference for ctralt3nd's solution? That would be real helpful. I guess you could parse it from the text name if it is a datasource bound control (say a textbox for example)...but that is kind of janky codewise.

Answer 6

public static int GetLengthLimit(Model.RIVFeedsEntities ent, string table, string field)
{
    int maxLength = 0;   // default value = we can't determine the length

    // Connect to the meta data...
    MetadataWorkspace mw = ent.MetadataWorkspace;

    // Force a read of the model (just in case)...
    // http://thedatafarm.com/blog/data-access/quick-trick-for-forcing-metadataworkspace-itemcollections-to-load/
    var q = ent.Clients;
    string n = q.ToTraceString();

    // Get the items (tables, views, etc)...
    var items = mw.GetItems<EntityType>(DataSpace.SSpace);
    foreach (var i in items)
    {
        if (i.Name.Equals(table, StringComparison.CurrentCultureIgnoreCase))
        {
            // wrapped in try/catch for other data types that don't have a MaxLength...
            try
            {
                string val = i.Properties[field].TypeUsage.Facets["MaxLength"].Value.ToString();
                int.TryParse(val, out maxLength);
            }
            catch
            {
                maxLength = 0;
            }
            break;
        }
    }

    return maxLength;
}