Thanks for the reply. GetDate() was only an example of DB functions and even with GetDate(), client date can't be used most of the time. So I guess anytime (read most of the time ) that kind of functionality needed, raw SQL needs to be executed. That is not a problem - just wanted to make sure.

Can you put anything in the select clause using that technique?  For instance, could I call a user-defined function?  I want to do something like this:

SELECT dbo.fnCanUserAccessThis(ID, @UserID) -- Returns 0 or 1
FROM Resources
WHERE ID = @ResourceID

As a note for future support: DateTime/Timestamps - IMO - should always be executed as a function using database specific functions. Some of this or most could be achieved using stored procedures which ES works with nicely, but I would like to suggest some serious thought given to support database specific Timestamps (I want to be careful here as the word timestamp is used differently by different users of different databases).

Essentially, let's assume we want every table in the database to store two pices of information:

1. The date/time created

2. The date/time last modified

In my opinion, a client-bsed date/time is always wrong: it can never match the exact moment in time properly. Further, it opens the door for improper manipulation.

Every database I am aware of uses/accepts a simple function, such as "GetDate()", as a "value". I suggest that thee are only a handful of officially supported databases by ES and that it wouldn;t take too long to list each database's respective function. I further suggest that ES use/allow a predefined Date/Time constant such as "November 12, 1901: 12:23:16". Such a constant is accurate to within millseconds and virtually impossible (statistically impossible) to "accidentally" coincide with a real date/time.

If this constant is applied as a value within an insert/update, the dummy constant value gets replaced with the database specific "GetDate()" function.

For me, this is HUGE! I could give speeches on why a client should never supply a Created/LastModified/Timestamp Date/Time. If the constant is named InsertServerBasedTimestamp, the code for an ES developer would be something like:

ESObject.Created = EsConstants.InsertServerBasedTimestamp;

Of course, you may imgine another alternative... please.. understand I am only making a suggestion to start some consideration for support in this direction. I STRONGLY believe that Date/Times, especially for the two scenarios I described (created/modified), should always be created by the server on the exact moment the server actually processes a particular transaction.

TIA

I agree--this would be handy.  I don't think a constant datetime value would be necessary, though.  The query interface accepts any object as a value, so the "esConstants.ServerTimestamp" could just be a recognizable object that is replaced when the query is built, similar to the way ADO.NET uses DBNull.Value.

I'm with you on the importance of this feature, though.  My application doesn't care so much about accurate [server] dates (mostly just a FYI for the user), but I can see how many applications would.  If the feature was there, I would definitely use it.

Is there any chance of allowing raw SQL in WHERE clauses in the near future?  I'm coming to a point in my application where I could really use this feature, and I suspect a lot of other people would find it useful as well.

Up until now, most of my app's queries have been pretty straight-forward--just based on simple checks of columns in a single table.  But I now have hierarchical projects with user & role-based authorization that requires a host of recursive user-defined functions to check whether a user has access to a project or resource, and whether a resource is within a project.  Now 75% of my application's dynamic queries will need to include these udfs to return only the available resources/projects/users.  Modeling the cascading user access or resource visibility is impossible using the dynamic query interface (or even a non-recursive function), since I'm not limiting the number of levels in the project tree.

// In a nutshell...

// The majority of my dynamic queries will need to change from this:

Resource resource = new Resource();
resource.Query.Where(resource.Query.SomeColumn.IsNotNull());
resource.Load();

// To this (ideally):

Resource resource = new Resource();
resource.Query.Where(
    resource.Query.SomeColumn.IsNotNull(),
    resource.Query.Raw("1 = fnCanUserAccessResource({0}, Id)", CurrentUserId)
);
resource.Load();