CODEWITHSUNDEEP
c#entity-frameworklinq
vamsi penta
vamsi penta

Reputation: 73

Linq query to join tables on multiple conditions C#

I am new to Writing Linq Queries and would like to write a query as below.

Brief Info on Requirement:

I need to get a count of distinct group keys for a user with a join to another table having its name

TABLE - 1:                                         Table - 2: 
---------------                                    -------------

| Id | GrpKey | prdId | UserId|                    | Id | GrpKey | GrpName  | UserId
| 1  | 123455 | Test1 | 11111                      | 1  | 123455 | GroupOne | 1111
| 2  | 123455 | Test2 | 22222                      | 2  | 551234 | GroupTwo | 1111
| 3  | 123455 | Test3 | 22222
| 4  | 551234 | Test4 | 11111
| 5  | 551234 | Test5 | 11111
| 6  | DBNULL | Test4 | 11111
| 7  | DBNULL | Test5 | 11111

REQD. RESULT for UserId : 11111 
--------------------------------

GrpKey | GrpName | Count(GrpKey)
DBNULL | DBNULL  |  2
551234 | GroupTwo|  2
123455 | GroupOne|  1

Queries Tried:
1)
from grp in table2
                          join item in table1 on grp.GrpKey equals item.GrpKey  into j1
                          where grp.UserId == "1111"
                          select new Group
                          {
                                Count = j1.Count(),
                                Name = grp.GrpName,
                                Key = grp.GrpKey,
                          }).ToList();

2)
var result = (from item in table1
                          join grp in table2 on item.GrpKey equals grp.GrpKey into j1
                          from rt in j1.DefaultIfEmpty()
                          where item.userId == userId
                          select new Group
                          {
                            GrpKey = item.GrpKey,
                            userId = item.userId,
                            Count = j1.Count(),
                            GrpName = rt.GroupName
                          }).ToList();

Issues With TriedQuery1:

With the above LINQ query, I am able to get the count of all values except the rows having GrpKey and GrpName as NULL. Can Anybody help me with a query to get data as per my required data set

Issues With TriedQuery2:

Count of Rows having null or coming Zero even though there are rows with values as NULL.

Thanks in Advance

Upvotes: 2

Views: 224

Answers (1)

Mohammed Sajid
Mohammed Sajid

Reputation: 4913

For the second query, you could add Group by for lefted join result, like the following code : 

var result = (from item in table1
              join grp in table2 on item.GrpKey equals grp.GrpKey into j1
              from rt in j1.DefaultIfEmpty()
              where item.userId == 11111
              group rt by rt?.GrpKey into g
              select new
              {
                 GrpKey = g.Key,
                 GrpName = g.First()?.GrpName,
                 Count = g.Count(),
              }).ToList();

I hope you find this helpful.

Upvotes: 2

Related Questions