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?

UserControl · Answer 1 · 2015-07-03T14:18:21.303

3

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:

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.
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.

edited Jul 03 '15 at 14:18

answered Jun 30 '15 at 12:45

UserControl

14,766
20
100
187

This method is described in question itself, but I am looking for better approach. – Dima Jun 30 '15 at 14:08
I can't see how the question describes using of an extra property which does address the issue you have. It works in both projection (as in the example) and selection scenarios. – UserControl Jun 30 '15 at 14:14
Well, It is simple: we have to introduce custom code, which has two disadvantages: 1. a third-party developer still cannot use DT property directly (therefore whole idea of automated DT property is broken) 2. while using CustomDT in queries, LINQ2SQL skips optimization: it requests all table fields from database instead of requesting only DT field. – Dima Jul 03 '15 at 13:55
1

@Dima Linq-to-SQL doesn't get all fields if you use this approach. I think this is the only thing you can do. I tend to agree with these words on separation of concerns. Don't try to use a simple ORM for things it's not designed for. And this is only the select part. What if you also want to update DT? – Gert Arnold Jul 04 '15 at 10:24
It does get all fields, @Gert, I have tested it. Not sure about update behavior, it needs separate testing. – Dima Jul 04 '15 at 20:38

Epsil0neR · Accepted Answer · 2015-07-08T08:43:10.380

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

edited Jul 08 '15 at 08:43

answered Jul 08 '15 at 08:39

Epsil0neR

1,676
16
24

I guess that is the only way for now... :/ – Dima Jul 08 '15 at 08:41

score 0 · Answer 3 · answered Jun 30 '15 at 12:24

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()

This breaks the idea of delegating the functionality to Linq2Sql. If some developer forgets to use AsUtc then he will receive incorrect data. — Dima, Jun 30 '15 at 14:07

score 0 · Answer 4 · answered Jul 03 '15 at 15:19

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)

score 0 · Answer 5 · answered Jul 04 '15 at 07:58

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.

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

5 Answers5