Efficient way to load lists of objects from database to instantiate a single object

Question

My situation

I have a c# object which contains some lists. One of these lists are for example a list of tags, which is a list of c# "SystemTag"-objects. I want to instantiate this object the most efficient way.

In my database structure, I have the following tables:

dbObject - the table which contains some basic information about my c# object
dbTags - a list of all available tabs
dbTagConnections - a list which has 2 fields: TagID and ObjectID (to make sure an object can have several tags)

(I have several other similar types of data)

This is how I do it now...

Retrieve my object from the DB using an ID
Send the DB object to a "Object factory" pattern, which then realise we have to get the tags (and other lists). Then it sends a call to the DAL layer using the ID of our C# object
The DAL layer retrieves the data from the DB
These data are send to a "TagFactory" pattern which converts to tags
We are back to the Object Factory

This is really inefficient and we have many calls to the database. This especially gives problems as I have 4+ types of lists.

What have I tried?

I am not really good at SQL, but I've tried the following query:

SELECT * FROM dbObject p
LEFT JOIN dbTagConnection c on p.Id=  c.PointId
LEFT JOIN dbTags t on c.TagId = t.dbTagId
WHERE ....

However, this retreives as many objects as there are tagconnections - so I don't see joins as a good way to do this.

Other info...

Using .NET Framework 4.0
Using LINQ to SQL (BLL and DAL layer with Factory patterns in the BLL to convert from DAL objects)

...

So - how do I solve this as efficient as possible? :-) Thanks!

I take it your `WHERE` clause has the id of the object that you wish to retrieve? — Russ Cam, Sep 22 '12 at 09:41
Yes that is true. However, I also need to get a list of my objects - in those cases of course not :-) — Lars Holdgaard, Sep 22 '12 at 09:45
There are a lot of considerations. There is some great data access discussion at this [SO thread](http://stackoverflow.com/questions/2698151/entity-framework-vs-linq-to-sql-vs-ado-net-with-stored-procedures). Specifically, if you're looking for speed (in your call for efficiency), I would consider Entity Framework. In my experience, if you can afford to put in the time upfront to get everything appropriately wired, linq to entities is considerably faster than linq to sql. — samuelesque, Sep 24 '12 at 19:11

score 1 · Answer 1 · answered Sep 22 '12 at 09:52

We used stored procedures that returned multiple resultsets, in a similar situation in a previous project using Java/MSSQL server/Plain JDBC.

The stored procedure takes the ID corresponding to the object to be retrieved, return the row to build the primary object, followed by multiple records of each one-to-many relationship with the primary object. This allowed us to build the object in its entirety in a single database interaction.

score 1 · Answer 2 · answered Sep 24 '12 at 13:50

Have you thought about using the entity framework? You would then interact with your database in the same way as you would interact with any other type of class in your application.

It's really simple to set up and you would create the relationships between your database tables in the entity designer - this will give you all the foreign keys you need to call related objects. If you have all your keys set up in the database then the entity designer will use these instead - creating all the objects is as simple as selecting 'Create model from database' and when you make changes to your database you simply right-click in your designer and choose 'update model from database'

The framework takes care of all the SQL for you - so you don't need to worry about that; in most cases..

A great starting place to get up and running with this would be here, and here

Once you have it all set up you can use LINQ to easily query the database.

You will find this a lot more efficient than going down the table adapter route (assuming that's what you're doing at the moment?)

Sorry if i missed something and you're already using this.. :)

Luis Quijada · Accepted Answer · 2012-09-30T23:18:09.057

At first sight I don't see your current way of work as "inefficient" (with the information provided). I would replace the code:

SELECT * FROM dbObject p
LEFT JOIN dbTagConnection c on p.Id=  c.PointId
LEFT JOIN dbTags t on c.TagId = t.dbTagId
WHERE ...

by two calls to the DALs methods, first to retrieve the object main data (1) and one after that to get, only, the data of the tags related (2) so that your factory can fill-up the object's tags list:

(1)

SELECT * FROM dbObject WHERE Id=@objectId

(2)

SELECT t.* FROM dbTags t
INNER JOIN dbTag Connection c ON c.TagId = t.dbTagId
INNER JOIN dbObject p ON p.Id = c.PointId
WHERE p.Id=@objectId

If you have many objects and the amount of data is just a few (meaning that your are not going to manage big volumes) then I would look for a ORM based solution as the Entity Framework.

I (still) feel comfortable writing SQL queries in the DAOs to have under control all queries being sent to the DB server, but finally it is because in our situation is a need. I don't see any inconvenience on having to query the database to recover, first, the object data (SELECT * FROM dbObject WHERE ID=@myId) and fill the object instance, and then query again the DB to recover all satellite data that you may need (the Tags in your case).

You have be more concise about your scenario so that we can provide valuable recommendations for your particular scenario. Hope this is useful you you anyway.

score 0 · Answer 4 · answered Sep 24 '12 at 14:03

As far I guess, your database exists already and you are familiar enough with SQL.

You might want to use a Micro ORM, like petapoco.

To use it, you have to write classes that matches the tables you have in the database (there are T4 generator to do this automatically with Visual Studio 2010), then you can write wrappers to create richer business objects (you can use the ValueInjecter to do it, it is the simpler I ever used), or you can use them as they are.

Petapoco handles insert / update operations, and it retrieves generated IDs automatically.

Because Petapoco handles multiple relationships too, it seems to fit your requirements.

Efficient way to load lists of objects from database to instantiate a single object

4 Answers4