How to order by with related entity properties in EF core

Question

Hello I want to sort my end result using related entity property which is in this case Locality. I got the keyword from client end as a string that includes column name and sort direction eg. "locality=asc" but when I do orderby with any parent entity properties it run fine however, the property with related entity gives me an error by saying that customer object does not have any locality property

here is my both class customer and Address

public class Customer : IEntity
{
    public Guid Id { get; set; }
    public string FirstName { get; set; }
    public string LastName { get; set; }
    public string Email { get; set; }
    public string Mobile { get; set; }
    public Guid UserId { get; set; }
    public DateTime DateCreated { get; set; }
    public DateTime LastUpdated { get; set; }
    [ForeignKey("Address")]
    public Guid AddressId { get; set; }

    public virtual Address Address { get; set; }
}
   


 public class Address: IEntity
{
    public Guid Id { get; set; }
    public string Lat { get; set; }
    public string Lon { get; set; }
    public string Address1 { get; set; }
    public string Address2 { get; set; }
    public string Locality { get; set; }
}

Here I am trying to sort it with Address property like locality

int skip = (pageNum - 1) * pageSize;

        if (skip < 0)
        {
            skip = 0;
        }

        searchTerm = searchTerm.ToLower();
        var query = _context.Customers.Include(q => q.Address)
                .Where(c => c.FirstName.ToLower().Contains(searchTerm)
                        || c.LastName.ToLower().Contains(searchTerm)
                        || c.Email.ToLower().Contains(searchTerm)
                        || c.Mobile.ToLower().Contains(searchTerm));

        //var sortOrderSplit = sortOrder.Split('=');
        if (sortOrderSplit[0].ToLower() != "locality")
        {
            
              query = query.OrderByField("Email", "asc");
        }
         {
            
              query = query.OrderByField("locality", "asc"); //that gives me an error because type is Address not Customer
        }
          
        var customers = query
                       .Skip(skip)
                       .Take(pageSize)
                       .ToList();

What is `OrderByField`? Sounds like custom method. It must be adjusted to handle dot separated property path (like https://stackoverflow.com/questions/41068799/dynamic-linq-to-entities-orderby-with-pagination/41068979#41068979), so you can call it passing "Address.Locality" similar to the standard static `.OrderBy(c => c.Address.Locality)` call. — Ivan Stoev, Mar 18 '21 at 07:47

score 2 · Answer 1 · edited Mar 18 '21 at 08:23

u want order by Locality ASC,right?
I think Class type of query is IEnumerable,so you can use lumbda expression.
because Locality is in Address Class,should follow the flow Customer => Address => Locality,not only search property Locality.

if (sortOrderSplit[0].ToLower() != "locality")
{
    query = query.OrderBy(o => o.Email);
}
else
{
    query = query.OrderBy(o => o.Address.Locality);
}

score 0 · Answer 2 · answered Mar 18 '21 at 07:48

0

If your two entity classes have One-to-One relationship, you must add

public Customer Customer { get; set; }

into your Address class too. Do this and try again.

answered Mar 18 '21 at 07:48

Majid Shahabfar

4,010
2
28
36

How to order by with related entity properties in EF core

2 Answers2