CODEWITHSUNDEEP
asp.netsqldatabaselinq-to-sql
Novkovski Stevo Bato
Novkovski Stevo Bato

Reputation: 1043

LINQ to SQL get one-to-many table column result as array

I have one table[T1] with this informaions

  1. ID
  2. UserID
  3. Other...

2nd table[T2] is

  1. ID
  2. UserID
  3. Name

Relationship is one user from T1 can have many T2

I want to get result like

  1. ID = number
  2. UserID = number
  3. array[t2.name,t2.name,t2.name]

My ling to sql is like 

 var result = (from t1 in context.t1
              join t2 in context.UserID on t1.ID equals t2.UserID 
              select new CLASS
              {
                  ID = t1.ID,
                  UserID = t1.UserID,
                  Names = t2.name
               }).Take(10).ToList();

But this give me result as each t2.name as separated row. How i can gather all names in array?

LINQ to SQL , ASP.NET C# 4.0

Upvotes: 1

Views: 1137

Answers (3)

Juan Leung
Juan Leung

Reputation: 356

I think something like this should work

var result = (from t1 in context.t1
              join t2 in context.UserID on t1.ID equals t2.UserID 
              select new CLASS
              {
                  ID = t1.ID,
                  UserID = t1.UserID,
                  Names = (from t2 in context.t2
                           select t2.Name
                           where t2.userID = t1.userID).toArray(),
              }).Take(10).ToList();

hope it helps

Upvotes: 1

MarcinJuraszek
MarcinJuraszek

Reputation: 125620

You have to group your results by ID/UserID:

 var result = (from t1 in context.t1
              join t2 in context.UserID on t1.ID equals t2.UserID
              group by new { t1.ID, t1.UserID } into g
              select new CLASS
              {
                  ID = g.Key.ID,
                  UserID = g.Key.UserID,
                  Names = g.ToArray()
              }).Take(10).ToList();

However, when LINQ to SQL classes are prepared right that kind of queries can be made like:

var result = from t1 in context.t1 select new { id = t1.id, names = t1.t2s.ToArray() };

Upvotes: 0

orandov
orandov

Reputation: 3276

You don't need to use the join. Create a relationship b/n your two linq entites in the linq designer and then you can run a query like this:

var q = from t in context.t1
        select new { id = t1.id, names = t1.t2s.Select(t => t.Name).ToArray() };

Upvotes: 0

Related Questions