LINQ2SQL: How to modify fields values when loading anonymous entities?

Question

!!! Please do not redirect to this article, as it does not solve the problem described below.

Let's say we have such table in database:

SomeTable

• ID (int)
• DT (datetime)

We have configured a Linq2Sql data context. And we have configured an entity for SomeTable: OnLoaded method modifies DT in such way that DateTimeKind of DT becomes Utc (initially it is Unspecified).

Now here is the problem:

If we request data by using whole entity, the OnLoaded method is called:

From x In ourDataContext.SomeTable Select x

But if we request only part of table (and therefore generate an anonymous type), the OnLoaded is not called:

From x In ourDataContext.SomeTable Select x.DT

It is clear that OnLoaded is defined in SomeTable entity, and not in anonymous type.

At the moment I consider creating custom entities which would replace anonymous types. But maybe somebody has better solution?

Answer 1

We had similar problem as we needed to receive part of fields from entity as anonymous object and always know that we have DateTimeKind of date fields as DateTimeKind.UTC without using additional functions in LINQ request.

We tried a lot of things, but we found only one good enough solutions - code generation for Linq2Sql with T4.

P.S. If you want to learn more about Linq2Sql code generation with T4, you can begin from http://www.hanselman.com/blog/T4TextTemplateTransformationToolkitCodeGenerationBestKeptVisualStudioSecret.aspx

Answer 2

Linq2Sql generates partial classes for tables thus making it very easy to extend. Just add SomeTable.cs file to your solution (within the same namespace as your auto-generated db context) and define an extra property with any behavior you need:

public partial class SomeTable {
    public System.DateTime CustomDT {
        get { return DT.AddYears(120); }
    }
}

Now you can query it like usual:

        var e = ctx.SomeTable.Select(x => new { x.CustomDT }).First();
        Console.WriteLine(e.CustomDT);

Update:

Based on the comments I think the problem you're facing is due to incorrect separation of responsibilities. You're trying to pass a business logic (data transformation) responsibility to your DAL. While L2S provides some flexibility here (as shown above) you have another options if the solution is not satisfying:

1. Explicit layer above L2S DAL. Typically it's a repository pattern that returns DTOs very similar to ones auto-generated by L2S. In this case you can hide DT property forcing consumers to use CustomDT only.
2. Put the logic into the database (views, calculated columns, SPs). I wouldn't consider this approach for a new project but it may be a viable option for some legacy applications.

Answer 3

You could specify the DateTimeKind within the query:

from x in ourDataContext.SomeTable 
select DateTime.SpecifyKind(x.DT, DateTimeKind.Utc)

---

If you will be doing this often, an extension method might help make it less verbose:

public static class Ext
{
    public static DateTime AsUtc(this DateTime dateTime)
    {
        return DateTime.SpecifyKind(dateTime, DateTimeKind.Utc);
    }

    public static DateTime? AsUtc(this DateTime? dateTime)
    {
        if(dateTime == null) return null;
        return AsUtc(dateTime.Value);
    }
}

Then your query becomes:

from x in ourDataContext.SomeTable select x.DT.AsUtc()

Answer 4

You could use linq-to-sql for the query portion and use linq-to-objects to grab the DateTime property you want (you are not actually returning an anonymous type).

(From x In ourDataContext.SomeTable _
 Select x).AsEnumerable() _
          .Select(Function(x) x.DT)

Answer 5

Can you try this code? Instead of using Anonymous type, you can specify the same Table type but load just one field. I don't know whether it will work or not in your case.

SomeTable.Select( x => new SomeTable {
    DateField = x.DateField
})

Otherwise there is no simple solution to it.