How to trim values using Linq to Sql?

Question

In the database, I have a table called Contact. The first name and other such string fields are designed to use a Char datatype (not my database design). My object Contact maps to a string type in the properties. If I wanted to do a simple test retrieving a Contact object by id, I would do something like this:

Contact contact = db.Contacts.Single(c => c.Id == myId);
Contact test = new Contact();
test.FirstName = "Martin";

Assert.AreEqual(test.FirstName, contact.FirstName);

The contact.FirstName value is "Martin " because of the char type. Where can I intercept the FirstName property when it is being loaded? OnFirstNameChanging(string value) does not get called on the initial load (contact), but does on the test object.

You can do this `contact.FirstName.Trim()`. I saw some old code of my own that I need to update now. Here is the reference on Canonical String LINQ to SQL functions: https://learn.microsoft.com/en-us/dotnet/framework/data/adonet/ef/language-reference/string-canonical-functions — threadster, Mar 22 '18 at 14:12

score 8 · Accepted Answer · answered Mar 16 '09 at 20:50

8

Maybe you could put it in the OnLoaded() partial method? Note: I've never used this, but I assume it would look like this:

public partial class Contact
{
    partial void OnLoaded()
    {
        FirstName = FirstName.Trim();
    }
}

answered Mar 16 '09 at 20:50

Chris Shaffer

32,199
5
49
61

This works. Its is fairly easy to do for each field because I am not showing that many fields for display. Thanks. – Marsharks Mar 16 '09 at 21:26
This is going to generate a property changed event -- which could trigger other actions -- and could result in unnecessary updates to the database since you are changing the value of the property without making any semantic changes to the content. – tvanfosson Mar 19 '09 at 18:47
3

The point of the question was to find the place to do such logic (in this case OnLoaded()); The method itself could just as easily consist of "_FirstName = _FirstName.Trim();" to avoid the propery changed event firing. – Chris Shaffer Mar 19 '09 at 18:52
That is probably a good idea for what I need, since I cannot change the database schema. I really only use this class as a data object to hold the values of a database record. It would be nice if I could have intercepted a property loading event, but I can't. – Marsharks Mar 20 '09 at 20:06
3

Be aware that you could use the OnLoad() method only if the corresponding table to this class has a primary key. – Flo Oct 16 '09 at 08:55

score 6 · Answer 2 · answered Mar 16 '09 at 20:18

6

If you can't change the schema, you might want to make the designer generated accessor private/protected and create a public accessor to front-end the property in a partial class implementation. You can then Trim the value in the get accessor.

public partial class Contact
{

    public string RealFirstName
    {
       get { return this.FirstName.Trim(); }
       set { this.FirstName = value; }
    }

    ...
}

answered Mar 16 '09 at 20:18

tvanfosson

524,688
99
697
795

1

This is a good solution, because not only are the fields a char type, they also are called First_Name. So I could fix all the fields as well, without changing it in my Object designer. – Marsharks Mar 16 '09 at 21:28
The problem is when I create a query and try to use something like FullName = c.FirstName + ' ' + c.LastName. I was getting the Could not translate expression and could not treat it as a local expression Message. – Marsharks Mar 19 '09 at 18:24
Once you've done your filtering you could use ToList() to generate the actual query then do your select on the resulting list. Since they are objects at that point the construction of FullName should work. – tvanfosson Mar 19 '09 at 18:50
I was using a light weight object, not returning a list. Here is the line that was blowing up: ContactName = act.PhoneContact.FirstName + " " + act.PhoneContact.LastName – Marsharks Mar 20 '09 at 20:09

score 1 · Answer 3 · answered Mar 16 '09 at 20:16

1

Contact contact = db.Contacts.Single(c => c.Id.Trim() == myId);

And check that the LINQ provider translates that into the appropriate SQL.

answered Mar 16 '09 at 20:16

Richard

106,783
21
203
265

I don't think the problem was with the Id field. – tvanfosson Mar 16 '09 at 20:19
1

@tvanfosson - I think the op intended it for examples sake only, not be taken literally. – Ralph Willgoss Dec 08 '11 at 23:27
1

@threadster You've linked to Entity SQL as used by Entity Framework which uses LINQ to Entities. LINQ to SQL is used by the older pre-EF ORM. – Richard Mar 22 '18 at 14:13

score 1 · Answer 4 · answered Mar 16 '09 at 20:39

It depends on the control you have over the schema and the code.

If the values are being set up by calling a constructor with all parameters, do the trims, etc as they are assigned to the member variables.

If they are being assigned to the properties (probably from the example), change the SET accessor so that it does the trim there.

You do have potential issues if you actually WANT leading or trailing spaces at some point though.

If you can't modify the base class code, try using partial classes, or inheriting from the class and overriding the properties there.

If you can't do that, then my last suggestion would be to write a factory class of some sort that you pass a created object to and it cleans it up per the rules you want.

score 0 · Answer 5 · answered Mar 16 '09 at 20:27

0

You can run a ForEach on your list of contacts before trying to search for Martin.

var contacts = db.Contacts.ForEach(c => c.FirstName = c.FirstName.Trim());
Contact contact = contacts.Single(c => c.Id == myId);
contact.FirstName // = "Martin"

But this way is not very easy to maintain if it has to be done for multiple fields.

answered Mar 16 '09 at 20:27

Samuel

37,778
11
85
87

1

What happens if he has 100,000 contacts? – Ralph Willgoss Dec 08 '11 at 23:29

How to trim values using Linq to Sql?

5 Answers5

Linked