CODEWITHSUNDEEP
c#entity-frameworklinq
Hosein Aqajani
Hosein Aqajani

Reputation: 1603

using loop inside linq

Very short brief overview on my business: A customer needs our periodic service, so we have some reservedServices which reserved already with a specified duration. Everyday we should retrieve those reservedServices between start date and end date based on their turn.

For this purpose I need sth like this:

1 using (MehrpouyanEntities dbContext = new MehrpouyanEntities())
2 {        
3     var query1 = dbContext.ReservedServices.Where(r => 
4                  DbFunctions.AddDays(r.LastServiceDate, r.Duration) >= start && 
5                  DbFunctions.AddDays(r.LastServiceDate, r.Duration) <= end);
6     var query2 = dbContext.ReservedServices.Where(r => 
7                  DbFunctions.AddDays(r.LastServiceDate, 2*r.Duration) >= start && 
8                  DbFunctions.AddDays(r.LastServiceDate, 2*r.Duration) <= end);
9      .
10     .
11     .
12      var queryN = dbContext.ReservedServices.Where(r => 
13                 DbFunctions.AddDays(r.LastServiceDate, N * r.Duration) >= start && 
14                 DbFunctions.AddDays(r.LastServiceDate, N * r.Duration) <= end);
15     // this means that the loop continues untill:
       // DbFunctions.AddDays(r.LastServiceDate, N * r.Duration) > end
16     return query1.Concat(query2). ... .Concat(queryN).ToList();
17 }

How can I implement the above code using a loop structure?

Note1: The N parameter is various for different r, I need to implement a loop inside query.

Note2--over emphasizes Note1 : N as the Upper-bound of the loop, acquire inside query based on each record r , so each record r may has different N. More precisely the loop continues until DbFunctions.AddDays(r.LastServiceDate, N * r.Duration) > end

Note3--example: 

Customer A: LastServiceDate = october 05 2017
            Duration = 7 (Weekly)
start = october 06 2017
end = october 27 2017

I want to retrieve 3 services as follows:

  1. october 12 2017
  2. october 19 2017
  3. october 26 2017

Upvotes: 1

Views: 1343

Answers (3)

Ivan Stoev
Ivan Stoev

Reputation: 205589

Tricky question. And of course cannot be solved with LINQ to Entities query. But can be solved by mixed LINQ to Entities/LINQ to Objects approach.

Let split the problem in two parts.

(1) Retrieve all service records having 

LastServiceDate + i * Duration >= start &&
LastServiceDate + i * Duration <= end

for any integer i, where + means add days.

Let take the first condition

LastServiceDate + i * Duration >= start

Therefore it's satisfied for any

i >= (start - LastServiceDate) / Duration

hence the first service date is (taking into account integer arithmetic)

LastServiceDate + Duration * ceiling((start - LastServiceDate) / Duration)

(2) For each service retrieved by the first step, generate all service dates between start and end and produce the final result.

(1) can be implemented with LINQ to Entities (database) query, while (2) will be done in memory using LINQ to Objects.

Here is the implementation of the above concept:

var dbQuery = 
    from r in dbContext.ReservedServices
    where r.LastServiceDate <= start
    let firstDate = DbFunctions.AddDays(r.LastServiceDate, ((DbFunctions.DiffDays(r.LastServiceDate, start).Value + r.Duration - 1) / r.Duration) * r.Duration).Value
    where firstDate <= end
    select r;

var result = (
    from r in dbQuery.AsEnumerable()
    let firstDate = r.LastServiceDate.AddDays((((start - r.LastServiceDate).Days + r.Duration - 1) / r.Duration) * r.Duration)
    let count = 1 + (end - firstDate).Days / r.Duration
    from i in Enumerable.Range(0, count)
    select new { Service = r, Date = firstDate.AddDays(i * r.Duration) }
    ).ToList();

You can adjust the final anonymous type projection to anything which meets your requirements.

Upvotes: 2

Vikhram
Vikhram

Reputation: 4394

You can just loop over your query till your query returns nothing

IEnumerable<Data> GetData() {
    using (MehrpouyanEntities dbContext = new MehrpouyanEntities()) {
        bool hasData = true;
        for (int i = 1; hasData; ++i) {
            int n = i;
            hasData = false;
            var query = dbContext.ReservedServices.Where(r =>
                            DbFunctions.AddDays(r.LastServiceDate, n * r.Duration) >= start &&
                            DbFunctions.AddDays(r.LastServiceDate, n * r.Duration) <= end);

            foreach (var item in query) {
                hasData = true;
                yield return item;
            }

        }

    }
}

Upvotes: 0

Kyle
Kyle

Reputation: 5547

Try this:

IEnumerable<ReservedService> myReservedServices;
var runningQuery = new List<ReservedService>();

var index = 1;
do{
    // query here, you need to set myReservedServices to a value
    // and check if it's non-empty to decide if you'd like to check the next day
    runningQuery.Add(query);

    // increment the index
    index++;
} while(myReservedServices != null && myReservedServices.Any());

Upvotes: 0

Related Questions