Retrieve index in LinQ Lambda Join

Question

I'm quite new to all the .Net stuff and just wondering about something I would like to do. I've been using for some time the LinQ Lambda queries to return list values with the Select statement, like this:

        var data = list.Select((x, i) =>
            new
            {
                Index = i,       // <--- this is what I need!
                Value = x.Value,
                Text = x.Text
            })
            .ToList();

The thing is that I have a LinQ Lambda Join expression as it follows:

        var data = DataBase.Table1
            .Join(DataBase.Table2,
                x => x.Id,
                y => y.Id,
                (x, y) => new    // <--- I can't do the same here :(
                {
                    Index = ???, // <--- Here is where I need the index!!
                    Value = x.Id,
                    Text = y.Description,
                })
            .OrderBy(x => x.Id)
            .ToList();

My question is, how can I retrieve the index in this second linq query as I did in the first?

Are you sure Linq to SQL can translate Select with index to SQL? I think you first query will not work. — Sergey Berezovskiy
– Sergey Berezovskiy, Commented Jun 17, 2014 at 10:28
True, sorry, the first statements works with object lists. My confusion is beacuse the database tables I normally use are parsed to objects by EntityFramework. I've edited the question. The matter is that in the end all are IEnumerable lists so I think there must be a suitable way to do it! — Kutyel
– Kutyel, Commented Jun 17, 2014 at 11:07

Sergey Berezovskiy · Accepted Answer · 2014-06-17 16:18:44Z

3

You can't use Select<T>(Func<T,int,bool>) with Linq to SQL. You will see NotSupportedException if you'll try to execute your first query:

Unsupported overload used for query operator 'Select'

You can use this operator with Linq to Objects. Just move query execution to memory with AsEnumerable() call. With your second query add another projection after you received result of join and moved execution to memory:

 var data = DataBase.Table1
            .Join(DataBase.Table2,
                t1 => t1.Id,
                t2 => t2.Id,
                (t1, t2) => new { Value = t1.Id, Text = t2.Description }
            )
            .AsEnumerable() //otherwise next operator cannot be translated to SQL
            .Select((x,i) => new { x.Value, x.Text, Index = i})
            .OrderBy(x => x.Value)
            .ToList();

UPDATE: If you want index of t2, then you can use GroupJoin instead of Join:

 var data = DataBase.Table1
            .GroupJoin(DataBase.Table2,
                t1 => t1.Id,
                t2 => t2.Id,
                (t1, t2items) => new { Value = t1.Id, t2items }
            )
            .AsEnumerable() //otherwise next operator cannot be translated to SQL
            .SelectMany(x => x.t2items.Select((t2, i) => 
               new { x.Value, Text = t2.Description, Index = i}))
            .OrderBy(x => x.Value)
            .ToList();

If you need both indexes, then move whole join to memory:

 var data = DataBase.Table1.AsEnumerable().Select((t1,i) => new { t1, i })
            .Join(DataBase.Table2.AsEnumerable().Select((t2,i) => new { t2, i }),
                  x1 => x1.t1.Id,
                  x2 => x2.t2.Id,
                  (x1, x2) => new { 
                      Value = x1.t1.Id, 
                      Text = x2.t2.Description,
                      Index1 = x1.i,
                      Index2 = x2.i
                  })
            .OrderBy(x => x.Value)
            .ToList();

edited Jun 17, 2014 at 16:18

answered Jun 17, 2014 at 10:37

Sergey Berezovskiy

237k44 gold badges441 silver badges468 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Kutyel Over a year ago

I'm afraid your answer is not what I'm looking for, I need the index inside the inner select of the join. I've edited my question.

Sergey Berezovskiy Over a year ago

Inner select of join returns single item. It does not have any index. Please clarify what index you want

Kutyel Over a year ago

It does return a list of items, the ones that match the conditions of the inner join, and if it returns a list, it has an index.

Sergey Berezovskiy Over a year ago

No. Join returns single pair of items - one from each collection. It does not return list of items. What index you want - index of t1 from Table1 or index of t2 from Table2?

Collectives™ on Stack Overflow

Retrieve index in LinQ Lambda Join

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related