ad hoc where clause

Posts   
 
    
mkamoski avatar
mkamoski
User
Posts: 116
Joined: 06-Dec-2005
# Posted on: 29-Jun-2006 20:55:43   

How can one write a truly ad hoc where clause and then fetch an entity collection based on it?

That is, pass something like this...

" where CompanyName='SomeCompanyName' "

...to FetchEntityCollection?

Is that possible?

If so, how?

(I know this is not "best practices"; but, note that right now it is just an experiment.)

Thoughts?

Please advise.

Thank you.

-- Mark Kamoski

Otis avatar
Otis
LLBLGen Pro Team
Posts: 39588
Joined: 17-Aug-2003
# Posted on: 29-Jun-2006 21:39:47   

Create a derived class from Predicate and emit your SQL in an override of ToQueryText simple_smile

Frans Bouma | Lead developer LLBLGen Pro
mkamoski avatar
mkamoski
User
Posts: 116
Joined: 06-Dec-2005
# Posted on: 29-Jun-2006 21:46:25   

Otis wrote:

Create a derived class from Predicate and emit your SQL in an override of ToQueryText simple_smile

Ah ha.

To cast an old adage, one can say...

"Where there is an Otis, there is a way."

Thank you.

-- Mark Kamoski

Otis avatar
Otis
LLBLGen Pro Team
Posts: 39588
Joined: 17-Aug-2003
# Posted on: 29-Jun-2006 22:31:34   

haha smile

Frans Bouma | Lead developer LLBLGen Pro
Rob
User
Posts: 54
Joined: 17-Sep-2004
# Posted on: 09-Jun-2007 08:04:23   

Hi!

Maybe I am just dreaming. But can this be a way to use your own Sql-statement but within the LLBLGen framework?

I sometimes have queries that either are too hard to convert to predicates or (from reading the forums) sometimes are not even possible to convert. That is unless I resort to sprocs or views, but I am not willing to do that since I am working on an enterprise level product with nearly 200 tables. If I had to add a sproc or view everytime I could not solve something with predicates it would be very messy indeed. To me manageability is everything and wading through sprocs and views in order to work out what a small little getXXXX function returns is simply not manageble.

I can obviously use .NET's own SQLClient capabilities to return DataTables. But I have not yet been able to work out how I can invoke a LLBLGen Transaction into it. If somebody can give me a pointer towards how that can be done I would probably simply just do that.

If however, that is not possible, then I wonder if deriving a class from Predicate would give me the freedom to come up with any SQL-query and be able to attach my LLBGen transaction to it. That would possibly be even better. Since then I could perhaps use the GetMulti to return Entity Collections, and perhaps use GetScalar for simple getXXX functions.

If it is possible to use a derived Predicate class for this I would be very grateful for some example code. That is, what the class would look like and how it would be used.

Hopefully,

!Rob

jbb avatar
jbb
User
Posts: 267
Joined: 29-Nov-2005
# Posted on: 11-Jun-2007 16:27:19   

Hello,

if you derive a class from Predicat, you will create a custom predicate but that is only a predicate and not a complete request. Btw you can create as complicated where clause as you want by deriving the Predicate. What sort of query you cannot do directly in llbl, did it cames from a predicate or a custom function?

Rob
User
Posts: 54
Joined: 17-Sep-2004
# Posted on: 12-Jun-2007 01:46:08   

Hi!

I realize that predicates are only a part of the story and that I probably cannot use them for this purpose.

I have two main problems.

  1. Sometimes the SQL is complex enough so that I cannot translate it to llbl, I will give you two examples below.

  2. I am writing an enterprise application. I don't want to hard code views to data in the user interface, so I have tables in the database that store SQL-queries and column mappings and such.

In this application I sometimes refer to SQL stored in database during transactions. Like for instance I write share certificates. Different clients have different layouts so I store the locations on a pdf-template and SQL to retrieve the data in database. Sometimes I write multiple certificates and therefore write back to the database which ones have been written. This means that I have a transaction going while calling the database with normal SQL-queries. This means that potentially I might be requesting data from a table that is locked by my transaction.

So to solve my problem number 2 I would like to know if there is a way to attach the llbl transaction (or its' underlying db transaction) to a normal data-adapter SQL request?

Here are two examples of seemingly simple SQL-queries that I don't know how to do in llbl.


SELECT 
  MAX(AccountNumber) 
FROM 
  Account 
WHERE 
  ProductID = " + productID.ToString() + " 
AND 
  LEN(AccountNumber) = 
  (
  SELECT 
    LEN(GenerateAccountNumberPattern) 
  FROM 
    Product 
  WHERE 
    ProductID = " + productID.ToString() + ")


SELECT 
  Sum(P.Price * C.Quantity) 
FROM 
  Certificate C
INNER JOIN 
  Investment P ON P.InvestmentID = C.InvestmentID
WHERE 
  C.CertificateStatusID = " + Constants.CertificateStatus___Issued.ToString() + 
AND 
  C.ProductID = " + this.ProductID