Hello,

I'm developing several reportings for a Commerce solution. Several of them were relate to retrieving data from Purchase Orders, and I could easily do that using the EPiServer way with the OrderSearchOptions and OrderSearchParameters classes (documentation), so I could retrieve a List of PurchaseOrders over which I could easily apply more LINQ Queries. But now I require to perform a more complex query, which consists on retrieving all contacts and group them by date, region, city and postal code. Here's the SQL query I performed to retrieve this data in SSMS:

SELECT CONVERT(VARCHAR(10), c.Created, 103) AS 'Date', a.RegionName as 'Provincia', a.City as 'Ciudad', a.PostalCode as 'Código postal', count(*) as 'Registrados'
FROM [dbManagerDev2].[dbo].[cls_Contact] c, [dbManagerDev2].[dbo].[cls_Address] a
where c.PreferredShippingAddressId = a.AddressId and regionname is not null and regionname <> ''
group by c.Created, a.RegionName, a.City, a.PostalCode

I've posed several approaches to do this in a report in EPiServer Commerce Manager (always trying to look for the most Object Oriented approach if possible):

1- Create a LINQ2SQL file *.dbml and performed the query over the Data Context so I can use LINQ.

2- Use the function GetAllContacts from CustomerContext. The problem I think this approach has is that it loads all the Contacts data in an IEnumerable into the memory instead of an IQueryable, which can be very resource and time consuming in comparison of the first option.

3- Use ADO.NET directly in a "stringy" way, launching directly my query (very similar to the #1st approach, without LINQ).

For the time being and for the sake of simplicity, I'm using the approach #1, but I wonder if there is a more "EPiServish" and friendly way of doing the query I require. Does anyone have any idea or mine is the right way to go?

Thank you and kind regards,

David