How to get the maximum length of a string from an EDMX model in code?

惠威订单导入，判断字段哪个超长？

How to get the maximum length of a string from an EDMX model in code?

Ask Question

 

up vote3down votefavorite

4

I've created an EDMX object from a database I'm programming against.

I need to get input from a user and save it to a row in the database table. The problem is that I need to limit the length of input strings to the width of the corresponding VARCHAR column in the database.

When I browse the model, I can clearly see in the properties window that the model knows the max length of the string, but I don't know how to access this data in code.

If I want to write something like this:

Entities entities = new Entities();
myTable = entities.myTable.First();
if (userInput.length > myTable.columnA.MaxLength)
{
    // tell the user that the input is too long.
}
else
{
    myTable.columnA = userInput;
}

How do I write it?

Update: I would like to point out that the IObjectContextAdapater mentioned in the answers below is in the System.Data.Entity.Infrastructure namespace.

entity-framework edmx

shareeditflag

edited Nov 19 '14 at 22:31

asked Nov 19 '14 at 18:57 Daniel Allen Langdon 13.3k40139256

I'm surprised by the low number of views on this question. Thanks very much for the helpful answers; I might have never figured it out on my own. – Daniel Allen Langdon Nov 20 '14 at 20:35

add a comment

2 Answers

activeoldestvotes

up vote5down voteaccepted

Here are two methods by which you can read the meta data:

int? GetMaxLength(DbContext context, string tableName, string propertyName)
{
    var oc = ((IObjectContextAdapter)context).ObjectContext;

    return oc.MetadataWorkspace.GetItems(DataSpace.CSpace).OfType<EntityType>()
             .Where(et => et.Name == tableName)
             .SelectMany(et => et.Properties.Where(p => p.Name == propertyName))
             .Select (p => p.MaxLength)
             .FirstOrDefault();
}

int? GetMaxLength<T>(DbContext context, Expression<Func<T, object>> property)
{
    var memberExpression = (MemberExpression)property.Body;
    string propertyName = memberExpression.Member.Name;
    return GetMaxLength(context, typeof(T).Name, propertyName);
}

So you can either enter the table name and property name, or an expression that specifies the property you're interested in.

Another approach could be to create a MetaData class and use the MaxLength attribute.

shareeditflag

answered Nov 19 '14 at 19:29 Gert Arnold 68.6k15111158

I'm confused. I thought that the EDMX entities is an ObjectContext, which has nothing to do with DbContext. – Daniel Allen Langdon Nov 19 '14 at 22:19

1

Not necessarily. You'd have to check. If it is an ObjectContext you don't need this ((IObjectContextAdapter)context).ObjectContext part. – Gert Arnold Nov 19 '14 at 22:21

I did check and I see that it's a DbContext. To be honest, I have heard of DbContext, but I haven't read up on all the new features of the new Entity Framework versions, so this one got past me. Wow, there is so much keeping up to do in the .net world! – Daniel Allen Langdon Nov 19 '14 at 22:24

The function GetItems returns an enumeration of 393 items. However, the OfType<myTable> function, where myTable is a model of a SQL table, returns an empty enumeration. – Daniel Allen Langdon Nov 19 '14 at 22:56

2

You should leave EntityType there, it's an EF class. – Gert Arnold Nov 19 '14 at 23:05

show 1 more comment

 

up vote1down vote

It's not very pretty; reading edmx properties at runtime is not something Microsoft exposed easily or documented well (or in some cases, at all). context is your DBContext. var objectContext = ((IObjectContextAdapter)context).ObjectContext; var entityType = objectContext.MetadataWorkspace.GetItems<EntityType>(DataSpace.CSpace).Where(e => e.Name == "your entity name").First(); var facets = entityType.Properties["your property name"].TypeUsage.Facets; facets will look something like this, so you'll need to look for the MaxLength Name(may not exist, depending on the underlying field type) and get the Value: Count = 5 [0]: Nullable=false [1]: DefaultValue=null [2]: MaxLength=250 [3]: Unicode=false [4]: FixedLength=false

---

Home

Applications

 Robyn Cerda  April 18, 2012  Applications  No Comments

inShare

 

 Retrieving Entity Framework Entity Property MaxLength Value from EDMX

I have an auto-generated entity framework Entity Data Model Designer File (edmx).  And while I am using Data Annotations for validation purposes, there are times when retrieving the maxlength of an entity property programmatically would be very useful. 

In some cases, I may want to simply truncate the value, rather than throw an error if the maxlength of a property is exceeded.  Or I may want to surface the maxlength property to the client for client-side validation purposes.

It turns out to be a simple query, but it took me a while to find it.  You will need to reference the following libraries:

using System.Data.Objects.DataClasses; 
using System.Data.Metadata.Edm;

This is the method to retrieve the MaxLength property.  Please note that you may get an exception if the property does not have a MaxLength property specified.

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28

public static int? GetMaxLength(this EntityObject entityObject, string entityProperty) { CATDBEntities _context = new CATDBEntities(); int? result = null; using (_context) {     var q = from meta in _context.MetadataWorkspace.GetItems(DataSpace.CSpace)                         .Where(m => m.BuiltInTypeKind == BuiltInTypeKind.EntityType)             from p in (meta as EntityType).Properties                 .Where(p => p.DeclaringType.Name == entityObject.GetType().Name                     && p.Name == entityProperty                     && p.TypeUsage.EdmType.Name == "String")             select p;       var queryResult = from meta in _context.MetadataWorkspace.GetItems(DataSpace.CSpace)                         .Where(m => m.BuiltInTypeKind == BuiltInTypeKind.EntityType)                         from p in (meta as EntityType).Properties                             .Where(p => p.DeclaringType.Name == entityObject.GetType().Name                                 && p.Name == entityProperty                                 && p.TypeUsage.EdmType.Name == "String")                         select p.TypeUsage.Facets["MaxLength"].Value;     if (queryResult.Count() > 0)     {         result = Convert.ToInt32(queryResult.First());     } } return result; }

　　

 

To call this method, simply instantiate the entity object and pass in the proper parameters:

project _project = new project(); 
            int? maxLength = DataAnnotation.GetMaxLength(_project, "project_name");

Tags:.NET, Entity Framework, MVC

About The Author

Robyn Cerda

Robyn is a senior lead developer at Catapult Systems and blogs about .NET development (MVC, AJAX, JSON, WCF), XRM, SQL Server (Transact SQL), BI Data Warehousing (SSIS, SSAS, ETL), SharePoint, User Experience, and Responsive Design.

---

 

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.Linq;
using System.Data.SqlClient;
using System.Data.Objects;
using System.Data.Metadata.Edm;

namespace CustomerPlatform.DataAccess
{
 public static class DataContextExtensions
 {

//Prepare global exception handler and log truncation details:

//protected void Application_Error(object sender, EventArgs e)
//{
//    Exception ex = Server.GetLastError();
//    string message = ex.Message;
//    //TODO - log to file
//}
//Finally use the code:

//Datamodel.SubmitChangesWithDetailException();

  public static void SubmitChangesWithDetailException(this DataContext dataContext)
  {
   //http://stackoverflow.com/questions/3666954/string-or-binary-data-would-be-truncated-linq-exception-cant-find-which-fiel
   try
   {
    //this can failed on data truncation
    dataContext.SubmitChanges();
   }
   catch (SqlException sqlException) //when (sqlException.Message == "String or binary data would be truncated.")
   {

    if (sqlException.Message == "String or binary data would be truncated.") //only for EN windows - if you are running different window language, invoke the sqlException.getMessage on thread with EN culture
     throw new SqlTruncationExceptionWithDetails(sqlException, dataContext);
    else
     throw;
   }
  }

  public static int SubmitChangesWithDetailException(this ObjectContext dataContext)
  {
   try
   {
    return dataContext.SaveChanges();
   }
   catch (System.Data.UpdateException updateException)
   {
    if (updateException.Message.StartsWith("String or binary data would be truncated.") || 
    updateException.InnerException.Message.StartsWith("将截断字符串或二进制数据")) 
     throw new SqlTruncationExceptionWithDetails(updateException, dataContext);
    else
     throw;
   }
  }
 }

 public class SqlTruncationExceptionWithDetails : ArgumentOutOfRangeException //指定的参数不在参数范围
 {
  public SqlTruncationExceptionWithDetails(System.Data.SqlClient.SqlException inner, DataContext context)
   : base(inner.Message + " " + GetSqlTruncationExceptionWithDetailsString(context))
  {
 
  }

  public SqlTruncationExceptionWithDetails(System.Data.UpdateException inner, ObjectContext context)
   : base(inner.Message + " " + GetSqlTruncationExceptionWithDetailsString(context))
  {
  }

  /// <summary>
  /// PArt of code from following link
  /// http://stackoverflow.com/questions/3666954/string-or-binary-data-would-be-truncated-linq-exception-cant-find-which-fiel
  /// </summary>
  /// <param name="context"></param>
  /// <returns></returns>
  static string GetSqlTruncationExceptionWithDetailsString(DataContext context)
  {
   StringBuilder sb = new StringBuilder();

   foreach (object update in context.GetChangeSet().Updates)
   {
    FindLongStrings(update, sb);
   }

   foreach (object insert in context.GetChangeSet().Inserts)
   {
    FindLongStrings(insert, sb);
   }
   return sb.ToString();
  }
  
  static string GetSqlTruncationExceptionWithDetailsString(System.Data.Objects.ObjectContext context)
  {
   StringBuilder sb = new StringBuilder();

   foreach (ObjectStateEntry update in context.ObjectStateManager.GetObjectStateEntries(System.Data.EntityState.Modified))
   {
    FindLongStrings(update, sb, context);
   }

   foreach (ObjectStateEntry insert in context.ObjectStateManager.GetObjectStateEntries(System.Data.EntityState.Added))
   {
    FindLongStrings(insert, sb, context);
   }
   return sb.ToString();
  }

  public static void FindLongStrings(object testObject, StringBuilder sb)
  {
   foreach (var propInfo in testObject.GetType().GetProperties())
   {
    foreach (System.Data.Linq.Mapping.ColumnAttribute attribute in 
     propInfo.GetCustomAttributes(typeof(System.Data.Linq.Mapping.ColumnAttribute), true))
    {
     if (attribute.DbType.ToLower().Contains("varchar"))
     {
      string dbType = attribute.DbType.ToLower();
      int numberStartIndex = dbType.IndexOf("varchar(") + 8;
      int numberEndIndex = dbType.IndexOf(")", numberStartIndex);
      string lengthString = dbType.Substring(numberStartIndex, (numberEndIndex - numberStartIndex));
      int maxLength = 0;
      int.TryParse(lengthString, out maxLength);

      string currentValue = (string)propInfo.GetValue(testObject, null);

      if (!string.IsNullOrEmpty(currentValue) 
      && maxLength != 0 
      && currentValue.Length > maxLength)
      {
       //string is too long
       sb.AppendLine(testObject.GetType().Name + "." + propInfo.Name + " " + currentValue + " Max: " + maxLength);
      }

     }
    }
   }
  }

  public static void FindLongStrings(ObjectStateEntry testObject, StringBuilder sb, System.Data.Objects.ObjectContext context)
  {
   foreach (var propInfo in testObject.Entity.GetType().GetProperties())
   {
    //propInfo .TypeUsage.EdmType.Name == "String")
    var maxLength = GetMaxLengthOfField(context, testObject, propInfo.Name);
    if(maxLength.HasValue)
    {
     string currentValue = testObject.CurrentValues[propInfo.Name].ToString();

     if (!string.IsNullOrEmpty(currentValue) 
     && maxLength != 0 
     && currentValue.Length > maxLength)
     {
      //string is too long
      sb.AppendLine(testObject.Entity.GetType().Name + "." + propInfo.Name + " " + currentValue + " MaxLength: " + maxLength);
     }
    }
   }
  }

  public static int? GetMaxLengthOfField(System.Data.Objects.ObjectContext context, ObjectStateEntry entityObject, string fieldName)
  {
   int? result = null;
   var _context = context;
   using (_context)
   {
    var queryResult = from meta in _context.MetadataWorkspace.GetItems(DataSpace.CSpace)
         .Where(m => m.BuiltInTypeKind == BuiltInTypeKind.EntityType)
          from p in (meta as EntityType).Properties
           .Where(p => p.DeclaringType.Name == entityObject.Entity.GetType().Name
            && p.Name == fieldName
            && p.TypeUsage.EdmType.Name == "String")
          select p.TypeUsage.Facets["MaxLength"].Value;
    if (queryResult.Count() > 0)
    {
     result = Convert.ToInt32(queryResult.First());
    }
   }
   return result; 
  }
 }
}