LINQ to SQL ,Join tables to get distinct result?

Question

I am trying to write a LINQ query that will get me some distinct values from two SQL Server data tables.

I have two tables named, Facility_Cost_TBL and Tenant_Bills_TBL. I then have a column that is named Nursing_Home_Name which I am trying to get the distinct data from.

This is my effort in LINQ , however it does not work,

var name = (from f in dataContext.Facility_Cost_TBLs
                join t in dataContext.Tenant_Bills_TBLs on f.Tenant_Code equals t.Tenant_Code
                where f.Tenant_Code == code && f.Date_Month == date.Month && f.Date_Year == date.Year
                select new {Facility_Cost_TBL = f, Tenant_Bills_TBL = t}).Distinct();

And this is a working SQL statement I made that does what I want via T-SQL.

SELECT DISTINCT Nursing_Home_Name 
         FROM (SELECT Nursing_Home_Name 
         FROM Facility_Cost_TBL 
         WHERE Date_Year = 2016 AND Date_Month = 10 AND Tenant_Code = 664250 
         UNION SELECT Nursing_Home_Name 
         FROM Tenant_Bills_TBL 
         WHERE Year_Data = 2016 AND Month_Data = 10 AND Tenant_Code = 664250) 
         a

Could someone show me what LINQ sytax AND what LINQ extension method query would look like?

In your `LINQ` join, shouldn't you be joining on `Year` and `Month` too? — SS_DBA, Nov 30 '16 at 13:25
@WEI_DBA, I don't know how to do that which is why this is not working. — KyloRen, Nov 30 '16 at 13:47
The problem with your Linq statement is that it is doing an inner join and hence only returning results if you have entries if both tables. — sgmoore, Nov 30 '16 at 14:11
@sgmoore, Thanks for the help, I didn't quite know what I was doing wrong. Still new to LINQ. — KyloRen, Nov 30 '16 at 23:18

J-Mean · Accepted Answer · 2016-11-30T14:11:39.503

1

Please try following

var names = ((from f in dataContext.Facility_Cost_TBLs 
                                          where f.Tenant_Code == "664250" && f.Date_Month == "10" && f.Date_Year == "2016"
                                          select new { Nursing_Home_Name = f.Nursing_Home_Name }).
                                          Union(
                                          from t in dataContext.Tenant_Bills_TBLs
                                          where t.Tenant_Code == "664250" && t.Date_Month == "10" && t.Date_Year == "2016"
                                          select new { Nursing_Home_Name = t.Nursing_Home_Name })).ToList();

Hope this will help you

edited Nov 30 '16 at 14:11

answered Nov 30 '16 at 13:44

J-Mean

1,192
1
8
14

Interesting approach to this. I was looking for a single query if possible. – KyloRen Nov 30 '16 at 13:47
If you remove the `.ToList()` on the first two querys, then you will end up with one sql query. – sgmoore Nov 30 '16 at 14:03
1

There isn't any need to include the `.ToDistinct()` as `.Union` will automatically exclude duplicates. – sgmoore Nov 30 '16 at 14:05
Thank you @sgmoore and KyloRen Please review had modified solution :) – J-Mean Nov 30 '16 at 14:12
Thanks, now I see what I was doing wrong. I didn't think to use Union for a LINQ query. UV and marked the answer solved. Thanks again. – KyloRen Nov 30 '16 at 23:03

score 1 · Answer 2 · edited May 23 '17 at 11:53

1

Try this to see if this works. LINQ to SQL: Multiple joins ON multiple Columns. Is this possible?

var name = (from f in dataContext.Facility_Cost_TBLs
                join t in dataContext.Tenant_Bills_TBLs  equals on new { f.Tenant_Code, f.Date_Month, f.Date_Year } equals new { t.Tenant_Code, t.Date_Month, t.Date_Year } 
                where f.Tenant_Code == code && f.Date_Month == date.Month && f.Date_Year == date.Year
                select new {Facility_Cost_TBL = f, Tenant_Bills_TBL = t}).Distinct();

edited May 23 '17 at 11:53

Community

1
1

answered Nov 30 '16 at 13:54

SS_DBA

2,403
1
11
15

Thanks for the effort, I found @J-Mean 's method to be better for what I need. UP vote for the help. – KyloRen Nov 30 '16 at 23:03

LINQ to SQL ,Join tables to get distinct result?

2 Answers2