Select entity plus additional fields

Posts   
1  /  2
 
    
worldspawn avatar
worldspawn
User
Posts: 321
Joined: 26-Aug-2006
# Posted on: 08-Feb-2011 01:33:08   

Hello,

I was told once here that with linq2llbl its not possible to write something like:


var users = from c in meta.User
                        join cp in meta.ContextPermissions on new { EntityType = "User" } equals new { cp.EntityType }
                        where cp.EntityId == c.Id
                        select new { User = c };

and by extension this doesn't work either:


var users = from c in meta.User
                        join cp in meta.ContextPermissions on new { EntityType = "User" } equals new { cp.EntityType }
                        where cp.EntityId == c.Id
                        select new { User = c, Permission = cp.Permission };

and it doesn't. What I am trying to do is filter out authorised access to rows. That part is easy enough. I have actually modified my linqmetadata class to provide properties like this:


public IQueryable<ClientEntity> Client
        {
            get { return 
                    from c in new DataSource2<ClientEntity>(_adapterToUse, new ElementCreator(), _customFunctionMappings, _contextToUse)
                    join cp in ContextPermissionsFor(typeof(ClientEntity)) on new { EntityType = "Client" } equals new { cp.EntityType }
                    where c.Id == cp.EntityId || cp.EntityId == -1/* || cp.EntityId == null -- we dont want to allow entity level permissions to filter to the row level */
                    select c;
                }
        }

ContextPermissionsFor method.


IQueryable<ContextPermissionsEntity> ContextPermissionsFor(Type entityType)
        {
            var contextPermissions = ContextPermissions;
            int permission = (int)dataSessionManager.GetDemandsFor(entityType);
            if (permission != 0)
            {//None - no permission demanded
                contextPermissions = contextPermissions.Where(p => (p.Permission & permission) == permission);
                string entityName = entityType.Name.Substring(0, entityType.Name.LastIndexOf("Entity"));
                var permissions = from c in contextPermissions
                                  where c.EntityType == entityName && c.EntityId != null
                                  select new { c.EntityId, c.Permission };

                var principal = ServiceContainer.Resolve<IPrincipalAccessor>().Principal;
                foreach (var p in permissions)
                    principal.AddPermission(entityName, p.EntityId.GetValueOrDefault(), (Shivam.ASL.Core.Security.Permission)p.Permission);
            }

            return contextPermissions;
        }

The join filters out unauthorised records. The pain comes however with the fact that I want to cache in memory (or tack on to the entity) the associated permission. The current system isn't going to work for me as I have introduced a type of permission that once defined applies to multiple entities, as such the query executed in ContextPermissionsFor wouldn't be to resolve those particular permissions to a particular entity id.

ContextPermissions is a view that aggregates (applies a bitwise or) to all the relevant rows in the permissions table. For this reason I can't make Permission a column on the entity and I can't have a relationship btw the entity and the view (because it's a view).

My goals are: * for certain entities I want their query to always be joined on to the contextpermissions table, this will be something the given entity explicitly opts in to * for the top entities (that is the entity created by the query) I want to cache it's associated permission either in memory in a dictionary like I am now or on the entity itself. * don't want the system to execute additional connections to get permission data

I toyed around with overriding various methods in DataAccessAdapter/DynamicQueryEngine to see if I could inject an additional column (Permission) in to the select list and then read it into my dictionary. However anything I tried seem to break something, resulting in InvalidCastExceptions or NullReferenceExceptions.

Additional Info, heres some of my database schema and views.


USE [MyMobileApplication]
GO

/****** Object:  Table [Security].[Permission]  Script Date: 02/08/2011 11:31:27 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [Security].[Permission](
    [EntityId] [int] NOT NULL,
    [GroupId] [int] NOT NULL,
    [EntityType] [nvarchar](128) NOT NULL,
    [UserSetId] [int] NOT NULL,
    [UserSetType] [nvarchar](64) NOT NULL,
    [Permission] [int] NULL,
 CONSTRAINT [PK_Permission] PRIMARY KEY CLUSTERED 
(
    [UserSetType] ASC,
    [EntityId] ASC,
    [GroupId] ASC,
    [EntityType] ASC,
    [UserSetId] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

ALTER TABLE [Security].[Permission]  WITH CHECK ADD  CONSTRAINT [FK_Permission_Group] FOREIGN KEY([GroupId])
REFERENCES [Security].[Group] ([Id])
ON DELETE CASCADE
GO

ALTER TABLE [Security].[Permission] CHECK CONSTRAINT [FK_Permission_Group]
GO

ALTER TABLE [Security].[Permission]  WITH CHECK ADD  CONSTRAINT [FK_Permission_UserSet] FOREIGN KEY([UserSetId])
REFERENCES [Security].[UserSet] ([Id])
GO

ALTER TABLE [Security].[Permission] CHECK CONSTRAINT [FK_Permission_UserSet]
GO

ALTER TABLE [Security].[Permission] ADD  CONSTRAINT [DF_Permission_UserSetType]  DEFAULT (' ') FOR [UserSetType]
GO

USE [MyMobileApplication]
GO

/****** Object:  View [Security].[ContextGroups]    Script Date: 02/08/2011 11:32:07 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO





--select * from [ContextGroups]

CREATE VIEW [Security].[ContextGroups]   AS
SELECT DISTINCT
    [Security].[Group].Id, [Security].[Group].Name, [Security].[User].[UserSetId], [Security].[UserSet].[Type] [UserSetType], [Security].[User].[Id] [UserId]
From [Security].[Group], [Security].[UserGroup], [Security].[User], [Security].[UserSet]
Where 
    [Security].[Group].Id = [Security].UserGroup.GroupID AND [Security].[User].Id = UserGroup.UserId AND [Security].[User].UserSetId = [Security].[UserSet].Id
    AND
    [Security].[User].Username = ISNULL(CAST(CONTEXT_INFO() as nvarchar(128)), SUSER_SNAME())

GO

USE [MyMobileApplication]
GO

/****** Object:  View [Security].[ContextPermissions]   Script Date: 02/08/2011 11:32:17 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO





CREATE VIEW [Security].[ContextPermissions]  
As

SELECT 
    P.UserId,
    dbo.BitwiseOr(ISNULL(P.Permission, 0) | ISNULL(E.Permission, 0)) [Permission],
    NULLIF(P.EntityId, 0) [EntityId],
    P.EntityType
FROM
(
SELECT 
    [Security].ContextGroups.UserId, 
    dbo.BitwiseOr([Security].Permission.Permission) [Permission], 
    [Security].Permission.EntityId, 
    [Security].Permission.EntityType
FROM 
    [Security].Permission, [Security].ContextGroups
WHERE
    [Security].Permission.GroupId = [Security].ContextGroups.Id AND 
        ([Security].Permission.UserSetId = [Security].ContextGroups.UserSetId OR [Security].Permission.UserSetType = [Security].ContextGroups.UserSetType)
GROUP BY
    [Security].ContextGroups.UserId, 
    [Security].Permission.EntityType,
    [Security].Permission.EntityId) [P]
    LEFT JOIN (
        SELECT 
            [Security].ContextGroups.UserId, 
            dbo.BitwiseOr([Security].Permission.Permission) [Permission], 
            [Security].Permission.EntityId, 
            [Security].Permission.EntityType
        FROM 
            [Security].Permission, [Security].ContextGroups
        WHERE
            [Security].Permission.GroupId = [Security].ContextGroups.Id
            AND 
            (
            [Security].Permission.UserSetId = [Security].ContextGroups.UserSetId
            OR
            [Security].Permission.UserSetType = [Security].ContextGroups.UserSetType
            )
            AND ([Security].Permission.EntityId = 0 OR [Security].Permission.EntityId = -1)
        GROUP BY
            [Security].ContextGroups.UserId, 
            [Security].Permission.EntityType,
            [Security].Permission.EntityId
    ) [E] ON P.UserId = E.UserId AND P.EntityType = E.EntityType
GROUP BY P.UserId, P.EntityType, P.EntityId


GO


The contextgroups view relies on this being executed when a connection is opened.


DECLARE @CONTEXT_INFO varbinary(128)
select @CONTEXT_INFO = cast(N'sam.critchley' as varbinary(128))
set CONTEXT_INFO @CONTEXT_INFO

sam.critchley being a value matched from User.Username.

Walaa avatar
Walaa
Support Team
Posts: 14946
Joined: 21-Aug-2005
# Posted on: 08-Feb-2011 09:10:17   

I can't make Permission a column on the entity and I can't have a relationship btw the entity and the view (because it's a view).

A view can be mapped to an Entity in LLBLGen, and then you can define custom relationShips with other entities in the designer.

My goals are: * for certain entities I want their query to always be joined on to the contextpermissions table, this will be something the given entity explicitly opts in to

This can be done at the database level, by using Views.

  • for the top entities (that is the entity created by the query) I want to cache it's associated permission either in memory in a dictionary like I am now or on the entity itself.
  • don't want the system to execute additional connections to get permission data

This is an application level issue, not a DAL issue, you can pick permission when user is connected and store it in a session variable.

worldspawn avatar
worldspawn
User
Posts: 321
Joined: 26-Aug-2006
# Posted on: 08-Feb-2011 11:02:36   

Hi Walaa,

If I made of the entity + the permission column I'm led to believe I would not be able to delete from it without taking on the extra work of creating an INSTEAD OF DELETE trigger for the view.

Is there a way to "tell" the delete statement to target fields in the view that are only from a particular table?

This is an application level issue, not a DAL issue, you can pick permission when user is connected and store it in a session variable.

I am implementing a row level security mechanism. Each row having one or more of the following permissions - list, read, edit, delete. It's not possible/practical to retrieve all the permissions for the logged on user for all the secured entities and cache them. Its also not scalable to select all possible matches of my query into memory and then filter them by permission. It must be enforced in the data layer, before the data ever gets to the application layer.

MTrinder
User
Posts: 1461
Joined: 08-Oct-2008
# Posted on: 08-Feb-2011 21:20:40   

It is possible to delete records from one table in a view which references more than one, but there are some restrictions. See http://msdn.microsoft.com/en-us/library/ms180800.aspx for more information.

Matt

worldspawn avatar
worldspawn
User
Posts: 321
Joined: 26-Aug-2006
# Posted on: 09-Feb-2011 01:08:27   

Hello Matt, as far as I can see you still have to define a INSTEAD OF DELETE trigger for the view if the view has columns that are sourced from more than one table or are derived in some way... (think they call that "deterministic"?).

I'm not able to sell the boss on a view + trigger per entity rage

All our technology has to be "junior indian programmer" compatible... triggers it seems, fall out side that scope.

Walaa avatar
Walaa
Support Team
Posts: 14946
Joined: 21-Aug-2005
# Posted on: 09-Feb-2011 07:59:08   

If I made of the entity + the permission column I'm led to believe I would not be able to delete from it without taking on the extra work of creating an INSTEAD OF DELETE trigger for the view.

Why delete from the view, and not from the user permission table itself. Just use the view for Reads.

worldspawn avatar
worldspawn
User
Posts: 321
Joined: 26-Aug-2006
# Posted on: 09-Feb-2011 08:13:41   

Why delete from the view, and not from the user permission table itself. Just use the view for Reads.

Need to consider the amount of overhead I'm willing to add to support this.

I don't want to have to add the main Entity AND the View to my DAL and direct delete operations to the entity and everything else to the view. That mean a fatter DAL plus a whole lot of re factoring.

There's a lot about the view approach that I like, at least as long as it keeps the application layer simpler not more complicated. Frankly I would probably be ok with using views. It would be pretty easy to write some templates that generated the view + any required triggers for each secure entity. Having to manually add the relationships in the designer would suck though... but perhaps a plugin could do it...

Walaa avatar
Walaa
Support Team
Posts: 14946
Joined: 21-Aug-2005
# Posted on: 09-Feb-2011 09:19:24   

Let's sart again from here:

  • for certain entities I want their query to always be joined on to the contextpermissions table, this will be something the given entity explicitly opts in to

Do you want to join to the ContextPermission table for filtering, or to fetch data from the ContextPermission table along with the main entity?

worldspawn avatar
worldspawn
User
Posts: 321
Joined: 26-Aug-2006
# Posted on: 10-Feb-2011 00:38:16   

Yes I want to join for the filtering.

I also want to fetch data (the permission column value) along with the main entity.

What I do with this value is a I cache it in the current user's principal object. It's cached just for the life of the request.

An example of how this is used is when using a page of entities for a list page. I need to filter the inital sql query to make sure I have List access to them. On the list page I want to show an edit link if I have edit permission, a delete link if i have delete permission etc. So for each row I look for the entities permission in the principal and determine what to show and what to hide.

  • Crazy idea - Now I know nothing about the DQE... but it would be nice if I could alter the DQE so anytime it generated a select from certain entities it joined it onto ContextPermissions and added the Permisison Column, likewise when selecting from a subquery (which would already have the join) it added the permission column to the select list.

That way all joins would be secure and top most select list would also contain the permission column.

Walaa avatar
Walaa
Support Team
Posts: 14946
Joined: 21-Aug-2005
# Posted on: 10-Feb-2011 10:44:51   

You will ned to derive from DataAccessAdapter and override CreateSelectDQ() Please check the following link: http://www.llblgen.com/tinyforum/Messages.aspx?ThreadID=15230

To add extra field/property to an entity which should be populated from the database, you will need to create a custom EntityFactory extending your entityFactory, here you put this custom field so when the data will be fetched, your custom field as well should be fetched.

This article illustrates (among other things) how to obtain a calculated scalar query inside an entity using a special factory. http://weblogs.asp.net/fbouma/archive/2006/06/09/LLBLGen-Pro-v2.0-with-ASP.NET-2.0.aspx Please check the "Extending the CustomerEntityFactory" part in "Step 6".

worldspawn avatar
worldspawn
User
Posts: 321
Joined: 26-Aug-2006
# Posted on: 11-Feb-2011 02:17:05   

Great thanks Walaa!!

I've got my join being inserted.. haven't really tested all the scenarios yet though. But it's coming up in my query:


SELECT [MyMobileApplication].[dbo].[ContentPage].[ContentPageTypeId], [MyMobileApplication].[dbo].[ContentPage].[CreatedBy], [MyMobileApplication].[dbo].[ContentPage].[CreatedOn], [MyMobileApplication].[dbo].[ContentPage].[Description], [MyMobileApplication].[dbo].[ContentPage].[Id], [MyMobileApplication].[dbo].[ContentPage].[PageTitle], [MyMobileApplication].[dbo].[ContentPage].[UpdatedBy], [MyMobileApplication].[dbo].[ContentPage].[UpdatedOn] FROM ( [MyMobileApplication].[dbo].[ContentPage]  INNER JOIN [MyMobileApplication].[Security].[ContextPermissions]  ON  [MyMobileApplication].[dbo].[ContentPage].[Id] = [MyMobileApplication].[Security].[ContextPermissions].[EntityId]) WHERE ( [MyMobileApplication].[Security].[ContextPermissions].[EntityId] IS NOT NULL)

Now I'm trying to get Permission added to the top select list by way of overridding CreateFields.

I wrote this:


public override IEntityFields2 CreateFields()
        {
            IEntityFields2 toReturn = base.CreateFields();
            toReturn.Expand(1);

            IEntityField2 permissionField = new EntityField2("Permission",
                "ContextPermissions", typeof(long?));
            toReturn.DefineField(permissionField, toReturn.Count - 1);

            return toReturn;
        }

But that generated this broken SQL:


SELECT [MyMobileApplication].[dbo].[ContentPage].[ContentPageTypeId], [MyMobileApplication].[dbo].[ContentPage].[CreatedBy], [MyMobileApplication].[dbo].[ContentPage].[CreatedOn], [MyMobileApplication].[dbo].[ContentPage].[Description], [MyMobileApplication].[dbo].[ContentPage].[Id], [MyMobileApplication].[dbo].[ContentPage].[PageTitle], [MyMobileApplication].[dbo].[ContentPage].[UpdatedBy], [MyMobileApplication].[dbo].[ContentPage].[UpdatedOn], [ContextPermissions].[Permission] FROM ( []  INNER JOIN [MyMobileApplication].[Security].[ContextPermissions]  ON  [MyMobileApplication].[dbo].[ContentPage].[Id] = [MyMobileApplication].[Security].[ContextPermissions].[EntityId]) WHERE ( [MyMobileApplication].[Security].[ContextPermissions].[EntityId] IS NOT NULL)

See the "FROM ( [] INNER JOIN". ContentPage isn't in the FROM clause. Permission is in the select list however I appear to be missing a step. Could you point me in the right direction?

Here is the additions to dataaccessadapter I wrote/copied to include my join:


public partial class DataAccessAdapter
    {
        protected override IRetrievalQuery CreateSelectDQ(IEntityFields2 fieldsToFetch, IFieldPersistenceInfo[] persistenceInfoObjects, IPredicateExpression filter, long maxNumberOfItemsToReturn, ISortExpression sortClauses, IRelationCollection relationsToWalk, bool allowDuplicates, IGroupByCollection groupByClause, int pageNumber, int pageSize)
        {
            if (fieldsToFetch[0].ContainingObjectName == "ContentPageEntity")
            {
                var bucket = CreateRelationPredicateBucket(filter, relationsToWalk);
                AddRowAuthorisationFilter(fieldsToFetch.GetAsEntityFieldCoreArray(), bucket);

                InsertPersistenceInfoObjects(bucket.PredicateExpression);
                filter = bucket.PredicateExpression;
                InsertPersistenceInfoObjects(bucket.Relations);
                relationsToWalk = bucket.Relations;
            }

            return base.CreateSelectDQ(fieldsToFetch, persistenceInfoObjects, filter, maxNumberOfItemsToReturn, sortClauses, relationsToWalk, allowDuplicates, groupByClause, pageNumber, pageSize);
        }

        static RelationPredicateBucket CreateRelationPredicateBucket(IPredicate filter, IRelationCollection relations)
        {
            var bucket = new RelationPredicateBucket(filter); //alas this does not take relations as well
            bucket.Relations.AddRange(relations as RelationCollection);
            if (relations != null)
            {
                bucket.Relations.SelectListAlias = relations.SelectListAlias;
                bucket.Relations.ObeyWeakRelations = relations.ObeyWeakRelations;
            }
            return bucket;
        }

        void AddRowAuthorisationFilter(IEntityFieldCore[] fieldsToFetch, IRelationPredicateBucket bucket)
        {
            //if (RowAuthorisationFilterToApply != null)
            //{
                if (bucket.Relations != null)
                    for (var i = 0; i < bucket.Relations.Count; i++)
                        AddRowAuthorisationFilter(bucket.Relations[i]); //Recursive

                CreateRowAuthorisationFilter(fieldsToFetch, bucket);
                //RowAuthorisationFilterToApply(fieldsToFetch, bucket);
            //}
        }

        void AddRowAuthorisationFilter(IRelation relation)
        {
            var relationAsDynamicRelation = relation as IDynamicRelation;
            var relationIsDynamicRelation = (relationAsDynamicRelation != null);
            if (relationIsDynamicRelation)
            {
                if (relationAsDynamicRelation.LeftOperandIsDerivedTable)
                    AddRowAuthorisationFilter((IDerivedTableDefinition)relationAsDynamicRelation.LeftOperand);
                if (relationAsDynamicRelation.RightOperandIsDerivedTable)
                    AddRowAuthorisationFilter((IDerivedTableDefinition)relationAsDynamicRelation.RightOperand);
            }
        }

        void AddRowAuthorisationFilter(IDerivedTableDefinition derivedTable)
        {
            var bucket = CreateRelationPredicateBucket(derivedTable.Filter, derivedTable.Relations);
            AddRowAuthorisationFilter(derivedTable.GetFieldsAsArray(), bucket); //Recursive
            if (bucket.PredicateExpression.Count > 0)
                derivedTable.Filter = bucket.PredicateExpression;
            if (bucket.Relations.Count > 0)
                derivedTable.Relations = bucket.Relations;
        }

        void CreateRowAuthorisationFilter(IEntityFieldCore[] fieldsToFetch, IRelationPredicateBucket bucket)
        {
            var entityAliases = (from entityField in fieldsToFetch
                                 where entityField.ActualContainingObjectName == "ContentPageEntity" && !entityField.ActAsDerivedTableField
                                 select
                                     new { entityField.ObjectAlias }).Distinct();

            if (entityAliases.Count() > 0)
                foreach (var occurrenceAlias in entityAliases)
                    AddAuthorisationFilter(bucket, string.IsNullOrEmpty(occurrenceAlias.ObjectAlias) ? bucket.SelectListAlias : occurrenceAlias.ObjectAlias);
        }

        void AddAuthorisationFilter(IRelationPredicateBucket bucket, string alias)
        {
            var rowLevel = ContextPermissionsFields.EntityId != DBNull.Value;
            var permissionCheck = ContentPageFields.Id.SetObjectAlias(alias) == ContextPermissionsFields.EntityId;
            
            bucket.PredicateExpression.Add(rowLevel);


            DynamicRelation relation = new DynamicRelation(EntityType.ContentPageEntity, JoinHint.Inner, EntityType.ContextPermissionsEntity, alias, string.Empty, permissionCheck);
            bucket.Relations.Add(relation);
        }
    }

worldspawn avatar
worldspawn
User
Posts: 321
Joined: 26-Aug-2006
# Posted on: 11-Feb-2011 03:55:24   

I change CreateFields to this:


public override IEntityFields2 CreateFields()
        {
            IEntityFields2 toReturn = base.CreateFields();
            toReturn.Expand(1);

            IEntityField2 permissionField = EntityFieldFactory.Create("ContextPermissionsEntity", "Permission");
            toReturn.DefineField(permissionField, toReturn.Count - 1);

            return toReturn;
        }

Outputted SQL is:


SELECT [MyMobileApplication].[dbo].[ContentPage].[ContentPageTypeId], [MyMobileApplication].[dbo].[ContentPage].[CreatedBy], [MyMobileApplication].[dbo].[ContentPage].[CreatedOn], [MyMobileApplication].[dbo].[ContentPage].[Description], [MyMobileApplication].[dbo].[ContentPage].[Id], [MyMobileApplication].[dbo].[ContentPage].[PageTitle], [MyMobileApplication].[dbo].[ContentPage].[UpdatedBy], [MyMobileApplication].[dbo].[ContentPage].[UpdatedOn], [MyMobileApplication].[Security].[ContextPermissions].[Permission] FROM ( [MyMobileApplication].[Security].[ContextPermissions]  INNER JOIN [MyMobileApplication].[Security].[ContextPermissions]  ON  [MyMobileApplication].[Security].[ContextPermissions].[EntityId] = [MyMobileApplication].[Security].[ContextPermissions].[EntityId]) WHERE ( [MyMobileApplication].[Security].[ContextPermissions].[EntityId] IS NOT NULL)

Seem to be breaking the from clause still... it seems to change the "FROM" from ContentPageEntity to ContextPermissionsEntity

Walaa avatar
Walaa
Support Team
Posts: 14946
Joined: 21-Aug-2005
# Posted on: 11-Feb-2011 08:29:18   

What's the type of relation between ContentPage and ContextPermission?

(Edit) I also don't see where is the relation between ContentPage and ContextPermission is being added to the Query?

worldspawn avatar
worldspawn
User
Posts: 321
Joined: 26-Aug-2006
# Posted on: 11-Feb-2011 08:40:37   

What's the type of relation between ContentPage and ContextPermission?

There is no defined relation. I am adding it in as a dynamic relation. Buts 1 (as in one content page) to many (many context permissions).

Dynamic relation is here:


 void AddAuthorisationFilter(IRelationPredicateBucket bucket, string alias)
        {
            var rowLevel = ContextPermissionsFields.EntityId != DBNull.Value;
            var permissionCheck = ContentPageFields.Id.SetObjectAlias(alias) == ContextPermissionsFields.EntityId;
            
            bucket.PredicateExpression.Add(rowLevel);


            DynamicRelation relation = new DynamicRelation(EntityType.ContentPageEntity, JoinHint.Inner, EntityType.ContextPermissionsEntity, alias, string.Empty, permissionCheck);
            bucket.Relations.Add(relation);
        }

This is working fine. If i dont' override CreateFields in the entity factory this works perfectly. I get a filtered resultset.

All or most of this code is pulled from the thread you referenced. Modified slightly to suit my schema.

As soon as I add the new field in CreateFields it breaks. I can see the field is added to the select list, however where it should say FROM ContentPageEntity INNER JOIN... it changes to ContextPermissions INNER JOIN. It's like as soon as I add another field that references another table the FROM will switch to whatever entity the new fields is derived from.

The post you referenced shows how to add a scalarqueryexpression which is not what I want to do. It makes messy sql that is doing more than it needs to.

I'm so close! Yet so far frowning

Walaa avatar
Walaa
Support Team
Posts: 14946
Joined: 21-Aug-2005
# Posted on: 11-Feb-2011 09:15:56   

Buts 1 (as in one content page) to many (many context permissions).

Is this relation defines in the designer? If so then you can use prefetchPath to fetch the ContextPermission collection and forget about the CreateFields way.

worldspawn avatar
worldspawn
User
Posts: 321
Joined: 26-Aug-2006
# Posted on: 11-Feb-2011 09:33:13   

No its not defined in the designer. I don't really want to introduce an additional query either.

Is there some way to correct the from clause using the createfields approach?

Walaa avatar
Walaa
Support Team
Posts: 14946
Joined: 21-Aug-2005
# Posted on: 11-Feb-2011 09:58:12   

The issue is how would you fetch many permissions into one field? You need another query IMHO.

worldspawn avatar
worldspawn
User
Posts: 321
Joined: 26-Aug-2006
# Posted on: 11-Feb-2011 12:19:01   

You are right.

Sorry the Permission table is 1 to many but the contextpermissions view already aggregates the information so there will only be one row per unique entityid+entitytype so it would be... 1 to 1 I guess.

I updated the AddAuthorisationFilter method to restrict the join on the entitytype column:


void AddAuthorisationFilter(IRelationPredicateBucket bucket, string alias)
        {
            var rowLevel = ContextPermissionsFields.EntityId != DBNull.Value;
            var permissionCheck = (EntityField2)EntityFieldFactory.Create("ContextPermissionsEntity", "EntityId") == ContextPermissionsFields.EntityId;
            permissionCheck = permissionCheck & (EntityField2)EntityFieldFactory.Create("ContextPermissionsEntity", "EntityType") == "ContentPage";
            
            bucket.PredicateExpression.Add(rowLevel);

            DynamicRelation relation = new DynamicRelation(EntityType.ContentPageEntity, JoinHint.Inner, EntityType.ContextPermissionsEntity, alias, string.Empty, permissionCheck);
            bucket.Relations.Add(relation);
        }

How can I fix up the other issue with the from clause?

daelmo avatar
daelmo
Support Team
Posts: 8245
Joined: 28-Nov-2005
# Posted on: 12-Feb-2011 05:07:12   

worldspawn wrote:

How can I fix up the other issue with the from clause?

Just to start over on the 'FORM clause' issue, please elaborate again on what do you want (add a Permission field which is not originally in the main query?), how are you doing that and what are you getting.

David Elizondo | LLBLGen Support Team
worldspawn avatar
worldspawn
User
Posts: 321
Joined: 26-Aug-2006
# Posted on: 12-Feb-2011 06:37:37   

I'm trying to add the field by adding this to the entities entityfactory.


public override IEntityFields2 CreateFields()
        {
            IEntityFields2 toReturn = base.CreateFields();
            toReturn.Expand(1);

            IEntityField2 permissionField = EntityFieldFactory.Create("ContextPermissionsEntity", "Permission");
            toReturn.DefineField(permissionField, toReturn.Count - 1);

            return toReturn;
        }

The ContextPermissions should be in scope because in the CreateSelectDQ in my dataaccessadapter I am adding a join to the context permissions view.

Like so:


void AddAuthorisationFilter(IRelationPredicateBucket bucket, string alias)
        {
            var rowLevel = ContextPermissionsFields.EntityId != DBNull.Value;
            var permissionCheck = (EntityField2)EntityFieldFactory.Create("ContextPermissionsEntity", "EntityId") == ContextPermissionsFields.EntityId;
            permissionCheck = permissionCheck & (EntityField2)EntityFieldFactory.Create("ContextPermissionsEntity", "EntityType") == "ContentPage";
            
            bucket.PredicateExpression.Add(rowLevel);

            DynamicRelation relation = new DynamicRelation(EntityType.ContentPageEntity, JoinHint.Inner, EntityType.ContextPermissionsEntity, alias, string.Empty, permissionCheck);
            bucket.Relations.Add(relation);
        }

I am getting this SQL:


exec sp_executesql N'SELECT [MyMobileApplication].[dbo].[ContentPage].[ContentPageTypeId], [MyMobileApplication].[dbo].[ContentPage].[CreatedBy], [MyMobileApplication].[dbo].[ContentPage].[CreatedOn], [MyMobileApplication].[dbo].[ContentPage].[Description], [MyMobileApplication].[dbo].[ContentPage].[Id], [MyMobileApplication].[dbo].[ContentPage].[PageTitle], [MyMobileApplication].[dbo].[ContentPage].[UpdatedBy], [MyMobileApplication].[dbo].[ContentPage].[UpdatedOn], [MyMobileApplication].[Security].[ContextPermissions].[Permission] FROM ( [MyMobileApplication].[Security].[ContextPermissions]  INNER JOIN [MyMobileApplication].[Security].[ContextPermissions]  ON  ( [MyMobileApplication].[Security].[ContextPermissions].[EntityId] = [MyMobileApplication].[Security].[ContextPermissions].[EntityId] AND [MyMobileApplication].[Security].[ContextPermissions].[EntityType] = @p1)) WHERE ( [MyMobileApplication].[Security].[ContextPermissions].[EntityId] IS NOT NULL)',N'@p1 nvarchar(128)',@p1=N'ContentPage'

As you can see it's selecting from ContextPermissions and joining onto ContextPermissions. It should be from ContentPage joining onto ContextPermissions. This problem appears when I add the extra field. If I don't the from clause is correct.

daelmo avatar
daelmo
Support Team
Posts: 8245
Joined: 28-Nov-2005
# Posted on: 13-Feb-2011 23:23:20   

worldspawn wrote:

As you can see it's selecting from ContextPermissions and joining onto ContextPermissions. It should be from ContentPage joining onto ContextPermissions. This problem appears when I add the extra field. If I don't the from clause is correct.

I think that is because you are using the same entity in the filter:

 var permissionCheck = (EntityField2)EntityFieldFactory.Create("ContextPermissionsEntity", "EntityId") == ContextPermissionsFields.EntityId

Besides, I don't understand why you are using DynamicRelation when it seems that a normal relation is enough. Also, Why are you creating the field with EntityFieldFactory when you can use the field object directly?

Does this work instead?:

void AddAuthorisationFilter(IRelationPredicateBucket bucket, string alias)
{
    var rowLevel = ContextPermissionsFields.EntityId != DBNull.Value;
    bucket.PredicateExpression.Add(rowLevel);
            
    EntityRelation relationToAdd = new EntityRelation(ContentPageFields.Id, ContextPermissionsFields.EntityId, JoinHint.Inner);
    bucket.Relations.Add(relation);
}
David Elizondo | LLBLGen Support Team
worldspawn avatar
worldspawn
User
Posts: 321
Joined: 26-Aug-2006
# Posted on: 14-Feb-2011 07:33:34   

Hi daelmo,

What you posted didn't quite build. Almost but not quite, need a relationtype rather than joinhint.

I'm using a dynamic relation because I have no idea what I am doing. The relation wasn't predefined so... in my mind... that made it dynamic!


void AddAuthorisationFilter(IRelationPredicateBucket bucket, string alias)
        {
            var rowLevel = ContextPermissionsFields.EntityId != DBNull.Value;
            var permissionCheck = (EntityField2)EntityFieldFactory.Create("ContextPermissionsEntity", "EntityId") == ContextPermissionsFields.EntityId;
            permissionCheck = permissionCheck & (EntityField2)EntityFieldFactory.Create("ContextPermissionsEntity", "EntityType") == "ContentPage";
            
            bucket.PredicateExpression.Add(rowLevel);

            EntityRelation relationToAdd = new EntityRelation(ContentPageFields.Id, ContextPermissionsFields.EntityId, RelationType.OneToOne);
            relationToAdd.CustomFilter = new PredicateExpression(ContextPermissionsFields.EntityType == "ContentPage");
            bucket.Relations.Add(relationToAdd);
        }

Produced:


go
exec sp_executesql N'SELECT [MyMobileApplication].[dbo].[ContentPage].[ContentPageTypeId], [MyMobileApplication].[dbo].[ContentPage].[CreatedBy], [MyMobileApplication].[dbo].[ContentPage].[CreatedOn], [MyMobileApplication].[dbo].[ContentPage].[Description], [MyMobileApplication].[dbo].[ContentPage].[Id], [MyMobileApplication].[dbo].[ContentPage].[PageTitle], [MyMobileApplication].[dbo].[ContentPage].[UpdatedBy], [MyMobileApplication].[dbo].[ContentPage].[UpdatedOn], [MyMobileApplication].[Security].[ContextPermissions].[Permission] FROM ( [MyMobileApplication].[dbo].[ContentPage]  INNER JOIN [MyMobileApplication].[Security].[ContextPermissions]  ON  [MyMobileApplication].[dbo].[ContentPage].[Id]=[MyMobileApplication].[Security].[ContextPermissions].[EntityId] AND ( ( [MyMobileApplication].[Security].[ContextPermissions].[EntityType] = @p1))) WHERE ( [MyMobileApplication].[Security].[ContextPermissions].[EntityId] IS NOT NULL)',N'@p1 nvarchar(128)',@p1=N'ContentPage'
go


Its' mostly working now. Except when I add a where clause to the query I get this:


exec sp_executesql N'SELECT [LPA_L1].[ContentPageTypeId], [LPA_L1].[CreatedBy], [LPA_L1].[CreatedOn], [LPA_L1].[Description], [LPA_L1].[Id], [LPA_L1].[PageTitle], [LPA_L1].[UpdatedBy], [LPA_L1].[UpdatedOn], [LPA_L1].[Permission] FROM ( [MyMobileApplication].[dbo].[ContentPage]  INNER JOIN [MyMobileApplication].[Security].[ContextPermissions]  ON  [MyMobileApplication].[dbo].[ContentPage].[Id]=[MyMobileApplication].[Security].[ContextPermissions].[EntityId] AND ( ( [MyMobileApplication].[Security].[ContextPermissions].[EntityType] = @p1))) WHERE ( ( ( ( ( [LPA_L1].[PageTitle] LIKE @p2)))) AND [MyMobileApplication].[Security].[ContextPermissions].[EntityId] IS NOT NULL)',N'@p1 nvarchar(128),@p2 nvarchar(6)',@p1=N'ContentPage',@p2=N'%Test%'

So ContentPage never got aliased and the Permission column has had the same alias applied... it wouldn't be the same.

worldspawn avatar
worldspawn
User
Posts: 321
Joined: 26-Aug-2006
# Posted on: 14-Feb-2011 07:57:34   

Ok I wasn't putting the alias on the relation...


void AddAuthorisationFilter(IRelationPredicateBucket bucket, string alias)
        {
            var rowLevel = ContextPermissionsFields.EntityId != DBNull.Value;
            var permissionCheck = (EntityField2)EntityFieldFactory.Create("ContextPermissionsEntity", "EntityId") == ContextPermissionsFields.EntityId;
            permissionCheck = permissionCheck & (EntityField2)EntityFieldFactory.Create("ContextPermissionsEntity", "EntityType") == "ContentPage";
            
            bucket.PredicateExpression.Add(rowLevel);

            EntityRelation relationToAdd = new EntityRelation(ContentPageFields.Id, ContextPermissionsFields.EntityId, RelationType.OneToOne);
            relationToAdd.CustomFilter = new PredicateExpression(ContextPermissionsFields.EntityType == "ContentPage");
            relationToAdd.SetAliases(string.Empty, alias);

            bucket.Relations.Add(relationToAdd);
        }

It doesn't make sense to me that I am setting the alias of the entity entity. This is then applied to the entity with the PK?? Isn't that around the wrong way?

Anyway that gets me close... the alias on the Permission Field is wrong though.


exec sp_executesql N'SELECT [LPA_L1].[ContentPageTypeId], [LPA_L1].[CreatedBy], [LPA_L1].[CreatedOn], [LPA_L1].[Description], [LPA_L1].[Id], [LPA_L1].[PageTitle], [LPA_L1].[UpdatedBy], [LPA_L1].[UpdatedOn], [LPLA_1].[Permission] FROM ( [MyMobileApplication].[dbo].[ContentPage] [LPA_L1]  INNER JOIN [MyMobileApplication].[Security].[ContextPermissions]  ON  [LPA_L1].[Id]=[MyMobileApplication].[Security].[ContextPermissions].[EntityId] AND ( ( [MyMobileApplication].[Security].[ContextPermissions].[EntityType] = @p1))) WHERE ( ( ( ( ( [LPA_L1].[PageTitle] LIKE @p2)))) AND [MyMobileApplication].[Security].[ContextPermissions].[EntityId] IS NOT NULL)',N'@p1 nvarchar(128),@p2 nvarchar(6)',@p1=N'ContentPage',@p2=N'%Test%'

Am I able to control the alias applied to this field?

worldspawn avatar
worldspawn
User
Posts: 321
Joined: 26-Aug-2006
# Posted on: 14-Feb-2011 08:11:11   

Success (so far)!

I explicitly set the object alias on anythign to do with ContextPermissions. LLB does some magic where it matches that up and creates its own alias( LPA_2 or whatever).

Output:


exec sp_executesql N'SELECT [LPA_L1].[ContentPageTypeId], [LPA_L1].[CreatedBy], [LPA_L1].[CreatedOn], [LPA_L1].[Description], [LPA_L1].[Id], [LPA_L1].[PageTitle], [LPA_L1].[UpdatedBy], [LPA_L1].[UpdatedOn], [LPA_C2].[Permission] FROM ( [MyMobileApplication].[dbo].[ContentPage] [LPA_L1]  INNER JOIN [MyMobileApplication].[Security].[ContextPermissions] [LPA_C2]  ON  [LPA_L1].[Id]=[LPA_C2].[EntityId] AND ( ( [LPA_C2].[EntityType] = @p1))) WHERE ( ( ( ( ( [LPA_L1].[PageTitle] LIKE @p2)))) AND [LPA_C2].[EntityId] IS NOT NULL)',N'@p1 nvarchar(128),@p2 nvarchar(6)',@p1=N'ContentPage',@p2=N'%Test%'


void AddAuthorisationFilter(IRelationPredicateBucket bucket, string alias)
        {
            var rowLevel = ContextPermissionsFields.EntityId.SetObjectAlias("ContextPermissions") != DBNull.Value;
            bucket.PredicateExpression.Add(rowLevel);

            EntityRelation relationToAdd = new EntityRelation(ContentPageFields.Id, ContextPermissionsFields.EntityId, RelationType.OneToOne);
            relationToAdd.CustomFilter = new PredicateExpression(ContextPermissionsFields.EntityType.SetObjectAlias("ContextPermissions") == "ContentPage");
            relationToAdd.SetAliases("ContextPermissions", alias);

            bucket.Relations.Add(relationToAdd);
        }

and in the entity factory:


public override IEntityFields2 CreateFields()
        {
            IEntityFields2 toReturn = base.CreateFields();
            toReturn.Expand(1);

            IEntityField2 permissionField = ContextPermissionsFields.Permission;
            permissionField.SetObjectAlias("ContextPermissions");
            
            toReturn.DefineField(permissionField, toReturn.Count - 1);

            return toReturn;
        }

Walaa avatar
Walaa
Support Team
Posts: 14946
Joined: 21-Aug-2005
# Posted on: 14-Feb-2011 09:30:56   

Thanks for the feedback.

1  /  2