<p>SQL:</p>

<pre><code>SELECT
   u.id,
   u.name,
   isnull(MAX(h.dateCol), '1900-01-01') dateColWithDefault
FROM universe u
LEFT JOIN history h 
   ON u.id=h.id 
   AND h.dateCol&lt;GETDATE()-1
GROUP BY u.Id, u.name
</code></pre>

[{'answer_id': 837, 'author': 'vzczc', 'author_id': 224, 'author_profile': 'https://Stackoverflow.com/users/224', 'pm_score': 4, 'selected': True, 'text': '<p>A solution, albeit one that defers handling of the null value to the code, could be:</p>\n\n<blockquote>\n  <p>DateTime yesterday = DateTime.Now.Date.AddDays(-1);</p>\n</blockquote>\n\n<pre><code>var collection=\n    from u in db.Universe\n    select new\n    {\n        u.id,\n        u.name,\n        MaxDate =(DateTime?)\n       (\n           from h in db.History\n           where u.Id == h.Id\n           &amp;&amp; h.dateCol &lt; yesterday\n           select h.dateCol \n       ).Max()\n    };\n</code></pre>\n\n<p>This does not produce exactly the same SQL, but does provide the same logical result. Translating "complex" SQL queries to LINQ is not always straightforward.</p>\n'}, {'answer_id': 33140, 'author': 'AdamB', 'author_id': 2176, 'author_profile': 'https://Stackoverflow.com/users/2176', 'pm_score': 0, 'selected': False, 'text': "<p>This isn't a full answer for you, but on the left join piece you can use the DefaultIfEmpty operator like so:</p>\n\n<pre><code>var collection = \nfrom u in db.Universe\njoin history in db.History on u.id = history.id into temp\nfrom h in temp.DefaultIfEmpty()\nwhere h.dateCol &lt; DateTime.Now.Date.AddDays(-1)\nselect u.id, u.name, h.dateCol ?? '1900-01-01'\n</code></pre>\n\n<p>I haven't had the need to do any <code>groupby</code> commands yet, so I left that out as to not send you down the wrong path.  Two other quick things to note.  I have been unable to actually join on two parameters although as above there are ways to get around it.  Also, the ?? operator works really well in place of the <code>isnull</code> in SQL.  </p>\n"}, {'answer_id': 80296, 'author': 'Orion Adrian', 'author_id': 7756, 'author_profile': 'https://Stackoverflow.com/users/7756', 'pm_score': 0, 'selected': False, 'text': "<p>You're going to want to use the <code>join into</code> construct to create a group query.</p>\n\n<pre><code>TestContext db = new TestContext(CreateSparqlTripleStore());\nvar q = from a in db.Album\n        join t in db.Track on a.Name equals t.AlbumName into tracks\n        select new Album{Name = a.Name, Tracks = tracks};\nforeach(var album in q){\n    Console.WriteLine(album.Name);\n    foreach (Track track in album.Tracks)\n    {\n        Console.WriteLine(track.Title);\n    }\n}\n</code></pre>\n"}, {'answer_id': 36693733, 'author': 'Community', 'author_id': -1, 'author_profile': 'https://Stackoverflow.com/users/-1', 'pm_score': 1, 'selected': False, 'text': '<pre><code>var collection=\n    from u in db.Universe\n    select new\n    {\n        u.id,\n        u.name,\n        MaxDate =(DateTime?)\n       (\n           from h in db.History\n           where u.Id == h.Id\n           &amp;&amp; h.dateCol &lt; yesterday\n           select h.dateCol \n       ).Max()\n    };\n</code></pre>\n\n<p>Just youse the above code and this should work fine!</p>\n'}]
['https://Stackoverflow.com/questions/832', 'https://Stackoverflow.com', 'https://Stackoverflow.com/users/224/']