Wilson ORMapper Forums : OPath Queries

Returning all object that not in a ManyToMany relationship

Thu, 08 Nov 2007 22:07:44 GMT

When I use a query similar to the following:

OPath<User>("!Group[1==1]")

I get an exception saying that the not operator is only valid for boolean types. Any idea what I might be doing wrong here or if there is a simple fix?

Thanks!

How to: convert OPath queries to database independent sqls

Fri, 14 Sep 2007 20:50:52 GMT

I have a few questions about convert OPath expressions to database independent sqls.

Say you have the following sql:

select field from table where fieldtwo='Value'

I think the code would be somewhat like the following:

Dim helper As QueryHelper = orEngine.QueryHelper()

Dim sql As String = String.Format("select {0} from {1} where {2}", _

helper.GetFieldName("table.field"), _

helper.GetTableName("table"), _

helper.GetExpression("table.fieldtwo", record.Code))

what I would rather do is:

Dim helper As QueryHelper = orEngine.QueryHelper()

dim query as new OPathQuery(of Table)(table.fieldtwo="Value")

Dim sql As String = String.Format("select {0} from {1} where {2}", _

helper.GetFieldName("table.field"), _

helper.GetTableName("table"), _

query.???))

The old method would be:

Dim helper As QueryHelper = orEngine.QueryHelper()

dim query as new OPathQuery(of Table)(table.fieldtwo="Value")

Dim sql As String = String.Format("select {0} from {1} where {2}", _

helper.GetFieldName("table.field"), _

helper.GetTableName("table"), _

helper.GetExpression("<OPath String>"))

The QueryHelper.GetExpression method that takes an OPathQuery object as it's parameter, is now marked as 'Obsolete'. So what is the proper method to get a database independent sql clause from the OPath object? I don't see any methods on the OPathQuery Object that would return the 'transformed' sql.

The 'Obsolete' statement says:

Use the newer full OPath Engine instead -- see the OPathQuery and OPathQuery<T> classes.

I am attempting to use above type of expressions in the ExecuteScalar method. How is this done?

Thanks

Returning all object that not in a ManyToMany relationship

Thu, 30 Aug 2007 14:45:17 GMT

Imagine you were trying to you have a simplistic Users and Groups relationship where there is a ManyToMany expressed between users and groups. If you want to find all the Users that are not in any Groups what is the best OPath Query.

The best query I have found looks like this:

OPath<User>("!Group[1==1]")

How ever this seems to be counterintuitive and produces some verbose SQL.

This looks better but does not work because it results in a logically no-op SQL statement. OPath<User>("Group[!notNull(GroupID)]") because it evaluates the groups a user is in before it calculates which one are not in groups.

Thanks

James

Querying child objects from same table

Tue, 21 Aug 2007 17:48:44 GMT

I found the work around. It was easy to find once I discovered that this is referred to as an embedded object. The work around is to not use dot notation in the alias field:

<attribute member="address.line" field="AddressLine" alias="AddressLine" />

I can live with that.

I know nothing about the OPath parser, but could it check the member field for dot notation and use that to recognize an embedded object?

Querying child objects from same table

Tue, 21 Aug 2007 17:17:40 GMT

Can anyone take a stab at this? I have inherited a database with the same address fields in many tables, and I'd like to represent them with an Address object. Normalizing isn't an option at this point.

Querying child objects from same table

Mon, 13 Aug 2007 18:23:08 GMT

I have a mapping similar to what Paul has posted in the examples, so I'll use his to illustrate my problem:

 <entity type="Contact" table="Contacts" keyMember="id" keyType="Auto"
      sortOrder="ContactType DESC, ContactName ASC" typeField="ContactType" typeValue="?">
    <attribute member="id" field="ContactId" alias="Id" />
    <attribute member="name" field="ContactName" alias="Name" />
    <attribute member="address.line" field="AddressLine" alias="Address.Line" />
    <attribute member="address.city" field="AddressCity" alias="Address.City" />
    <attribute member="address.state" field="AddressState" alias="Address.Staet" />
    <attribute member="address.zip" field="AddressZip" alias="Address.Zip" />

Is it possible to write an OPath query for "Contact.Address.City='Boston'"? I receive the exception "Type 'VEIC.Mobile.Library.UserInfo' does not have a relationship named 'Audit' in the entity map." when trying to do something similar. I could throw a fake relationship in there to get it to work, but I'd rather not.

Thanks, Jamie

OPathQuery TOP x

Tue, 24 Jul 2007 17:36:22 GMT

I have implemented TOP x for use with an OPathQuery. It's a dirty hack and i'm not proud of it. It probably only works with MS SQL. Again,its a dirty hack. If you'd like to see this hack let me know.

Now onto implementing OPathQuery paging.... dont hold your breath.

One-to-many relationships with different number of key fields fails

Mon, 02 Jul 2007 21:17:35 GMT

The automatic relationship features expects both sides to be using the same number of fields as the parent's primary key. If that is not the case, then you wil need to retrieve related records with a query of your own construction.

Thanks, Paul Wilson

One-to-many relationships with different number of key fields fails

Mon, 02 Jul 2007 20:04:33 GMT

Consider the following schema:

create table CalendarDay (
year int, month int, days int,
constraint pk_calendarDays primary key ( year, month, day )
)

create table YearNote (
year int, note varchar(200),
constraint pk_yearNotes primary key ( year ),
constraint fk_yearCal foreign key ( year ) references calendarDays ( year )
)

Each CalendarDay has a one-to-many relationship with YearNote. However, the relation is based on a subset of CalendarDay's primary keys.

This fails in the OPath query engine, which expects the number of primary key and foreign key fields to match. Obviously, the problem is not trivial -- how would the query parser know which fields to match in the case above?

Does anyone have suggestions on how to work around this issue?

Bad SQL being returned by an OPath query.

Tue, 01 May 2007 23:18:16 GMT

Thank you Jeff on your detailed reply; all points are noted and many are salient.

I would point out that this code came from my unit test that I have written for entity/domain mapping purposes (and to learn more about the ORMapper).

Regarding the ID column fields (i.e CarePackageID, PaperworkID), these are actually protected field's (that map directly to the ObjectHolder key member), as from an entity perspective they are non-sensical; and are there purely for implementation purposes only (i.e. WilsonORMapper). In my object domain, navigation is accomplished via object traversal alone. When an object is not lazy loaded then I don't actually reference these fields in the objects. Yes I appreciate that at times this may result in less than optimal performance, but if this come's and issue then I may well revisit the above.

As a side note I did try to access those members when protected, but if my memory holds true, you only expect public properties (which is sensible enough).

I do like the parameterize query comment though, wasn't aware that this was possible using oPath, I guess it may be worth me reading all the literature available on this Wilson stuff ;-)

I have moved on from that point, and had a holiday in between, when I have accomplished my next task I will look at the mappings to determine if they are as expected.

A big thank for your help here and for your contributions to the ORMapper.

Regards

Stephen Smith

Bad SQL being returned by an OPath query.

Thu, 12 Apr 2007 22:58:32 GMT

Thanks for your detailed post.

First, a couple things in terms of “best practices” with regard to how the query is setup and executed:

In your query, I noticed you are traveling to related tables unnecessarily to test the column values. For example rather than traversing the CarePackage table to test the PK column, you could just test the FK column on your root table. Same for the Paperwork table. So, the original query:

string.Format("CarePackage.ID == '{0}' AND Paperwork.ID == '{1}'", inserted.CarePackage.ID, inserted.Paperwork.ID);

could be simplified/optimized down to something like this:

string.Format("CarePackageID == '{0}' AND PaperworkID == '{1}'", inserted.CarePackage.ID, inserted.Paperwork.ID);

Also note that we can optimize the parameters used in the string format:

string.Format("CarePackageID == '{0}' AND PaperworkID == '{1}'", inserted.CarePackageID, inserted.PaperworkID);

These two things are small changes in terms of the character count, but it can have a very positive impact on performance since (a) you avoid lazing loading two objects in order to get their ID values, and (b) you reduce the query to one that only involves a single table.

Going a step farther, we can “parameterize” the query to remove the string building via string.Format():

"CarePackageID == ? AND PaperworkID == ?"

The parameter values are passed when the query is executed:

manager.GetObjectSet(query, inserted.CarePackageID, inserted.PaperworkID);

Doing this causes OPath to produce a parameterized SQL statement, which makes it a LOT easier for SQL to reuse the execute plan. I’ve read that SQL Server (assuming that’s what your use) will internally parameterize a query in the hopes of not having to build a new plan every time, but I haven’t tested this to verify. Either way, I can’t imagine it being as fast as query that already contains the parameters.

Anyway, enough on that.

Now, regarding the suspected bug in the generated SQL statement: I’m not yet convinced it’s actually a bug in the OPath engine. I think the bug is in the mapping file. The “CarePackage” relationship in has the “PaperworkID” column specified as the key field:

Because this is the ManyToOne relation, that field is referring to the FK on the CarePackagePaperwork table. As least I’m pretty sure it dones. If I’m right about that, then the relationship is relating the PK of the CarePackage table (e.g., the CarePackageID column) to the PaperworkID column of the CarePackagePaperwork table. I’m guessing this is not what you actually want. If this mapping file was auto-generated (how many aren’t these days), than it would mean the bug is in the map generator.

Check the relationship in the database to make sure it’s correct (been there one too many times), then check your code generator.

Try that and let me know if you can’t track down the source of the problem and it still looks the OPath Engine is to blame.

Thanks,

Jeff Lanning

Bad SQL being returned by an OPath query.

Wed, 11 Apr 2007 10:39:44 GMT

Background:

ORMapper release date 4/11/2006.

I have three entity objects, a CarePackage, Paperwork and a joining table/entity CarePackagePaperwork, that is composite Key'd on CarePackageID and PaperworkID.

The mapping file looks like the following (CarePackagePaperwork):

<relation relationship="ManyToOne" member="fCarePackage" field="PaperworkID"

type="Smithfield.AGCare.CarePackage" alias="CarePackage" lazyLoad="true" />

<relation relationship="ManyToOne" member="fPaperwork" field="PaperworkID"

type="Smithfield.AGCare.Paperwork" alias="Paperwork" lazyLoad="false" />

</entity>

(If required I can post/email mappings for all entities and class files)

Problem:

I use the following opath string

string.Format("CarePackage.ID == '{0}' AND Paperwork.ID = '{1}'", inserted.CarePackage.ID,

inserted.Paperwork.ID))

And invoke the opath query like this

ObjectSpace manager = PersistencyManager.Mapper;

OPathQuery<CarePackagePaperwork>query = new OPathQuery<CarePackagePaperwork>(opath);

return manager.GetObjectSet(query);

The return is always NULL and when viewing the SQL generated from the above it is:

Select: SELECT a.[CarePackageID], a.[PaperworkID], a.[RecievedDate], a.[IsValid], a.[CommunicationMedium]
FROM [CarePackagePaperwork] a
WHERE (EXISTS
(
SELECT *
FROM [CarePackage] b
WHERE (a.[PaperworkID] = b.[CarePackageID]) AND (b.[CarePackageID] = 'TESTTE0711')
) AND EXISTS
(
SELECT *
FROM [Paperwork] c
WHERE (a.[PaperworkID] = c.[PaperworkID]) AND (c.[PaperworkID] = 'Test')
));

If you view the sql carefully you will spot the line:

WHERE (a.[PaperworkID] = b.[CarePackageID]) AND (b.[CarePackageID] = 'TESTTE0711')

This is obviously wrong and should read!

WHERE (a.[CarePackageID] = b.[CarePackageID]) AND (b.[CarePackageID] = 'TESTTE0711')

Any help you can provide would be very helpful!

Regards

Stephen Smith

Smithfield Solutions Ltd

Performing Group By

Tue, 27 Mar 2007 18:48:40 GMT

You can't do group by clauses through the O/R mapper. You can use a stored procedure or view to expose your data. Add the locationid to the resultset and load entity objects based on those values.

Performing Group By

Sun, 25 Mar 2007 22:23:11 GMT

Hello Guys,

Please Help me out as to How would I use the ORMapper to Generate a Query Below. The query gives me the Top 5 locations where the Live Jobs are posted.

select Top 5 count(*) as [LocationCount],ss_location.name as [Name] from ss_job
inner join SS_location on
ss_location.id=ss_job.Locationid
where ss_job.active=1 and DateExpired>Getdate()
group by [Name] order by [LocationCount] DESC

Thank you,

Rahul Sharma.

Checking Many-to-One Relation For Missing Relation

Thu, 15 Mar 2007 06:00:02 GMT

The EXISTS keyword can be used to test for records in a related table... but it's not in the demo version of WORM. I need to get with Paul and get a new release published that has support for this.

However, since you are going from many-to-one you probably don't need to use EXISTS and can instead just test the FK column for null. Something like the following might work:

OPathQuery<Transaction>("IsNull(SubjectMatterExpertId)")

Hope this helps,
Jeff Lanning

Checking Many-to-One Relation For Missing Relation

Tue, 13 Mar 2007 19:56:53 GMT

During evaluation of the demo, I am testing how to check if a relation exists.

I have an entity (Transaction) and an entity (SubjectMatterExpert). Transactions are assigned to SubjectMatterExperts with a Many-to-One relation. Everything is working fine with loading and persisting.

What I want to do is use the OPathQuery.GetList<Transaction> to retrieve a list of Transactions that are not assigned to a SubjectMatterExpert (i.e. SubjectMatterExpertId is null). I do not want to expose the internal member (_subjectMatterExpertId) to the consumers of my objects.

I feel like I'm missing the obvious, but after searching the forums, I haven't come up with a solution. If OpathQuery won't do it, please tell me what will.

Tracy

How to determine if child records exist

Tue, 13 Mar 2007 19:56:27 GMT

Hi Jeff,

I am also working with OPath since a while. Some time ago, we had some contact in this forum, therefore I am also interested in the latest version of the OPath Engine

Could you mail it to me

stefan.roesch@wega-informatik.ch

Thanks and regards

Stefan

Update multiple records

Tue, 13 Mar 2007 19:52:45 GMT

I'm not sure if OPath gives you anything to help with batch updates, but there is an ExecuteUpdate method that may be good enough for you.

Thanks, Paul Wilson

Update multiple records

Tue, 13 Mar 2007 19:51:55 GMT

I am trying to build a query to update many records according to a specific WHERE clause.

Icannot find any examples on such thing.

can you please help me?

Using A Lookup Property In A Query

Thu, 08 Feb 2007 21:01:47 GMT

I probably will just need the entity node containing that lookup field, but you never know. You could post your entity here if you want, or just email me the whole mapping file.

Thanks,
Jeff Lanning

Using A Lookup Property In A Query

Wed, 07 Feb 2007 00:12:29 GMT

I have tried placing the traversal outside the isnull function and it has the same effect (I noticed that solution in another post).

Do you want me to send you my entire mapping file to an email address or post it here? Or do you just want part. It's kinda large.

Using A Lookup Property In A Query

Mon, 05 Feb 2007 04:14:23 GMT

Have you made any progress on this issue? If not, could you send me a bit more of your mapping file? I don’t use lookup fields very often so this could easily be a bug.

One thing to try in the mean time is to place the traversal outside the isnull (e.g., design[isnull(orderStatus)] ). The expression you are using may not be valid and could be causing a error much farther down the pipe.

Thanks,
Jeff

Using A Lookup Property In A Query

Tue, 30 Jan 2007 00:18:36 GMT

Is this possible? I keep getting an exception:

System.Exception: Could not find table alias for property 'orderStatus' in entity 'Design'.

<lookup member="orderStatus" field="status"
table="orders" foreignKey="order_id" lookupKey="order_id" />

string where = "isnull(design.orderStatus)";

Am I missing an attribute somwhere?

oPath engine that supports the EXISITS keyword

Fri, 12 Jan 2007 01:09:45 GMT

In the previous thread Jeff mentioned that he had a version of the oPath engine that supported Exists and described its use for checking if a child relation exists or not, can we have access to this buid?

Thanks

Floyd

How to determine if child records exist

Wed, 10 Jan 2007 18:12:36 GMT

Jeff, can i get a copy of this opath engine with the exists support?

Cheers,

Floyd Price

f.price@componentworkshop.com

How to determine if child records exist

Fri, 05 Jan 2007 07:16:03 GMT

Hi Bill,

You can use the EXISTS keyword... but support for it has not made it into a release version of WORM yet. I have a version of 4.2.2.1 with an updated OPath Engine which supports EXISTS and IIF. If you are interested, just send me your email address and I can send it to you.

The current requirement for using EXISTS is that it must start the expression with a relationship traversal and have some sort of test within that.

For example:

OPathQuery<Customer>("EXISTS(Orders[Freight > 100])");

where Orders is the traversal giving the exists context, and Freight > 100 is the test in that context.

In you case, the expression might look something like this:

OPathQuery<Parent>("EXISTS(Children(1 = 1)")

The "1 = 1" part is something of a hack until I can figure out how to get around it. You should be able to have a relationship name in the exists without a test (e.g., EXISTS(Children)) but I haven't had the mental energy yet to figure out what needs to be modified in the parser.

Sorry for the late reply; sort of dropped everything during the holiday season.

- Jeff Lanning

How to determine if child records exist

Thu, 21 Dec 2006 16:17:56 GMT

I'm fairly comfortable with SQL, but OPath is new to me. I've looked through the OPath documentation and I've done some searching on the Web, but I haven't been able to find any examples showing how to construct an OPath query which retrieves only objects which have at least one child in a child collection.

I'd like to retrieve records in table 'Parent' which have a record in table 'Child' where Parent has a one-to-many (foreign key) relationship with Child.

The SQL I'm trying to generate would look something like:

SELECT parent_id FROM Parent WHERE EXISTS (SELECT * FROM Child WHERE Child.parent_id = Parent.parent_id)

OR

SELECT DISTINCT parent_id FROM Parent P INNER JOIN Child C ON P.parent_id = C.parent_id

OR

SELECT DISTINCT parent_id FROM Child

OR

SELECT parent_id, COUNT(child_id) FROM Parent P INNER JOIN Child C ON P.parent_id = C.parent_id GROUP BY parent_id HAVING Count(child_id) > 0

Does anyone have any hints?

Thank you,

Bill Daly

parameterising OPath queries

Thu, 21 Dec 2006 16:17:45 GMT

many thanks :)

opath query and paging

Thu, 21 Dec 2006 16:17:43 GMT

Hi jeff

thanks for your reply. very informative :)

parameterising OPath queries

Wed, 08 Nov 2006 10:38:10 GMT

OPathQuery query = new OPathQuery(typeof(SettingValue), "FormatList.FormatID == ? && SettingID == ?");

MyObjectSpaceObject.GetObjectSet(typeof(SettingValue), query, formatId, settingId);

or for .NET 2:

OPathQuery query = new OPathQuery<SettingValue>("FormatList.FormatID == ? && SettingID == ?");

MyObjectSpaceObject.GetObjectSet<SettingValue>(query, formatId, settingId);

opath query and paging

Thu, 02 Nov 2006 10:08:12 GMT

Paging has become my personal nemesis. I've poured god knows how many hours into solving the problem but I'm pretty much back at the drawing board at this point and haven't worked on it for months.

This problem is made particularly bad due to a number of things. Supporting Access is really hard, almost impossible, so I'll likely be putting a NotSupportedException in that code path. Support for SQL Server 2000 and 2005 almost has to be hard coded into the engine because MS doesn't like the LIMIT keyword for some reason. In addition, I've found it hard to provide any sort of flexibility via the CustomerProvider configuration settings. SQL 2000 support is really the kicker though. All sorts of nasty problems surface as soon as you have joins, table aliases, and parameters in the core select statement. Overcoming this will require either a compete overhaul of the OPath engine or some really smart regex patterns, or the like, to chop up the results from the compiler, clone the fragments into the right pattern, then re-alias all the tables. When my day job settles down a bit I'll try to hack some post-processor thing together that could be used to build paging queries. If anyone has messed around with approach at all, I'd love to see it.

Regarding specifying the paging values, I'm still flexible here but I don't think I want to take the page size, page number route. The last version of WinFS that had an OPath engine in it used the keywords "Skip" and "Take". If you combine that with the "Fluent Interface" [Fowler] I'm working on you would get something like this:

List<Address> addresses = OPathQuery<Address>()
   .Where("City = ? AND State = ?")
   .Sort("ZipCode ASC, StreetLine1 ASC")
   .Skip(40).Take(10)
   .Parameters(someCity, someState)
   .GetList(engine);

Or a number of variations on that theme:

List<Address> addresses = OPathQuery<Address>()
.Where("City = ? AND State = ?")
.Sort("ZipCode ASC, StreetLine1 ASC")
.Skip(40).Take(10)
.GetList(engine, someCity, someState);

List<Address> addresses = OPathQuery<Address>()
   .Where("City = ? AND State = ?", someCity, someSate)
   .Sort("ZipCode ASC, StreetLine1 ASC")
   .Skip(40).Take(10)
   .GetList(engine);

Perhaps even:

List<Address> addresses = engine.GetList<Address>()
   .Where("City = ? AND State = ?", someCity, someSate)
   .Sort("ZipCode ASC, StreetLine1 ASC")
   .Skip(40).Take(10);

- Jeff Lanning

parameterising OPath queries

Thu, 02 Nov 2006 09:33:39 GMT

Just replace each variable in your query with a question mark, like so:

OPathQuery query = new OPathQuery(typeof(SettingValue), "FormatList.FormatID = ? && SettingID = ?);

Then pass the values when you execute the query:

ObjectSet results = WormEngine.GetObjectSet(formatID, settingID);

- Jeff Lanning

opath query and paging

Tue, 31 Oct 2006 14:25:47 GMT

Hi Jeff

Last time I looked I saw that paging was not yet implemented for opath queries. I know you're working on it. Aren't you a star! I'm sitting with baited breath patiently waiting for paging support

My question is hypothetical. When paging is released will it be based on an object design whereby the opath query object has properties like startIndex and maxRows?

thanks

andrea

parameterising OPath queries

Tue, 31 Oct 2006 14:25:37 GMT

I have an opath query as show below. How would i parameterise it please?

OPath query query = new OPathQuery(typeof(SettingValue), " FormatList.FormatID = '" + formatID + "' && SettingID='" + settingID + "'");

many thanks

anndrea

OPath query in a self-referencing table

Tue, 03 Oct 2006 02:19:57 GMT

Hi Jeff,

I'm so glad to get a response on this :o) It's a fairly important part of this app so I fretted about it a bit. I have not made further attempts to do it with OPath - instead I have kludged it using raw SQL to project just Dog IDs and iterating through the list to get the Dog objects. Nasty!! It's definitely not ideal since one of my original aim for the app is to be as DMBS-neutral as possible.

I will try your OPath query but at the moment I've left OPath alone - especially since I couldn't get it working for a fairly simple query that I posted on another thread.

Cheers,
Dany.

A simple OPath question

Tue, 03 Oct 2006 02:10:15 GMT

Sorry, forgot to mention, the mapping and classes were generated with Paul Welter's latest template. I have never ventured to manual mapping generation yet...

A simple OPath question

Tue, 03 Oct 2006 02:08:16 GMT

Hi Jeff,

Yup, I'm still having problems. It's more like the former of the two you mentioned. The collection "pups" always has zero count.

I'm using SharpDevelop and fairly new at it so not sure how all the features work. Not sure if I can debug and see what the SQL query from that OPath looks like, nor can I "profile" the DB calls like MSSQL.

Thanks,
D.

OPath query in a self-referencing table

Mon, 02 Oct 2006 07:24:22 GMT

Hi Dany,

Sorry for the delayed reply. Have you figured this problem out yet? I’ve been kicking this around a bit but nothing easy is coming to mind... and nothing nearly as efficient as your hand built sql query).

Breaking down your joins, I get the following:

M = Mother
GP = Mother.Mother or Mother.Father
GGF = Mother.Mother.Father or Mother.Father.Father

From this, my best guess is that the following query would be close:

OPathQuery<Dog>("Mother[(Mother.Father.ID = Father.ID) OR (Father.Father.ID = Father.ID)]")

However, I’m pretty sure this is unsupported syntax. It might just work but I have a feeling you’ll get an error about traversals on both sides of the expression. If so, that can be solved using the parent traversal operator:

OPathQuery<Dog>("Mother[Mother.Father[ID = ^.^.^.Father.ID] OR Father.Father[ID = ^.^.^.Father.ID]]")

The generated query is going to look really nasty but I have your dataset is pretty small so performance is not terribly important.

Let me know if this works or gets you on the right track.

- Jeff Lanning

Paging / Sorting / Dynamic Query!!!

Mon, 02 Oct 2006 06:49:53 GMT

There is no simple answer to this at the moment. The best I can recommend is to either manually build the query using ObjectQuery and QueryHelper or get the generated sql by compiling the OPathQuery and doing some string parsing to build a paging version.

- Jeff Lanning

For gods' sake!!

Mon, 02 Oct 2006 06:45:35 GMT

Sorting can be done by specifying the sort expression when building the query. Paging is not currently supported. This is something I have spent a lot of time working on but have not gotten the code to a point where it is ready for release. Perhaps things will calm down a bit at work over fall/winter and I can invest more time into tackling this issue. Until then your best option is to either use an ObjectQuery with the QueryHelper to build the paging query manually or get the generated sql by compiling the OPathQuery and do some string parsing to build a paging version.

- Jeff Lanning

A simple OPath question

Mon, 02 Oct 2006 06:39:06 GMT

Hi Dany,

Are you still having trouble? If so, is the problem with the OPath query not returning the correct results or the objects returned from the query not having the correct data in the collections? If it's the latter as I suspect, than there is likely something up with the mapping file/entity class.

Thanks,
Jeff Lanning

A simple OPath question

Wed, 27 Sep 2006 11:12:55 GMT

Hi everyone,

I have used Paul's ORM for a few days now and really enjoying it. Things are beginning to work quite nicely! Today was the first time I wanted to use an OPathQuery.

Here's what my SQL query looks like:

select p.petname
from dog p
inner join dog s on p.sire = s.id
where s.petname = 'turi'

The Dog object was mapped by Paul Welter's template to have a SireDogObject

Here's my OPath query and the code:

OPathQuery<Dog> query = new OPathQuery<Dog>("SireDogObject.PetName == ?");
Collection<Dog> pups = DataManager.ObjectSpace.GetCollection<Dog(query,"Turi");

Well, the SQL query returns two rows but the pups collection contains 0 objects! It feels like I have a fundamentally wrong understanding of how OPath works. Can someone please help put me on the right track?

Many thanks,
Dany.

For gods' sake!!

Tue, 26 Sep 2006 09:08:56 GMT

I need to do paging and sorting given an OPath query? How?

Paging / Sorting / Dynamic Query!!!

Tue, 26 Sep 2006 09:08:53 GMT

Hi,

I have an OPathQuery and i want to call GetObjectSet. Also i have a object[] of query parameters. What should i do?

I tried converting OPathQuery to ObjectQuery but i could not. I tried using CompiledQuery but it doesnt support paging and sorting?

Please advice.

Thanks

-Shadi Mari

OPath query in a self-referencing table

Tue, 26 Sep 2006 09:08:39 GMT

Hi folks,

I am new to the whole ORM usage, let alone the OPath language. The helper tool has interpreted the mapping and object correctly. Here's the scenario:

I have a Dog table with columns: Id, Name, Mother, Father, where Mother and Father are self-referencing foreign keys.

If you're familiar with animal breeding then I'm after Dogs resulting from line breeding, i.e. Grandfather/Granddaughter mating. No bluegrass music jokes please...these are Dogs!

In SQL my query looks like:

Select x.*
From dog x
Join dog m on m.id = x.mother
Join dog gp on gp.id = m.mother or gp.id = m.father
Join dog ggf on ggf.id = gp.father and ggf.id = x.father

How on earth would I interpret that into OPath? Any assistance would be immensely appreciated.

Many thanks,
Dany.

TOP + LEFT JOIN + ORDER

Tue, 19 Sep 2006 17:15:59 GMT

Have you looked at the lookup feature? The Example app has an example, and it is documented in the Wiki with all the other mapping docs. Without knowing a lot more about your particular case I can't give you the exact details, but it sounds like this would be what you want. If that isn't it for some reason, then I typically recommend creating a view -- not totally database independent, but its almost universal.

Thanks, Paul Wilson

TOP + LEFT JOIN + ORDER

Tue, 19 Sep 2006 15:42:15 GMT

Hi Paul,

Thanks for the reply and for your professional opinion ;p

By the why… my question remains… using the same approach (QueryHelper/ObjectQuery to build queries) How you do this ?:

//SELECT TOP 1 * FROM page_Content
//LEFT JOIN page_Attachments on page_Content.FileAttachId=page_Attachments.FileAttachId
//WHERE PageId=@PageId ORDER BY PageVersion DESC

The problem here is how you reproduce the LEFT JOIN using the QueryHelper/ObjectQuery approach

Thanks

TOP + LEFT JOIN + ORDER

Tue, 19 Sep 2006 14:56:11 GMT

Its a minor difference to use the QueryHelper:

int PageId = 1;
string where = QueryHelper.GetExpression("PageContent.PageId", PageId);
ObjectQuery<PageContent> query = new ObjectQuery<PageContent>(where, string.Empty, 1, 1);

What's the difference? It will take care of the things like quotes for strings, including "cleaning" of strings, and things like formatting of dates for your specific database. It also means that you can think in terms of your Class.Property naming, so somewhat OO, without having to worry about table and field names which is another good abstraction.

Again all I can say is this is my personal preference, but I don't want to imply OPath is not just as "good" -- its simply a case here with paging where OPath doesn't yet give you what you need.

Thanks, Paul Wilson

TOP + LEFT JOIN + ORDER

Tue, 19 Sep 2006 10:03:48 GMT

Hi Paul,

>> I hate to be firm, but I've also given you the correct syntax for doing this in your other thread several weeks ago
No problem! Note that I am not asking the same question. I only said that I am trying to avoid that "build a SQL query manually" and give the example, why :p

>>You use the QueryHelper with the ObjectQuery so that you won't be manually building expressions and worrying about table and field names, so it basically lets you think in sql syntax while still helping you with the actual sql.
>>...Anyhow, to make a long story short, you do have what you need to create this query...

I did not say that I don’t have what I need to create this query... the problem is HOW to create it !
Please provide examples !!

If I want this:

//SELECT TOP 1 * FROM page_Content
//WHERE PageId=@PageId

I do this:

int PageId = 1;
string where = "PageId=" + PageId;
ObjectQuery<PageContent> query = new ObjectQuery<PageContent>(where, string.Empty, 1, 1);

Can you put the sample using the QueryHelper/ObjectQuery to build this query ? :

//SELECT TOP 1 * FROM page_Content
//LEFT JOIN page_Attachments on page_Content.FileAttachId=page_Attachments.FileAttachId
//WHERE PageId=@PageId ORDER BY PageVersion DESC

That is my question!! How to do it!!

Sorry and please understand that I new in this kind of stuff and I need some "basic" help.!
Thanks again :p

TOP + LEFT JOIN + ORDER

Mon, 18 Sep 2006 14:12:18 GMT

The ObjectQuery allows you to specify page size and count in a way that the ORMapper will build the sql for the correct database. In this case case you want the first page with the size also being one. I hate to be firm, but I've also given you the correct syntax for doing this in your other thread several weeks ago, so I'm assuming I do not need to do so again.

Note that using the ObjectQuery is not somehow "worse" than using OPath -- it simply expects you to think using sql syntax instead of object syntax. Some people prefer one technique over the other, so both are included, but one is not "worse" or "better" than the other. You use the QueryHelper with the ObjectQuery so that you won't be manually building expressions and worrying about table and field names, so it basically lets you think in sql syntax while still helping you with the actual sql. It is true that Jeff has supplied a lot more operators in the OPath syntax, but they don't apply to most queries, and in most cases they are the types of things that are pretty standard in most databases anyhow, so I still think its almost always a case of your preference being sql or object syntax. I'm an OO developer, but I'm also a database developer, and I like to think of my queries in terms of sql because of that -- no matter how much O/R Mappers bridge the gap its still true that you are working with a database in the end, and I already know sql so I don't see the point in learning another language (like OPath). That's also the beauty that MS is working on for Linq -- you get to work with sql-like syntax instead of learning a new language, although certainly the tool support will make it better than what I can offer.

Anyhow, to make a long story short, you do have what you need to create this query, and even though it may not be possible with OPath, all that means is that you have to think in terms of sql -- it does not mean that you actually have to create all the sql yourself or get stuck without support for multiple databases.

Thanks, Paul Wilson