LINQ to SQL

I have two datatables T1 and T2
I neeed to do a join but only get the first or default from T2
Because T2 could have multiple rows that match the join condition

I tried this
 var result = (from dataRows1 in T1.AsEnumerable()
                            join dataRows2 in T2.AsEnumerable()
                            on dataRows1["ID"] equals dataRows2["ID"]
                            select new Returns
                            {
                                ID = dataRows1["ID"],
                                Date = dataRows1["Date"],
                                StartPrice = Convert.ToDecimal(dataRows2["StartPrice"]),
                                EndPrice = Convert.ToDecimal(dataRows2["EndPrice"])

                            }).Distinct().ToList();
LVL 1
countrymeisterAsked:
Who is Participating?
 
käµfm³d 👽Connect With a Mentor Commented:
Add a group by to the mix:

e.g.

 var result = (from dataRows1 in T1.AsEnumerable()
               join dataRows2 in T2.AsEnumerable()
               on dataRows1["ID"] equals dataRows2["ID"]
               group new { ID = dataRows1["ID"], Date = dataRows1["Date"], StartPrice = dataRows2["StartPrice"], EndPrice = dataRows2["EndPrice"] }
               by dataRows1["ID"] into g
               let first = g.FirstOrDefault()
               let firstDate = (first == null ? (DateTime)null : Convert.ToDateTime(dataRows1["Date"]))
               let firstStartPrice = (first == null ? 0M : Convert.ToDecimal(dataRows2["StartPrice"]))
               let firstEndPrice = (first == null ? 0M : Convert.ToDecimal(dataRows2["EndPrice"]))
               select new Returns
               {
                   ID = g.Key,
                   Date = firstDate,
                   StartPrice = firstStartPrice,
                   EndPrice = firstEndPrice,

               }).ToList();

Open in new window

0
 
Fernando SotoConnect With a Mentor RetiredCommented:
Hi countrymeister;

You state the following, "I need to do a join but only get the first or default from T2", it is possible the the other lines are getting filtered out because of the use of the Distinct method call in the query. To test remove the Distinct() and leaving everything else in place.
0
 
countrymeisterAuthor Commented:
I removed the distinct that did not help
t1 has 2550 rows, and T2 has 4890 rows, I just want the 2550 rows back in my join

Example t2 could have multiple rows with ID matching the ID in T1
0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
Fernando SotoRetiredCommented:
How many rows are you getting back?
0
 
countrymeisterAuthor Commented:
I am getting 4890 rows back
0
 
Fernando SotoRetiredCommented:
I have tried similar Linq query and it seems to work on my systems as expected.

Running the query using the Distinct() method and you tell me the number of records returned?

Also can you post a screen shot of the results where data is missing?
0
All Courses

From novice to tech pro — start learning today.