Injecting code into linq

[Andy]

Suppose I have the following class which maps to a table:

public class People {
[Column( IsDbGenerated = true, IsPrimaryKey = true )]
public int PersonId { get; set; }
[Column]
public string FirstName { get; set; }
[Column]
public string LastName { get; set; }
[Column]
public DateTime? BirthDate { get; set; }
}

In the database all DateTimes MUST be stored in UTC. When the client
of the data layer gets the DateTime, it MUST be in LocalTime. To keep
easily forgotten code out of the business layer, I'd like to have a
way to specify when the BirthDate field is sent to the db,
ToUniversalTime is automatically called. Likewise, when getting the
BirthDate, ToLocalTime should be called.

Is there some where to hook into the Linq to Sql execution that would
allow me to do this translation?

Thanks
Andy

 

[Marc Gravell]

How about leave the [Column] one as the UTC date, and add a shim/
facade property? No idea if it'll work, but worth a try... note that
if you are using the designer you can use partial classes to help:

public partial class People
{
// ...
[Column(Name = "BirthDate")]
public DateTime? BirthDateUtc { get; set; }
}

partial class People
{
public DateTime? BirthDateLocal
{
get
{
DateTime? when = BirthDateUtc;
if (when.HasValue)
{
when = when.Value.ToLocalTime();
}
return when;
}
set
{
if (value.HasValue)
{
value = value.Value.ToUniversalTime();
}
BirthDateUtc = value;
}
}
}

 

[Andrus]

Marc,

How about leave the [Column] one as the UTC date, and add a shim/
facade property? No idea if it'll work, but worth a try... note that
if you are using the designer you can use partial classes to help:

Andy wrote:

"In the database all DateTimes MUST be stored in UTC."

There may be hundreds of DataTime properties in database.
Re-writing code of all datetime properities code is not reasonable.

The only reasonable way is to use DbLinq driver.
DbLinq allows to implement data conversion in single place inside driver
itself.

Andrus.

 

[Marc Gravell]

btw, if it *does* work, you'd benefit from some extension methods on
Nullable<DateTime> (below); and any "where" and projections etc for
LINQ would have to use just the UTC - i.e.

DateTime? whenUtc = whenLocal.ToUniversalTime();
.... where foo.BirthDateUtc == whenUtc


(extensions)

public static class DateTimeExt {
public static DateTime? ToLocalTime(this DateTime? when) {
if (when.HasValue) when = when.Value.ToLocalTime();
return when;
}
public static DateTime? ToUniversalTime(this DateTime? when) {
if (when.HasValue) when = when.Value.ToUniversalTime();
return when;
}
}
partial class People {
public DateTime? BirthDateLocal {
get {return BirthDateUtc.ToLocalTime();}
set {BirthDateUtc = value.ToLocalTime();}
}
}

 

[Marc Gravell]

There may be hundreds of DataTime properties in database.

And there might be 10... ;-p

The only reasonable way is to use DbLinq driver.

Or potentially ADO.NET Entity Framework when it is fully released.

I haven't had time to try this yet myself (just the lite LINQ-to-SQL),
but *as I understand it* this is designed to allow this type of
abstraction between the physical and logical models. Personally I
would much sooner use the RTM MS tools than DbLinq - it is my belief
that it will be easier to support long term.

(and for comparison, DbLinq *also* describes itself as "prototype"...
I suspect it will remain so long after ADO.NET EF is RTM).

Marc

 

[Andy]

And there might be 10... ;-p

Well, there's probably closer to 100 than 10.

Or potentially ADO.NET Entity Framework when it is fully released.

I'm not sure the DbLinq project will work for me, as it only seems to
do Postgres, MySql and Oracle.. I'm on MS Sql Server. It also doesn't
seem finished.

I haven't had time to try this yet myself (just the lite LINQ-to-SQL),
but *as I understand it* this is designed to allow this type of
abstraction between the physical and logical models. Personally I
would much sooner use the RTM MS tools than DbLinq - it is my belief
that it will be easier to support long term.

Which tools? The designer that creates the classes? Maybe I should
play with that some, it might give me some ideas.

The only thing with the method you propose is that I might use the
wrong field; but it is just me, however if more developers are added
or someone else takes over, that's something that may be confusing.
Can Linq handle the private properties flagged with the Column
attribute? That way I could use a "hidden" property that Linq to sql
will utilize, and force the busienss layer to use the public property
which does the translation as you described.

 

[Andy]

Well, looks like its an easy answer. From the ColumnAttribute
documentation: "You can apply this attribute to any field or property
that is public, private, or internal."

So, there it is. I'll try your method.

Thanks!

 

[Marc Gravell]

Which tools?  The designer that creates the classes?
Yes - but in particular the ADO.NET Entity Framework, which is a more
sophisticated beast than LINQ-to-SQL.

That way I could use a "hidden" property that Linq to sql
will utilize, and force the busienss layer to use the public property
which does the translation as you described.

Well, maybe... but I strongly suspect that the LINQ provider will
only be able to generate queries that use the [Column] property; if
you are only ever returning entire objects then this is probably fine;
but if you want to do projections or filters involving the dates, then
you will have to use the [Column] property.

Like I say; the new ADO.NET EF tooling might be the answer... but a
little too early to say... sorry...

Marc

 

[Andrus]

I'm not sure the DbLinq project will work for me, as it only seems to

do Postgres, MySql and Oracle.. I'm on MS Sql Server.

DbLinq has MS SQL support.

It also doesn't seem finished.

Can you list the software which is finished ?

Windows ?
C# ?
..NET framework ?
Entity framework ?
Linq ?
your application ?

Andrus.

 

[Frans Bouma [C# MVP]]

Suppose I have the following class which maps to a table:

public class People {
[Column( IsDbGenerated = true, IsPrimaryKey = true )]
public int PersonId { get; set; }
[Column]
public string FirstName { get; set; }
[Column]
public string LastName { get; set; }
[Column]
public DateTime? BirthDate { get; set; }
}

In the database all DateTimes MUST be stored in UTC. When the client
of the data layer gets the DateTime, it MUST be in LocalTime. To keep
easily forgotten code out of the business layer, I'd like to have a
way to specify when the BirthDate field is sent to the db,
ToUniversalTime is automatically called. Likewise, when getting the
BirthDate, ToLocalTime should be called.

Is there some where to hook into the Linq to Sql execution that would
allow me to do this translation?

I don't think Birthdate is a good example. The semantic value of a
birthdate is that it's bound to the PLACE of birth. So you might want
to store it in UTC format, but that's really not that useful. If
someone from the US says: birthdate: 01-feb-1980, and the local time
was 9 pm, likely the UTC time was 2 feb 1980. So calling LocalTime on
such a datetime could reveal a different date depending on where the
software is used and you transport the data (so people's birthdate
changes).

Anyway, using 1 storage format is logical. The thing is that you need
a public field which is indeed as Marc said, a shim property which
simply calls into the PRIVATE property/field which is the datetime
field which is mapped.

This indeed can be a bit awkward. Though linq to sql doesn't support
converters which can be placed in-between client and db and which
convert values back/forth. LLBLGen pro does, but we're not yet done
with linq (comes in 1 month)

FB

--
------------------------------------------------------------------------
Lead developer of LLBLGen Pro, the productive O/R mapper for .NET
LLBLGen Pro website: http://www.llblgen.com
My .NET blog: http://weblogs.asp.net/fbouma
Microsoft MVP (C#)
------------------------------------------------------------------------

 

[Andrus]

LLBLGen pro does, but we're not yet done

with linq (comes in 1 month)

How this will be implemented ?
Are you using events, subclassing or partial classes ?
Will it support conversions in both reading and writing ?

Andrus.

 

[Jon Skeet [C# MVP]]

DbLinq has MS SQL support.

Presumably it's been added quite recently, given that it's not
mentioned in the sparse documentation.

Can you list the software which is finished ?

Windows ?
C# ?
.NET framework ?
Entity framework ?
Linq ?
your application ?

Other than "your application" and "Entity framework" they have at least
been released - and are therefore supported. It's unlikely there will
be significant breaking changes in the future - not something you can
say about something which is currently at version 0.15.

They're all likely to be considerably more rigorously tested than
DbLinq too...

Here's another reason not to start using DbLinq for a production
application at the moment:

"DB_Linq has currently no documentation."

(From http://code2code.net/DB_Linq/)

Hardly encouraging, is it? It seems the page is slightly out of date,
in that there is *some* documentation. Far from enough to be seriously
worthy of consideration for a use in significant product though, IMO.

 

[Frans Bouma [C# MVP]]

How this will be implemented ?
Are you using events, subclassing or partial classes ?
Will it support conversions in both reading and writing ?

It already supports this, though as the topic starter uses linq, I
mentioned that our linq wasn't done yet . The support for this
conversion feature is inside our framework since 2005.

It uses a type converter, which is a simple class which converts a
value in type A to type B and back. This type converter class is a
derived class of the .NET type converter and you can write them
yourself, it's a few lines of code. You can specify with a field
mapping that you want to use such a type converter.

Now, at runtime, when a value is read from the database and it has to
be placed inside an entity field (e.g. Order.OrderDate) and the field
has a typeconverter associated with it, the type converter is called to
process the value. The same thing happens when the entity is saved only
then the other way around (a typeconverter has two methods: convertfrom
and convertto). Also with predicate specifications.

So you as the developer have no notion that the typeconverter is
there. You can use this to convert an int to a bool and back on oracle
for example or more advanced to convert a byte[] to a real image/bmp
object and back. However, you can also use this to process a value and
keep the same type, as with this situation.

FB

--
------------------------------------------------------------------------
Lead developer of LLBLGen Pro, the productive O/R mapper for .NET
LLBLGen Pro website: http://www.llblgen.com
My .NET blog: http://weblogs.asp.net/fbouma
Microsoft MVP (C#)
------------------------------------------------------------------------

 

[Andrus]

You can specify with a field mapping that you want to use such a type

converter.

So Andy must manually add field mappings to every his 100 DateTime columns
manually ?

So you as the developer have no notion that the typeconverter is there.

What happes if Andy forgets to add mapping to same column ?
What happens if new columns are added and adding mapping is forgotten ?

I expected that Andy can specify default typeconverter to all DateTime
columns by subscribing to datetime type conversion event handler.

Andrus.

 

[Frans Bouma [C# MVP]]

So Andy must manually add field mappings to every his 100 DateTime
columns manually ?

no you can automate that in our designer.

What happes if Andy forgets to add mapping to same column ?
What happens if new columns are added and adding mapping is forgotten?

you can automate that.

FB

--
------------------------------------------------------------------------
Lead developer of LLBLGen Pro, the productive O/R mapper for .NET
LLBLGen Pro website: http://www.llblgen.com
My .NET blog: http://weblogs.asp.net/fbouma
Microsoft MVP (C#)
------------------------------------------------------------------------

 

[Andy]

DbLinq has MS SQL support.

Didn't see it mentioned on their site.

Can you list the software which is finished ?

Windows ?
C# ?
.NET framework ?
Linq ?

The above have been released. They don't carry a message like this on
the DbLinq page: "DB_Linq is prototype software. Simple queries do
work, complex ones probably not. "

You know the difference between beta and release, right?

Entity framework ?

Which is still in beta, and why I'm not using it at the moment.

your application ?

My application has been released and in production. In other words,
tested and meets specifications. I'm working on a beta version now,
which I would not let my user's use for everyday use. I'll have them
test it first, and after property testing and feedback, release it.

 

[Andy]

Yes - but in particular the ADO.NET Entity Framework, which is a more
sophisticated beast than LINQ-to-SQL.

Well, I'm still coming up to speed on L2S. Hopefully EntFramework
will be finished soon, its something I'd like to look into if it would
be helpful for these scenarios.

Well, maybe... but I strongly suspect that the LINQ provider will
only be able to generate queries that use the [Column] property; if
you are only ever returning entire objects then this is probably fine;
but if you want to do projections or filters involving the dates, then
you will have to use the [Column] property.

Yes, I tried to order by one of the dates, and it said there was no
translation to Sql. So, a drawback. In this case though I can order
another field to get the same ordering, because I'm getting a history
of revisions. So ordering by revision date or revision number should
always yield the same order.

Like I say; the new ADO.NET EF tooling might be the answer... but a
little too early to say... sorry...

No worries. When its out, I'll check it out. For now, this solution
works. I wonder though if the same solution couldn't work with just
one property / backing field. Does link bypass the property setter
when it loads fields from the db? I think I read it did so that the
getter / setter in the class wouldn't cause change notifications. If
that's the case, I could properly return the backing field (right now
I'm using automatic properties) and specify it using the Storage
property of Column. I'll test that some other time.

Thanks again!
Andy