Universal time (UTC) and dynamic Linq

Hi guys

We're facing a wall in one situation here and would appreciate any advice.

A short introduction (please bare with me :) )
For displaying a list of data to the client via WCF application server, we already made the following "infrastracture": We have a DtoGenerator (not using Lightspeed DTOs because they are copies of all properties) by which we can select only a few fields of original entites (so i.e. I have Product and ProductDto).
Our DtoGenerator also creates a method with LambdaExpression by which to get ProductDto from Product.
I.e.
        public static Expression<Func<Product, ProductDto>> GetLinqMapBLOCKED EXPRESSION
        {
            Expression<Func<Product, ProductDto>> mapExpress = origItem => new ProductDto
            {
                Id = origItem.Id,
                Description = origItem.Description,
            };
            return mapExpress;
        }

This allows us to make a dynamic linq query in the application server i.e.
var query = uow.Products.Select(ProductDto.GetLinqMapBLOCKED EXPRESSION
which allows us further to apply generic methods for dynamic linq filtering & ordering lists according to client's selection.

The problem:
We were planing on using UTC times in the database (although all events-records are actually happening in one time zone) because of problems with Daylight savings and calculating duration. However, we can not get arround the problem of applying local server time in a dynamic query,
i.e. Adding one more line to the method above:
                CreatedAt = origItem.CreatedAt.ToLocalTime(),
as in runtime "method not supported by Lightspeed" pops-up.

So, not sure what to do... We can try to adjust the times in the list of fetched Dtos by running once through the list. If possible, it will be additionaly slow, as we have generic mehthods so this means "System.Reflection zone" for finding properties which are DateTime type and adjusting them.
Any ideas would be appriciated. Thanks.

I think we might need a repro project to fully get to grips with this, because I am not quite sure how the DTO stuff fits together with the query stuff (and our beloved forum software has helpfully substituted BLOCKED EXPRESSION for parts of your message which doesn't help...), but it looks like your query is effectively performing a projection (i.e. I don't think it's materialising a real Product entity at all), and the projection includes a ToLocalTime() call which we cannot translate into a server expression (SQL function).

Again I am not quite sure of the fix, but I think what you may need to do is split the "load entity using query criteria" and "copy entity properties into DTO" bits, and force the "copy entity properties into DTO" bit to be run using LINQ to Objects rather than being translated to the server:

var productEntities = uow.Products.Select(query);
var productDtos = productEntities.ToList().Select(p => mapExpress(p));  // or AsEnumerable()

If this doesn't help can you post a simple console project and SQL CREATE TABLE scripts that demonstrates the problem?  That will help us get our heads around how it all fits together.  Thanks!

Hi Ivan, thanks for a quick reply.

Despite my complicated explanation, you've got it right. We are doing a projection. Why? Speed.
Once Lightspeed will support joins and full LINQ, we will easily switch to getting one DTO from several entities, with the same lambda expressions concept. And we use a DtoGenerator, because for different projects we are partialy changing columns, and we want to avoid manually creating simple transfer objects as partial copies of already existing properties. And dynamic linq heavy usage is in there also because of changing projects (and LINQ heavy usage because of try to stick to a wide dev. standard).

Back to speed, i.e. if our Product table has 100 columns, when we are displaying a list of products to the client, he wants to know only Id, Name and Description. So our query in first example will return a transfer object with only those columns. When client wants to edit a Product he will get a different single DTO with all properties.
Your proposed solution is valid, but by using it, we will lose speed - get all columns from the database all the time (and waist the "infrastructure" already mentioned).

A compromise option would be to adjust times after we load the dto list (maybe to avoid reflection as mentioned in inital post, we could use our generator to get a function for Dto which can be then exectued on the collection). Something like:
var query = uow.Products.Select(mapExpress);
// ...
// apply dynamic linq filtering, orderding to the query
// ...
var productDtos = query.ToList(); // fetch from DB
foreach productDto p in productDtos
   p.AdjustTime();

Last option I see, the fastest, would be to have a way of telling Lightspeed to translate ToLocalTime() (or some other method in linq query) into a function call in database (Oracle), so that the inital query example would be translated into something like:
select id, name, APPLY_LOCAL_TIME(changed_at) changed_at
from product;
Not sure if there is a supported way to do that. If so, please let me know.
Alternatively, as we got the Enterprise edition of Lightspeed, we could try to adjust it ourselves to translate that method, but not sure I would like to go down by that road (if we are even allowed to do that by the license agreement)...

Ah, sorry Ivan, I forgot to mention that I can try to prepare a sample project, but I think you already got to the root of the issue.
Anyway, today I completed option no. 2 from last post (adjusting times by one loop on loaded Dto list) without reflection, so we have a workaround.

So please, let me know only the answer to the last thing - is it possible for us to make Lightspeed translate method call in LINQ query into a database function call in SQL query?

Your "compromise option 2" is definitely the most pragmatic -- good to hear that it solves your problem for now.

We don't currently have an extensibility mechanism for teaching LightSpeed how to translate CLR methods such as ToLocalTime() to SQL functions, and there would be licensing issues around doing it yourself.  I'll have a look at this and see whether we can add this feature, and failing that I'll see if we can at least add ToLocalTime() translation on Oracle.

Okay, I need to ask a dumb question here.  In your example of translating the ToLocalTime() call to Oracle, you refer to an APPLY_LOCAL_TIME function.  I can't find this in the Oracle docs.  Is this a custom user-defined function that you have created / would create?  Thanks!

Yes, it is a custom user-defined function we created. That's why my question was more in a general possibility of "translating" a method into a customer-selected database function call.

AFAIK, Oracle (nor SQL Server for that matter) don't have a built in function to calculate local time from UTC. I'll look it up again in morning (after midnight here). If I find one, I will let you know.
Of course, from your point of view, not sure if it does make sense for a quick fix for Oracle translating ToLocalTime() into something not offically from Oracle (i.e. into a function you include also as a script in Lightspeed insallation folder, like you have for generating KEYVALUE table).

Okay, I have now committed *basic* support for user-defined translations of functions in projections, and it will be in the 30 July nightly build, available from about 1430 GMT.  I think that what I've done will be enough to solve your immediate problem (should you have the time and inclination to go back and revisit it), though I may be wrong.  It is NOT a fully general solution for translating CLR methods into SQL.  However we will be happy to extend and revise it as further use cases come up.

The API in the 30 July nightly will be as follows.  (This may be subject to breaking changes as more use cases come up.  I know this is a pain but I didn't want to try to build the wonder-API on day one: not enough time and not enough use cases!)

* To register a CLR method as translatable to SQL, call ServerFunctionDescriptor.Register, passing the MethodInfo of the CLR method and the name of the SQL function.

Example:

MethodInfo indexOfMethod = typeof(string).GetMethod("IndexOf", new Type[] { typeof(string) });
ServerFunctionDescriptor.Register(indexOfMethod, "INSTR");

You can now use the method in projections, e.g.:

var query = from m in UnitOfWork.Members
                 select m.UserName.IndexOf("a");

and it will be executed on the server.

* By default, the expression to which the function is being applied will be the first argument to the server-side function.  Thus, the above example becomes SELECT INSTR(Members.UserName, 'a')...  If the expression needs to be in another position, call the Register(sqlFunctionName, implicitArgumentIndex) overload.  implicitArgumentIndex is the 0-based index of where the column expression should appear.

For example, on SQL Server, String.IndexOf translates to CHARINDEX, but CHARINDEX takes the string to search for first, and the string to search in second, i.e. we need to generate SELECT CHARINDEX('a', Members.UserName).  So our Register call would need to look like this:

ServerFunctionDescriptor.Register(indexOfMethod, "CHARINDEX", 1);

Note the implicitArgumentIndex of 1, indicating that the target expression appears at index 1 in CHARINDEX's argument list.

Caveats:

* We do not yet support reordering of arguments i.e. if the CLR method takes its arguments in a different order to the SQL function.  This would be easy for us to add if required.

* This has not been tested with user-defined functions but I believe it should work as long as the sqlFunctionName string is the correct SQL literal for calling the UDF.

* We do not yet support different registrations for different data providers.  E.g. if you have an app that talks to both Oracle and SQL Server databases, and wants to use IndexOf in queries to both of them, that's currently not possible because IndexOf can have only one translation.

* We don't attempt any kind of data type coercion, which can lead to unexpected behaviour.  For example, while testing, my initial use case was the IndexOf(char) overload.  That worked on SQL Server, but failed on Oracle because the char parameter ended up being passed to Oracle as a Byte, which INSTR silently failed to match.  So if you see odd results, try setting LightSpeedContext.VerboseLogging = true and check whether the data types look right.  We don't yet provide a way to *fix* any such problems (like I said, this feature is in its basic stage), but we'd be happy to address them if they came up.

As always we welcome feedback, and please let us know if you run into any problems or have additional use cases that it would be useful for us to handle.

Ivan, wow... If you just heard a noise, that was my jaw dropping on the floor :)
That's brilliant.I will test it immediately when the nightly comes out.

I've tried this, but I can't get it to work. When it comes to the query, I always get an exception ""Unexpected expression type while resolving subexpression."

Stack trace is:
   at Mindscape.LightSpeed.Linq.SelectTypeContext.ResolveInProjectionContext(IList`1 path, Type resolvingType)
   at Mindscape.LightSpeed.Linq.SelectTypeContext.Resolve(IList`1 path, Type resolvingType)
   at Mindscape.LightSpeed.Linq.DeferredPathExpression.Resolve(Type resolvingType)
   at Mindscape.LightSpeed.Linq.DeferredPathResolver.Visit(DeferredPathExpression deferredPathExpression)
   at Mindscape.LightSpeed.Linq.DeferredPathResolver.Visit(QueryExpression expression)
   at Mindscape.LightSpeed.Linq.DeferredPathResolver.Visit(DeferredPredicateExpression deferredPredicateExpression)
   at Mindscape.LightSpeed.Linq.DeferredPathResolver.Visit(QueryExpression expression)
   at Mindscape.LightSpeed.Linq.DeferredPathResolver.Visit(LogicalExpression logicalExpression)
   at Mindscape.LightSpeed.Linq.DeferredPathResolver.Visit(QueryExpression expression)
   at Mindscape.LightSpeed.Linq.LinqQueryExecutionPlan.LoadFinalQueryBLOCKED EXPRESSION
   at Mindscape.LightSpeed.Linq.LinqQueryExecutionPlan.Execute()
   at Mindscape.LightSpeed.Linq.Sqo.CountBase.Evaluate(LinqQueryExecutionPlan plan, MethodCallExpression expression)
   at Mindscape.LightSpeed.Linq.Sqo.ImmediateSqoWithPredicateWithExpressionContext.PerformVisit(LinqQueryTranslator visitor, MethodCallExpression expression)
   at Mindscape.LightSpeed.Linq.LinqQueryTranslator.VisitMethodCall(MethodCallExpression m)
   at Mindscape.LightSpeed.Linq.ExpressionVisitor.Visit(Expression exp)
   at Mindscape.LightSpeed.Linq.LinqQueryTranslator.Translate(Expression expression, IUnitOfWork unitOfWork, String viewName)
   at Mindscape.LightSpeed.Linq.LinqQueryProvider.Execute(Expression expression)
   at Mindscape.LightSpeed.Linq.LinqQueryProvider.System.Linq.IQueryProvider.Execute(Expression expression)
...

To be clear on my example, this is what I added. In repository when creating context (connection string, etc.):
  System.Reflection.MethodInfo toLocalTimeMethod = typeof(DateTime).GetMethod("ToLocalTime");
  ServerFunctionDescriptor.Register(toLocalTimeMethod, "APPLY_LOCAL_TIME");

A simple example of Oracle function for testing:
  create or replace function APPLY_LOCAL_TIME (utc_date in date)
  return date
  is
  begin
      return utc_date + 1;
  end;

 Could you provide us with a small console project that reproduces the issue?  Thanks!

For comparison here is a test case I have tried to model on yours:

[Test]
public void Marko()
{
  MethodInfo similarityTo = typeof(SoundexExtensions).GetMethod("SimilarityTo");
  ServerFunctionDescriptor.Register(similarityTo, "DIFFERENCE");

  var results = UnitOfWork.Members.Select(GetLinqMap());

  foreach (var result in results)
  {
    Console.WriteLine(result.MemberId + " -> " + result.MatchQuality);
  }
}

private static Expression<Func<Member, MemberMatchResult>> GetLinqMap()
{
  Expression<Func<Member, MemberMatchResult>> projector = m => new MemberMatchResult
  {
    MemberId = m.Id,
    MatchQuality = m.UserName.SimilarityTo("ivon")
  };

  return projector;
}

(Here SimilarityTo is an extension method on String, MemberMatchResult is a trivial Plain Old Data type, and DIFFERENCE is a built-in SQL Server function.  I know you guys are using Oracle, but the stack trace indicates that this isn't a database issue, and it was just a bit easier for me to try to repro with SQL Server.)

This works and generates the following SQL as you'd expect:

SELECT
  Members.Id,
  DIFFERENCE(Members.UserName, 'ivon')
FROM
  Members

Looking at the stack trace I'm guessing that you perhaps have some other expressions in the query that I've not got in my test case.  Hopefully seeing my test case will help you to identify what we are doing differently and we can either get it fixed or suggest a workaround.

Thanks Ivan. From what I see just by looking, we have additional dynamic linq where and order by clauses and query is first called as count (which immediately breakes).
Something like:
var query = uow.Products.Select(GetLinqMap());
query = query.Where(filterString, filterValues); // dynamic linq used for filtering
query = query.OrderBy(orderByParameters); // dynamic linq used for ordering
var totalRecords =query.Count(); // used for verifying paging request, throws mentioned exception

But if i have my lambda expression without that custom translated function, everything is fine. When I add usage of that new method, I get the exception, so pretty sure it's clashing with something.
I will try a bit more, and see to wrap-up a sample project...

OK, I have managed to narrow down the problem just before weekend starts here :)
The problem appears when there is a filter applied on that column which has a custom method called. The SQL probably wants to end-up into something like this:
select name, APPLY_LOCAL_TIME(created_at)
from product
where APPLY_LOCAL_TIME(created_at) > @min_date

And this type of SQL should probably be avoided as would probably not use the index (if existing) on that time column.
Our goal would be to end up with an SQL like:
select name, APPLY_LOCAL_TIME(created_at)
from product
where created_at > @min_date
but not sure if getting that will be possible with the concept of projections which we already made.

So, I am not sure about what exactly throws that Exception, probably translator parsing the where part of LINQ query trying to find his way by that projection.
I can look it up more on Monday, but with more time spent on this issue (possibly from your side also), looks like that compromise solution of adjusting values in memory after they are loaded looks even more acceptable.

When I try the following query:

UnitOfWork.Members.Where(m => m.UserName.StartsWith("j")).Select(GetLinqMap());

(where GetLinqMap() performs a projection involving the UserName property) I still get the expected SQL:

SELECT
  Members.Id,
  DIFFERENCE(Members.UserName, 'ivon')
FROM
  Members
WHERE
  Members.UserName LIKE 'j%' ESCAPE '\'

i.e. I'm not seeing the function being applied to the column in the WHERE clause.  So there should be no problem getting the SQL you want provided your .Where expression refers to product.CreatedAt rather than product.CreatedAt.ToLocalTime().

Anyway, I appreciate you have a solution that is acceptable to you and you probably don't want to spend more time on this issue.  We are quite keen to shake the bugs out of this new feature so if you do have time to create a repro case then that would be really useful for us.  But if you don't have time, no worries.

OK. Here it is.
I was maybe not fully understandable before. When I mentioned dynamic linq, this means we are using a linq dynamic extension (included in the solution I'm sending) which was posted by Scott Gu (and which should have been, but didn't make it into the 3.5. framework).
http://weblogs.asp.net/scottgu/archive/2008/01/07/dynamic-linq-part-1-using-the-linq-dynamic-query-library.aspx

This type of dynamic linq filtering is giving that situation. I've also added a small sql script to create all the necessary database objects for the test.

Not sure if you've looked it up, but I was curios, so when I got some time, explored a little bit further.

Actually now I see that this problem is not connected with your new custom function feature. It is a problem of projections and trying to make an sql with a filter on a complex column in projection.
By "complex column" I mean any which is not a straightforward copy of original property column.

I've made a similar test to show this. My projection mapping is now:
                Name = origItem.Name,
                result = origItem.Minor + origItem.Maxor // composite column, throws Exception when filtered
and this will throw exception:
                var query = uow.Products.Select(ProductDto.GetLinqMapBLOCKED EXPRESSION;
                query = query.Where("result > @0", 10); // filter on composite projection column, Exception
                var noOfItems = query.Count();

So back to that SQL generating, I think the only possible (if anything) command to generate from this is:
   select name, minor + maxor
   from product
   where minor + maxor > 10;

Here is the test sample:

Thanks for the additional info, Marko -- really appreciate you taking the time to look into this (we haven't had time to do so yet, unfortunately).  I'm not sure whether this is something we'll be able to fix but at least this will give us a head-start on what the real problem was!  I'm assuming that this remains non-critical for you because you are able to use your existing fix for the UTC/local time scenario -- let us know if you do need us to give this some more urgency.

You're correct, it remains non-critical for us.

As we are using projections and was finally suspecting the problem lies within them, I wanted to explore the boundaries. Although we have to be careful about possibilty of client trying to filter by "complex projection columns/properties", much more important for us is LS 3.0 and wider LINQ support.