0

I'm working on a program that keeps a list of physical advertisement spots and their reservations (a date range). The program needs to be able to find "open spots" for ads within an ad category.

I have three tables: AdTypes, AdPlaces and Reservations. Currently, I'm implementing a query that searches for reservations where the dates don't collide with the date range the user selected, and returns the AdType items as a list. This method works if every AdType has had an reservation at some point, but it doesn't list AdTypes that are not found in the Reservations table.

The filtering is done in PreProcessQuery of an AdTypes query, as such:

query = query.Where(r => r.Reservations.Any(res => (res.Begindate > Begindate && Enddate < res.Enddate) || (res.Enddate < Begindate && Enddate > res.Begindate)));

How can I "extend" the query so that all those AdTypes that have no reservations would be listed alongside "expired" AdType reservations?

user1470930
  • 1
  • Unrelated to the meat of your question, but I think your date query needs some work. The fourth comparison is always true if the third comparison is true, and the first two comparisons don't make much sense either. If you're looking for "`res` doesn't overlap the main period at all" you just need `res.Enddate < Begindate || Enddate < res.Begindate`. – Rawling Jun 21 '12 at 07:27
  • please show the basic relationships among the tables – Jake Berger Jul 03 '12 at 14:57

1 Answers1

0

Maybe I'm mssing something, or not quite understanding what you want, but how can you expect see AdTypes that don't exist?

Is it that you want to see the AdTypes that don't have any reservations during the period you're testing for?

If so, I imagine you'd have to use the adType entity as the basis of your screen, not the Reservation entity (with a query based on AdType, not Reservation). That way you can produce a list of AdTypes that don't have any overlapping reservations.

Does that make any sense?

Yann Duran
  • 3,842
  • 1
  • 24
  • 24