CODEWITHSUNDEEP
c#sqlsql-serverlinq
KyloRen
KyloRen

Reputation: 2741

LINQ to SQL ,Join tables to get distinct result?

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?

Upvotes: 0

Views: 2306

Answers (2)

J-Mean
J-Mean

Reputation: 1200

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

Upvotes: 1

SS_DBA
SS_DBA

Reputation: 2423

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();

Upvotes: 1

Related Questions