CODEWITHSUNDEEP
c#linqdatatable
Expert Novice
Expert Novice

Reputation: 1963

Multiple joins using linq

This is my query:

var results = from table1 in dtSplitDates.AsEnumerable()
                      join table2 in dtSplitDates.AsEnumerable() on (int)table1["FID"] equals (int)table2["FID"] into lj
                      from r in lj.DefaultIfEmpty()
                      select dtSplitDates2.LoadDataRow(new object[]
                        {
                            r["FID"],
                            r["SLNO"],
                            r == null ? string.Empty : r["Dates"]
                        }, false);

Currently i am joining on Column FID - due to which i am getting 36 records (duplicates):

However in order to avoid duplicates i need to join also on SLNO column but i am unable to write that query - please help.

Upvotes: 1

Views: 73

Answers (2)

tango
tango

Reputation: 39

As per my understanding you want two join condition; Try this

   var results = from table1 in dtSplitDates.AsEnumerable()
                  join table2 in dtSplitDates.AsEnumerable() 
                  on new {id1 =(int)table1["FID"], SLno1= (int)table1["SLNO"]} 
                  equals new {id2=(int)table2["FID"], SLno2=(int)table2["SLNO"]}  into lj
                  from r in lj.DefaultIfEmpty()
                  select dtSplitDates2.LoadDataRow(new object[]
                    {
                        r["FID"],
                        r["SLNO"],
                        r == null ? string.Empty : r["Dates"]
                    }, false);

Upvotes: 2

Vishal
Vishal

Reputation: 2017

Try to implement with this example:

For Multiple Joins:

var result=(from com in db.Company.AsEnumerable()
                    join c in db.Country.AsEnumerable() on com.CountryID equals c.CountryID
                    join s in db.State.AsEnumerable() on com.StateID equals s.StateID
                    join ct in db.City.AsEnumerable() on com.CityID equals ct.CityID
                    orderby com.Name
                    select new CompanyModel()
                     {
                         CompanyID = com.CompanyID,
                         Name = com.Name,
                         AddressLine1 = com.AddressLine1,
                         CountryID = com.CountryID,
                         StateID = com.StateID,
                         CityID = com.CityID,
                         Country = c.CountryID,
                         State = s.StateID,
                         City = ct.CityID,
                         Pin = com.Pin,
                         Phone = com.Phone,

                     }).Distinct().ToList();

Upvotes: 0

Related Questions