I would like to know if for the linq expressions, the LLBLGen provider supports a method that will provide the actual NATIVE query that will be executed against the database.

This feature should not require a database connection.

This feature already exists in Entity Framework, the ObjectQuery<T> supports the ToTraceString() method.

Thank you. Liviu

Hi Liviu,

ToTraceString doesn't exists the way it's used in EF. And IMHO it's not needed as the LLBLGen DQE Tracing is more mature and powerful. LINQ2LLBL is build on the top of the LLBLGen API, so it's best to doing tracing in the way LLBLGen provides.

You also can use Debug visualizers. Added to that, most of the time it's desirable to switch tracing on and off. You can do that with LLBLGen, and IMHO is more transparent as you don't want to fill your BusinessLayer code with a bunch of tracing lines.

In your case, what is the intention of use that method?

Hi, thank you for the answer.

In our projects, in order to have greater flexibility we have implemented a generic repository that hides the details of the underlying ORM needed. The ability to get the datastore sql is essential in order to do some instrumentation needed by our repository. We have concrete implementations over Linq or Entity Framework.

I am currently evaluating LLblgen, drawn by the fully blown implementation of LINQ. Inspite of this, the functionality i mentioned is essential to our needs. The feature must be there but it is not publicly available, and maybe is not that hard to expose...(but that depends of the initial architectural decisions of LLblgen...)

Best Regards Liviu

LLBLGen Pro provide Tracing Which can be catched by listners to be written on a file, as discussed in this thread: http://www.llblgen.com/TinyForum/Messages.aspx?ThreadID=13069

If you want to catch the sql query at runtime, you may derive a class from DataAccessAdapter and override OnFetchEntity, OnFetchEntityCollection.... etc. You'll receive an IRetrievalQuery object in the method parameters, which has a Command property of type IDBCommand, holding the query to be executed.

Hi Walaa,

Ok. but is it possible to get this WITHOUT actually fetching anything?

I want to use exactly this pattern from EF:

var e = from c in db.Customers where c.Active == true select c;

// NO EXECUTION, no trip to the datastore, the connectionstring might even not be // configured here

var sql = (ObjectQuery(e)).ToTraceString();

//EXECUTION triggered by enumerator

foreach(var c in e) {

}

Best Regards

Again that's not available out of the box, so you have manupulate it to stop the execution. In your derived DataAccessAdapter class, you may raise an exception to stop the execution of the query, or you may clear the query out.

Could you elaborate a bit about why you need the generated SQL before it's executed? The linq query first has to be parsed, and then sent to a routine which executes the request, which is then converted into SQL which you'll see in the trace output.

The generated SQL is something following the query, so I'm not sure why you would need the generated SQL in the .NET code. (hence the feature isn't there).

The biggest mistake framework architects make is thinking that nobody will ever need feature X,Y just because it appears that at the moment there is no reason to do so. So there isn't even an extensibility point. Microsoft does it with EF (and other frameworks: As.net mVC)... The user of the framework should be the king ;-)

You say: 1) The linq query first has to be parsed 2) then sent to a routine which executes the request, 3)which is then converted into SQL which you'll see in the trace output.

It seems to me that you cannot do 2) if you have not already got 3)

ursuletzu wrote:

The biggest mistake framework architects make is thinking that nobody will ever need feature X,Y just because it appears that at the moment there is no reason to do so.

I find that a bit beyond reality. 'Biggest mistake' seems to suggest that if a framework doesn't have every feature thinkable, it's flawed due to that mistake. By definition, that means that every framework out there is thus flawed.

The thing is that every feature takes time to implement. To ship a version of a system, you have to focus on what features are necessary and if time permits, which features are nice to have. For every feature, there has to be a reason to be implemented, otherwise other features should be considered as the # of features to consider is endless.

A feature which is required for a particular user because the user has a very specific project requirement could be a feature not considered very useful, simply because the framework will always limit a user one way or the other, as a framework is never tailor made for the project at hand (even if you try).

So I asked you why you need it. You didn't answer me. Of course, that's fine, but please realize that if you don't answer my question, I can't decide if the requirement is fulfillable using another route or if it's even doable at all.

So there isn't even an extensibility point. Microsoft does it with EF (and other frameworks: As.net mVC)... The user of the framework should be the king ;-)

The user of the framework might be considered king, but should also realize that bending frameworks to match the work is never going to work.

There are extension points which might make this workable. One way to do this is for example to use a subclass of DataAccessAdapter and produce the queries like normal execution would but stop there. However this will never work properly in all cases, and I'll give you an example:

var q = from c in metaData.Customer group c by c.Country into g select g;

this is a query which results in 2 SQL queries: one being: SELECT Country FROM Customers GROUP BY country

the other being: SELECT CustomerID, CompanyName, ... FROM Customers WHERE Country IN (@country1, @country2, .... , @countryn)

(depending on the # of countries of course, it might also decide to use a subquery instead of the IN clause. ).

If you want both queries BEFORE execution, you can't: the second is based on the data fetched by the first. Not executing them will not even create the second query, as there's no data fetched by the first.

Optimization logic inside the engine might also decide to use a Subquery, because the # of countries is above a threshold. (this is for example done as well in other nested query scenario's and for example in prefetch paths / graph fetching (the 'Include' operator in EF)). This means that if you fetch: Customers from USA and their orders, you might get for the Orders a query with an IN clause with constants on CustomerID, but if the # of customers is above a threshold, it might be the engine decides to use an IN clause with a subquery for customerid.

So what will you do then in your code? IMHO this becomes very complex for you to process, and therefore I'd like to know why you even want this. Also because in graph fetches, the orders aren't fetched as there are no customers fetched because the query isn't ran!

You say: 1) The linq query first has to be parsed 2) then sent to a routine which executes the request, 3)which is then converted into SQL which you'll see in the trace output.

It seems to me that you cannot do 2) if you have not already got 3)

Linq -> Own query api objects -> Execution routine -> calls DQE to produce SQL query, execution routine executes SQL -> gets results back -> consumes results into result -> returns result.

Now, please answer my question about why you need all the SQL up front before executing the Linq query. (You're using it to do cache management?)

(IMHO, if you need to perform processing in your repository, it's better to process the Linq query as string. But then you run into stuff like non-IQueryable linq queries which aren't deferred executed by definition: var q = (from c in metaData.Customer where c.Country=="USA" select c).Count();

here 'q' is a value, directly executed, so you can't obtain any sql.)

Hi Otis,

There are some obvious extension points in any ORM. The SQL executed could be an easy one to spot

Yes you are right. Queries like those you showed, with grouping, require further processing in principle, but not necessarily. All the eager loading stuff might be run in a single sql statement if the fwk was built that way. And it might be even more performant. But i digress.

The aggregate execute instantly. This is a linq little flaw. I would have thought a consistent deferred behaviour would have been more apropriate. The Agg could have returned a TQueryResult<T> which when casted to T would have triggered the execution.

anyway, I can route all Linq expression through an IQueryProvider wrapper and resolve the aggregates myself = extracting the inner query. That's not an issue.

Why i need this?

I would like to implement that abstract repository. I don;t want the client of the repository to know if it talks to a local 2 tier model or over wcf to a remote server.

But remoting linq queries is douable but not 100% working out of the box. Remoting the SQL and materializing on the client is easy ( if we put aside the eager loading ).

That;s why we need this.

ursuletzu wrote:

Hi Otis,

There are some obvious extension points in any ORM. The SQL executed could be an easy one to spot

Though only usable when you're about to execute it. Which is possible (override some On* methods in a subclass to DataAccessAdapter, which are called right before actions happen so you can alter the generated sql).

Yes you are right. Queries like those you showed, with grouping, require further processing in principle, but not necessarily. All the eager loading stuff might be run in a single sql statement if the fwk was built that way.

Multi-branched graphs are hard to do with joins unless you want a lot of duplicated data.

The aggregate execute instantly. This is a linq little flaw. I would have thought a consistent deferred behaviour would have been more apropriate. The Agg could have returned a TQueryResult<T> which when casted to T would have triggered the execution.

Yes, that would be better. MS listened to my similar complaint but thought it would be better if it was done the way it is done now... So, there's little we can do.

Why i need this?

I would like to implement that abstract repository. I don;t want the client of the repository to know if it talks to a local 2 tier model or over wcf to a remote server.

But remoting linq queries is douable but not 100% working out of the box. Remoting the SQL and materializing on the client is easy ( if we put aside the eager loading ).

That;s why we need this.

Generic repositories are IMHO better off with a specification pattern implementation (see Nilsson, ADDDP). That's generic too and you can use it without digging through sql. The problem with producing SQL on the client is that you need the DB specifics on the client as well, which totally mitigates the whole point of a repository (which abstracts away the data-access), as the developer can do whatever s/he wants outside the repository. With a specification pattern you don't have that: all data-access and database related stuff is part of the repository, and outside code is not aware of a database at all.

(edit) also, if you want to switch databases, your client doesn't have to know (sqlserver -> oracle for example).

Hi Otis,

A specification pattern is not acceptable because you have to recompile the services and all applications that share the service every time you need to extend the service. And that is a lot. If you want to just do a LINQ query that is not exposed by the repository, without rebuilding the repository There is a lot of hidden strong bounds between client and service.

A generic repository as i did, is super light. I might move some common methods in a shared services library, when time is apropriate, but i can query directly if i want to when i want to.

I do not have a dependency on the datastore, because when the application starts, it requests the service configuration. So the client will always match the datastore configurations on the server ( only the provider settings are important)

I don't see why you even want to use a repository then, as all the things it should abstract away for you is necessary for you to define queries. Also the specification pattern implementation.. a change in the service requires clients to be recompiled or that they stay on the older version. If you don't, it's not using services in a very maintainable way (as in: the reason there should be a service is precisely the abstraction level it provides. By moving all the details to the client, why bother having a service?)

Anyway, if you want to solve remoting queries by sending SQL and parameters (in some form as parameters are never serializable), IMHO that's asking for problems sooner or later. Also, your repository might 'hide' the tiny detail about the o/r mapper context for you, but that's about it: the client is still very much tied to the o/r mapper you're using.

But enough architecture babble. You want the queries to be converted to sql so you can execute them directly on the server (and I don't know how you have planned to obtain the results properly ?). That's not directly possible in a way which works properly in all cases, nor should one strive for such a situation. if you want services in your architecture, provide a reasonable abstraction level so clients don't have to know about queries at all.

I do not have a dependency on the datastore, because when the application starts, it requests the service configuration. So the client will always match the datastore configurations on the server ( only the provider settings are important)

... and mappings and other meta-data so queries can even be produced on the client. In short: everything is required on the client. Otherwise no query can be produced. So every time your model changes, all clients have to be updated.

Otis.

I think this little chat about architecture is interesting ( for me at least). But i think i haven't made my point clear enough ( it is my fault ).

Let me show you more details:

a) i have a data model defined only with interfaces:

public interface ICustomer { string CustomerName {get;set;} IList<IAddress> Addresses {get;} }

b) I have a code generation process that gets an Entity Framework model. (EFModel)

c) I have the following query on the client ( configuration is external ):

var customers = from c in AppStore.Db.Table<ICustomer> select c where c.IsActive;

d) This linq query is rewritten to refer entities in the EFModel. At this stage it is no need to have the SQL. Passing the query directly to an OBjectContext from EFModel will work like a charm.

e) Now i want to expose my repository as a service. At that point the things will not work because Expression are not serializable and although serialization will work with customized metalinq there are closures and other stuff that cause problems.

So we have a WcfClientRepository that transofrms the Linq queries in SQL and runs them over the wire, only for this remoting scenario.

This is working very nicely with EF.

ursuletzu wrote:

Otis.

I think this little chat about architecture is interesting ( for me at least). But i think i haven't made my point clear enough ( it is my fault ).

Let me show you more details:

a) i have a data model defined only with interfaces:

public interface ICustomer { string CustomerName {get;set;} IList<IAddress> Addresses {get;} }

b) I have a code generation process that gets an Entity Framework model. (EFModel)

c) I have the following query on the client ( configuration is external ):

var customers = from c in AppStore.Db.Table<ICustomer> select c where c.IsActive;

d) This linq query is rewritten to refer entities in the EFModel. At this stage it is no need to have the SQL. Passing the query directly to an OBjectContext from EFModel will work like a charm.

So you have a visitor which replaces the I<something> to <something> in the expression tree, I pressume?

e) Now i want to expose my repository as a service. At that point the things will not work because Expression are not serializable and although serialization will work with customized metalinq there are closures and other stuff that cause problems.

Yes, it's in general not a good idea to do that.

So we have a WcfClientRepository that transofrms the Linq queries in SQL and runs them over the wire, only for this remoting scenario.

This is working very nicely with EF.

though you ran into a problem, otherwise you'd keep the EF stack I think

A service isn't a tier, it's a standalone application with an interface (accessable through code). This means that if you make it a tier like you try to do, the network becomes a serious bottleneck and cause of other problems (like objects on both sides of the wire which contain the same entity data but are considered being different etc. ), besides the things like having a service which accepts SQL and will run it whatever is sent to it.

Ideally, what would solve your problem is an IDataAccessAdapter implementation which is ran on the client but which simply passes on the calls to the service, and passes back the results as if it was executed locally. This will work over remoting, not WCF as everything is serializable to binary streams but predicates and relations etc. aren't serializable to XML in our framework (only entity graphs). This will get chatty though. We have a custom binary serialization framework implemented, which means you get very fast and very compact data packets (see 'Fast serialization' in the manual) and you can also encrypt/decrypt this if required (by adding processing objects, like packers, crypters etc.)

So in short you then get an IDataAccessAdapter implementing class on the client which simply remotes the data it receives in the methods to a service which has the same methods and the real DataAccessAdapter implementation, and which executes the call and returns the data received (if there's data to return of course).

For Linq, it's a bit easier because Linq is just fetching and you don't need to implement all methods in IDataAccessAdapter (which is an interface in the ormsupportclasses assembly, see the reference manual).

This method works regardless if you're fetching hierarchies, scalars etc., and it works always, as there's no sql generated on the client, just calls are made to the service.

If you want to proceed with this, I can give you details about how to do this and what you need on the client and which methods to implement get an initial call across. Again, Wcf between client-service for the query processing won't work, predicates and the like aren't serializable to XML and wcf uses always xml even in binary mode. As your setup is very low-level, I don't think the service is exposed to the outside world nor to other applications.

One caveat: the IDataAccessAdapter instance on the client has to contain a DataAccessAdapter instance for FunctionMappings, as these aren't serializable from the server (as the dataaccessadapter isn't considered a candidate to be directly serializable).

The methods and properties of the IDataAccessAdapter interface you have to implement are: - OpenConnection() (which should do nothing, the connection is opened/closed on the service automatically) - CloseConnection() (see open connection above) - KeepConnectionOpen property (just ignore input - public virtual void FetchEntityCollection(IEntityCollection2 collectionToFill, IRelationPredicateBucket filterBucket, int maxNumberOfItemsToReturn, ISortExpression sortClauses, IPrefetchPath2 prefetchPath, ExcludeIncludeFieldsList excludedIncludedFields, int pageNumber, int pageSize) (this routine fetches the entities) - public void FetchProjection( List<IDataValueProjector> valueProjectors, IGeneralDataProjector projector, IEntityFields2 fields, IRelationPredicateBucket filter, int maxNumberOfItemsToReturn, ISortExpression sortClauses, IGroupByCollection groupByClause, bool allowDuplicates, int pageNumber, int pageSize ) (routine which fetches the dynamic lists. )

Though I haven't tried it (and I saw the Projector classes aren't serializable, so it might be going wrong somewhere, and sometimes with very complex projections it goes wrong as it then uses compiled lambda's in the projectors). If you're doing just entity fetches, it should work.

Yes,

I would need some guidance on implementing the IDataAccessAdapter. Thank you. Predicates ( at least a subset ) are serializable (not by default ).

This solution, if possible, sounds nice and i will give it a try.

ursuletzu wrote:

Yes,

I would need some guidance on implementing the IDataAccessAdapter. Thank you.

I updated my post with some details. projections to anonymous types is not really going to work I'm afraid, nor are scalars, as they're fetched using projections as well.

Predicates ( at least a subset ) are serializable (not by default ). This solution, if possible, sounds nice and i will give it a try.

I meant our predicate classes (which are all serializable).

The projections to anonymous types and the like in our Linq provider is done via a projector using a compiled form of the new expression inside the expression tree (with some elements replaced with array indexers so it works on a raw list of values fed to it). This isn't serializable, so what I suggested above only works for entity fetches. Not sure if that's sufficient for your system.

Too bad. Initially i used to "remote" the whole expression, but because of anonymous types, I switched to the SQL route, which works in EF.

Give me please some sketch of how i can use the scenario you mentioned, even if some things won't work. Let me just explore the possibilities.

Thank you for your patience

ursuletzu wrote:

Too bad. Initially i used to "remote" the whole expression, but because of anonymous types, I switched to the SQL route, which works in EF.

Give me please some sketch of how i can use the scenario you mentioned, even if some things won't work. Let me just explore the possibilities.

Ok, entity fetching only, as all other linq fetches are done through projection fetches (except single values like scalars, but the projector of that isn't serializable either, I can make it serializable if you want to, but let's look at entity fetching first)

Implement IDataAccessAdapter interface on a class, e.g. ProxyDataAccessAdapter. Using visual studio you can stub out all methods / properties. Implement the ones I gave above. How, see below

Use a constructor which accepts a normal DataAccessAdapter instance and return that instance' FunctionMappings in the IDataAccessAdapter interface implementation.

On the server you need a Service which exposes a method called FetchEntityCollection with the same parameters as IDataAccessAdapter.FetchEntityCollection (the one I gave above). This service simply accepts the parameters and internally creates a new DataAccessAdapter instance and calls FetchEntityCollection with the parameters received and returns the result.

Now, in your IDataAccessAdapter implementing class, you connect to the service and you call FetchEntityCollection and you simply pass the parameters. Use remoting to connect to this service. Switch on FastSerialization on both sides (!) to make it faster (as .net's binary formatter is pretty slow)

WHen creating a linq query on the client you do:

// create a new instance of the IDataAccessAdapter implementing class
// and pass a real instance to use function mappings in linq
EntityCollection<CustomerEntity> customers = null;
using(ProxyDataAccessAdapter adapter = 
    new ProxyDataAccessAdapter(new DataAccessAdapter))
{
    LinqMetaData metaData = new LinqMetaData(adapter);
    var q = from c in metaData.Customer
            where c.Country == "Germany"
            select c;
    
    // now simply execute it.
    // for example:
    customers = ((ILLBLGenProQuery)q).Execute<EntityCollection<CustomerEntity>>();
    // you can also traverse it in a foreach for example, same thing.
}

This will under the hood call ProxyDataAccessAdapter.FetchEntityCollection(...) and that method calls the service and fetches the data. Entities come back and are returned as normal.

This as an illustration how it works. Of course, if you want to use your interfaces and post-processing, you have to convert the whole query to different elements, and implement the interfaces on the entities. (though I doubt if that's useful anymore).

Again, not sure if this is worth the effort as our entities aren't poco classes.