goods中的数据:
categoryId = 1, count = 4
categoryId = 3, count = 1
categoryId = 10, count = 15
db.Categories表中包含完整的类别数据。
现在需要查询db.Categories左连接goods表,要得到如下查询结果:
categoryId = 1, count = 4
categoryId = 2, count = 0
categoryId = 3, count = 1,
categoryId = 4, count = 0
……
查询语句的写法:
var categories = from category in db.Categories
join g in goods
on category.Id equals g.categoryId into categoryGoods
from c in categoryGoods.DefaultIfEmpty()
select new
{
id = category.Id,
name = category.Name,
count = c == null ? 0 : c.count
};
查询方法的写法
var categories = db.Categories
.GroupJoin(goods, c => c.Id, g => g.categoryId, (c, g) => new
{
id = c.Id,
name = c.Name,
count = !g.Any() ? 0 : g.First().count
});