none
GROUP BY & Top 1 Question RRS feed

  • Question

  • I'm trying to rewrite a query that already exists but I'm having trouble combining different functionality for Linq to SQL

    var query = from c in dataContext.CustomerDbEntities
                                join messages in dataContext.MessageDbEntities on c.CustomerId equals messages.CustomerId
                                join transactionTypes in dataContext.TransactionTypeDbEntities on messages.TransactionTypeId equals transactionTypes.TranactionTypeId
                                join salesmen in dataContext.SalesmenDbEntities on c.SalesmenId equals salesmen.SalesmenId
                                where SalesRepIds.Contains(salesmen.SalesmenId)
                                join contract in dataContext.CustomerContractDbEntities on c.CustomerId equals contract.CustomerId
                                join contracttype in dataContext.ContractRateTypeDbEntities on contract.ContractRateTypeId equals contracttype.ContractRateTypeId
                                where transactionTypes.Code == "TOFF"
                                  && messages.CreatedOn >= fromDate.Date
                                  && messages.CreatedOn <= toDate.Date
                                  && c.ServiceTypeId == (ServiceType == "Residential" ? 0 : 1)
                                select new TOFFReportDetails
                                {
                                    CustomerName = c.InvoiceBillingName,
                                    AGLCAccountNumber = c.AGLCAccountNumber,
                                    MarketerCustomerNumber = c.MarketerCustomerNumber,
                                    IsSeasonal = c.IsSeasonal.HasValue ? c.IsSeasonal.Value : false,
                                    DDDC = c.AGLCDDDC,
                                    DateTOFFReceived = messages.Date,
                                    SalesRep = salesmen.FullName,
                                    ContractType = contracttype.Description
                                };

    The problem with the above is that a customer could have multiple contracts associated to their account so I only want the most recent contract for each customer.

    Wednesday, August 2, 2017 6:20 PM

All replies

  • Maybe try without GroupBy too: remove the ‘join contact’ and ‘join contacttypes’ lines, and then adjust the calculation of ContactType using an additional query:

       . . .
       ContactType = ( from contract in dataContext.CustomerContractDbEntities
                                  join contracttype in dataContext.ContractRateTypeDbEntities on contract.ContractRateTypeId equals contracttype.ContractRateTypeId
                                  where contract.CustomerId == c.CustomerId
                                  orderby contact.SomeDateField descending
                                  select contacttype.Description ).FirstOrDefault()
     };


    • Edited by Viorel_MVP Thursday, August 3, 2017 5:09 AM
    Thursday, August 3, 2017 5:08 AM
  • Hi Steven Aiello,

    Thank you for posting the problem here.

    >> The problem with the above is that a customer could have multiple contracts associated to their account so I only want the most recent contract for each customer.

    I suppose that you would like to group your data according to a contracts ‘s property. So I modified your LINQ as follows. It groups the data by TransactionTypeDbEntity’s  Code because I didn’t know contracts ‘s properties  .

    var result = from c in dataContext.CustomerDbEntities
    
                             join m in dataContext.MessageDbEntities on c.CustomerId equals m.CustomerId
    
                             join cc in dataContext.CustomerContractDbEntities on c.CustomerId equals cc.CustomerId
    
                             join t in dataContext.TransactionTypeDbEntities on m.TransactionTypeId equals t.TranactionTypeId
    
                             where t.Code == "TOFF" && m.CreatedOn >= fromDate && m.CreatedOn <= toDate && c.ServiceTypeId == (ServiceType == "Residential" ? 0 : 1)
    
                             orderby m.CreatedOn descending //You could sort your data according to your requirement
    
                             //select m;
    
                             group m by t.Code into groups // You could group your data according to your requiement
    
                             select groups.FirstOrDefault();
    

    >>  GROUP BY & Top 1 Question

    You could refer to the following link which provides Group by and Top 1.

    https://smehrozalam.wordpress.com/2009/12/29/linq-how-to-get-the-latest-last-record-with-a-group-by-clause/

    Besides, if you would like to know more knowledge about the LINQ, please refer to the following.

    https://docs.microsoft.com/en-us/dotnet/csharp/programming-guide/concepts/linq/getting-started-with-linq

    I’m glad to be of help to you.


    Note:This response contains a reference to a third party World Wide Web site.Microsoft is providing this information as a convenience to you.

    Microsoft does not control these sites and has not tested any software or information found on these sites; therefore, Microsoft cannot make any representations regarding the quality, safety, or suitability of any software or information found there.

    There are inherent dangers in the use of any software found on the Internet, and Microsoft cautions you to make sure that you completely understand the risk before retrieving any software from the Internet.

    Best Regards,

    Cole


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, August 4, 2017 2:55 AM
    Moderator