Auto=RTrim Strings in Entity Framework, ServiceStack OrmLite, PetaPoco, etc

Question

Edited for Clarity

I've been looking at ORMs for the last week, as well as trying to decide if I want to bother with them. At the end of the day, there seem to be about a dozen worthy contenders, of which most are fairly hard to tell apart. I eventually settled on the potential trio of EF, OrmLite and PetaPoco, all of which seem pretty good.

One feature I've been looking for is the ability to magically configure the code generator to automatically right trim all strings in the generated POCOs, without any changes to the DB. I have a database with literally thousands of records spread across hundreds of fields, and every single string field has a bunch of spaces at the end of it for legacy reasons. Those need to be stripped from the resulting POCOS/Entities to make the processing less ugly, but I can't make any changes to the DB (it's not mine), so I'm wondering if there is easy-easy way to do it.

With Entity Framework I looked a little bit at the process for Database First and Model First design, and those look like you could probably tweak the T4 template code to generate appropriate code on a case by case basis. This seems like it would be viable, but I don't want to reinvent the wheel if someone has already done it. I would just like to have the code that takes care of the problem.

For the other ORMs, I could probably pull them in the house, figure out how they work and plug-in some kind of logic that does the magic.

So does anybody have a suggestion for an ORM that has a configuration switch that can automatically right-trim all strings? It would make the database much easier to work with, hundred percent certain there is never any value in those extra spaces at the end.

See this answer: http://stackoverflow.com/a/20141289/150342. I'd look for a solution that fixes the database instead: http://stackoverflow.com/questions/507869/trim-all-database-fields — Colin, Apr 23 '14 at 10:22
That's a good suggestion, but I can't do it in this case. It's someone elses database ;( — Wade Hatler, Apr 23 '14 at 14:05
And you're not allowed to edit the data? Everything is read-only? Can you add read-only views and map to them instead? — Colin, Apr 23 '14 at 14:24
Yes, I deliberately have read-only access to the DB. It's a live system that gets written to by several different technologies (3 flavors of ADO, 2 ORMs and maybe more), and the legacy system writes new data with the space padding all the time. I can do it all by making Views in a shadow database, or by hand-rolling SQL for all data access, but was hoping to avoid that. — Wade Hatler, Apr 23 '14 at 18:20

score 1 · Answer 1 · answered Apr 24 '14 at 06:55

1

Thought this was a good feature so I've just added this to ServiceStack.OrmLite where you can now add a custom filter for strings, e.g:

OrmLiteConfig.StringFilter = s => s.TrimEnd();

public class Poco
{
    public string Name { get; set; }
}

using (var db = OpenDbConnection())
{
    db.DropAndCreateTable<Poco>();

    db.Insert(new Poco { Name = "Value with trailing   " });
    var row = db.Select<Poco>().First();

    Assert.That(row.Name, Is.EqualTo("Value with trailing"));
}

It will be in the next v4.0.19+ NuGet release of ServiceStack, which is now available on ServiceStack's MyGet Feed.

answered Apr 24 '14 at 06:55

mythz

141,670
29
246
390

1

Dang, That Was Fast ;)) I've been trying to decide on an ORM, bouncing back and forth between OrmLite and EF for the last week. I was leaning towards OrmLite anyway, but I think this is the killer feature that breaks the camel's back. Excellent work. I'll download it and try it at the first chance. – Wade Hatler Apr 24 '14 at 17:31
I don't see the SQL Server version in the MyGet feed. Any idea when that will be available? Thanks. – Wade Hatler May 04 '14 at 17:04
@WadeHatler all the packages are there, here's [ServiceStack.OrmLite.SqlServer](https://www.myget.org/feed/servicestack/package/ServiceStack.OrmLite.SqlServer) – mythz May 04 '14 at 17:26
Perfecto - Got it and it's working great. It seems to be on by default, which is fine with me. Thanks. – Wade Hatler May 08 '14 at 21:58
Oops, that was wrong. I tested with a DB field that was actually correct. Got a more typical one, and the switch works as advertised. – Wade Hatler May 13 '14 at 23:50

score 0 · Answer 2 · answered Apr 23 '14 at 14:42

With Entity Framework (and possibly PetaPoco which I don't know personally) you should be able to modify the T4 template and add read-only properties to your entities, returning the trimmed value of database-related property.

public string Name
{
    get { return this.DbName.TrimEnd(); }
}

But...

You have to find a way to do this for string properties only (I think one of the methods that are visible in the T4 template can be used for that, but I'm not sure).
Modifying T4 templates is something you may have to do again when updates are released.
You can't use the read-only properties directly in LINQ-to-entities because EF can't translate them into SQL. You'll alway have to use them after an AsEnumerable() call.

That's an approach I may try. I'm actually probably going to do CodeFirst, so I think I should be able to do something like that, and make a Setter as well to go the other way. I'll experiment with it. Thanks for the suggestion. — Wade Hatler, Apr 23 '14 at 18:22

Auto=RTrim Strings in Entity Framework, ServiceStack OrmLite, PetaPoco, etc

2 Answers2