Get a list of all aliases and union with name

Question

Let's say I have the following entities.

class Product
{
    public int Id { get; set; }
    public string Name { get; set; }
}

class ProductAlias
{
    public int ProductId { get; set; }
    public string Alias { get; set; }
}

Given a product ID, how could I create a list of all aliases for that product that includes the product Name itself in a single query?

I don't know if I can do something like the following. This syntax doesn't work because Union() doesn't take a lambda expression.

DbContext.Products
    .Where(p => p.Id == productId)
    .Select(p => p.Name)
    .Union(p => p.ProductAliases.Select(a => a.Alias))
    .ToList();

Answer 1

Try the following:

var products = DbContext.Products
    .Where(p => p.Id == productId);

products
    .Select(p => p.Name)
    .Union(products.SelectMany(p => p.ProductAliases.Select(a => a.Alias)))
    .ToList();

Answer 2

Join the two tables on Id and ProductId, select all the needed attribuites (Id, Name, Alias) and filter on Id.

products
.Join(aliases, p => p.Id, a => a.ProductId, (p,a) => new { p.Name, p.Id, a.Alias})
.Where(p => p.Id == productId);

In another form:

(from p in products
    join a in aliases on p.Id equals a.ProductId
    select new  
    {
        p.Name,
        p.Id,
        a.Alias
    })
.Where(p => p.Id == productId);

The result (with LINQ2Object for the test, but it should work also with LINQ2SQL):

Answer 3

You can do the equivalent of a SQL Server unpivot like this

DbContext.Products
    .Where(p => p.Id == productId)
    .SelectMany(p => p.ProductAliases.Select(a => a.Alias).Append(p.Name))
    .ToList();