can I use native SQL to materialize entities?

Posts   
 
    
hp
User
Posts: 3
Joined: 27-Aug-2008
# Posted on: 27-Aug-2008 19:25:42   

Hi there

I can't believe the question weren't answered yet, but I couldn't find any answer so:

Is there any possibility to use arbitrary native sql queries (eg. oracle CONNECT BY) to fill entity classes?

It seems very important in case of any ORM to have a possibility to fall back to proven solutions until some constructions are added (or even discovered by the user) to the orm.

thanks HP

jmeckley
User
Posts: 403
Joined: 05-Jul-2006
# Posted on: 27-Aug-2008 20:57:46   

I can't believe the question weren't answered yet, but I couldn't find any answer so:

actually this is the first question developers ask when they here about ORM for the first time. and the response is always the same. Avoid at all costs.

If you must implement raw sql get the connection object from the adapter (not sure where to get this for SS). and execute the sql using ADO.Net.

It seems very important in case of any ORM to have a possibility to fall back to proven solutions until some constructions are added...

what constructions are missing? you can get an entity by id, or unique constraint. These are the only 2 methods to guarantee uniqueness. you can get a collection of entities using a predicate. that's all there is to entities.

if you want to create an aggregation (readonly) of data the you have typed lists, dynamic lists and projections. but you still need to go through the ORM to generate the query. Again, that's the point of ORM.

or even discovered by the user

This is the fault of the user, not the ORM. It's the creators job to make the ORM intuitive and efficient. It's the users job to understand how to use the ORM.

Walaa avatar
Walaa
Support Team
Posts: 14946
Joined: 21-Aug-2005
# Posted on: 28-Aug-2008 10:16:56   
hp
User
Posts: 3
Joined: 27-Aug-2008
# Posted on: 28-Aug-2008 11:53:16   

jmeckley wrote:

If you must implement raw sql get the connection object from the adapter (not sure where to get this for SS). and execute the sql using ADO.Net.

I can always use ADO.Net to execute any sql code, my question is how to create LLBLGEN entities based on that code!

see http://www.hibernate.org/hib_docs/nhibernate/html/querysql.html for example.

In nhibernate I can provide arbitrary sql code and an entity type that should be returned - the engine will match properties with column aliases and return typed entities. The same with Entity Framework - there's a Materializer class that return entities for a given DbReader argument.

My question is whether and how can I do it with Llblgen Pro. It's that simple.

jmeckley wrote:

It seems very important in case of any ORM to have a possibility to fall back to proven solutions until some constructions are added...

what constructions are missing? you can get an entity by id, or unique constraint. These are the only 2 methods to guarantee uniqueness. you can get a collection of entities using a predicate. that's all there is to entities.

no, it's not. I may want to filter them using CONNECT BY in Oracle, I may create their fields using FOR XML PATH in Sql Server, I may use hundred other database specific ways to filter/add content to them, so please stop telling me that I don't need it, unless you're saying that LLBLGEN is only for toy purposes so I will by retrieving objects only by id anyway. Of course I want it only for exceptional situations, but I have to know that there is such a possibility before I decide to choose it for the project I'm responsible for.

Walaa avatar
Walaa
Support Team
Posts: 14946
Joined: 21-Aug-2005
# Posted on: 28-Aug-2008 12:35:27   

You may use a Stored Procedure to return a resultSet which you can project to an EntityCollection using a DataReader & Projection.

Generated code - Fetching DataReaders and projections, Adapter

hp
User
Posts: 3
Joined: 27-Aug-2008
# Posted on: 28-Aug-2008 12:48:20   

Great, that's what I meant. Is it constrained to stored procedures, or can I also use ad-hoc sql for projections?

thanks HP

Walaa avatar
Walaa
Support Team
Posts: 14946
Joined: 21-Aug-2005
# Posted on: 28-Aug-2008 12:50:59   

It is limited to SPs, to be able to project to an EntityCollection.

Otis avatar
Otis
LLBLGen Pro Team
Posts: 39588
Joined: 17-Aug-2003
# Posted on: 29-Aug-2008 17:41:29   

You can use native SQL to materialize entities, using projections, it's not necessarily restricted to SP, although it will require extra work.

What you need is a RetrievalQuery object which will form the query and the necessary projectors as described in the manual, where it's explained how to project entities from a proc result.

If you look at the RetrievalProcedures.cs file generated for retrieval procs, you'll notice code like:

/// <summary>Creates an IRetrievalQuery object for a call to the procedure 'CustOrderHist'.
/// 
/// </summary>
/// <param name="customerId">Input parameter of stored procedure</param>
/// <returns>IRetrievalQuery object which is ready to use for datafetching</returns>
public static IRetrievalQuery GetCustOrderHistCallAsQuery( System.String customerId)
{
    RetrievalQuery toReturn = new RetrievalQuery( new SqlCommand("[Northwind].[dbo].[CustOrderHist]" ) );
    toReturn.Parameters.Add(new SqlParameter("@CustomerID", SqlDbType.NChar, 5, ParameterDirection.Input, true, 0, 0, "",  DataRowVersion.Current, customerId));

    toReturn.Command.CommandType = CommandType.StoredProcedure;
    return toReturn;
}

This is generated code to obtain a RetrievalQuery object to directly execute it to obtain a resultset to project onto entities.

You can build manually a RetrievalQuery object using hardcoded SQL, as you simply feed it with a Command which has its commandtype set to text and which receives the required parameters from you.

After you've created the RetrievalQuery, you can follow the same path as you would with a proc. So the extra work is in building the RetrievalQuery object.

Frans Bouma | Lead developer LLBLGen Pro