tags:

views:

812

answers:

4

SQL:

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<GETDATE()-1
group by u.Id, u.name
+2  A: 

A solution (albeit one that defers handling of the null value to the code) could be:

DateTime yesterday = DateTime.Now.Date.AddDays(-1);

var collection=
from u in db.Universe
select new
{
u.id,
u.name,
MaxDate =(DateTime?)
(
from h in db.History
where u.Id == h.Id
&& h.dateCol < yesterday
select h.dateCol
).Max()
};

This does not produce exactly the same SQL, but does provide the same logical result. Translating "complex" SQL queries to LINQ is not allways straightforward.

vzczc
A: 

Does this actually produce a left join in the SQL? I don't know anything about linq but it looks like it should produce a subselect? How does one find this kind of thing out?

Orion Edwards
A: 

This isn't a full answer for you, but on the left join piece you can use the DefaultIfEmpty operator like so:

var collection = 
from u in db.Universe
join history in db.History on u.id = history.id into temp
from h in temp.DefaultIfEmpty()
where h.dateCol < DateTime.Now.Date.AddDays(-1)
select u.id, u.name, h.dateCol ?? '1900-01-01'

I haven't had the need to do any groupby 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 isnull in SQL.

AdamB
A: 

You're going to want to use the join into construct to create a group query.

TestContext db = new TestContext(CreateSparqlTripleStore());
var q = from a in db.Album
        join t in db.Track on a.Name equals t.AlbumName into tracks
        select new Album{Name = a.Name, Tracks = tracks};
foreach(var album in q){
    Console.WriteLine(album.Name);
    foreach (Track track in album.Tracks)
    {
        Console.WriteLine(track.Title);
    }
}
Orion Adrian