Example of using CROSS APPLY in EntitySpaces query

Reply

jhotchkiss Posted: 10-12-2009 5:28 AM

rated by 0 users

I would like to write the following query within EntitySpaces:

SELECT DISTINCT
	 C.[CostCentreID]
	,C.[OrganisationID]
	,C.[ParentCostCentreID]
	,C.[Name]
	,C.[Description]
	,C.[IsActive]
	,C.[LastAction]
	,C.[CreatedBy]
	,C.[CreatedDate]
	,C.[LastUpdatedBy]
	,C.[LastUpdated]
	,C.[ShadowCostCentreID]
FROM [dbo].[OrganisationCostCentreUserAccountRole] R
CROSS APPLY [dbo].GetCostCentreHierarchy(@OrganisationID, R.CostCentreID) C 
WHERE R.UserAccountID = @UserAccountID
ORDER BY C.Name

I currently have business objects created for OrganisationCostCentreUserAccountRole and CostCentre but don't know how to CROSS APPLY the results of a function (which returns data from my CostCentre table) to the EntitySpaces query.

I understand that I have to use some kind of RAW SQL but I can't seem to find a lot of documentation on the subject, specifically using a CROSS APPLY.

The reason I want to do it this way is so I can use the auto generation of CTE code when using paging.

Anyway help is appreciated.

Reply

Mike.Griffin replied on 10-12-2009 6:34 AM

rated by 0 users

Take a look at this blog post. You can interject raw SQL in our API in various area's of the API such as Select, Where, OrderBy, GroupBy and so on ... but there is no real place to interject the CROSS APPLY. You could do a few things howerver.

• Make that SQL above a View and generate an entity from it (however,views are read only)
• Make that SQL above a stored procedure, this is very easy to call via the ES API

 An example of calling the above if it were a stored proc, I assumed the UserAccountID was an int, but if it were a guid or string it's no problem ...

using EntitySpaces.Interfaces;
using EntitySpaces.Core;
using EntitySpaces.DynamicQuery;

namespace BusinessObjects
{
    public partial class EmployeesCollection : esEmployeesCollection
    {
        public bool GetRoles(int userId)
        {
            return this.Load(esQueryType.StoredProcedure, "sp_GetRoles()", userId);
        }
    }
}

Reply

jhotchkiss replied on 10-12-2009 7:31 AM

rated by 0 users

Cheers Mike, I think the View idea is probably what I will use since that should allow me to still use the es.PageNumber and es.PageSize query properties for paging. Writing a stored procedure to do it gets messy when you throw in dynamic sorting as well...

Logically if you were to implement you would have to add in a separate CrossApply property on the dynamic query builder, similar to the InnerJoin property. Since the intellisense would have no way of knowing what the function specified returns, it would have to be written as a static string parameter (unless you could specify what type of class the function returned).

So something like:

1    Guid organisationID = "Some GUID"
2    
3    OrganisationCostCentreUserAccountRoleQuery roleQuery = new OrganisationCostCentreUserAccountRole("r");
4    CostCentreQuery costCentreQuery = new CostCentreQuery("c", "GetCostCentreHierarchy", QueryType.Function);
5    
6    roleQuery.Select(costCentreQuery);
7    roleQuery.CrossApply(costCentreQuery).Parameters(organisationID, roleQuery.CostCentreID);
8    
9    CostCentreCollection costCentres = new CostCentreCollection();
10   costCentres.Load(roleQuery);

Anyway just food for thought.