Business Entity Hierarchy OO question

[Narshe]

I've been struggling with this for a while.

I have a business entity Employee that has a Company entity attached
to it.

Ex:

public class Compay{ // blah }

public class Employee
{
private Company _company;

public Company Company{ // get/set }
}

I'm looking for a good way to get the data for the Company reference.
Right now, the company info is got when needed, so there is a separate
query for the company. This presents a problem when you have a list
of Employees. You get 10 employees with one query, but then there are
10 more queries, one for each Company. This adds up if you have more
references on the Employee object than just Company, and if Company
has reference objects on it.

I know one way to solve this is to use a builder. So, you get your
list of Employees, pass it to a builder, the builder does a query to
get the Companies for those Employees, and you loop through and match
them up. I've found this to not work too well, which could be the way
I implemented it.

When LINQ in .NET 3.5 is released, this should solve the issue, but
that's not for a while yet and I still would like to know of a good
way to do it that will work for all languages, not just .NET 3.5.

Any suggestions?

Thanks.

 

[sloan]

I have an example

Customers.
Customers have Orders

See
http://sholliday.spaces.live.com/blog/
6/5/2006
Custom Objects and Tiered Development II // 2.0


I use the IDataReader.NextResult() call.

There are 2 queries. Which I believe is the appropriate granularity for
what you're looking for.

 

[PS]

I've been struggling with this for a while.

I have a business entity Employee that has a Company entity attached
to it.

Ex:

public class Compay{ // blah }

public class Employee
{
private Company _company;

public Company Company{ // get/set }
}

I'm looking for a good way to get the data for the Company reference.
Right now, the company info is got when needed, so there is a separate
query for the company. This presents a problem when you have a list
of Employees. You get 10 employees with one query, but then there are
10 more queries, one for each Company. This adds up if you have more
references on the Employee object than just Company, and if Company
has reference objects on it.

You can use lazy loading with a registry. With lazy loading until you
actually use the child object the Employee object is not going to do
anything. When you do request the Company you ask the registry. If the
registry already has loaded the Company then it returns the reference, if it
has not then it builds it and then returns the reference.

A little bit of code to give you the idea

class Employee
{
public Company Company {
get {
return Registry.CompanyByID(companyID)

class Registry {
public static CompanyByID(string companyID) {
if(companyList.ContainsKey(companyID))
return comapnyList[companyID]
else {
Company company = Builder.BuildCompany(companyID);
companyList.Add(companyID, company);
return company;

I know one way to solve this is to use a builder. So, you get your
list of Employees, pass it to a builder, the builder does a query to
get the Companies for those Employees, and you loop through and match
them up. I've found this to not work too well, which could be the way
I implemented it.

A easier way would be to build the complete company list at one time then
use this as a lookup "table" by key from the employee objects. If you have
objects that are fairly static then it can be easier to build them all when
the software starts and then delgate your properties to then like
public Company Company {
get{
return CompanyList[this.companyKey];

When LINQ in .NET 3.5 is released, this should solve the issue, but
that's not for a while yet and I still would like to know of a good
way to do it that will work for all languages, not just .NET 3.5.

There are other ORM products already available.

PS

 

[Narshe]

You can use lazy loading with a registry. With lazy loading until you

actually use the child object the Employee object is not going to do
anything. When you do request the Company you ask the registry. If the
registry already has loaded the Company then it returns the reference, if it
has not then it builds it and then returns the reference.

How is the registry invalidated then? When data is retrieved, it is
more than likely going to be changed soon after, so I would need a way
to invalidate the registry. I know in SQL 2005 you can do row level
cache invalidation, but we're on 2000 here.

A easier way would be to build the complete company list at one time then
use this as a lookup "table" by key from the employee objects. If you have
objects that are fairly static then it can be easier to build them all when
the software starts and then delgate your properties to then like
public Company Company {
get{
return CompanyList[this.companyKey];

I have thought about this too, but the list would need to be
invalidated at some point.

There are other ORM products already available.

I will have to look into this.

-Josh

 

[todos_menos_msft]

uh.. use a JOIN?

do you know anything about databases?

C# is for fags


oh, yay.. sharepoint and Linq are going to save us! (NOT)

MOVE TO DREAMWEAVER KIDS

 

[Narshe]

uh.. use a JOIN?

do you know anything about databases?

C# is for fags

oh, yay.. sharepoint and Linq are going to save us! (NOT)

MOVE TO DREAMWEAVER KIDS

Um..... Is this for real? And where on earth did the SharePoint
comment come from?

I'm not going to even bother commenting on this. Not worth my time.

 

[Todos Menos [MSFT]]

eat a dick mofo

seriously.. if you want to join two tables together; then join two
tables together

what's the friggin problem




I just find it humorous; you kids are here; getting spoonfed BLOATWARE
and you don't have the balls to stand up to us


We take your money and invent crap like the xbox instead of fixing
bugs


-Todos

 

[RobinS]

He's trolling. The best way to get rid of him is not to respond to him.

Robin S.
---------------------

 

[Bruce Wood]

I've been struggling with this for a while.

I have a business entity Employee that has a Company entity attached
to it.

public class Compay{ // blah }

public class Employee
{
private Company _company;

public Company Company{ // get/set }
}

I'm looking for a good way to get the data for the Company reference.
Right now, the company info is got when needed, so there is a separate
query for the company. This presents a problem when you have a list
of Employees. You get 10 employees with one query, but then there are
10 more queries, one for each Company. This adds up if you have more
references on the Employee object than just Company, and if Company
has reference objects on it.

You can use lazy loading with a registry. With lazy loading until you
actually use the child object the Employee object is not going to do
anything. When you do request the Company you ask the registry. If the
registry already has loaded the Company then it returns the reference, if it
has not then it builds it and then returns the reference.

A little bit of code to give you the idea

class Employee
{
public Company Company {
get {
return Registry.CompanyByID(companyID)

class Registry {
public static CompanyByID(string companyID) {
if(companyList.ContainsKey(companyID))
return comapnyList[companyID]
else {
Company company = Builder.BuildCompany(companyID);
companyList.Add(companyID, company);
return company;

I know one way to solve this is to use a builder. So, you get your
list of Employees, pass it to a builder, the builder does a query to
get the Companies for those Employees, and you loop through and match
them up. I've found this to not work too well, which could be the way
I implemented it.

A easier way would be to build the complete company list at one time then
use this as a lookup "table" by key from the employee objects. If you have
objects that are fairly static then it can be easier to build them all when
the software starts and then delgate your properties to then like
public Company Company {
get{
return CompanyList[this.companyKey];

When LINQ in .NET 3.5 is released, this should solve the issue, but
that's not for a while yet and I still would like to know of a good
way to do it that will work for all languages, not just .NET 3.5.

There are other ORM products already available.

LINQ probably won't make a difference in this case: it'll just allow
you to express the same thing you're expressing now in a more compact,
elegant syntax.

I agree with PS, except that I wouldn't call it a "Registry". It's
called cache.

Basically, you load an object into memory only when some code asks for
it. Unfortunately, as is usual in computing, there are competing
considerations:

1) You don't want to load large tables into memory at start up just in
case you'll need them.
2) You don't want to make thousands of calls to the database to
retrieve single rows when you can make fewer calls to return groups of
rows.
3) The more you cache, the less you have to go to the database (over
time).
4) The more you cache, the more stale your data becomes, and the more
likely that the database will change "beneath" your cache, making
updates / writes that your user does conflict with the data in the
database.

For some tables it will be a no-brainer. If they're small and rarely
change, just load them at start-up, cache them, and grab objects as
they're needed.

For other tables it will be problematic: large tables that shouldn't
be read all at once, or tables that change frequently and so shouldn't
be cached for long periods.

Unfortunately there's no one-size-fits-all answer here. One of my apps
loads most of the tables upon first reference (because they're small,
< 1000 rows, and rarely change), but loads others piecemeal because
loading them at startup would cause an unacceptable delay.

Oh... and ignore the troll. Every newsgroup has to have a pet, I
suppose. It's just unfortunate that ours pees on the carpet.

 

[Todos Menos [MSFT]]

yeah dude.. let's just sit around and analyze for an hour.. whether to
keep a table in memory or in a database

times a hundred thousand

times a hundred thousand

times a hundred thousand

AS IF. KEEP DATA IN A DATABASE. IF YOU NEED TO MAKE IT FASTER TAKE A
****ING SQL CLASS INSTEAD OF HOPPING ON THE 'NEW PROGRAMMING LANGUAGE
BANDWAGON'

YOU TRENDY ****ING DIPSHITS

-Todos

You can use lazy loading with a registry. With lazy loading until you
actually use the child object the Employee object is not going to do
anything. When you do request the Company you ask the registry. If the
registry already has loaded the Company then it returns the reference, if it
has not then it builds it and then returns the reference.

A little bit of code to give you the idea

class Employee
{
public Company Company {
get {
return Registry.CompanyByID(companyID)

class Registry {
public static CompanyByID(string companyID) {
if(companyList.ContainsKey(companyID))
return comapnyList[companyID]
else {
Company company = Builder.BuildCompany(companyID);
companyList.Add(companyID, company);
return company;

I know one way to solve this is to use a builder. So, you get your
list of Employees, pass it to a builder, the builder does a query to
get the Companies for those Employees, and you loop through and match
them up. I've found this to not work too well, which could be the way
I implemented it.

A easier way would be to build the complete company list at one time then
use this as a lookup "table" by key from the employee objects. If you have
objects that are fairly static then it can be easier to build them all when
the software starts and then delgate your properties to then like
public Company Company {
get{
return CompanyList[this.companyKey];

When LINQ in .NET 3.5 is released, this should solve the issue, but
that's not for a while yet and I still would like to know of a good
way to do it that will work for all languages, not just .NET 3.5.

There are other ORM products already available.

LINQ probably won't make a difference in this case: it'll just allow
you to express the same thing you're expressing now in a more compact,
elegant syntax.

I agree with PS, except that I wouldn't call it a "Registry". It's
called cache.

Basically, you load an object into memory only when some code asks for
it. Unfortunately, as is usual in computing, there are competing
considerations:

1) You don't want to load large tables into memory at start up just in
case you'll need them.
2) You don't want to make thousands of calls to the database to
retrieve single rows when you can make fewer calls to return groups of
rows.
3) The more you cache, the less you have to go to the database (over
time).
4) The more you cache, the more stale your data becomes, and the more
likely that the database will change "beneath" your cache, making
updates / writes that your user does conflict with the data in the
database.

For some tables it will be a no-brainer. If they're small and rarely
change, just load them at start-up, cache them, and grab objects as
they're needed.

For other tables it will be problematic: large tables that shouldn't
be read all at once, or tables that change frequently and so shouldn't
be cached for long periods.

Unfortunately there's no one-size-fits-all answer here. One of my apps
loads most of the tables upon first reference (because they're small,
< 1000 rows, and rarely change), but loads others piecemeal because
loading them at startup would cause an unacceptable delay.

Oh... and ignore the troll. Every newsgroup has to have a pet, I
suppose. It's just unfortunate that ours pees on the carpet.- Hide quoted text -

- Show quoted text -

 

[Todos Menos [MSFT]]

uh because Microsoft spends fully HALF of their resources on trying to
con us into SharePoint?
uh because Microsoft spends fully HALF of their resources on trying to
con us into SharePoint?
uh because Microsoft spends fully HALF of their resources on trying to
con us into SharePoint?
uh because Microsoft spends fully HALF of their resources on trying to
con us into SharePoint?


because MIcrosoft cons us into sharepoint instead of FIXING BUGS

uh because Sharepoint makes your little trivial internet crap obsolete

 

[Narshe]

LINQ probably won't make a difference in this case: it'll just allow

you to express the same thing you're expressing now in a more compact,
elegant syntax.

There is a LINQ to SQL adapter or something like that, that will
create a query that will pull the exact data you need. If you just use
LINQ without that, I'll just loop through your results, and basically
do exactly what I'm doing at the moment anyways.

I guess that is my ideal situation. To have one query that gets
exactly the data I need for the objects I'm accessing in the
hierarchical tree. One way I've seen to do this would be to create a
"view" object that does one specific query and brings back the exact
data you need. So, "Customer" would be comprised of data from the
Contact, Company, Opportunity, etc objects.

I agree with PS, except that I wouldn't call it a "Registry". It's
called cache.

Basically, you load an object into memory only when some code asks for
it. Unfortunately, as is usual in computing, there are competing
considerations:

1) You don't want to load large tables into memory at start up just in
case you'll need them.
2) You don't want to make thousands of calls to the database to
retrieve single rows when you can make fewer calls to return groups of
rows.
3) The more you cache, the less you have to go to the database (over
time).
4) The more you cache, the more stale your data becomes, and the more
likely that the database will change "beneath" your cache, making
updates / writes that your user does conflict with the data in the
database.

For some tables it will be a no-brainer. If they're small and rarely
change, just load them at start-up, cache them, and grab objects as
they're needed.

For other tables it will be problematic: large tables that shouldn't
be read all at once, or tables that change frequently and so shouldn't
be cached for long periods.

I've thought about doing this, and would be great using SQL 2005 since
there is row level cache invalidation. I've even thought about having
all data go through a service/cache/registry.

Unfortunately there's no one-size-fits-all answer here. One of my apps
loads most of the tables upon first reference (because they're small,
< 1000 rows, and rarely change), but loads others piecemeal because
loading them at startup would cause an unacceptable delay.

Thanks. I have a good idea of where I need to invest more research
time into, and a few ways I can solve this issue.

Oh... and ignore the troll. Every newsgroup has to have a pet, I
suppose. It's just unfortunate that ours pees on the carpet.

Yeah, we need some Resolve.

Thanks.

 

[Bruce Wood]

There is a LINQ to SQL adapter or something like that, that will
create a query that will pull the exact data you need. If you just use
LINQ without that, I'll just loop through your results, and basically
do exactly what I'm doing at the moment anyways.

I guess that is my ideal situation. To have one query that gets
exactly the data I need for the objects I'm accessing in the
hierarchical tree. One way I've seen to do this would be to create a
"view" object that does one specific query and brings back the exact
data you need. So, "Customer" would be comprised of data from the
Contact, Company, Opportunity, etc objects.

Yes, but... my point was that this isn't a technology issue, it's a
design issue. No matter what technology you use, or how you store the
data in memory, you still have the problem of the competing concerns
that I outlined. Even if LINQ allows you to pull in just the data you
need, you still have to determine how much data to pull in and how
often. Pulling in all of the data at once often produces unacceptable
delays. Pulling in each piece only and as you need it often produces
too many database queries that again cuase delays (when pulling in
more than you need in anticipation of needing it later may be more
efficient).

The app I mentioned still has performance problems, most of which stem
from pulling in data piecemeal from the one large table it
manipulates. I can't pull it all at once because the user would wait
for ages. However, if I pull in a row at a time, as needed, then I end
up with a flurry of database queries that bog down the app. The answer
lies somewhere in between, but the problem is coming up with a clever
method that pulls in more than a row at a time without creating
massive, bloated queries.

What little understanding I have of LINQ tells me that it will allow
you to succinctly express what data you need and will formulate that
into SQL for you. That still doesn't solve the problem of going back
to "the well" too often. (Unless you have a wickedly fast connection
and don't really care how many queries you fire at your database.)

 

[Todos Menos [MSFT]]

back to the well 'too often'?

learn how to write SQL you friggin Newbie!

I repeat.. pull the results you need from the database. it is ONE
PULL.


-Todos

 

[Todos Menos [MSFT]]

And for the record, LINQ has been officially named as 'Visual Fred
3.0'

who gives a crap about Visual Fred Crap?

-Todos

 

[Todos Menos [MSFT]]

I don't pee on the carpet

I pee on Microsoft

I call for the resignation of Steve Ballmer.

Only Ralph Nader can save us now.
Nader for CEO '07

Time for 'Regime Change in Redmond'

-Todos

 

[Todos Menos [MSFT]]

99% of apps and developers should 'never touch the registry'

that is what Microsoft needs to do to make Windows more secure.

Not UAC.
Lock down the damn registry

You can use lazy loading with a registry. With lazy loading until you
actually use the child object the Employee object is not going to do
anything. When you do request the Company you ask the registry. If the
registry already has loaded the Company then it returns the reference, if it
has not then it builds it and then returns the reference.

How is the registry invalidated then? When data is retrieved, it is
more than likely going to be changed soon after, so I would need a way
to invalidate the registry. I know in SQL 2005 you can do row level
cache invalidation, but we're on 2000 here.

A easier way would be to build the complete company list at one time then
use this as a lookup "table" by key from the employee objects. If you have
objects that are fairly static then it can be easier to build them all when
the software starts and then delgate your properties to then like
public Company Company {
get{
return CompanyList[this.companyKey];

I have thought about this too, but the list would need to be
invalidated at some point.

There are other ORM products already available.

I will have to look into this.

-Josh

 

[PS]

I agree with PS, except that I wouldn't call it a "Registry". It's
called cache.

I misused the terms a little. I meant an object that would talk to the cache
and if the cache doesn't have the object then would talk to the builder.
This way you have one "point of entry" to get an object.

Basically, you load an object into memory only when some code asks for
it. Unfortunately, as is usual in computing, there are competing
considerations:

1) You don't want to load large tables into memory at start up just in
case you'll need them.
2) You don't want to make thousands of calls to the database to
retrieve single rows when you can make fewer calls to return groups of
rows.
3) The more you cache, the less you have to go to the database (over
time).
4) The more you cache, the more stale your data becomes, and the more
likely that the database will change "beneath" your cache, making
updates / writes that your user does conflict with the data in the
database.

For some tables it will be a no-brainer. If they're small and rarely
change, just load them at start-up, cache them, and grab objects as
they're needed.

For other tables it will be problematic: large tables that shouldn't
be read all at once, or tables that change frequently and so shouldn't
be cached for long periods.

Unfortunately there's no one-size-fits-all answer here. One of my apps
loads most of the tables upon first reference (because they're small,
< 1000 rows, and rarely change), but loads others piecemeal because
loading them at startup would cause an unacceptable delay.

Nice reply Bruce.

 

[Todos Menos [MSFT]]

there is a 1-size fits all solution

a) don't use the middle tier.. get a faster webserver & db server
b) don't build objects GAG-- too much verbosity
c) don't use bloatware