LinqQ Query

Goldy · 11 May 2015 at 08:29

Hi,

Im joining together 2 lists in C# with this query, which works OK.

Code:

var joinedList = from a1 in custRefs
                             join a2 in itemRefs
                             on a1.id equals a2.id
                             select new { a1.id, a1.idname, a2.itemvalue };

However, in my list there are are always 2 identical IDs, each with a different NAME. I want my Linq joined list to skip the item if its already been joined to a itemValue in itemRefs.

The Lists

custRefs:
ID, NAME
abc1, daytimeref
abc1, nighttimeref
bcd, daytimeref
bcd, nighttimeref
xyz, daytimerefer
xyz, nighttimeref

itemRefs:
ID, VALUE
xyz, 4512.0
bcd, 221.9
abc1, 6612.3

This is the result I want:

abc1, daytimeref, 6612.3
abc1, nighttimeref, 6612.3
bcd, daytimeref, 221.9
bcd, nighttimeref, 221.9
xyz, daytimerefer, 4512.0
xyz, nighttimeref, 4512.0

Its possible there is a much simpler way to do this than the way I am currently doing it. Thanks!

Gambisk · 11 May 2015 at 09:55

That is how I would go about it, looks fairly simple to me.

Goldy · 11 May 2015 at 11:25

Ah sorry, I should have been clearer. This is the result I am currently getting with the query above:

abc1, daytimeref, 6612.3
abc1, daytimeref, 6612.3
bcd, daytimeref, 221.9
bcd, daytimeref, 221.9
xyz, daytimerefer, 4512.0
xyz, daytimeref, 4512.0

You can see its returning the first NAME where the ID is joined.

This is the result I want:

abc1, daytimeref, 6612.3
abc1, nighttimeref, 6612.3
bcd, daytimeref, 221.9
bcd, nighttimeref, 221.9
xyz, daytimerefer, 4512.0
xyz, nighttimeref, 4512.0

Gambisk · 11 May 2015 at 11:59

double check the contents of the lists that you are joining. I just copied and pasted your join exactly and got the results you expect.

Phonesis · 11 May 2015 at 12:11

Having a slow morning so recreated your lists. How are they stored? I made them KeyValuePairs so I was able to re-use the same Key:

Code:

        public static List<KeyValuePair<string, string>> custRefs = new List<KeyValuePair<string, string>>();
        public static List<KeyValuePair<string, string>> itemRefs = new List<KeyValuePair<string, string>>();


        public static void SetUpLists()
        {
            custRefs.Add(new KeyValuePair<string, string>("abc1", "daytimeref"));
            custRefs.Add(new KeyValuePair<string, string>("abc1", "nighttimeref"));
            custRefs.Add(new KeyValuePair<string, string>("bcd", "daytimeref"));
            custRefs.Add(new KeyValuePair<string, string>("bcd", "nighttimeref"));
            custRefs.Add(new KeyValuePair<string, string>("xyz", "daytimeref"));
            custRefs.Add(new KeyValuePair<string, string>("xyz", "nighttimeref"));

            itemRefs.Add(new KeyValuePair<string, string>("xyz", "4512.0"));
            itemRefs.Add(new KeyValuePair<string, string>("bcd", "221.9"));
            itemRefs.Add(new KeyValuePair<string, string>("abc1", "6612.3"));
  
        }

I then called this method:

Code:

        public static void JoinLists()
        {
            var joinedList = from item1 in custRefs
                             join item2 in itemRefs
                             on item1.Key equals item2.Key
                             select new { item1.Key, item1.Value, item2Value = item2.Value };



        }

I get the result you want without issue.

Goldy · 11 May 2015 at 12:16

Sorry Gambisk, you're right. Its just me being a complete wolly. Thanks for looking into this.

Goldy · 11 May 2015 at 12:17

Yup i get the same as well Phonesis. I was duplicating the rows which was giving me an issue.