1

I have a function that uses Lambda to join 2 tables

the below code is working fine when I try to order by:

List<site> sites = cityContext.sites.OrderBy(x => x.SiteID).ToList();

but when I try to join 2 tables, I am confused with the syntax

List<site> sites = cityContext.sites.Join(cityContext.benches, st => st.SiteID, bh => bh.SiteID, (st, bh) => new { site = st, bench = bh }).select().ToList();

I want to connect 2 tables benches and sites on SiteID.

I have also tried the below code, but I am not getting the same results.

 var sites = (from bs in cityContext.benches
                     join st in cityContext.sites on bs.SiteID equals st.SiteID
                     orderby st.SiteID
                     select new 
                     {
                         st.SiteID,
                         st.Category,
                         st.Borough,
                         st.Type,
                         st.Confirm_Verdict,
                         st.ComDist,
                         st.Bus_Route,
                         st.BID,
                         st.ID,
                         st.Plaza,
                         st.Benches,
                         st.Address,
                         st.Link_Path,
                         st.Directory_Link,
                         st.Street,
                         st.Geocode_Address,
                         st.Zip_Code,
                         st.Description,
                         st.SW_Location,
                         st.X_Street,
                         st.Confirm_Date,
                         st.Installation_Date
                     }).ToList();
D Stanley
  • 149,601
  • 11
  • 178
  • 240
Raj Parekh
  • 94
  • 1
  • 12
  • You'll get multiple results when there are multiple benches at one site, and no results when there are no benches. Is that the problem with the results? Your query-syntax join looks fine. Joins in lambda syntax are hard to read. – D Stanley Aug 28 '17 at 21:54
  • Why are you joining anyways if you aren't including the joined table in the results? – D Stanley Aug 28 '17 at 21:55
  • 1
    You are missing the `Where` portion, https://stackoverflow.com/questions/2767709/c-sharp-joins-where-with-linq-and-lambda – nurdyguy Aug 28 '17 at 22:08
  • what is relation of bench and site, 1 to many, 1 to 1 or many to 1? – Mohammad Akbari Aug 29 '17 at 03:23
  • @DStanley I am joining 2 tables as I need only those sites that are present in bench table. – Raj Parekh Aug 29 '17 at 21:22
  • @MohammadAkbari One Site -> Many Bench – Raj Parekh Aug 29 '17 at 21:23
  • @Rparekh with your code, sites that not have any benches, not contain in query result, this is correct with your query. Is this a problem? – Mohammad Akbari Aug 30 '17 at 03:22
  • @MohammadAkbari the problem is resolved. I was not able to retrieve correct results as **'Type'** column was in both the tables. Thank you for help. – Raj Parekh Sep 05 '17 at 14:22

0 Answers0