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

Question

My situation

I have a C # object that contains some lists. One of these lists is, for example, a tag list, which is a list of C # "SystemTag" objects. I want this object to be the most efficient way .

In my database structure, I have the following tables:

• dbObject is a table containing basic information about my C # object
• dbTags - a list of all available tabs
• dbTagConnections - a list that has 2 fields: TagID and ObjectID (to make sure that an object can have multiple tags)

(I have several other similar data types)

This is how I do it now ...

• Retrieve my object from the database using id
• Send the DB object to the "Object factory" template, which will then understand that we should get the tags (and other lists). Then it sends a call to the DAL layer using the ID of our C # object
• DAL level retrieves data from DB
• This data is sent to the "TagFactory" template, which is converted to tags
• Let's go back to the Factory object

This is really inefficient and we have a lot of database calls. This is especially problematic since I have 4 types of lists.

What have i tried?

I am not very good at SQL, but I 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 returns as many objects as there are tag associations, so I don’t see connections being a good way to do this.

Additional Information...

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

...

So - how can I solve this problem as efficiently as possible? :-) Thank you!

Answer 1

At first glance, I don’t see your current way of working being “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 get the main data of the object (1) and one of them, to get only the data associated with the tags (2) so that your factory can fill out the List of object tags:

(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 only a few (this means that you are not going to manage large volumes), I would look for an ORM solution as Entity Framework .

I (still) feel comfortable writing SQL queries to the DAO to control all queries sent to the database server, but, in the end, this is due to the fact that in our situation there is a need. I do not see any inconvenience associated with the need to query the database to restore, firstly, the object data ( SELECT * FROM dbObject WHERE ID=@myId ) and populating the object instance, and then again request the database to restore all satellite data that may you will need (tags in your case).

Please tell us more about your scenario so that we can provide valuable guidance for your specific scenario. I hope this is all the same to you.

Answer 2

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

The stored procedure takes an identifier corresponding to the object to be restored, returns a string for constructing the primary object, followed by several records of each one-to-many relationship with the main object. This allowed us to fully build the object in one interaction with the database.

Answer 3

Have you considered using entity structure? Then you interact with your database in the same way as you interact with any other type of class in your application.

This is very easy to set up, and you will create relationships between your database tables in the object designer - this will give you all the foreign keys needed to invoke related objects. If you have all of your keys configured in the database, then the creator of the entity will use them instead - creating all objects is as simple as selecting "Create a model from the database", and when you make changes to your database, you just right-click in your designer and select "update model from the database"

The framework takes care of all the SQL for you — so you don’t have to worry about it; In most cases.

A great starting place to start and run with this would be here , and here

Once you have everything set up, you can use LINQ to simply query the database.

You will find this much more efficient than navigating the table adapter path (assuming what you are doing at the moment?)

Sorry if I missed something and you are already using it. :)

Answer 4

As far as I understand, your database already exists, and you are fairly familiar with SQL.

You might want to use Micro ORM like petapoco .

To use it, you need to write classes that correspond to the tables that you have in the database (for this you need to create a T4 generator using Visual Studio 2010), then you can write wrappers to create richer business objects (you can use ValueInjecter , it's easier than ever), or you can use them the way they are.

Petapoco handles insert / update operations and automatically extracts the generated identifiers.

Since Petapoco also handles several relationships , it seems to fit your requirements.