LINQ join and orderby issues

Question

OK, I've been banging my head against this for a few days, and after studying LINQ I think I am on the right track. But I have a SQL brain and that is sometimes hard to translate to C#.

I have two arrays, one sorted alphabetically, and the other ordered by ID. I need to order the second array alphabetically. The IDs are the joining factor. I.E. A.ID = P.ID.

Here are my arrays and example values;

private IGenericListItem[] _priceLevels = new IGenericListItem[0];
_priceLevels is in the form of {ID, Name}
{3, A}
{8, B}
{4, C}
{7, D}
{5, E}
{9, F}
{1, G}

Edit: updated this to show _assignmentControls contains a sub array. I didn't make it so excuse the insanity. It actually contains a copy of _priceLevels...

protected ArrayList _assignmentControls = new ArrayList();
_assignmentControls is in the form of {ID, LastPrice, NewPrice, _priceLevels[]}
{1, 1.00, 2.00, _priceLevels}
{2, 1.00, 2.00, _priceLevels}
{3, 1.00, 2.00, _priceLevels}
{4, 1.00, 2.00, _priceLevels}

Part of the problem as that I'm trying to compare/join an ArrayList and an IGenericListItem.

In SQL I would do something like this;
SELECT A.* 
FROM _assignmentControls A JOIN _priceLevels P
    ON A.ID = P.ID
ORDER BY P.Name

This Returns me an _assignmentControls table sorted by the values in _priceLevels.

In C# LINQ I got this far, but can't seem to get it right;

        var sortedList =
            from a in _assignmentControls
            join p in _priceLevels on a equals p.ID
            orderby p.Name
            select _assignmentControls;

I am getting red squigglies under join and orderby and the p in p.Name is red. And A) it doesn't work. B) I'm not sure it will return sortedList as a sorted version of _assignmentControls sorted by _priceLevels.Name.

EDIT: When I hover over "join" I get "The type arguments for the method 'IEnumerable System.Linq.Enumerable.Join(this Enumerable,IEnumerable, Func,Func....'cannot be infered from the query. I am researching that now.

Thanks for looking!

Answer 1

When I hover over "join" I get "The type arguments for the method IEnumerable System.Linq.Enumerable.Join(this Enumerable,IEnumerable, Func,Func.... cannot be infered from the query.

I can explain what is going on here so that you can track it down.

When you say

from firstitem in firstcollection
join seconditem in secondcollection on firstkey equals secondkey
select result

the compiler translates that into:

Enumerable.Join(
    firstcollection,
    secondcollection, 
    firstitem=>firstkey, 
    seconditem=>secondkey, 
    (firstitem, seconditem)=>result)

Enumerable.Join is a generic method that has four type parameters: the element type of the first collection, the element type of the second collection, the key type, and the result type.

If you're getting that error then one of those four things cannot be deduced given the information you've provided to the compiler. For example, maybe:

• The type of the first collection is not actually a sequence.
• The type of the second collection is not actually a sequence.
• The type of the result cannot be deduced
• The two keys are of inconsistent types and there is no unique best type.

That last point is the most likely one. Suppose for example the first key is int and the second key is short. Since every short can be converted to int, int would win, and the second key would be automatically converted to int. Now suppose that the first key type is Giraffe and the second key type is Tiger. Neither is better than the other. C# does not say "oh, they're both kinds of Animal, so let's pick that." Rather, it says that you haven't provided enough information to determine which one you meant; you should cast one of them to Animal and then it becomes clear.

Make sense?

There's a half-hour video of me explaining this feature back in 2006 -- this was back when I was adding the feature in question to the compiler -- so if you want a more in-depth explanation, check it out.

http://ericlippert.com/2006/11/17/a-face-made-for-email-part-three/

UPDATE: I just read your question again more carefully:

Part of the problem as that I'm trying to compare/join an ArrayList and an IGenericListItem.

There's the problem. The type of the sequence cannot be determined from an ArrayList. You should not use ArrayList anymore. In fact, you should not use it in any code written after 2005. Use List<T> for some suitable T.

Answer 2

I think you should write:

var sortedList =
                from a in _assignmentControls
                join p in _priceLevels on a.ID equals p.ID
                orderby p.AnotherValue
                select a;

When you write from a in _assignmentControls - you are declaring a variable that refers to current element in a sequance that the operation to be performed on. And when you're calling select - you're projecting element from the sequence. Imagine it like conveyer.

Let me give you some example with dump data:

public class SomeCLass
        {
            public int ID { get; set; }
            public string Name { get; set; }
        }

        public class AnotherClass
        {
            public int ID { get; set; }
            public int Value { get; set; }
            public int AnotherValue { get; set; }
        }

public void TestMEthod()
        {
            List<SomeCLass> _assignmentControls = new List<SomeCLass>()
                {
                    new SomeCLass() { ID = 1, Name = "test"},
                    new SomeCLass() { ID = 2, Name = "another test"}
                };
            List<AnotherClass> _priceLevels = new List<AnotherClass>()
                {
                    new AnotherClass() {ID = 1, AnotherValue = 15, Value = 13},
                    new AnotherClass() {ID = 2, AnotherValue = 5, Value = 13}
                };


            var sortedList =
            //here you're declaring variable a that will be like caret when you going through _assignmentControls
            from a in _assignmentControls
            join p in _priceLevels on a.ID equals p.ID
            orderby p.AnotherValue
            select a;


            foreach (var someCLass in sortedList)
            {
                Console.WriteLine(someCLass.Name);
            }
        }

Result:

another test
test

Answer 3

Your select clause is wrong, it should be like this:

    var sortedList =
        from a in _assignmentControls
        join p in _priceLevels on a equals p.ID
        orderby p.Name
        select a;

Another issue is that _assignmentControls is of type ArrayList, which has elements of type Object, so the compiler doesn't know the actual type of a, and can't use it as the join criteria since a doesn't have the same type as p.ID.

You should use a List<int> (assuming p.ID is of type int) instead of ArrayList. Another option is to specify the type of a explicitly:

    var sortedList =
        from int a in _assignmentControls
        join p in _priceLevels on a equals p.ID
        orderby p.Name
        select a;