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
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.