问题描述:

I need to retrieve a set of records from db and attach them to List<FirstRecord> variable. However, each FirstRecord object has a set of SecondRecord objects, so I also need to define a variable like List<SecondRecord>.

Currently, to do that, first I make a call to DB to build the List<FirstRecord> variable, then for each FirstRecord, I make another DB call to retrieve the data and build the List<SecondRecord> variable for each FirstRecord object.

Of course, this is a very weak design. I am using SQL Helper. Is it possible to handle this task with a single call to the db? Or, do you think that Entity Framework can handle this, and I should better switch to Entity Framework?

UPDATED WITH AN EXAMPLE

TABLE1

UserId | UserName

1422 | erkaner

TABLE2

PostId | Text | UserId

1 | messge1 | 1422

1 | messge2 | 1422

1 | messge3 | 1422

public class UserInfo

{

public Guid UserId{set ; get ;}

public string Username {set ; get ;}

public List<PostInfo> Posts {set ; get ;}

}

public class PostInfo

{

public Guid PostId{set ; get ;}

public string PostText{set ; get ;}

}

Then, I should use inner join to merge TABLE1 and TABLE2 and retrieve all data at once. Then, with appropriate coding I should create the UserInfo object and the List obhect inside it. Is that correct?

Thanks!

网友答案:

Not much sure if this will be appropriate but why not make SecondRecord a instance member of FirstRecord like

public class SecondRecord
{
  //properties

  //Methods
}


public class FirstRecord
{
  public SecondRecord secondRecord;
}

Then you can get all FirstRecord and secondrecord together from database and populate accordingly which will avoid making a double call to the DB.

Per your edited post; Yes, that's what should be done. Since you already have a relationship between the tables, you can do a leftjoin on userid column (since every user may/may not have a post necessarily) and get all records and then fill the userinfo and postinfo accordingly.

select t1.userid, 
t1.username,
t2.postid,
t2.text
from table1 t1
left join table2 t2 on t1.userid = t2.userid
网友答案:

Use inner join in your query to get all the data and then in code you can fill your lists with that data. This will hit SQL only one time.

Also,Entity FrameWork will provide you what you need.

It will be your decision to switch to EF or not.

相关阅读:
Top