CODEWITHSUNDEEP
linq
Mirza
Mirza

Reputation: 1

Linq join two tables and Get a count column

I have two tables.

TableA

TableAId | value

 1        a     
 2        ab    
 3        abc
 4        abcd

TableB

TableBId | TableAId | success

 1        1     y
 2        2     n
 3        2     n
 4        1     y
 5        3     y
 6        2     y

I am trying to create list like

TableAId | value | Count(from tableB where status = y)

 1        a     2
 2        ab    1
 3        abc   1
 4        abcd  0

Any help how I can query would be appreciated.

Thanks in advance

Upvotes: 0

Views: 47

Answers (2)

Svyatoslav Danyliv
Svyatoslav Danyliv

Reputation: 27282

This query should be effective:

var query =
    from a in TableA
    join b in TableB on a.TableAId equals TableBId 
    group b by new { a.TableAId, a.value } into g
    select new 
    {
        g.Key.TableAId, 
        g.Key.value,
        Count = g.Sum(x => x.status == 'y' ? 1 : 0)
    }

Upvotes: 0

Harald Coppoolse
Harald Coppoolse

Reputation: 30464

It seems to me that every A from tableA has zero or more Bs from tableB; every B from tableB belongs to exactly one A, namely the A that foreign key TableAId refers to: a straightforward one-to-many relation.

Whenever you have a one-to-many relation, and you want "items with their zero or more subitems", like Schools with their Students, or Customers with their Orders, or in your case "As with their Bs", consider to use one of the overloads of Queryable.GroupJoin

If you have your tables in your local process, use the variant with IEnumerable. The principle remains the same.

In a one-to-many relation, if you want to start at the "one" side: items with their sub-items use GroupJoin, if you want to start at the "many" side: items with their one and only parent, use Join.

I use the overload with a parameter resultSelector, so I can specify the exact result:

(note: as is plural of a, bs is plural of b)

IQueryable<A> tableA = ...
IQueryable<B> tableB = ...

var asWithTheSuccessfulBCount = tableA.GroupJoin(

    a => a.TableAId,     // from every a in tableA take the primary key
    b => b.TableAId,     // from every b in tableB take the foreign key

    // parameter resultSelector: from every a in tableA, with the zero or more bs from tableB,
    // that belong to this a, make one new object:
    (a, bsOfThisA) => new
    {
        TableAId = a.TableAId,
        Value = a.Value,

        Count = bsOfThisA.Where(b => b.Status == y).Count(),
    });

I am not sure about the type of Status: char? string? You'll have to add the correct quotes. By the way: Why not enum or Boolean?

In words: from every A in tableA, find all Bs in tableB that have a value for foreign key that equals the value from the primary key of A. For this A, and all Bs of this A, make one new object, containing the following properties:

  • TableAId and Value: taken from A
  • Count: from all Bs of this A, keep only those Bs that have a value for property Status equal to y. Count the remaining Bs.

Simple comme bonjour!

Upvotes: 2

Related Questions