object relational mapping query

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am looking at creating an oo application mapped to a sql server database.
One issue I have come up against is say I have a page which must display
information from four different database tables (which assume I have mapped
to four software objects). The simplest way to do this would be to create a
query across the four tables and have a single method returning all the
relevant data. However this would seem to break the spirit of encapsulation.
It would seem to oo way to do this would be to create four seperate instances
of the objects, each querying the tables one at a time. This would seem
rather time consuming and inefficient. Any better ideas.

A side note I am considering using the OR mapper tool LLBLGenPro. Anyone had
any good/bad experiences with this?

Thanks
 
Maybe I was not being too clear, the main part of my question was not
specific to LLBLGenPro although it may offer a solution. I was wondering if
anyone had any solutions assuming they were going to build their own data
access layer.

I was really enquiring about LLBLGenPro in general as a footnote.
 
Right but Frans is the DUDE when you wanna talk about relational to object
mapping in general.

BTW, between the two options you present my vote goes for one query that
spans four tables. Let me ask you a question, are you also worried about
saving the data back into the database? (4 tables), or just a read only mode
object?

- Sahil Malik [MVP]
ADO.NET 2.0 book -
http://codebetter.com/blogs/sahil.malik/archive/2005/05/13/63199.aspx
 
scottrm said:
I am looking at creating an oo application mapped to a sql server
database. One issue I have come up against is say I have a page
which must display information from four different database tables
(which assume I have mapped to four software objects). The simplest
way to do this would be to create a query across the four tables and
have a single method returning all the relevant data. However this
would seem to break the spirit of encapsulation. It would seem to oo
way to do this would be to create four seperate instances of the
objects, each querying the tables one at a time. This would seem
rather time consuming and inefficient. Any better ideas.
Click to expand...

Some O/R mappers offer the ability to define lists, like we do with
'TypedLists' which are basicly views on entity fields. You can then
create a single fetch of data from 4 entities, which is often enough,
as the data is for example used to fill a list in a webpage (which is
readonly anyway).

If you need objects (because you want to update the data) you need to
fetch the objects indeed separately, or can specify prefetch paths, if
the O/R mapper supports it. Prefetch paths are paths in a graph you
define which should be fetched together with the entity/entities you're
fetching.

An example is that you're fetching a list of customers, based on a
filter, and their orders and these order's orderdetail objects. This
can lead to a lot of different queries, if you want to fetch them all
individually. You can also specify a prefetch path which defines
customer - order - orderdetails. This then fetches the objects with 3
queries, one per node. This can lead to better performance in a lot of
scenarios.
A side note I am considering using the OR mapper tool LLBLGenPro.
Anyone had any good/bad experiences with this?
Click to expand...

We're one of the marketleaders, so you're in good company :). In the
upgrade currently in development (free for customers), we'll add
inheritance, which will allow you to map a hierarchy of entities onto
tables or views, which can result in an entity effectively mapped onto
for example 4 tables, and fetches of these are done in a single query.

Frans

--
 
Hi Scott,

The object has to be built in the BusinessLogic layer. In this layer, you
call the interfaces exposed by the DataAccess layer to access 4 databases,
get the data and put them into a single object. Is that what you need?

Kevin Yu
=======
"This posting is provided "AS IS" with no warranties, and confers no
rights."
 
Thanks for the reply although I think maybe I did not explain fully. I could
potentially have to display data on a single UI page from various different
tables that would map to more than one business object. What you seem to
suggest is creating a single business object which pulls in all the relevant
data but that would not seem to fit with object oriented principles because I
would be creating a specific business object for a specific UI page.


Scott
 
Hi Scott,

Yes, you're right. I'm not suggesting for a single business object. You can
create as much as business objects as you need. In the Businesslogic layer,
the business objects get data from the DataAccess layer directly. It is the
DAL's responsibility to determine how to get data from the 4 databases. HTH.

Kevin Yu
=======
"This posting is provided "AS IS" with no warranties, and confers no
rights."
 
Hi Scott,

A couple of things to keep in mind:

1) Objects are often, but NOT ALWAYS the same as tables. Many O/R Mappers
including LLBLGen and various codesmith templates i've used, treat them as
such. This is not always the case. You might need to see if all four of
these "objects" really need to be seperate in you business layer, even though
they're seperate in the database. Do they have seperate behavior? Do they
need to get added/updated seperately or always together?

2) Assuming these actually are4 seperate objects, related together by
aggregation, many O/R Mappers, as Frans has mentioned, provide a "Pre-Fetch"
feature, in which you query all the data at one time, using only a few
queries, and then put them together in the business layer using a hashtable
to find related entities by their Primary and Foreign keys. I know Paul
Wilson's O/R Mapper will let you do this. It requires a little coding by
hand, but is still way easier than doing it all yourself.

Check out this thread
http://www.wilsondotnet.com/Tips/ViewPosts.aspx?Thread=1389

3) I hate to say it, but sometimes when building reports or views of your
data, a datatable is just way easier than the OOP approach.

Craig
 
What is the relationship between the four objects in your domain model?
Presumably if you are displaying data from four objects on the same page
they are related in some way. Does your model have associations between any
of the objects?

It's perfectly fine to retrieve data for multiple objects in a single call
to the mapping layer if that fits your domain model. For example, you may
have Orders and OrderItems. It would quite reasonable to have a method to
retrieve orders that retrieved Order and OrderDetail objects togther. In
fact this may be a better model than retrieving them individualy.
 
Back
Top