How to re-write this inner join subquery from SQL to Lambda?

I think this does what you want: var result = userLogin. UserLoginClientConnection GroupBy(y => new { Id = y. UserLogin Day = y.CreatedAt.

Date }) . Select(x => new { Id = x.Key. Id, Day = x.Key.

Day, MostRecent = x. Max(y => y. CreatedAt) }) Here is a testbed for it: public class Program { class LoginEntry { public int UserLogin } public DateTime CreatedAt { get; set; } } class UserLogin { public List UserLoginClientConnection } public static void Main(string args) { UserLogin userLogin = new UserLogin(); userLogin.

UserLoginClientConnectionstories = new List { new LoginEntry {UserLogin CreatedAt = new DateTime(2009, 1, 1, 3, 0 ,0)}, new LoginEntry {UserLogin CreatedAt = new DateTime(2009, 1, 1, 15, 0 ,0)}, new LoginEntry {UserLogin CreatedAt = new DateTime(2009, 1, 3, 11, 0 ,0)}, new LoginEntry {UserLogin CreatedAt = new DateTime(2009, 1, 1, 10, 0 ,0)}, new LoginEntry {UserLogin CreatedAt = new DateTime(2009, 1, 3, 4, 0 ,0)}, new LoginEntry {UserLogin CreatedAt = new DateTime(2009, 1, 3, 5, 0 ,0)}, }; var result = userLogin. UserLoginClientConnectionGroupBy(y => new { Id = y. UserLogin Day = y.CreatedAt.

Date }) . Select(x => new { Id = x.Key. Id, Day = x.Key.

Day, MostRecent = x. Max(y => y. CreatedAt) }); foreach (var item in result) { Console.

WriteLine("User {0}, day {1}, most recent {2}", item. Id, item. Day, item.

MostRecent); } } } Output: User 1, day 01-01-2009 00:00:00, most recent 01-01-2009 15:00:00 User 1, day 03-01-2009 00:00:00, most recent 03-01-2009 11:00:00 User 2, day 03-01-2009 00:00:00, most recent 03-01-2009 05:00:00.

I think this does what you want: var result = userLogin. UserLoginClientConnection GroupBy(y => new { Id = y. UserLogin Day = y.CreatedAt.

Date }) . Select(x => new { Id = x.Key. Id, Day = x.Key.

Day, MostRecent = x. Max(y => y. CreatedAt) }); Here is a testbed for it: public class Program { class LoginEntry { public int UserLogin } public DateTime CreatedAt { get; set; } } class UserLogin { public List UserLoginClientConnection } public static void Main(string args) { UserLogin userLogin = new UserLogin(); userLogin.

UserLoginClientConnectionstories = new List { new LoginEntry {UserLogin CreatedAt = new DateTime(2009, 1, 1, 3, 0 ,0)}, new LoginEntry {UserLogin CreatedAt = new DateTime(2009, 1, 1, 15, 0 ,0)}, new LoginEntry {UserLogin CreatedAt = new DateTime(2009, 1, 3, 11, 0 ,0)}, new LoginEntry {UserLogin CreatedAt = new DateTime(2009, 1, 1, 10, 0 ,0)}, new LoginEntry {UserLogin CreatedAt = new DateTime(2009, 1, 3, 4, 0 ,0)}, new LoginEntry {UserLogin CreatedAt = new DateTime(2009, 1, 3, 5, 0 ,0)}, }; var result = userLogin. UserLoginClientConnectionGroupBy(y => new { Id = y. UserLogin Day = y.CreatedAt.

Date }) . Select(x => new { Id = x.Key. Id, Day = x.Key.

Day, MostRecent = x. Max(y => y. CreatedAt) }); foreach (var item in result) { Console.

WriteLine("User {0}, day {1}, most recent {2}", item. Id, item. Day, item.

MostRecent); } } } Output: User 1, day 01-01-2009 00:00:00, most recent 01-01-2009 15:00:00 User 1, day 03-01-2009 00:00:00, most recent 03-01-2009 11:00:00 User 2, day 03-01-2009 00:00:00, most recent 03-01-2009 05:00:00.

Here is the inner join portion as a lambda. I assumed CreatedAt was a dateTime. UserLoginClientConnectionGroupBy (ulcch1 => new { Name = ulcch1.Name }) .

Select (g => new { maxCreatedAt = (DateTime? )(g. Max (p => p.

CreatedAt)) }).

I think you want to group by CreatedAt rather than UserLoginstoryID: var q = userLogin. UserLoginClientConnection GroupBy(h => h. CreatedAt) .

OrderByDescending(g => g. Key) // Sort by CreatedAt .First() . Select(h => new { h.Id, h.

UserLogin ... }); This will return the set of UserLoginClientConnectionstory entries that share the most recent CreatedAt value.

Thanks for all your help guys, i've voted you all up, but you wouldn't believe it but a few hours later I searched for a program to convert SQL to LINQ, and to my surprise found one called "Linqer". Sounds crazy and didn't expect to get far, but it worked perfectly.. definitely worth checking out that app if anyone else gets stuck in the same boat... Check the mammoth query it returned! After analysing it, don't think it's got extra bloat?

Anyone have any optimisation tips or spot any unnecessary code? ModuleDeviceStates = from ulh in user. UserLoginstories join ulcch in userLogin.

UserLoginClientConnectionstories on new { ID = ulh. ID } equals new { ID = ulcch. UserLoginstoryID } join cm in clientModuleRepository.

GetAll(GenericStatus. Active) on new { ClientModuleID = ulcch. ClientModuleID } equals new { ClientModuleID = cm.ID } join mo in moduleRepository.

GetAll(GenericStatus. Active) on new { ModuleID = cm. ModuleID } equals new { ModuleID = mo.

ID } join m in ( (from ulcch1 in userLogin. UserLoginClientConnectionstories group ulcch1 by new { ulcch1. UserLoginstoryID } into g select new { maxCreatedAt = g.

Max(p => p. CreatedAt) })) on new { maxCreatedAt = ulcch. CreatedAt } equals new { maxCreatedAt = m.

MaxCreatedAt } select new ModuleDeviceState() { ModuleID = mo. ID, Name = mo.Name, DeviceState = (State. DeviceState)ulcch.

DeviceState, CreatedAt = ulcch. CreatedAt }; Cheers for your help dahlbyk, but I did want to group on UserLogin I had my query confirmed in SQL before delving into a lambda equivalent :) thanks. @Mark Thanks for taking the time to reply, yes I do what the last entries per user (userloginhistory.. which in turn contains a userID) for each day, and exporting my sql into the linq query did produce what I wanted (which can be seen in the query result below; this is what I want.

The reason you see double entries for each day is because there are also attached ClientModule's.. so I really want all client module, per login entry per day - so hard to get a programming requirement across over a discussion forum argh!) Perhaps yours does exactly the same thing (it appears to if I am reading your output correctly) just a lot more streamlined. See I didn't know too much about the anon casting you've done there with GroupBy and Select, but now I see it, it makes sense. I might give yours a go.

Hopefully I can give it a tweak to include distinct ClientModule's per day too. So anyway.. here is the query result from my SQL, and effectively what I got through my own lambda: ID UserLoginstoryID StatusID ClientModuleID DeviceState UpdatedAt CreatedAt 277 62 1 1 4 NULL 2009-10-31 13:28:59.003 278 62 1 16 4 NULL 2009-10-31 13:28:59.003 331 65 1 1 4 NULL 2009-10-31 17:13:28.333 332 65 1 16 4 NULL 2009-10-31 17:13:28.333 Update Mark: well after a couple of tweaks on your query, I could produce the same object graph in . NET between both lambda statements.

This is the one I will use now, derived from yours as it's more streamlined and easier to understand than the auto-gen'd one and I will award you the points :) I added a few more entries to the Group By as I need that for my new ModuleDeviceState class. ModuleDeviceStates = userLogin. UserLoginClientConnectionGroupBy(y => new { Id = y.

UserLogin CreatedAt = y.CreatedAt. Date, ModuleID = y.ClientModule. ModuleID, ModuleName = y.ClientModule.Module.Name, DeviceState = y.

DeviceState }) . Select(x => new ModuleDeviceState() { ModuleID = x.Key. ModuleID, Name = x.Key.

ModuleName, DeviceState = (State. DeviceState)x.Key. DeviceState, CreatedAt = x.

Max(y => y. CreatedAt) }).

In your question you stated that you wanted "the last unique entry per user for each day. " But if I am reading this correctly, this query does not return a record for each day - only an overall maximum per user. What am I missing?

I have posted an answer that I think does what your question asks for. – Mark Byers Dec 5 '09 at 9:40.

I cant really gove you an answer,but what I can give you is a way to a solution, that is you have to find the anglde that you relate to or peaks your interest. A good paper is one that people get drawn into because it reaches them ln some way.As for me WW11 to me, I think of the holocaust and the effect it had on the survivors, their families and those who stood by and did nothing until it was too late.