Linq Join - Duplicates

Question

I have two tables.

Table 1.Users (Username, Name)

2. Table of pictures (identifier, username, IsPrimary)

Each user can have from zero to many images.

I am trying to write a query that will return to all users (with or without pictures) and one image identifier (images 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 strings. (if the user has more than one photo).

I want to get one row for each user. Is it possible?

Answer 1

, .

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

, SQL db.ExecuteQuery<User> .

Answer 2

:

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

First() ...

Answer 3

IsPrimary . , , .

EDIT: exmaple ( )

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
}

: , , , , .

Answer 4

var upic =            join p    u.UserName  p.UserName  g           o  g.DefaultIfEmpty()          o == null || o.IsPrimary == true          {UserName = u.UserName, Id = (o == null? "( )": o.Id)};