Entity Framework 4 - Trim Database Char(50) value for Name on legacy database

This should be simple, but I haven开发者_如何转开发't found a way yet...

I have a legacy database with name fields that are stored as CHAR(50). When this is bound to a TextBox with a Max Length of 50, you cannot insert.

How can I make the EF trim these values or at least map to RTrim(Column)?

I've tried using value converters, but the round trip causes issues with back spacing and spaces getting deleted between words.

Note that I only want to trim some fields, not all.

We are using SQL Server 2000 as the database. Soon to move to SQL 2008.

Thanks!

Entity framework is able to map only to table directly. You can also map to view or custom DB query but in such case your entity will became readonly unless you also map Insert, Delete and Update operations to stored procedures.

I think the problem you describes is related to ANSI PADDING behavior. It can be turned on but:

• It is not recommended. In future version of SQL server it will be considered as error.
• Must be configured before you create a column

You must handle trimming in the application. You can for example modify T4 template (if you use them) to trim string properties. Not sure how it works with WPF but you probably can inherit text box and override Text property to trim values.

Another way is handling ObjectMaterialized event on ObjectContext and manually trimming text properties but it can slow down your execution of your queries.

There's no way to do this with EF and SQL Server that I have found. I solved it with an extension method on IEnumerable<T> that calls TrimEnd() on each string property:

    public static IEnumerable<TEntity> Trim<TEntity>(this IEnumerable<TEntity> collection)
    {
        Type type = typeof(TEntity);

        IEnumerable<PropertyDescriptor> properties = TypeDescriptor.GetProperties(type).Cast<PropertyDescriptor>()
            .Where(p => p.PropertyType == typeof(string));

        foreach (TEntity entity in collection)
        {
            foreach (PropertyDescriptor property in properties)
            {
                string value = (string) property.GetValue(entity);

                if (!String.IsNullOrEmpty(value))
                {
                    value = value.TrimEnd();
                    property.SetValue(entity, value);
                }
            }
        }

        return collection;
    }

Just make sure you call it after EF has retrieved the entities from the database. For example, after ToList():

    public IEnumerable<Country> FetchCountries()
    {
        return _context.Set<Country>().ToList().Trim();
    }

Have a look at the available attributes for your Database Connection String. I had a similar issue with Sybase Advantage database and solved with it's TrimTrailingSpaces attribute. Your database may support something similar.

Data Source=\\serverx\volumex\path\db.add;User ID=user;Password=pass;ServerType=REMOTE;TrimTrailingSpaces=TRUE;

http://www.connectionstrings.com/