Getting records from many to many relationship with their order in the joining table using EF?

Question

I have a table T1 with many to many relationship with T2, the joining table is T12, I want to get the related records from T2, giving the Id of the record in T1, using EF and LINQ, I'm doing it like so:

var listIds = oContext.T12.Where(x => x.T1Id == id).Select(x => x.T2Id).ToList(); 
//then 
var theList = oContext.T2.In(x => x.Id, listIds).ToList(); 

The implementation of In.

I don't think this is the best way querying data from m-t-m relationship. But the real problem is that records from T2 aren't in order they appear in SQL (the order matter in the application):

but their order is T2Id: 58478, 58479, 58480, 58481

EDIT

I used OrderBy to get listIds in order, but the list still not in the order, I went to SSMS, and made that query:

SELECT* FROM T2 WHERE Id IN(58481, 58478, 58479, 58480)

and this is the result:

Answer 1

Your gut feeling is correct, it is not the best way. If you set-up the many-to-many relationship in Entity Framework correctly, you don't have to mention the joinging table T12.

See Configure Many To Many.

Adjusted to your classes:

class T1
{
    public int Id {get; set;}

    // every T1 has zero or more T2
    public virtual ICollection<T2> T2s {get; set;}

    // other properties
    public int A {get; set;}
    public string B {get; set;}
}

class T2
{
    public int Id {get; set;}

    // every T2 has zero or more T1
    public virtual ICollection<T1> T1s {get; set;}

    // other properties
    public int C {get; set;}
    public string D {get; set;}
}

public MyDbContext : DbContext
{
    public DbSet<T1> T1s {get; set;}
    public DbSet<T2> T2s {get; set;}
}

That is all! Entity framework will recognize that you are modelling a many-to-many relation between T1s and T2s and create the extra database table for you.

In your linq queries you don't need this extra table. The join will be created by Linq when you access the collections.

"Give me all T1 where property X == ... with all his T2 where property Y == ..."

using (var myDbContext = new MyDbContext)
{
    var result = myDbContext.T1s
        .Where(t1 => t1.X == ...)
        .Select(t1 = new
        {
            // select the T1 properties you want
            A = t1.A,
            B = t1.B,
            // a T1 has zero or more T2s
            // select the T2s you want:
            T2s = t1.T2s
                .Where(t2 => t2.Y == ....)
                .Select(t2 => new
                {   // Take the T2 properties you want
                    C = t2.C,
                    D = T2.D,
                }),
        });
}

You could also start with T2:

var result = myDbContext.T2s
        .Where(t2 => t2.Y == ...)
        .Select(t1 = new
        {
            // select the T1 properties you want
            C = t2.C,
            D = t2.C,
            // a T2 has zero or more T1s
            // select the T1s you want:
            T1s = t2.T1s
                .Where(t1 => t1.Y == ....)
                .Select(t1 => new
                {   // Take the T1 properties you want
                    A = t1.A,
                    B = T2.B,
                }),
        });

Because you access the collection properties in the many-to-many, Entity Framework knows that a join with the extra created many-to-many table must be performed.

The result is similar to a GroupJoin. If you want it ungrouped, more like a real join you'll need SelectMany

var result = MyDbContext.T1s.SelectMany(t1 => t1.T2s
    .Select(t2 => new
    {
        A = t1.A,
        B = t1.B,
        C = t2.C,
        D = t2.D,
    });

In my experience, whenever you define your one-to-many and many-to-many relationships properly, you seldom need to think in joins. You just access the properties and entity framework will automatically know which tables to join.