1

In the database we have to work with (which is DB2) there are fields stored as character but are in fact other objects, the most common being custom ways the underlying application stores dates and times. For example: 

[Table]
public class ExampleTable {
    // This is stored in the DB as a char in the format: 2016-01-11-11.39.53.492000
    [Column(Name = "WTIMESTAMP")] public string WriteTimestamp { get; set; }
}

Would there be a way to tell linq2db a conversion method to use when converting to / from the database, that would also allow us to access those properties as an object we want (for instance, a C# DateTime object), but get saved back in the proper format?

One thing I thought of was something like:

[Table]
public class ExampleTable {

    public DateTime WriteTimestamp { get; set; }

    // This is stored in the DB as a char in the format: 2016-01-11-11.39.53.492000
    [Column(Name = "WTIMESTAMP")] public string WriteTimestampRaw 
    { 
        get {
            return ConvertWriteTimestampToDb2Format(WriteTimestamp);
        } 
        set {
            WriteTimestamp = ConvertWriteTimestampToDateTime(value);    
        }
    }
}

And then we access WriteTimestamp, but the linq2db uses WriteTimestampRaw in the queries.

But, I'm not sure if that's the best or only option. Thanks in advance.

Thomas F.
  • 766
  • 10
  • 17
  • Is the actual data type in DB2 `(VAR)CHAR` ? If it's a `TIMESTAMP` type, .net should be able to convert it if you use the DateTime data type. – bhamby Jan 11 '16 at 19:27
  • @bhamby Unfortunately no, it's a character type with CCID of 37. There are several others with things like this, too. For instance, in another spot they store just a date in Julian date format (YYYYDDD where DDD is day of the year), which is actually stored in decimal format. – Thomas F. Jan 11 '16 at 19:30
  • inq2db 3.0.0-rc1 scheduled for release this week adds per-column conversion configuration support using value converters https://github.com/linq2db/linq2db/wiki/Release-Notes-3.0.0#value-converters-2273 –  Jun 17 '20 at 10:22

2 Answers2

2

Well... just noticed that you said linq2db and not Entity Framework after I posted my answer. Maybe it will still give you some ideas, though.

What I have done before with Entity Framework (although not specifically with DB2, but I think it should still work), is to use the code provided in this answer to allow private properties to be mapped to a database column. Then, I have something similar to your code, except the getters and setters are reversed:

[Table("ExampleTable")]
public class ExampleTable
{
    [NotMapped]
    public DateTime WriteTimestamp
    {
        get
        {
            var db2Tstamp = DB2TimeStamp.Parse(WriteTimestampRaw);
            return db2Tstamp.Value;
        }
        set
        {
            var db2Tstamp = new DB2TimeStamp(value);
            WriteTimestampRaw = db2Tstamp.ToString();
        }
    }

    // This is stored in the DB as a char in the format: 2016-01-11-11.39.53.492000
    [Column("WTIMESTAMP")]
    private string WriteTimestampRaw { get; set; }
}

I used the DB2TimeStamp class to handle the conversion between string and DateTime values, but you could probably do it however you're comfortable.

Community
  • 1
  • 1
bhamby
  • 15,112
  • 1
  • 45
  • 66
  • I was figuring something like that would work. The main reason I reversed the getters/setters in mine was due to the fact that I figured if the DB sets the private `WriteTimestampRaw`, then that sets the public WriteTimestamp, it means it's only converting that once coming from the DB, and then once again going back. Where as reversed like you have it would (iirc) convert it each call to WriteTimestamp. The more I look at it, the more I'm thinking this is the way I'm going to go. Thanks! (Edit: Also, the concept should still carry from EF to this, as well.) – Thomas F. Jan 11 '16 at 20:06
  • @ThomasF. after thinking about it for a bit, I think I like your method more than my own. It seems likely to me that one would access the properties in your code more than writing to/from the database. I'll have to try some tests to see the differences! – bhamby Jan 12 '16 at 04:06
  • It would be interesting to know the differences. While I won't say this app is going to get hammered, it is going to have some traffic. I don't know that it would be enough for the difference between your implementation and mine to have much effect, but still. The only thing I don't like currently is the fact that linq2db can't do anything with private properties :/ Anyway, would be interested to hear what you come up with! – Thomas F. Jan 12 '16 at 13:11
0

You can use MappingSchema.SetConverter method to set conversion between specific types on client side. Or MappingSchema.SetConverterExpression to create converters as a part of query tree.

reptile
  • 175
  • 1
  • 9
  • Thanks, I'll have a look at this when I get into the office tomorrow. Do you happen to have a small code block demoing it, or docs I can reference? Edit: Basically I'm not sure where it's used, is all. – Thomas F. Jan 15 '16 at 03:14
  • https://github.com/linq2db/linq2db/blob/master/Tests/Linq/Mapping/MappingSchemaTest.cs – reptile Jan 15 '16 at 03:30
  • thanks! Didn't think to look in the tests. I'll have a look through that tomorrow – Thomas F. Jan 15 '16 at 03:34