Hi Geniuses,

I have a Call entity with Start and End properties (of type DateTime?).

I want to issue a query to get the average difference between Call.Start and Call.End (i.e. the average length of a call):

Query.All<call>().Where(call => call.Start.HasValue && call.End.HasValue).Select(call => call.End.Value.Ticks - call.Start.Value.Ticks).Average();

Unfortunately, DO cannot translate the DateTime.Ticks property. I tried to write my own LINQ translator according to: http://dataobjectsdotnet.googlecode.com ... ationToSQL

But I can't seem to figure it out. Could you please provide any advice?

Btw, I've already thought about having a persistent property that tracks the difference between Start and End, but the above is a very simplified example. There are many "DateTime difference" calculations I need to make with many types and properties, so I can't add duration properties for each calculation.

Thanks!

This thread was imported from our support forum. The original discussion may contain more detailed answer.

asked Jul 15 '10 at 03:53

ara's gravatar image

ara
395858791


One Answer:

psulek wrote:

Add property to you entity Call like this:

public class Call: Entity
{
...
    public long AverageCallLength
    {
        get
        {
            return End.Ticks - Start.Ticks;
        }
    }
}

And create new static class with linq translator method like this:

[CompilerContainer(typeof(Expression))]
public static class LinqExtensions
{
    [Compiler(typeof(CallEntity), "AverageCallLength", TargetKind.PropertyGet)]
    public static Expression AverageCallLength(Expression sourceExpression)
    {
        // sourceExpression -> CallEntity
        Expression<Func<CallEntity, long>> expression = (entity) => entity.End.Ticks - entity.Start.Ticks;
        return expression.BindParameters(sourceExpression);
    }
}

Now you can use property AverageCallLength like this:

long callLen = Query.All<CallEntity>().Select(call => call.AverageCallLength).Single();

Alex (Xtensive) wrote:

This really wont work, since you rewrite Call.get_AverageCallLength to an expression utilizing DateTime.Ticks, which can't be translated :roll:

So to use this, you must rewrite it so that DateTime.Ticks isn't used, i.e. express the result using some other translatable expressions for DateTime. I'll ask the guys if this is possible.

Btw, you can define a rewriter directly for DateTime.Ticks property:

[CompilerContainer(typeof(Expression))]
public static class LinqExtensions
{
    [Compiler(typeof(DateTime), "Ticks", TargetKind.PropertyGet)]
    public static Expression AverageCallLength(Expression sourceExpression)
    {
        // sourceExpression -> CallEntity
        Expression<Func<DateTime, long>> expression = d => (d - new DateTime(1900,1,1)).TotalMilliseconds * (long) 10000; // May be this will work
        return expression.BindParameters(sourceExpression);
    }
}

answered Jul 15 '10 at 06:55

Editor's gravatar image

Editor
46154156157

Alex my code works well, publish sample here after i try it :-)

(Jul 15 '10 at 06:55) Peter Ĺ ulek Peter%20%C5%A0ulek's gravatar image

Ah, clear - sorry, I missed the fact that second post is made by you ;) I though it's a continuation of original problem report.

(Jul 15 '10 at 06:55) Alex Yakunin Alex%20Yakunin's gravatar image

Like I said, "Hello Geniuses". Worked like a charm.

(Jul 15 '10 at 06:55) ara ara's gravatar image
Your answer
Please start posting your answer anonymously - your answer will be saved within the current session and published after you log in or create a new account. Please try to give a substantial answer, for discussions, please use comments and please do remember to vote (after you log in)!
toggle preview

Subscription:

Once you sign in you will be able to subscribe for any updates here

Tags:

×573

Asked: Jul 15 '10 at 03:53

Seen: 4,689 times

Last updated: Jul 15 '10 at 03:53

powered by OSQA