Help with writing a linq query

Question

can anyone help me write a linq query, its a bit confusing... Basically i have a variable which numeberOfDays in my code which for this example is 8

I have a table that lists this.

   DayFrom  DayTo    Price
   1        3        20
   4        5        30
   6        8        40
   8        25       150

I need to return from linq a line for every day in my case 8...

so hence i end up with

20   (because day 1 and its from 1 to 3)
20   (because day 2 and its from 1 to 3)
20   (because day 3 and its from 1 to 3)
30   (because day 4 and its from 4 to 5)
30   (because day 5 and its from 4 to 5)
40   (because day 6 and its from 6 to 8)
40   (because day 6 and its from 6 to 8)
40   (because day 7 and its from 6 to 8)
40   (because day 8 and its from 6 to 8)

// no more records will be listed because we are on 8 and its the last day..

SO basically i just need to return a TOTAL of all in this case

20+20+20+30+30+40+40+40+40 = 280 .....

I have no idea where to start,, if it was in code then i could use a counter for numOfDays and a for Next to check the day and the price in within Dayfrom and Dayto..

I would really appreciate any feedback..

Thanks

Konamiman · Accepted Answer · 2009-10-28 14:06:10Z

1

Assuming that you have a class named DataItem with the properties DayFrom, DayTo and Price, the following should work (note anyway that I haven't tested it):

int Total(int numberOfDays, DataItem[] items){
    var query =
        from item in items
        let daysInData = Math.Min(item.DayTo, numberOfDays) - item.DayFrom + 1
        where item.DayFrom >= numberOfDays
        select item.Price * daysInData;
    return query.Sum();
}

answered Oct 28, 2009 at 14:06

Konamiman

50.5k17 gold badges111 silver badges140 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

mark smith Over a year ago

hi there, thanks ... but it returns the following error in linqpad The best overloaded method match for 'System.Math.Min(sbyte, sbyte)' has some invalid arguments - Argument '1': cannot convert from 'int?' to 'sbyte'

Konamiman Over a year ago

That's probably because you have defined the rows in the database tables as nullable. Try this: Math.Min(item.DayTo.Value, numberOfDays)

mark smith Over a year ago

Thanks, finally got it working with some playing around, and yes you were right they were nullable ... thanks a bunch.. accepting answer.

Yuriy Faktorovich · Accepted Answer · 2009-10-28 14:08:34Z

1

var priceList = new[] {
    new {DayFrom = 1, DayTo = 3, PriceList = 20},
    new {DayFrom = 4, DayTo = 5, PriceList = 30},
    new {DayFrom = 6, DayTo = 8, PriceList = 40},
    new {DayFrom = 9, DayTo = 25, PriceList = 150}
};

int days = 8;

var total = (from p in priceList
         from d in Enumerable.Range(1, days)
         where p.DayFrom <= d && p.DayTo >= d
         select p.PriceList).Sum();

Console.WriteLine(total);

But there is a strangeness to your data in the DayTo and DayFrom at 8, so I changed it to 9.

edited Oct 28, 2009 at 14:08

answered Oct 28, 2009 at 13:57

Yuriy Faktorovich

68.9k14 gold badges107 silver badges150 bronze badges

1 Comment

mark smith Over a year ago

Thanks, i noticed you edited it .. i have tried witht he updated code but it gives me now NotSupportedException: Local sequence cannot be used in LINQ to SQL implementation of query operators except the Contains() operator.

Martin Jonáš · Accepted Answer · 2009-10-28 14:03:55Z

0

int numberOfDays = 8;

var data = new[] { new {DayFrom = 1, DayTo = 3, Price = 20},
              new {DayFrom = 4, DayTo = 5, Price = 30},
              new {DayFrom = 6, DayTo = 8, Price = 40},
              new {DayFrom = 8, DayTo = 25, Price = 150}};

int sum = data.SelectMany(x => Enumerable.Repeat(x, x.DayTo - x.DayFrom + 1))
    .Select(x => x.Price)
    .Take(numberOfDays)
    .Sum();

Console.WriteLine(sum);

answered Oct 28, 2009 at 14:03

Martin Jonáš

2,31715 silver badges12 bronze badges

3 Comments

mark smith Over a year ago

Hi thanks for the reply, i tried but its failing iwth this error The best overloaded method match for 'System.Linq.Enumerable.Repeat<LINQPad.User.House>(LINQPad.User.House, int)' has some invalid arguments - Argument '2': cannot convert from 'int?' to 'int'

Martin Jonáš Over a year ago

Because Enumerable.Repeat accepts second parameter of type int, not int? (syntactic sugar for Nullable<int>) which you seem to be providing. Try casting numberOfDays to int, using numberOfDays.Value or numberOfDays ?? 0.

mark smith Over a year ago

Hi .. ok thanks i got that... but now it gives the following error NotSupportedException: The query operator 'Repeat' is not supported.

Collectives™ on Stack Overflow

Help with writing a linq query

3 Answers 3

3 Comments

1 Comment

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

1 Comment

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related