Linq Join - Duplicates

Question

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?

Answer 1

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.

Answer 2

You could use grouping:

from p in photos
group p by p.username into g
select new
    {
     Username = g.Key,
     PicId = g.First().Id
    };

Instead of First() you could do some other filtering...

Answer 3

You should put IsPrimary in a where condition. Plus, since a user can have zero picture, you need a left join.

EDIT: exmaple is following, (may have typo)

from u in Users
join p in (from p1 in Pictures where p1.IsPrimary select p1) on u.Username equals p.Username into pp
from p in pp.DefaultIfEmpty()
select new
{
  u.UserName,
  PicturePath = p == null ? "DummyPath" : p.PicturePath
}

EDIT: By the way, John Gietzen answer seems does not give the answer you were asking.

Answer 4

var upic = from u in users
           join p in pictures on u.UserName equals p.UserName into g
           from o in g.DefaultIfEmpty()
          where o == null || o.IsPrimary == true
         select new { UserName = u.UserName, Id = ( o == null ? "(No picture)" : o.Id ) };