How do I add default null values in a Linq join statement

Question

I have two IEnumerables. One contains dates, the other contains data.

DateTime start = DateTime.Today.AddDays(-21);

var dates = Enumerable.Range(0, 21).Select(n => start.AddDays(n)).ToArray();

var data = MyClass.Data.Select(x => new { Date = x.Date, Views = x.Count });

I'm trying to build a table which shows the Views on a given day. However, data contains some gaps. How do I write a linq query which joins the two sets, and returns the Views number when present or 0 when there is no matching object in data?

I can do this the old fashioned way with foreach statements but I'd like to know how to do it in Linq.

Do you have an example output that you would like to achieve? — LukeHennerley, May 02 '13 at 08:05
@CodeIgnoto the question is answered now, but I meant that data would not contain a corresponding value for all of the dates generated, as on some days there would be no views. — roryok, May 02 '13 at 10:12

score 1 · Answer 1 · answered May 02 '13 at 08:22

This should work:

var data = from day in Enumerable.Range(0, 21).Select(n => start.AddDays(n))
           join d in MyClass.Data.Select(x => new { Date = x.Date, Views = x.Count })
           on day equals d.Date into gj
           from dd in gj.DefaultIfEmpty()
           select new { Date = day, Views = dd == null ? 0 : dd.Views };

This returns the views-number when there is one at the given day and 0 otherwise.

How to: Perform Left Outer Joins

Range is indeed the way to go. – Serge May 02 '13 at 08:43 — Serge, May 02 '13 at 08:43

Prabhu Murthy · Accepted Answer · 2013-05-02T09:02:07.230

1

Not sure i fully understood your question.If you want to generate a list of days which has at least one views,then this will get the job done.

DateTime start = DateTime.Today.AddDays(-21);

//Sample view data
var viewsData = new[] {new {id = "id", date = new DateTime(2013, 4, 12), views = 25}};

var dates = Enumerable.Range(0, 21)
                        .Select(d => start.AddDays(d))
                        .Select(n => new
                                        {
                                         Day = n,
                                         views =viewsData.Any(x => x.date == n)
                                         ? viewsData.FirstOrDefault(v => v.date == n).views
                                         : 0
                         });

Zero is populated for days having no views

edited May 02 '13 at 09:02

answered May 02 '13 at 08:54

Prabhu Murthy

9,031
5
29
36

cool, didn't realise I could use a conditional operator within a new statement like that. – roryok May 02 '13 at 10:11
Very inefficient. For every date it loops the whole collection to determine if it exists, then it loops it again to find the element. A `Join` is much [more efficient](http://stackoverflow.com/questions/5551264/why-is-linq-join-so-much-faster-than-linking-with-where). – Tim Schmelter May 02 '13 at 12:14

score 0 · Answer 3 · answered May 02 '13 at 08:05

0

Have a look at this MSDN article: http://msdn.microsoft.com/en-us/library/vstudio/bb397895.aspx

It explains how to do a left outer join which is what you need in this case.

answered May 02 '13 at 08:05

Davio

4,609
2
31
58

How do I add default null values in a Linq join statement

3 Answers3

Linked