CODEWITHSUNDEEP
c#asp.netasp.net-mvclinq
Rob
Rob

Reputation: 225

How can i get Max Date in Linq

I have the following linq expression to compare users parameter to Max(EndDate) field?. I have multiple EndDate's but i want to get the last one.

Here is Product 1

 EndDate

2018-09-30 23:59:59.000
2019-09-30 23:59:59.000

Product 2

 EndDate


2019-09-30 23:59:59.000
2019-12-31 23:59:59.000

When user enter 09/2019 I need to return only Product 1 since the Max(EndDate) == the value entered by the user which is 09/2009 in MM/yyyy format, currently it displays both product 1 & product 2.

In my model i have checkBillingMonth as bool

public bool checkBillingMonth { get; set; }

Here my linq expression looks like this but it return both prodcut 1 and product 2

var billingProductList = Context.Product.AsNoTracking()
        .Select(p => new ProductDTO 
         {
          checkBillingMonth = p.Billing
          .Any(b => b.EndDate.Month == request.FromDate.Month &&
               b.EndDate.Year == request.FromDate.Year)
         }).ToList();

How can i modify the above linq expression to get Max(EndDate) and compare to request.FromDate?

Upvotes: 0

Views: 83

Answers (2)

Sowmyadhar Gourishetty
Sowmyadhar Gourishetty

Reputation: 1878

You can try the below code.

    var billingProductList = Context.Product.AsNoTracking()
            .Select(p => new ProductDTO 
             {
              checkBillingMonth = GetCheckBillingMonth(p.Invoices, request.FromDate) 
        // I'm not sure why you were having the condition related to min date
             }).ToList();

Private method:

 private bool GetCheckBillingMonth(List<Invoice> invoices, DateTime fromDate)
 {
     if (invoices == null || invoices.Count == 0) return false;

     var latestInvoiceEndDate = invoices.OrderByDescending(x => x.EndDate).FirstOrDefault()?.EndDate;        
     return latestInvoiceEndDate != null && latestInvoiceEndDate.Month == fromDate.Month && latestInvoiceEndDate.Year == fromDate.Year;        
 }

Upvotes: 1

Jeremy Lakeman
Jeremy Lakeman

Reputation: 11100

If I'm understanding the question properly, you want to compare to the last invoice? Then you should flip your primary result set from Product to Invoice. Filter the latest invoices. Then select the related product details.

Context.Invoice
   .AsNoTracking()
   .Where(i => i.EndDate == i.Product.Invoices.Max(i2 => i2.EndDate))
   .Select(i => new ProductDTO{
      checkBillingMonth = i.EndDate.Month == request.FromDate.Month &&
               i.EndDate.Year == request.FromDate.Year && 
               !request.FromDate.Equals(DateTime.MinValue)),
      // plus other properties from i.Product
   });
;

Upvotes: 0

Related Questions