LINQ: Doing an order by!

Question

Hi there,

i have some Linq to Entity code like so:

var tablearows = Context.TableB.Include("TableA").Where(c => c.TableBID == 1).Select(c => c.TableA).ToList();

So i'm returning the results of TableA with TableB.TableBID = 1

That's all good

Now how can I sort TableA by one of its column? There is a many to many relation ship between the two tables

I tried various ways with no look, for example

var tablearows = Context.TableB.Include("TableA").Where(c => c.TableBID == 1).Select(c => c.TableA).OrderBy(p => p.ColumnToSort).ToList();

In the above case when i type "p." i don't have access to the columns from TableA, presumably because it's a collection of TableA objects, not a single row

Answer 1

How about using SelectMany instead of Select :

var tablearows = Context.TableB.Include("TableB")
     .Where(c => c.TableBID == 1)
     .SelectMany(c => c.TableA)
     .OrderBy(p => p.ColumnToSort)
     .ToList();

EDIT : The expression below returns collection of TableAs -every element of the collection is an instance of TableA collection not TableA instance- (that's why you can't get the properties of the TableA) :

var tablearows = Context.TableB.Include("TableB")
     .Where(c => c.TableBID == 1)
     .Select(c => c.TableA);

If we turn the Select to SelectMany, we get the result as one concatenated collection that includes elements :

var tablearows = Context.TableB.Include("TableB")
     .Where(c => c.TableBID == 1)
     .SelectMany(c => c.TableA);

Answer 2

Okay, so now I've taken on board that there's a many to many relationship, I think Canavar is right - you want a SelectMany.

Again, that's easier to see in a query expression:

var tableARows = from rowB in Context.TableB.Include("TableA")
                 where rowB.TableBID == 1
                 from rowA in rowB.TableA
                 orderby rowA.ColumnToSort
                 select rowA;

The reason it didn't work is that you've got a different result type. Previously, you were getting a type like:

List<EntitySet<TableA>>

(I don't know the exact type as I'm not a LINQ to Entities guy, but it would be something like that.)

Now we've flattened all those TableA rows into a single list:

List<TableA>

Now you can't order a sequence of sets by a single column within a row - but you can order a sequence of rows by a column. So basically your intuition in the question was right when you said "presumably because it's a collection of TableA objects, not a single row" - but it wasn't quite clear what you mean by "it".

Now, is that flattening actually appropriate for you? It means you no longer know which B contributed any particular A. Is there only actually one B involved here, so it doesn't matter? If so, there's another option which may even perform better (I really don't know, but you might like to look at the SQL generated in each case and profile it):

var tableARows = Context.TableB.Include("TableA")
                        .Where(b => b.TableBID == 1)
                        .Single()
                        .TableA.OrderBy(a => a.ColumnToSort)
                        .ToList();

Note that this will fail (or at least would in LINQ to Objects; I don't know exactly what will happen in entities) if there isn't a row in table B with an ID of 1. Basically it selects the single row, then selects all As associated with that row, and orders them.