vishwa vishwa - 3 months ago 24
C# Question

How to use orderby from another table column with C# lambda expression

I have two tables and i need to use lambda expression to order Data from second table.

Eg: table A is linked with table B with a one to many relationship. table A's primary key will be foreign key in table B. i need to get data from both table and order by table B date time column. can someone get me an idea for this. Thank you.

A.cs file has this code,

public partial class A
{
public A()
{
this.B = new HashSet<B>();
}
public virtual ICollection<B> B { get; set; }
}


B.cs file has this code,

public partial class B
{
public Nullable<long> a_pk { get; set; }
public Nullable<System.DateTime> system_date_time { get; set; }
public virtual A A { get; set; }
}


in the model, cs files have code like this. Thank you

Answer

It will probably not be very efficient, but you could do something like this:

// Sample data
var collection = new List<A>
{
    new A()
    {
        B = new List<B>
        {
            new B { system_date_time = DateTime.Now.AddHours(-1) },
            new B { system_date_time = DateTime.Now.AddHours(-2) },
            new B { system_date_time = DateTime.Now.AddHours(-3) },
        }
    },
    new A()
    {
        B = new List<B>
        {
            new B { system_date_time = DateTime.Now.AddDays(-1) },
            new B { system_date_time = DateTime.Now.AddDays(-2) },
            new B { system_date_time = DateTime.Now.AddDays(-3) },
        }
    },
    new A()
    {
        B = new List<B>
        {
            new B { system_date_time = DateTime.Now.AddYears(-1) },
            new B { system_date_time = DateTime.Now.AddYears(-2) },
            new B { system_date_time = DateTime.Now.AddYears(-3) },
        }
    }
};


var sorted = collection.OrderByDescending(x => x.B.Max(y => y.system_date_time));

The SQL will be something like this:

Select 
    *,
    (SELECT 
        MAX([Extent2].[system_date_time]) AS [A1]
        FROM [dbo].[B] AS [Extent2]
        WHERE [Var_38].[a_pk ] = [Extent2].[a_pk ]) AS [C1]
    FROM [dbo].[A] AS [Var_38]
)  AS [Project1]
ORDER BY [C1] ASC

If you want A and include B, then you can do something like this:

var result = _context.YourATable.Include(x => x.B).OrderByDescending(x => x.B.Max(y => y.system_date_time));
Comments