Click here to Skip to main content
13,734,179 members
Rate this:
 
Please Sign up or sign in to vote.
See more:
I have tbl1 and tbl2

tbl1:

id Service
==================
1 Microsoft
2 Oracle
3 Java


tbl2

Service RollNumber
==========================
Microsoft 13
Microsoft 12
Java 2
Java 16
Microsoft 15

How can i get as below using linq:

Service Counts
===================
Microsoft 3
Java 2
Oracle 0

What I have tried:

var data = from t1 in tbl1
join t2 in tbl2
on t1.Service.Trim() equals t2.Service.Trim() into eGroup
from t2in eGroup.DefaultIfEmpty()
Posted 19-Sep-18 19:13pm
Updated 19-Sep-18 23:34pm
Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 1

You are doing that wrong: tbl2 should contain the ServiceID, not the name:
ServiceID RollNumber
1          13
1          12
3           2
3          16
2          15
And use a JOIN to access the Service name.

That does make the final query you want slightly more complex, but not a lot:
SELECT a.Service, ISNULL(b.CNT, 0) 
FROM tbl1 a
LEFT JOIN (SELECT ServiceID, COUNT(RollNumber) AS CNT 
           FROM tbl2
           GROUP BY ServiceID) b 
       ON a.ID = b.ServiceID
And do yourself two favours:
1) Use sensible table names! It makes your queries so much more readable, and thus reliable...
2) Add an IDENTITY id column to tbl2, to prevent duplication. You don't need to use it in most of your queries, but SQL will not allow you to have duplicate rows.
  Permalink  
Comments
shaprpuff 20-Sep-18 2:37am
   
both tables are from different databases so cannot add key to database i have join them by linq.
Maciej Los 20-Sep-18 5:36am
   
You're right OG, but OP wants to get count of services in a table #1 based on data in table #2.
Please, see my answer.
Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 2

Try this:
var data = from t1 in tbl1
    join t2 in tbl2 on t1.Service.Trim() equals t2.Service.Trim() into eGroup
    from together in eGroup.DefaultIfEmpty()
    select new 
    {
       Service = together.?Service,
       Count = together.Count()
    };

For further details, please see: Perform left outer joins (LINQ in C#) | Microsoft Docs[^]

Another way is to calculate count of records in a table #2 based on service name:
var result = tbl1
	.Select(x=> new
	{
		Service = x.Service,
		Count = tbl2.Count(y=> y.Service.Trim()==x.Service.Trim()))
	});


Good luck!
  Permalink  

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
Top Experts
Last 24hrsThis month


Advertise | Privacy | Cookies | Terms of Service
Web01-2016 | 2.8.180920.1 | Last Updated 20 Sep 2018
Copyright © CodeProject, 1999-2018
All Rights Reserved.
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100