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

.net sql linq linq-to-sql

10,784

Solution 1

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.

Solution 2

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.

Solution 3

Answered here:

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?

Solution 4

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;
}

View more solutions

10,784