none
LINQ to SQL question on trailing spaces RRS feed

  • Question

  • Greetings forum-goers,

    I've seen a few scattered threads on this issue, but none with any *real* resolution, so I will ask, yet again :

    Is there any possible way using LINQ to SQL to portably "massage" or manipulate string (or any) data as it is read from the database and placed into code-generated entity objects and mapped into CLR types? One possible application of this is to trim leading and trailing whitespace from (n)varchar(x) fields.

    So far, I've tried the following :

    * defining a custom type that wraps a string type and, in constructing said type, trims the string. This type is implicitly castable to the CLR string type. This, unfortunately, causes quite a few type inferences in my queries to fail and, LINQ to SQL does not support this as I cannot define a custom "Convert." or "IConverter" mapping from System.String to my custom string type.

    * Looked at implementing the "hook" OnLoaded() function generated by SQLMetal's code generator, but this is not feasable as "OnLoaded()" is not called for anonymous type creation.

    * Looking at implementing the On<PropertyName>Changed() function for each string type that I care about to overwrite the private property with ".Trim()" called on it.

    I've also toyed around with appending ".Trim()" to each of the code-generated Getter calls, which would cause a maintenance nightmare as we have ~200 autogenerated table entity definitions. Unfortunately, this seems to be the most portable way.

    I've already looked at all the relevant Microsoft documentation on LINQ to SQL's type-mapping, which had little-to-no information on this issue.

    Any comments/answers are appreciated.
    Monday, April 6, 2009 9:51 PM

Answers

  • Yep, that was the solution I described above that I'd already attempted, except that I pre-generated the projections rather than generating them on the fly. Looks like I forgot to mention that I called .Trim() as part of the re-projection. The issues that I found were :

    * since we're projecting into a new type, we have to copy *everything*, including any associations as we have quite a few queries that are dependent on it, which causes LINQ to SQL to generate sql to get *everything* from the database for the most basic IQueryable, and for which the subqueries aren't trimmed.

    * updates in this fashion are broken because we're projecting into a new object that LINQ to SQL isn't tracking.

    Really, even if it worked, it's still a gross kludge just because we can't hook into the provider IMHO.

    Thanks, though!

    If you want tracking and deferred loading of navigation properties enabled you can use my projection sample above to generate the query, and the datacontext's .Translate<T> method to project into employee entities.

    var trimmedEmployees
        = (from emp in dc.Employees
          select emp).TrimStrings(dc);
    
    var trimmedAndTrackedEmployees = dc.Translate<Employee>(dc.GetCommand(trimmedEmployees).ExecuteReader());
    
    
    


    Using Translate with modified queries is described in Damien Guard's recent blog article "LINQ to SQL tips and tricks #2":
    http://damieng.com/blog/2009/04/12/linq-to-sql-tips-and-tricks-2 - a shortcut to do the trim without generating new projection classes etc is of course to use Damien's technique to just modify the T-SQL itself.

    Note: you will also need to either turn off UpdateCheck for the fields that can be trimmed (or use timestamps for updatechecks) to avoid failed update checks when the db record contain untrimmed strings and the entity object contain trimmed strings.

    Note 2: loading associations using navigation properties together with this technique will not trim the strings.


    Kristofer - Huagati Systems Co., Ltd. - www.huagati.com
    Thursday, April 16, 2009 11:15 AM
    Answerer

All replies

  • One option would be to access the database via stored procedures or views that implement the trim server-side.

    If you want to modify the code-generation process check out L2ST4 which provides a user-editable template to replace the DBML > C#/VB.NET code generation process.

    [)amien
    Tuesday, April 7, 2009 7:45 AM
    Moderator
  • Adding to the database would be an even larger maintenance nightmare because of the swath of tables we use, unfortunately.

    Those templates are pretty nice, but unfortunately don't entirely address the issue. It seems that when we use LINQ to SQL for database access and construct anonymous types as a result (e.g. .Select(new { foo =bar })), those properties aren't called, so modifying the code generation is useless.

    Another solution I attempted was to code-gen an expression tree to map the entity to a new type that derived from the entity <edit>calling trim on the requisite properties in the process</edit>, since LINQ to SQL does not support creating a new instance of the entity type mid-query. I then used this expression tree as the initial .Select() for all LINQ to SQL IQueryable's in my system. For anonymous types, this worked pretty well as the generated SQL strings contained inline trims.


    However, this failed because of :

    * retrieving a row and updating from it would silently fail as LINQ to SQL would detect no changes, unless I attached the row after retrieval, which is somewhat inefficent.

    * the generated expression tree also required that the entity references were mapped, otherwise LINQ to SQL queries that used these references would fail. LINQ to SQL would, namely enough, translate this mapping as an indication to retrieve all the rows from the referenced entity, slowing down common queries considerably.

    So it seems that there is no way to have LINQ to SQL automatically trim all strings unless queries are done a certain way.

    I'm curious as to the reason why the LINQ to SQL designers chose to not include a way to have users supply their own base type conversions in the TDS->CLR translation scheme, or made it difficult to specialize the provided SQL Server client providers.

    Thanks for the hints.

    Thursday, April 9, 2009 8:41 PM
  • This is maybe a bit of a hack and there may be something I haven't taken in consideration, but how about applying a projection that calls the Trim method on all string members?

    The following example uses System.Data.Linq.Mapping to get the members so it will not work with queries returning anonymous types, but you could tweak it to extract the members etc from the query's expression tree instead of from mapping...

    The end result is that you can do like this:

    var employees
        = (from emp in dc.Employees
           select emp).TrimStrings(dc);
    
    foreach (Employee emp in employees)
    {
        //do stuff...
    }
    
    
    


    ...and the generated query will call do a Ltrim(Rtrim(column)) for all columns mapped to string members:

    SELECT [t0].[EmployeeID], LTRIM(RTRIM([t0].[NationalIDNumber])) AS [NationalIDNumber], [t0].[ContactID], LTRIM(RTRIM([t0].[LoginID])) AS [LoginID], [t0].[ManagerID], LTRIM(RTRIM([t0].[Title])) AS [Title], [t0].[BirthDate], [t0].[MaritalStatus], [t0].[Gender], [t0].[HireDate], [t0].[SalariedFlag], [t0].[VacationHours], [t0].[SickLeaveHours], [t0].[CurrentFlag], [t0].[rowguid], [t0].[ModifiedDate]
    FROM [HumanResources].[Employee] AS [t0]
    
    
    ...while still allowing you to work with the DC-defined entity type in the end...


    Implementation

    IQueryableExtensions:

    /// <summary>
    /// various extensions to IQueryable and IQueryable&lt;T&gt;
    /// </summary>
    public static class IQueryableExtensions
    {
        /// <summary>
        /// Applies a projection of a known entity type into an inherited instance of the same, applying the .Trim method to all string properties in the process
        /// </summary>
        /// <typeparam name="T">an entity type defined in the datacontext passed in as a parameter</typeparam>
        /// <param name="baseQuery">query to apply the TrimStrings projection to</param>
        /// <param name="dc">data context where the entity type is defined</param>
        /// <returns>a new IQueryable applying a projection of the original query into a class inheriting from the base query's entity class</returns>
        public static IQueryable TrimStrings<T>(this IQueryable<T> baseQuery, DataContext dc)
        {
            //get hold of the meta table
            MetaTable table = dc.Mapping.GetTable(typeof(T));
            if (table == null)
            {
                throw new NotDataContextMember();
            }
    
            //get member properties
            List<MetaDataMember> memberProperties =
                (
                    from prop in table.RowType.DataMembers
                    where prop.IsAssociation == false && prop.IsPersistent == true
                    select prop
                ).ToList();
             
            //bind string members to the trim method, everything else to the property get...
            ParameterExpression[] baseTypeParams = new ParameterExpression[] { Expression.Parameter(typeof(T), "") };
            List<MemberBinding> memberBindings =
                (
                    from mem in memberProperties
                    select (mem.Type == typeof(string))
                        ? (MemberBinding)Expression.Bind(typeof(T).GetProperty(mem.Name), Expression.Call(Expression.Property(baseTypeParams[0], typeof(T).GetProperty(mem.Name)), typeof(string).GetMethod("Trim", new Type[] { })))
                        : (MemberBinding)Expression.Bind(typeof(T).GetProperty(mem.Name), Expression.Property(baseTypeParams[0], typeof(T).GetProperty(mem.Name)))
                ).ToList();
    
            //generate a lambda for creating a new instance of the inherited type, mapped to the base type for the table
            LambdaExpression lambda
                = Expression.Lambda(
                    Expression.MemberInit(
                        Expression.New(InheritedType.InheritEntityType(typeof(T))),
                        memberBindings),
                    baseTypeParams
                  );
    
            //create a new query returning the new projection instead of the base type
            return 
                baseQuery.Provider.CreateQuery(
                    Expression.Call(typeof(Queryable),
                            "Select",
                            new Type[] { baseQuery.ElementType, lambda.Body.Type },
                            baseQuery.Expression,
                            Expression.Quote(lambda)
                    )
                );
    } } /// <summary> /// exception thrown if IQueryableExtensions.TrimStrings is called on a query that don't use a base entity type... /// </summary> public class NotDataContextMember : NotSupportedException { public NotDataContextMember() : base("The base query does not select a member from the passed-in datacontext.") { } }


    ..and a class for generating types inheriting from entity types:


    /// <summary>
    /// class that enables runtime inheritance from entity classes
    /// </summary>
    internal static class InheritedType
    {
        private static Dictionary<string, AssemblyBuilder> _assemblyBuilders = new Dictionary<string,AssemblyBuilder>();
        private static Dictionary<string, ModuleBuilder> _moduleBuilders = new Dictionary<string,ModuleBuilder>();
        private static Dictionary<string, Type> _inheritedTypes = new Dictionary<string,Type>();
    
        internal static Type InheritEntityType(Type entityType)
        {
            //determine a [new] assembly name...
            string assemblyName = "InheritedEntityClasses_" + entityType.Assembly.GetName().Name;
    
            //get hold of existing, or create a new assembly builder...
            AssemblyBuilder assembly = null;
            if (_assemblyBuilders.ContainsKey(assemblyName))
            {
                assembly = _assemblyBuilders[assemblyName];
            }
            else
            {
                assembly = AppDomain.CurrentDomain.DefineDynamicAssembly(
                    new AssemblyName(assemblyName), AssemblyBuilderAccess.Run);
                _assemblyBuilders.Add(assemblyName, assembly);
            }
    
            //determine module name
            string moduleName = entityType.Namespace;
            string qualifiedModuleName = assemblyName + "." + moduleName;
    
            //get hold of existing, or create new module builder
            ModuleBuilder moduleBuilder = null;
            if (_moduleBuilders.ContainsKey(qualifiedModuleName))
            {
                moduleBuilder = _moduleBuilders[qualifiedModuleName];
            }
            else
            {
                moduleBuilder = assembly.DefineDynamicModule(entityType.Namespace);
                _moduleBuilders.Add(qualifiedModuleName, moduleBuilder);
            }
    
            //determine type name
            string typeName = "Inherited_" + entityType.Name;
            string qualifiedTypeName = qualifiedModuleName + "." + typeName;
    
            //get hold of, or create new type that inherit from the entity type passed in...
            Type inheritedType = null;
            if (_inheritedTypes.ContainsKey(qualifiedTypeName))
            {
                inheritedType = _inheritedTypes[qualifiedTypeName];
            }
            else
            {
                //define a new type that inherit from the given entity type
                TypeBuilder typeBuilder = moduleBuilder.DefineType("Inherited_" + entityType.Name, TypeAttributes.Class | TypeAttributes.Public, entityType);
                inheritedType = typeBuilder.CreateType();
                _inheritedTypes.Add(qualifiedTypeName, inheritedType);
            }
    
            return inheritedType;
        }
    }
    
    
    
    

    Kristofer - Huagati Systems Co., Ltd. - www.huagati.com
    • Edited by KristoferAEditor Friday, April 10, 2009 7:06 AM removed excess whitespace, corrected some confusing code comments
    Friday, April 10, 2009 7:00 AM
    Answerer
  • Yep, that was the solution I described above that I'd already attempted, except that I pre-generated the projections rather than generating them on the fly. Looks like I forgot to mention that I called .Trim() as part of the re-projection. The issues that I found were :

    * since we're projecting into a new type, we have to copy *everything*, including any associations as we have quite a few queries that are dependent on it, which causes LINQ to SQL to generate sql to get *everything* from the database for the most basic IQueryable, and for which the subqueries aren't trimmed.

    * updates in this fashion are broken because we're projecting into a new object that LINQ to SQL isn't tracking.

    Really, even if it worked, it's still a gross kludge just because we can't hook into the provider IMHO.

    Thanks, though!
    Friday, April 10, 2009 1:27 PM
  • Yep, that was the solution I described above that I'd already attempted, except that I pre-generated the projections rather than generating them on the fly. Looks like I forgot to mention that I called .Trim() as part of the re-projection. The issues that I found were :

    * since we're projecting into a new type, we have to copy *everything*, including any associations as we have quite a few queries that are dependent on it, which causes LINQ to SQL to generate sql to get *everything* from the database for the most basic IQueryable, and for which the subqueries aren't trimmed.

    * updates in this fashion are broken because we're projecting into a new object that LINQ to SQL isn't tracking.

    Really, even if it worked, it's still a gross kludge just because we can't hook into the provider IMHO.

    Thanks, though!

    If you want tracking and deferred loading of navigation properties enabled you can use my projection sample above to generate the query, and the datacontext's .Translate<T> method to project into employee entities.

    var trimmedEmployees
        = (from emp in dc.Employees
          select emp).TrimStrings(dc);
    
    var trimmedAndTrackedEmployees = dc.Translate<Employee>(dc.GetCommand(trimmedEmployees).ExecuteReader());
    
    
    


    Using Translate with modified queries is described in Damien Guard's recent blog article "LINQ to SQL tips and tricks #2":
    http://damieng.com/blog/2009/04/12/linq-to-sql-tips-and-tricks-2 - a shortcut to do the trim without generating new projection classes etc is of course to use Damien's technique to just modify the T-SQL itself.

    Note: you will also need to either turn off UpdateCheck for the fields that can be trimmed (or use timestamps for updatechecks) to avoid failed update checks when the db record contain untrimmed strings and the entity object contain trimmed strings.

    Note 2: loading associations using navigation properties together with this technique will not trim the strings.


    Kristofer - Huagati Systems Co., Ltd. - www.huagati.com
    Thursday, April 16, 2009 11:15 AM
    Answerer
  • [snip]
    Using Translate with modified queries is described in Damien Guard's recent blog article "LINQ to SQL tips and tricks #2":
    http://damieng.com/blog/2009/04/12/linq-to-sql-tips-and-tricks-2 - a shortcut to do the trim without generating new projection classes etc is of course to use Damien's technique to just modify the T-SQL itself.
    [snip]
    This is interesting, thanks for the link. I hadn't realized the possibility of using .Translate() in that fashion before.

    It's still a kludge, but it's possibly workable. I just fear for any space/runtime bloat.

    Thanks again!
    Tuesday, April 21, 2009 4:10 PM
  • Perhaps very few people are actually using these technologies in real world applications? When I found that no one had a real answer to this and why it isn't baked into the various products I was shocked. In 14 years of programming I've never created or worked on an application that cared about trailing spaces. Anyway I resolved this by creating my own custom template (.tt) for my entity model. I'm using Entity Framework 4 & Visual Studio 2010. Basically you double click your .edmx file and right click anywhere in the designer on a blank spot and choose "Add code generation item" from the pop-up. You'll get a "MyModel.tt" added to your project. You then modify the template to add trimming logic on the setter code shown below. This starts on line 614 at the time of this writing. There is no maintenence overhead once this is done. The template is a permanent part of your project and runs every time the code generator thinks it needs to refresh the entities from the database.

     <#+

            PushIndent(CodeRegion.GetIndent(1));

                }

                    if (ef.ClrType(primitiveProperty.TypeUsage) == typeof(string))

                    {

    #>

                // always trim strings

                if(string.IsNullOrEmpty(value) == false)

                    value = value.Trim();

    <#+

                    }

    #>

                <#=ChangingMethodName(primitiveProperty)#>(value);

                ReportPropertyChanging("<#=primitiveProperty.Name#>");

                <#=code.FieldName(primitiveProperty)#> = StructuralObject.SetValidValue(value<#=OptionalNullableParameterForSetValidValue(primitiveProperty, code)#>);

                ReportPropertyChanged("<#=primitiveProperty.Name#>");

                <#=ChangedMethodName(primitiveProperty)#>();

    <#+

    Tuesday, July 20, 2010 2:49 AM
  • i am trying Castle Dynamic Proxy to intercept the call to class member generated by linq to sql:

    http://www.cnblogs.com/RicCC/archive/2010/03/15/castle-dynamic-proxy.html


    update: it does not seem to work, since the class member from LINQ to SQL was not virtual. http://www.itgo.me/a/x2541739064314325745/dynamicproxy-how-to-intercept-non-virtual-methods
    • Edited by GuYuming Thursday, March 8, 2018 6:47 AM
    Thursday, March 8, 2018 3:47 AM