Geek Answers Handbook

Hierarchical data from the database - join or not join

I am trying to find a clean and effective solution to this problem, but somehow stuck.

General information:
-ASP.Net C # Application (.Net 3.5)
-MS-SQL Server 2005

Here's what the data looks like:
Category → Template → Instance

A category can contain several templates.
A template can contain multiple instances.

There is a class for each of these 3 and the corresponding database table with a large number of columns.

I want to load a full category from a database into an object of a class class in C #, including all associated template and instance objects.

I have two options:
1) Join all 3 tables and immediately read all the data.
Surface : much faster on the database side, all information in one query.
Downside . I am passing a lot of redundant data because each line has the same category and template data for each instance.

Example (simplified):

CategoryID | CategoryName | TemplateID | TemplateName | InstanceID | InstanceName  
1 | FirstCategory | 1 | FirstTemplate | 1 | FirstInstance   
1 | FirstCategory | 1 | FirstTemplate | 2 | SecondInstance  
1 | FirstCategory | 1 | FirstTemplate | 3 | ThirdInstance  
1 | FirstCategory | 1 | SecondTemplate | 4 | FourthInstance

2) I query each table myself, first collect the category data, then the associated template data with the category identifier, etc.
Surface . An intuitive approach that is easier to handle on the part of the code does not extract unnecessary data.
Downside . A few server requests may be slower.

? ?
1, , , "". .

1, ?
?
- ?

! .

+1
5

Entity Framework , . , 1 . , , Microsoft , , , , , .

- , 2 , . , , EF .

+1

: "select *" , . LINQ :

  class Category
  {
    public int CategoryId { get; set; }
    public List<Template> Templates
    {
      get
      {
        return Repository.Templates.Where(t => t.CategoryId == this.CategoryId).ToList();
      }
    }
  }

: Template/Instance:

  class Template
  {
    public int CategoryId { get; set; }
    public int TemplateId { get; set; }
    public List<Instance> Instances
    {
      get
      {
        return Repository.Instances.Where(i => i.TemplateId == this.TemplateId).ToList();
      }
    }
  }
+1

: ADO/ .

3 .

1) select c.* from category c where c.id = @categoryId

2) select t.* from templates t
    join category c on t.categoryid = c.id 
    where c.id = @categoryId

3) select i.* from Instance i
    join templates t on i.templateid = t.id  
    join category c on t.categoryid = c.id
    where c.id = @categoryId

sqldatareader, sqldatareader.read() sqldatareader.Nextresult()

, , - .

+1

1 , Template Instance, Template Instance Category, , .

2 , , .

+1

, , №2. , . , (getTemplates(), getInstances (234) ..).

, , ( №1).

0

More articles:


All Articles