Hi.. I've got a query I can't get to run correctly..

I've got 4 Entities that are tied together as follows

RequisitionRequestEntity PersonGuid) PersonEntity (has PersonGuid) PersonRole (has PersonGuid and RoleGuid) RoleGuid (has RoleGuid)

All of these have ClientGuid

And I've got a RelationsCollection - RequisitionRequestEntity.Relations.PersonEntityUsingPersonGuid

Just that works find.. I can get the Requisitions by Client by simply .

And I can add a Relation to PersonRole to filter by a list of filters keying off of RoleGuid ;

PersonRoleEntity.Relations.PersonEntityUsingPersonGuid

and then by looping through and appending the filter like;

filter.AddWithAnd(PersonRole.Fields.RoleGuid == roleguid)

However, I can't get a valid list when I try to get all Requistions for Peopel that aren't associated with a particular set of Roles, referenced in PersonRoles

and then looping through to add to the filter

filter.AddWithAnd(PersonRole.Fields.RoleGuid != roleguid)

And LLBGen things I'm missing here? Have I got my relations confused?

Thanks for any help,

Jay

Adding some code to help with my convoluted description;

IPredicateExpression filter = new PredicateExpression(RequisitionRequestFields.IsActive == true);
            
filter.AddWithAnd(RequisitionRequestFields.Purge == false);
filter.AddWithAnd(RequisitionRequestFields.NextFiscalYear == false);
            
RelationCollection rels = new RelationCollection();
rels.Add(RequisitionRequestEntity.Relations.PersonEntityUsingPersonGuid);
filter.AddWithAnd(PersonFields.CustomerGuid == FSPrincipal.GetCurrentPricipal().CustomerGuid);

rels.Add(PersonRoleEntity.Relations.PersonEntityUsingPersonGuid);
IPredicateExpression roleFilter = new PredicateExpression();
roleFilter.Add(!(PersonRoleFields.RoleGuid == new Guid("0cc69821-ba74-49cb-a28a-b2f812e5d1d1")));
filter.AddWithAnd(roleFilter);

reqs.GetMulti(filter, 25, new SortExpression(RequisitionRequestFields.Number | SortOperator.Ascending), rels, pageNumber, 25);

reqs.Items.Count <- returns 3 (which should be 13)
reqs.GetDbCount(filter, rels) <- returns 633 (which I'm not sure how it is figuring out)

reqs.Items.Count <- returns 3 (which should be 13) reqs.GetDbCount(filter, rels) <- returns 633 (which I'm not sure how it is figuring out)

here is the SQL query that works

select * from RequisitionRequest r, Person p where
r.IsActive = 1 and r.Purge = 0 and r.NextFiscalYear = 0 and p.CustomerGuid = '9A9ED07C-3DA2-4D71-BE0E-5EC05A42869B'
and r.PersonGuid = p.guid and p.guid  not in (
select PersonGuid from PersonRole where RoleGuid = '0cc69821-ba74-49cb-a28a-b2f812e5d1d1')

I read all your post but I'm not sure what is really your problem. If you are not retrieving the expected results it's most likely that you are putting a bad filter or an extra inner relation that is not necessary.

A good way to know what is going wrong is inspecting the Generated SQL.

To help you faster please post the following info: - LLBLGen version and runtime library version (http://llblgen.com/tinyforum/Messages.aspx?ThreadID=7725) - The generated SQL and point us to the part that you think are generated wrong. - The exact code you are using to reproduce this.

Thanks, David..

I'll try to get the GeneratedSQL using the link you sent. Though I don't see in the link how to use it.. I've added the web.config entry;

<system.diagnostics> <switches> <add name="SqlServerDQE" value="3" /> </switches> </system.diagnostics>

You can see the SQL in my post - it's not generated, it's a manually written query of what I'm trying to do with the PredeicateExpression filters and Entity relations above it.. but getting 2 different results.

And also, any idea why I would get a DBCount that is different than the number of items?

Thanks,

Jay

jokohews wrote:

Thanks, David..

I'll try to get the GeneratedSQL using the link you sent. Though I don't see in the link how to use it.. I've added the web.config entry;

<system.diagnostics> <switches> <add name="SqlServerDQE" value="3" /> </switches> </system.diagnostics>

You can see the SQL in my post - it's not generated, it's a manually written query of what I'm trying to do with the PredeicateExpression filters and Entity relations above it.. but getting 2 different results.

And also, any idea why I would get a DBCount that is different than the number of items?

Thanks,

Jay

I've found the DebugVisualizer2010 solution in my LLBLGen 2.6 folder Sourcecode > RuntimeLibraries > Net 2.x > DebugVisualizer.. but I can't get it to compile generate to put in the Visual Studio using these install directions;

Installation Copy the SD.LLBLGen.Pro.DebugVisualizers.dll from the folder Frameworks\LLBLGen Pro\RuntimeLibraries\DebuggerVisualizers\xxyy\ to the folder: My Documents\Visual Studio xxyy\Visualizers\ Also copy the ORMSupportClasses dll and the DQE dll of your choice to that folder. 'xxyy' is the vs.net year number of the vs.net version you're using.

You need to restart VS.NET to make them available to you. In a debug session, when you hit a breakpoint, you can hover your mouse over a variable of a type of any of the supported types below and you'll see a magnifyer glass which allows you to open the debug visualizer for that type with the data in that instance.

The error I'm getting is

Error 7 Could not load referenced assembly "C:\Program Files (x86)\Solutions Design\LLBLGen Pro v2.6\Sourcecode\RuntimeLibraries\Net2.x\ORMSupportClasses\bin\Debug\SD.LLBLGen.Pro.ORMSupportClasses.NET20.dll". Caught a FileNotFoundException saying "Could not load file or assembly 'C:\Program Files (x86)\Solutions Design\LLBLGen Pro v2.6\Sourcecode\RuntimeLibraries\Net2.x\ORMSupportClasses\bin\Debug\SD.LLBLGen.Pro.ORMSupportClasses.NET20.dll' or one of its dependencies. The system cannot find the file specified.". C:\Program Files (x86)\Solutions Design\LLBLGen Pro v2.6\Sourcecode\RuntimeLibraries\Net2.x\DebugVisualizers\ResGen DebugVisualizers

Error 1 Cryptographic failure while signing assembly 'C:\Program Files (x86)\Solutions Design\LLBLGen Pro v2.6\Sourcecode\RuntimeLibraries\Net2.x\DebugVisualizers\obj\Debug\SD.LLBLGen.Pro.DebugVisualizers.dll' -- 'Error reading key file 'C:\Myprojects\mystrongkey.key' -- The system cannot find the path specified. ' DebugVisualizers2010

Can this be applied to what I'm trying to do:

// C#
IPredicateExpression filter = new PredicateExpression();
filter.Add(new FieldCompareSetPredicate(YourEntityFields.Field, null, FooFields.Fieldb, null, SetOperator.In, null));

My Query

Person (PersonGuid) RequisitionRequest (Guid, PersonGuid) Role (RoleGuid) PersonRole (Guid, PersonGuid, RoleGuid)

select * from RequisitionRequest r, Person p where
r.IsActive = 1 and r.Purge = 0and r.NextFiscalYear = 0 and p.CustomerGuid = '9A9ED07C-3DA2-4D71-BE0E-5EC05A42869B'
and r.PersonGuid = p.guid and p.guid  not in (
select PersonGuid from PersonRole where RoleGuid = '0cc69821-ba74-49cb-a28a-b2f812e5d1d1' or RoleGuid = 'guid' or RoleGuid = 'guid'..etc)

Thanks,

Jay

Hi there,

To see the generated code just put this into your *.config file:

  <system.diagnostics>
    <switches>
      <add name="SqlServerDQE" value="4"/>
      <add name="ORMPersistenceExecution" value="4"/>
    </switches>
  </system.diagnostics>

Then when you debug the generated code will be shown in the output windows at your VSNet instance.

Ok, seeing the sql you want to generate I see you are missing the FieldCompareSetPredicate:

p.guid not in (
select PersonGuid from PersonRole where RoleGuid = '0cc69821-ba74-49cb-a28a-b2f812e5d1d1')

This is the code for that:

filter.Add(new FieldCompareSetPredicate(
     YourEntityFields.Field, FooFields.Fieldb, SetOperator.In, 
     new PredicateExpression(RoleGuid == '0cc69821-ba74-49cb-a28a-b2f812e5d1d1' || RoleGuid = 'guid' || RoleGuid = 'guid'),
     null, true));

Note the last boll parameter, that indicates if the Set predicate will be negated, so this will result in a 'NOT IN' predicate.

Hi David,

Thanks - I'm seeing the SQL debug output now.. that's a nice feature I wish I would have found a long time ago.

And the FieldCompare was the key.. along with the same relationships..thanks!

Thanks,

Jay

Hi David,

FYI - the reason I was getting different count in Entity.Items.Count and Entity.GetDBCount was because the items collection wasn't dupping, while the raw count from the query included dups.. so it was a botched query on my end with the way I configured the filters.

Thanks,

Jay

Glad it's sorted now