CODEWITHSUNDEEP
c#linq
John Doe
John Doe

Reputation: 3243

Trying to combine two tables in LINQ

Say I have a 

TableA
string Name
string Description

TableB
string Name
string Value

TableA and TableB are joined by Name. (In theory, i.e. not enforced in DB)

I want to create an object:

public MyObject
{
  string Name
  string Description
  List<string> Values
}

I'm tying to understand how to combine these using LINQ.

var tableA = _oda.GetTableA();
var tableB = _oda.GetTableB();
var model = from a in tableA
join b in tableB on a.NAME equals b.NAME
    select new MyObject
    {
         Name= a.Name,
         Description = a.Description,
         Values = "<Not sure to get list of tableb.Value>" 
    };

Upvotes: 0

Views: 44

Answers (2)

schlonzo
schlonzo

Reputation: 1406

Didn't try the code, but something like this should work.

var result = from a in TableA
             join b in TableB on a.Name equal b.Name
             group b.Value by a into g
             select new MyObject
             {
                Name = g.Key.Name,
                Description = g.Key.Description,
                Values = g.ToList()
             }

Upvotes: 1

Kevin Gosse
Kevin Gosse

Reputation: 39007

If it's an inner join, you can use GroupBy after your join:

var tableA = new List<TableA> { new TableA { Name = "1", Description = "D1" }, new TableA { Name = "2", Description = "D2"} };
var tableB = new List<TableB> { new TableB { Name = "1", Value = "V1" }, new TableB { Name = "1", Value = "V2"} };

var result = tableA.Join(tableB, a => a.Name, b => b.Name, (a, b) => new { A = a, B = b})
    .GroupBy(k => k.A, e => e.B.Value)
    .Select(g => new MyObject
    {
        Name = g.Key.Name,
        Description = g.Key.Description,
        Values = g.ToList()
    });

foreach (var res in result)
{
    Console.WriteLine("Name: {0}, Description: {1}, Value: {2}", res.Name, res.Description, string.Join(", ", res.Values));
}

Upvotes: 2

Related Questions