locked
using Min and Max funtion in this LINQ query with joins ? please guide RRS feed

  • Question

  • I am making a LINQ query which is join of multiple tables. In result set I want to ad a column which shows Min and Max date combinely [e.g.(20/3/2012 - 25/4/2012)] from table.

    The structure of table (which is a table to manage manay to many relationship) from where I want to pick date is as below:


    BookedAreaID    int
    AreaID  int
    LeasedDate  datetime
    InvoiceID   int

    Here is my LINQ query:

    var selectedResults=
    from InvoiceSet in Invoices
    join BookedAreaSet in BookedAreas on InvoiceSet.InvoiceID equals BookedAreaSet.InvoiceID
    join AreaSet in Areas on BookedAreaSet.AreaID equals AreaSet.AreaID
    join ContactSet in Contacts on InvoiceSet.ContactID equals ContactSet.ContactID
    join Contacts_ObjectsSet in Contacts_Objects on ContactSet.ContactID  equals Contacts_ObjectsSet.ContactID
    join CompanySet in Companies on Contacts_ObjectsSet.ObjectReferenceID  equals  CompanySet.CompanyID
    join BookedAreasSet in BookedAreas on InvoiceSet.InvoiceID equals BookedAreasSet.InvoiceID
    
    where Contacts_ObjectsSet.ObjectReference=="Company"
    
    select new {InvoiceSet.InvoiceNumber,InvoiceSet.Amount,InvoiceSet.TotalDiscount,InvoiceSet.GST,
    InvoiceSet.PaymentDate,InvoiceSet.ShoppingCentreID,BookedAreasSet.BookedAreaID,AreaSet.Name,Paid=(InvoiceSet.PaymentDate==null ? "UnPaid":"Paid"), 
    licensee=(CompanySet.CompanyName))
    };

    I want to add something like with this query:

    DateRange=
    (Min(BookedAreasSet.LeasedDate where BookedAreasSet.InvoiceID=InvoiceSet.InvoiceID) 
    + "-" + 
    Max(BookedAreasSet.LeasedDate where BookedAreasSet.InvoiceID=InvoiceSet.InvoiceID)

    Please guide me.

    Thanks






    • Edited by Haansi Wednesday, July 11, 2012 2:27 AM
    Wednesday, July 11, 2012 2:24 AM

Answers

  • Try This.

    DateRange=
    BookedAreasSet.where(bas => bas.InvoiceID == InvoiceSet.InvoiceID)
    .Select(bas => bas.LeasedDate).max().ToShortDateString() + "-" + BookedAreasSet.where(bas => bas.InvoiceID == InvoiceSet.InvoiceID)
    .Select(bas => bas.LeasedDate).max().ToSortDateString();

    I hope this helps.

    Please mark this post as answer if it solved your problem. Happy Programming!

    Wednesday, July 11, 2012 3:09 AM
  • Since you have already filtered the data, no need of using where clause again. So, try below select.

    select new 
    { 
       InvoiceSet.InvoiceNumber, InvoiceSet.Amount, InvoiceSet.TotalDiscount, InvoiceSet.GST, 
       InvoiceSet.PaymentDate, InvoiceSet.ShoppingCentreID, BookedAreaSet.BookedAreaID, AreaSet.Name, 
       Paid = (InvoiceSet.PaymentDate == null ? "UnPaid":"Paid"), licensee = CompanySet.CompanyName, 
       DateRange = BookedAreasSet.Select(bas => bas.LeasedDate).Min().ToShortDateString() + "-" + 
    BookedAreasSet.Select(bas => bas.LeasedDate).Max().ToShortDateString()
    };

    I hope this helps.

    Please mark this post as answer if it solved your problem. Happy Programming!

    Wednesday, July 11, 2012 3:54 AM
  • A small change is needed.. It's BookedAreas, not BookedAreasSet :)

    select new 
    { 
       InvoiceSet.InvoiceNumber, InvoiceSet.Amount, InvoiceSet.TotalDiscount, InvoiceSet.GST, 
       InvoiceSet.PaymentDate, InvoiceSet.ShoppingCentreID, BookedAreaSet.BookedAreaID, AreaSet.Name, 
       Paid = (InvoiceSet.PaymentDate == null ? "UnPaid":"Paid"), licensee = CompanySet.CompanyName, 
       DateRange = BookedAreas.Select(bas => bas.LeasedDate).Min().ToShortDateString() + "-" +  
    BookedAreas.Select(bas => bas.LeasedDate).Max().ToShortDateString() };

    I hope this helps.


    Please mark this post as answer if it solved your problem. Happy Programming!

    • Proposed as answer by Jason Dot Wang Thursday, July 12, 2012 8:04 AM
    • Marked as answer by Haansi Thursday, July 12, 2012 9:44 AM
    Wednesday, July 11, 2012 8:44 AM

All replies

  • Try This.

    DateRange=
    BookedAreasSet.where(bas => bas.InvoiceID == InvoiceSet.InvoiceID)
    .Select(bas => bas.LeasedDate).max().ToShortDateString() + "-" + BookedAreasSet.where(bas => bas.InvoiceID == InvoiceSet.InvoiceID)
    .Select(bas => bas.LeasedDate).max().ToSortDateString();

    I hope this helps.

    Please mark this post as answer if it solved your problem. Happy Programming!

    Wednesday, July 11, 2012 3:09 AM
  • Adavesh I want to use this query with my existing query. Should I simply add this in select list ?

    select new {InvoiceSet.InvoiceNumber,InvoiceSet.Amount,InvoiceSet.TotalDiscount,InvoiceSet.GST,
    InvoiceSet.PaymentDate,InvoiceSet.ShoppingCentreID,BookedAreaSet.BookedAreaID,AreaSet.Name,Paid=(InvoiceSet.PaymentDate==null ? "UnPaid":"Paid"), 
    licensee=(CompanySet.CompanyName),DateRange=
    BookedAreasSet.where(bas => bas.InvoiceID == InvoiceSet.InvoiceID)
    			  .Select(bas => bas.LeasedDate).max().ToShortDateString() + "-" + 
    BookedAreasSet.where(bas => bas.InvoiceID == InvoiceSet.InvoiceID)
    			  .Select(bas => bas.LeasedDate).max().ToSortDateString()
    };
    

    It shows two errors "Invalid expression term 'where'" near both BookedAreasSet.where

    Please advice.

    thanks

    Wednesday, July 11, 2012 3:14 AM
  • Since you have already filtered the data, no need of using where clause again. So, try below select.

    select new 
    { 
       InvoiceSet.InvoiceNumber, InvoiceSet.Amount, InvoiceSet.TotalDiscount, InvoiceSet.GST, 
       InvoiceSet.PaymentDate, InvoiceSet.ShoppingCentreID, BookedAreaSet.BookedAreaID, AreaSet.Name, 
       Paid = (InvoiceSet.PaymentDate == null ? "UnPaid":"Paid"), licensee = CompanySet.CompanyName, 
       DateRange = BookedAreasSet.Select(bas => bas.LeasedDate).Min().ToShortDateString() + "-" + 
    BookedAreasSet.Select(bas => bas.LeasedDate).Max().ToShortDateString()
    };

    I hope this helps.

    Please mark this post as answer if it solved your problem. Happy Programming!

    Wednesday, July 11, 2012 3:54 AM
  • Thanks Adavesh for your precious time and helping me.

    I m still having an error "'BookedArea' does not contain a definition for 'Select' and no extension method 'Select' accepting a first argument of type 'BookedArea' could be found (press F4 to add a using directive or assembly reference)"

    I m using LinqPad. It shows error near:

    BookedAreaSet.Select

    Kindly guide.


    • Edited by Haansi Wednesday, July 11, 2012 4:27 AM
    Wednesday, July 11, 2012 4:27 AM
  • There is also the MSDN.en-US.Linqproject forum.
     
    Wednesday, July 11, 2012 4:31 AM
  • A small change is needed.. It's BookedAreas, not BookedAreasSet :)

    select new 
    { 
       InvoiceSet.InvoiceNumber, InvoiceSet.Amount, InvoiceSet.TotalDiscount, InvoiceSet.GST, 
       InvoiceSet.PaymentDate, InvoiceSet.ShoppingCentreID, BookedAreaSet.BookedAreaID, AreaSet.Name, 
       Paid = (InvoiceSet.PaymentDate == null ? "UnPaid":"Paid"), licensee = CompanySet.CompanyName, 
       DateRange = BookedAreas.Select(bas => bas.LeasedDate).Min().ToShortDateString() + "-" +  
    BookedAreas.Select(bas => bas.LeasedDate).Max().ToShortDateString() };

    I hope this helps.


    Please mark this post as answer if it solved your problem. Happy Programming!

    • Proposed as answer by Jason Dot Wang Thursday, July 12, 2012 8:04 AM
    • Marked as answer by Haansi Thursday, July 12, 2012 9:44 AM
    Wednesday, July 11, 2012 8:44 AM