Lambda Select Top 1 item on underlying List

Question

I have 2 tables with relation

Customer

Id, Nbr, Name

Assignments

Id, CustomerId, Location, AssigmentTime

There is a relation on Customer.Id = Assigments.CustomerId

Every Customer can have lot of assignments, but I am only interested in the last Assignment according to DateTime field AssigmentTime

In SQL it should be a query like:

Select Top 1 From Customer c
Inner Join Assigments a On c.Id = a.CustomerId
Where c.Nbr = 1234
Order By AssigmentTime Desc

I have a problem to construct proper Lambda query.

This code works, but it’s not very effective:

var customerNbr = 1234:
var cst = context.Customers.FirstOrDefault(x => x.Nbr == customerNbr);
if (cst != null && cst. Assigments.Count > 1)
{
  cst. Assigments = new List<Assigments>
  {
     cst.Assigments.OrderByDescending(x => x.AssigmentTime).FirstOrDefault()
  };
}

How can I get Customer with just 1 top Assigments in Customer.Assigments List property?

Answer 1

Thank you for your suggestion Harald. I was on to same thig, but I found anonymous object to be a bit bloated. In my case I use EF.Reverse.POCO Generator, so every object is strictly mapped to DB. Customer and Assignments are in reality something else – tables with lot of columns. I can’t have anonymous object as a return from this function.

I could still do something like this:

using (var dbContext = new MyDbContext())
{
    var result = dbContext.Customers
    .Where(customer => customer.Nbr == 1234)
    .Select(customer => new Customer
    {
        // select the customer properties you will use, for instance
        Id = customer.Id,
        Nbr = customer.Nbr,
        Name = customer.Name,
        //…and lot of other property mapping
        // you only want the newest assignment:
        Assignments = new Collection<Assignments>
            {
                 customer.Assignments.OrderByDescending(assignment => assignment.AssignmentTime)
                 .FirstOrDefault()
            }
        });
    }
}

The anonymous Customer generations will result in lot of property mapping. That’s the minor issue.

Even if I skip Assignments property, this solution with typed object in Select generates an exception inside the result:

Message = "The entity or complex type 'MyNamespace.Customer' cannot be constructed in a LINQ to Entities query."

If I use anonymous object the same code works fine, but as I wrote above – I need typed objects as return.

Answer 2

If you have set-up your entity framework according to the proper coding conventions you'll have designed the one-to-many relation as follows:

class Customer
{
    public int Id {get; set;} // primary key

    // a Customer has zero or more Assignments
    public virtual ICollection<Assignment> Assignments {get; set;}

    public int Nbr {get; set;}
    ... // other properties
}

class Assignment
{
    public int Id {get; set;} // primary key

    // every Assignment belongs to one Customer via foreign key
    public int CustomerId {get; set;}
    public virtual Customer Customer {get; set;}

    public DateTime AssignmentTime {get; set;}
    ... // other properties
}

public MyDbContext : DbContext
{
    public DbSet<Customer> Customers {get; set;}
    public DbSet<Assignment> Assignments {get; set;}
}

If you've set-up the one-to-many like this, then this is all entity framework needs to know that you designed a one-to-many relationship. If you didn't want to follow the naming conventions, you probably have used fluent API or attributes to configure the one-to-many.

Get the Customer with Nbr = 1234 with his last (newest) Assignment:

using (var dbContext = new MyDbContext())
{
    var result = dbContext.Customers
        .Where(customer => customer.Nbr == 1234)
        .Select(customer => new
        {
            // select the customer properties you will use, for instance
            CustomerId = customer.Id,
            CustomerName = customer.Name,

            // you only want the newest assignment:
            NewestAssignment = customer.Assignments
               .OrderByDescending(assignment => assignment.AssignmentTime)
               .Select(assignment => new
               {   // take only the Assignment properties you will use:
                   Location = assignment.Location,
                   AssignmentTime = assignment.AssignmentTime,
               }
               .FirstOrDefault(),
        });
    }
}

If you are certain there is at utmost one customer with Nbr = 1234, you can end with SingleOrDefault; otherwise your result will be the sequence of Customers with this Nbr.

Each customer will only have the customer properties you will use, and the properties of the newest Assignment you will use. Efficient!

Answer 3

For example:

var lastAssignment = customers.Where(x => x.Nbr == customerNbr)
                              .SelectMany(x => x.Assignments)
                              .OrderByDescending(x => x.AssignTime)
                              .FirstOrDefault();