Mapping from relational database

Question

For one to one relationships things are easy.
When it comes to one to many or many to many problems appear...
I am not using an ORM tool now for many reasons and i am wondering when i want to get data whether it is better to reassemble one to many relationship using multiple queries or in code..

For example.. Having a class Category and a class Product...

A product table has a collumn for category id (one category many products).

So for my full catalog is it better to execute 2 queries to get the categories and products i want and then populate for each category its products List ? (It is very easy with LINQ) ..

Or i should call query for each category ? Like select id from products where category_id=5;

Also i dont know how to name the functions like to set whether i want to fetch the other side of the relationship or not..

Charles Graham · Accepted Answer · 2011-08-28 23:38:07Z

2

You should always use the least number of queries possible to retrieve your data. Executing one query per category to load the products is known as the N+1 problem, and can quickly cause a bottleneck in your code.

As far as what to name your methods that specify your fetch plans, name them after what the method actually does, such as IncludeProducts or WithProducts.

answered Aug 28, 2011 at 23:38

Charles Graham

1,1575 silver badges6 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Kirk Broadhurst · Accepted Answer · 2011-08-29 00:23:13Z

1

If you want to retrieve all categories and all their products, you can either select all categories and then select all products in two queries, or you can select in one query and group.

To use just one query, select an inner join for your two tables

SELECT c.*, p.* 
FROM Category c INNER JOIN Product p ON c.CategoryId = p.CategoryId

and then construct business objects from the resulting dataset

result.GroupBy(r => r.CategoryId).Select(group =>
    new Category(/* new Category using c.* columns */)
    { 
        Products = /* new list of Products from p.* values */
    });

But I have to ask - why aren't you using an ORM?

answered Aug 29, 2011 at 0:23

Kirk Broadhurst

28.9k20 gold badges111 silver badges179 bronze badges

2 Comments

GorillaApe Over a year ago

I dont have the time for the learning curve it requires in .NET. I am used to java ORM but they are too complicated. A join query wouldnt cause too much repetitive data?

Kirk Broadhurst Over a year ago

It will cause repetitive data but less queries. It depends on how many columns you're retrieving from the catalog table - how much data will be repeated in proportion to the total? If that's a concern then you should benchmark it.

Collectives™ on Stack Overflow

Mapping from relational database

2 Answers 2

Comments

2 Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Related