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();
CodePudding user response:
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();
CodePudding user response:
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):
CodePudding user response:
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();