Lambda Select Top 1 item on underlying List

c# entity-framework-6 lambda linq

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?

1
0
11/2/2017 5:13:54 PM

Popular Answer

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!

1
11/2/2017 8:46:47 PM


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow