CODEWITHSUNDEEP
vb.netlinq
Trind 07
Trind 07

Reputation: 919

Select records that have duration of StartDate and EndDate that not covered by Duration of given StartDate and EndDate

I have used LINQ to select records from a database table (Budgets) that have a structure like this:

A Sample record:

ID      BudgetName          StartDate                       EndDate
1       budget              2016-03-14 04:33:09.737         2016-04-13 04:33:09.737

My VB Code that illustrate my idea of "Select records that have duration of StartDate and EndDate that not covered by Duration of given StartDate and EndDate":

Dim startDate = DateTime.Today
Dim endDate = DateTime.Today.AddMonths(1).AddDays(1).AddSeconds(-1)
Dim budgets = (From item In dc.Budgets _
                Where Duration Of item.StartDate And item.StartDate Is Not Covered By Duration Of startDate And endDate _
                Select item)

'Note: Budgets is a System.Data.Linq.Table

I also have one more picture to illustrate my idea:

Select records that have duration of StartDate and EndDate that not covered by Duration of given StartDate and EndDate

My issue is: I don't know how to format a LINQ to achieve the result. So please help me to reformat my LINQ in the example above.

Upvotes: 2

Views: 72

Answers (1)

har07
har07

Reputation: 89295

Assuming that your data is consistent i.e EndDate always greater or equals to StartDate, you can try this way :

Dim budgets = (From item In dc.Budgets _
                Where item.StartDate > endDate Or item.EndDate < startDate
                Select item)

The above query should select items which starts after the given endDate or ends before the given startDate, in other words, items which duration doesn't overlap the given duration.

Upvotes: 2

Related Questions