How to retrieve a subset of a nested collection only?

[tmlipinski]

Hi,

There are two tables:

• Master, with fields: MID, MField1, MField2
• Detail, with fields: DID, MID (the foreign key of Master), DField1, DField2

Classes are named exactly as tables, fields too. The collection of Detail records in the Master class is named "Details".
When I ask:
select * from MasterExtent as m, m.Details as d where m.MField1 = 'xx' and d.DField1 = 'yy'
I get a list of filtered (as expected) Master records. Every Master has all its Details; but I want only Details that have DField1 = 'yy'. Therefore I must scan all the collection to find Details I want. It is not comfortable and forces unneccesary reads form the database.
I want them to read, not to manipulate. Is it possible to obtain only a subset of Details? In my case I know that there will be only one Detail and I would like to reach it as: master.Details[0].
Searching just for Details:
select * from DetailExtent as d where d.DField1 = 'yy' and d.ms.MField1 = 'xx'
("ms" is the reference to Master)
is not a solution because in the real case relations are more complex and I cannot freely navigate in the backward direction.

Regards
Tomasz

[PetarP]

Hello tmlipinski,
You will need to manually select the fields that you wish to display. Here is an example used against the northwind database.

SELECT d.Region FROM  EmployeeExtent AS x, x.EmployeeTerritories as d where d.Region.RegionDescription like "E*" and x.EmployeeID = 1

Note that since here the results are duplicated you can also use the Distinct key word in front of your selection.

Regards,
PetarP
the Telerik team

Check out Telerik Trainer , the state of the art learning tool for Telerik products.

[tmlipinski]

Hi,
well, it's not exactly what I want. The result is just a set of pure data (arrays of data with no names), not a set of objects. But I think that it is just impossible to get exactly what I want. I this case using relational approach is stronger.

Thanks for reply.

Regards
Tomasz