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
|
Hello. I’m having a little trouble returning the correct data when using Contains. Our appointment table is linked to a resource table via a one to many association. We need a Contains condition to reduce the results to only those appointments that belong to a subset of resources. We typically have over 50 types of resources but the user is only interested in appointments belonging to around 1 to 10 of them. When I use the below query without Contains it works well with a collection of appointments and each appointment has a collection of resources. But the result is huge as it hasn’t been filtered by the resource ids. from x in unitOfWork.Appointments.WithAggregate("WithAppointmentResources") where x.Start >= fromDate & x.End <= toDate & x.OnHold == false select x)
If I use this next query with the contains condition to filter out appointments that are not associated with the supplied resourceId collection, then I get duplicate appointments as the query has joined to the AppointmentResourceLink table. from x in unitOfWork.Appointments.WithAggregate("WithAppointmentResources") where x.Start >= fromDate & x.End <= toDate & x.OnHold == false select x).Where(a => resourceIds.Contains(a.AppointmentResourceLinks.FirstOrDefault().AppointmentResourceId));
Examinging the generated sql I can see the second query looks like this: FROM [Appointment] INNER JOIN [AppointmentResourceLink] ON [Appointment].[AppointmentId] = [AppointmentResourceLink].[AppointmentId] WHERE (((([Appointment].[Start] >= '25/08/2011 8:00:00 AM' AND [Appointment].[End] <= '25/08/2011 7:00:00 PM') AND [Appointment].[OnHold] = False) AND [Appointment].[AppointmentId] = 974013) AND [AppointmentResourceLink].[AppointmentResourceId] IN (64, 81, 68, 75, 71, 48, 61, 89, 77));
Is there a way to not join to the appointmentresourcelink table and rather do something like this: FROM [Appointment] --INNER JOIN -- [AppointmentResourceLink] --ON -- [Appointment].[AppointmentId] = [AppointmentResourceLink].[AppointmentId] WHERE [Appointment].[Start] >= '08/25/2011 8:00:00 AM' AND [Appointment].[End] <= '08/28/2011 7:00:00 PM' AND [Appointment].[OnHold] = 0 AND [Appointment].[AppointmentId] IN (select [AppointmentId] from [AppointmentResourceLink] where [AppointmentResourceId] IN (64, 81, 68, 75, 71, 48, 61, 89, 77))
Any ideas? Kind regards Brendon
|
|
|
You can do this using query objects as follows: object[] tagids = new object[] { 51, 52, 53 }; (Here ContributionTag is the through entity corresponding to AppointmentResourceLink, and Contribution is the main entity corresponding to Appointment in your scenario.) Obviously you can combine further criteria into the main query e.g. Entity.Attribute("OnHold") == false && ... I will investigate whether there is a way of expressing this in LINQ but the naive translation into LINQ doesn't seem to work at the moment... |
|
|
Hi and thanks for your reply. I'm having trouble building up the query to inlcude the fields Onhold, and start and end times. Do I use a QuerySubexpressionCollection object? Do you have any examples where the query has several criterias? Also how do I include an Aggregate? Thanks. |
|
|
No, just use the Entity.Attribute syntax again: var rs = unitOfWork.Find<Contribution>( To include an aggregate, create a Query object with this as the QueryExpression, and set Query.AggregateName. Query query = new Query |
|