This thread looks to be a little on the old side and therefore may no longer be relevant. Please see if there is a newer thread on the subject and ensure you're using the most recent build of any software if your question regards a particular product.
This thread has been locked and is no longer accepting new posts, if you have a question regarding this topic please email us at support@mindscape.co.nz
|
In my LSModel I have a Person entity, which has a collection of PersonRoles. Each PersonRole has from 0 to many PersonRoleOrganisations. Each PersonRoleOrganisation relates to an Organisation, like this: Person 1:M PersonRole 1:M PersonRoleOrganisation M:1 Organisation
When I pass the following predicate to the .Where<T>() method on my UnitOfWork all is well:
person => (person as Person).PersonRoles.Any( pr => pr.PersonRoleOrganisations.Any( pro => organisationIds.Contains(pro.OrganisationId)));
However when I pass a similar predicate: person => (person as Person).PersonRoles.Any(pr=>!pr.PersonRoleOrganisations.Any());
The following exception is generated:
System.InvalidCastException : Unable to cast object of type 'Mindscape.LightSpeed.Querying.NegatedExpression' to type 'Mindscape.LightSpeed.Querying.IAttributePathExpression'. at Mindscape.LightSpeed.Linq.Sqo.Any.ProcessSimple(String prefix, QueryExpression qx) at Mindscape.LightSpeed.Linq.Sqo.AnyAllBase.ApplyCriteria(LinqQueryPlanExpression plan, QueryExpression applyTo, Expression[] arguments) at Mindscape.LightSpeed.Linq.Sqo.ScalarCriteriaSqo.GetCriteria(LinqQueryPlanExpression plan, IEnumerable`1 arguments) at Mindscape.LightSpeed.Linq.Sqo.CriteriaSqo.ExtractMethodCriteria(LinqQueryPlanExpression plan, MethodCallExpression expression) at Mindscape.LightSpeed.Linq.Sqo.CriteriaSqo.ConvertToQueryExpression(LinqQueryPlanExpression plan, Expression expression) at Mindscape.LightSpeed.Linq.Sqo.CriteriaSqo.ExtractBinaryCriteria(LinqQueryPlanExpression plan, BinaryExpression expression) at Mindscape.LightSpeed.Linq.Sqo.CriteriaSqo.ExtractCriteriaCore(LinqQueryPlanExpression plan, Expression expression) at Mindscape.LightSpeed.Linq.Sqo.CriteriaSqo.ExtractCriteria(LinqQueryPlanExpression plan, Expression expression) at Mindscape.LightSpeed.Linq.Sqo.CriteriaSqo.ExtractCriteriaCore(LinqQueryPlanExpression plan, Expression expression) at Mindscape.LightSpeed.Linq.Sqo.CriteriaSqo.ExtractCriteria(LinqQueryPlanExpression plan, Expression expression) at Mindscape.LightSpeed.Linq.Sqo.CriteriaSqo.ExtractCriteriaCore(LinqQueryPlanExpression plan, Expression expression) at Mindscape.LightSpeed.Linq.Sqo.CriteriaSqo.ExtractCriteria(LinqQueryPlanExpression plan, Expression expression) at Mindscape.LightSpeed.Linq.Sqo.Where.ExtractWhereCriteria(MethodCallExpression expression, LinqQueryPlanExpression plan, GroupResultsPlan groupPlan) at Mindscape.LightSpeed.Linq.Sqo.Where.Evaluate(ExpressionVisitor visitor, MethodCallExpression expression) at Mindscape.LightSpeed.Linq.Plan.LinqQueryBuilder.VisitMethodCall(MethodCallExpression exp) at Mindscape.LightSpeed.Linq.Plan.LinqQueryBuilder.Visit(Expression exp) at Mindscape.LightSpeed.Linq.LinqQueryProvider.GetExecutionPlan(Expression expression) at Mindscape.LightSpeed.Linq.LinqQueryProvider.Execute(Expression expression) at Mindscape.LightSpeed.Linq.LinqQueryProvider.System.Linq.IQueryProvider.Execute(Expression expression) at HpsLibrary.Linq.ExpandableQueryProvider`1.System.Linq.IQueryProvider.Execute(Expression expression) in ExpandableQuery.cs: line 94 at HpsLibrary.Linq.BooleanReducingQueryProvider`1.Execute(Expression expression) in BooleanReducingQueryProvider.cs: line 46 at HpsLibrary.Plumbing.Lightspeed.LightSpeedSafeQueryable`1.LightSpeedSafeQueryProvider.Execute(Expression expression) in LightSpeedSafeQueryable.cs: line 27 at HpsLibrary.Linq.QueryProviderDecorator.System.Linq.IQueryProvider.Execute(Expression expression) in QueryProviderDecorator.cs: line 49 at HpsLibrary.Linq.QueryableDecorator`1.GetEnumerator() in QueryableDecorator.cs: line 53 at System.Collections.Generic.List`1..ctor(IEnumerable`1 collection) at System.Linq.Enumerable.ToList(IEnumerable`1 source)
If anyone can shed any light on this that would be awesome! Cheers, B |
|
|
Unfortunately we do not currently support negated expressions in Any or All clauses. I have looked into whether this would be easy to address but I'm afraid it doesn't look like we'll be able to provide a quick fix for this. Jeremy has suggested that you may be able to achieve what you need with a join, but warns that this could return duplicate instances. |
|
|
An example of this using the LightSpeed query API would be: var query = new Query(); var results = unitOfWork.Find<Person>(query).Distinct().ToList();
The use of the LINQ Distinct/ToList calls on the end of this will filter out duplicates client side - the duplicates will be introduced because of the join which is necessary to satisfy your intent of having a PersonRole with no PersonRoleOrganisations.
Jeremy
|
|
|
Thanks for this example Jeremy. Is there any way of folding an additional, arbitrary, predicate into the QueryExpression? The reason for wanting to do this is that in the system we are building there are certain types of entity which should not be visible to users who have certain characteristics, and I want to build this in to the repository so that it's centralised, meaning we're not having to write code all over the service layer to cater for it (and risking forgetting to do so). For example, if I am a User (which has an associated Person record in my original example) who relates to a certain Organisation then I should only be able to see Person records who relate to the same organisation, or who have no relationship with an organisation. My solution to this is to, whenever Find<TEntity> on our repository is called, check the type of TEntity, and, if the entity being requested is of a type which should be subject to this additional check, adding predicates to whatever predicate has been passed in to the Find method. This might be explained better with a bit of pseudo-SQL: If the repository is asked for people where name ='Fred' I want to automatically change this to people where name = 'Fred' AND (who are in 'my' organisation OR are not in an organisation) I have all the wiring in place to automatically pull predicates in based on type and apply them, and it all works great apart from the Negated Expression one. In order for your approach of using the lightspeed query API to work in my scenario I need to be able to add the original predicate (where name = 'Fred') into the QueryExpression. If this is not possible then I guess I'll have to do some additional wiring to pull all of the Persons with no affiliation to an Organisation back in a separate query (using your sample above) and manually fold the results into the collection returned by the Find method, rather than adding the predicate to the query which is issued to the unit of work, as I'd hoped. Cheers, B |
|
|
Hi Ben, Yes you can AND togethor two QueryExpressions to combine the two predicates, e.g.
var organisationId = 3; var queryExpression = Entity.Attribute("Foo") == "Bar" && Entity.Attribute("Bar") > 1; var filter = Entity.Attribute("OrganisationId") == organisationId;
var combinedExpression = queryExpression && filter;
Jeremy |
|
|
When will LightSpeed support negated expressions in Any clauses? I have a scenario where I need this functionality. The joins workaround isn't suitable for what I'm trying to do. |
|
|
We don't have a time frame for this. However if you can share any details of your requirement then we can try to suggest an alternative approach, or try to find a partial solution that would be practical for us to implement. |
|
|
The following is a simplified example of what I'm trying to do:
from p in RbPeople where p.Addresses.Any (a => !(new int[]{1,2}).Contains(a.CountryId)) select new {p.FirstName} It doesn't work because of: System.InvalidCastException : Unable to cast object of type 'Mindscape.LightSpeed.Querying.NegatedExpression' to type 'Mindscape.LightSpeed.Querying.IAttributePathExpression' I've worked around it by translating it into:
This works fine for me, but the generated SQL seems way more complicated than it ought to be:
|
|
|
The more numbers I add, for example 3, 4, 5, 6 and so on, the more complicated the generated SQL becomes. |
|
|
I've found a core API query that appears to do what you want: unitOfWork.Find<RbPerson>( Note the Exists clause needs to be there or it will also return people with no addresses. We'll see if we can teach the LINQ provider to make this transformation for any-not clauses but I can't make any promises -- in the meantime hopefully the old-skool query will suffice. |
|
|
hi., i have a problem regarding to group by method in lightspeed query i dont really know if im correct but i do this in this way sql.Group = Group.By(Entity.Attribute("Columnname")) this doesn't return data but the query has a result(i mean count) . please help me to do the right way of group by in lightspeed THANKS IN ADVANCE!! |
|
|
If you are executing a grouping query the result set will be the results from the grouping operation (e.g. the grouping keys). You will then need to use subsequent queries to fetch the entities associated with those groups.
|
|
|
thanks for the reply jeremy.. my problem is i want to select multiple column from multiple table and group the result of the query. but i wasn't able to select other column from different table here is my code. sql.Join = Join.Inner(Of namespace.namespace.Tnbiclearance, namespace.namespace.TnbiclearanceBiometric)("NbicControlNumber", "NbicControlNumber") sql.QueryExpression = Entity.Attribute(Of namespace.namespace.Tnbiclearance)("DateEncoded") Dim result As IList(Of namespace.namespace.Tnbiclearance) = unofwork.Find(Of namespace.namespace.Tnbiclearance)(sql) any advice will help thanks a lot! |
|
|
hi again.. sorry for asking too many question jeremy but i can't figure out why does this block of code doesn't return any value at all Using unofwork = nbiconn.CreateUnitOfWork() Dim tbl As New NBI.NBI.TnbiclearanceBiometric
please advice me if you have any idea thanks :) |
|
|
I would recommend attaching a logger to see what SQL is being generated - you can then run that SQL against the database manually to understand what is being returned and if you need to modify something which you can then fold back into your LightSpeed query. Check out http://www.mindscapehq.com/documentation/lightspeed/Testing-and-Debugging/Logging for details on configuring a logger to be used. Also could you please post a new thread when you have a query on a new topic rather than adding a post to an existing thread as it makes it much easier for us to track down new posts and keeps the threads on topic.
|
|