CODEWITHSUNDEEP
c#asp.netperformancelinqlist
Liz
Liz

Reputation: 413

Set property of a List which is also a List and from another linq query

I have the sample code below. I'd like to join all children of a user. There is a one to many relationship between UserInfo and ChildInfo. I have tried this using a nested select but took a lot of time to query for huge number of data. I need to return all UserInfo with joined ChildInfo. Is there a simple linq solution on this?

public class UserInfo 
{
    private int _userId;
    public int UserId
    {
        get { return _userId; }
        set { _userId = value; }
    }
    private string _name;
    public string Name
    {
        get { return _name; }
        set { _name = value; }
    }       
    private List<ChildInfo> _childInfo;
    public List<ChildInfo> ChildInfo
    {
        get { return _childInfo; }
        set { _childInfo = value; }
    }
}

public class ChildInfo
{
    private int _childId;
    public int ChildID
    {
        get { return _childId; }
        set { _childId = value; }
    }       
    private int _userId;
    public int UserId
    {
        get { return _userId; }
        set { _userId = value; }
    }
    private string _childName;
    public string ChildName
    {
        get { return _childName; }
        set { _childName = value; }
    }
}

var userList = new List<UserInfo>();      
userList = (from db in context.UserInfos                                                                        
    select new UserInfo ()
    {
        UserId = db.ID,
        Name = db.Name
    }).ToList();

var childList = new List<ChildInfo>();
childList = (from child in context.Children
          join user in userList on child.UserID equals cus.ID
          select new ChildInfo()
        {
            ChildID = db.ID,
            UserId = db.UserId,
            ChildName = db.Name    
        }).ToList();

Upvotes: 0

Views: 105

Answers (4)

Yaur
Yaur

Reputation: 7452

I belive what you want is:

userList = (from user in context.UserInfos                                                                        
select new UserInfo ()
{
    UserId = user.ID,
    Name = user.Name
    ChildInfo = (from child in context.ChildInfo where child.UserId == user.Id select
    new ChildInfo
    {
        ChildID = child.ID,
        UserId = user.Id,
        Name = child.Name
    }).ToList()
}).ToList();

If that is to slow chances are you are missing an index on the UserId column.

Upvotes: 0

King King
King King

Reputation: 63367

From what I can see in your query, all the rows in the last result have Child.ID = User.ID because of the join condition. But you still want to select both Child.ID and User.ID? In fact your query equals to this one:

var childList = from child in context.Children
                where context.UserInfos.Any(x=>x.ID == child.ID)
                select new ChildInfo(){
                           ChildID = child.ID,
                           UserID = child.ID,
                           ChildName = child.Name
                       };

Upvotes: 0

user2711965
user2711965

Reputation: 1825

Your current queries would execute twice on the database, once for getting a user list and later to get the ChildInfo instead you may execute one query with a join like:

childList = (from child in context.Children
          join user in context.UserInfos on child.ID equals user.ID
          select new ChildInfo()
        {
            ChildID = db.ID,
            UserId = db.UserId,
            ChildName = db.Name    
        }).ToList();

Upvotes: 0

D Stanley
D Stanley

Reputation: 152626

You could try to join the tables directly instead of the hydrated results:

from child in context.Children
join user in context.UserInfos 
    on child.userID equals user.ID
select new ChildInfo()
    {
        ChildID = child.ID,
        UserId = user.ID,
        ChildName = child.Name    
    }).ToList();

Upvotes: 1

Related Questions