Linq Join - Duplicates

.net, asp.net, c#, sql

Solution

This should do exactly what you want.

from u in Users
let p = Photos.Where(p => p.Username == u.Username).FirstOrDefault()
where p <> null
select new
{
    u.Username,
    p.ID
};

However, it is worth noting that you may be better off writing hand optimized SQL and retrieving the objects using `db.ExecuteQuery<User>` or similar.

Problem

I have Two tables. 1.Users table (Username , Name) 2.Picture table( ID , Username , IsPrimary) Each user can have zero to many pictures. I'm Trying to write a query that will return all users (with or without pictures) and a single picture Id (of the picture with IsPrimary = true). I wrote this Linq query : ``` var v = from u in Users join p in Photos on u.Username equals p.Username select new { u.Username, p.ID }; ``` This works but returns duplicate user rows. (if user has more than one photo). I want to get one row per user. Is that possible?

Original source