I have code like this,

IQueryable<WinnerClassExtend> dataList = 
from Class in _context.AllClass
join Winner in _context.AllWinners on Class.ClassId equals Winner.ClassId

let Extend = new { Winner, Class }
group Extend by Class.ClassId into Group

select new WinnerClassExtend
{
    Class = Group.Select(x => x.Class).FirstOrDefault(),
    NumberOfWinnerinClass = Group.Select(x => x.Winner).Count()
};

var count = dataList.Count();

I try to get the count of IQuerable in just like what I did in .net framework and entity framework, but seems not working... And return with error "Nullable object must have a value."

I have no idea what's wrong with it, and I also try using this,

IQueryable<WinnerClassExtend> dataList =
     _context.AllClass
    .Join(_context.AllWinners , Class => Class.ClassId, Winner => Winner.ClassId, (Class, Winner) => new { Class, Winner })
    .GroupBy(x => new { x.Class.ClassId })
    .Select(x => new WinnerClassExtend
    {
        Class = x.Select(i => i.Class).FirstOrDefault(),
        NumberOfWinnerinClass = x.Select(i => i.Winner).Count()
    });

var count = dataList.Count();

And it returns the same...

I have read some issue of this error, but it seems unlike the same problem, Can anyone help?

CodePudding user response：

It seems to me that you need a left outer join:

var dataList =
    from c in _context.AllClass
    join w in _context.AllWinners on c.ClassId equals w.ClassId into winners
    select new WinnerClassExtend
    {
        Class = c,
        NumberOfWinnerinClass = winners.Count()
    };

var count = dataList.Count();

CodePudding user response：

Try the following query:

var dataList = 
    from Class in _context.AllClass
    select new WinnerClassExtend
    {
        Class = Class,
        NumberOfWinnerinClass = _context.AllWinners.Where(w => w.ClassId == Class.ClassId).Count()
    };

var count = dataList.Count();

Or another one:

var grouped = 
    from Winner in _context.AllWinners
    group Winner by Class.ClassId into Group
    select new 
    {
        ClassId = Group.Key,
        NumberOfWinnerinClass = Group.Count()
    };

var dataList = 
    from Class in _context.AllClass
    join g in grouped on ClassId.ClassId equals g.ClassId
    select new WinnerClassExtend
    {
        Class = Class,
        NumberOfWinnerinClass = g.NumberOfWinnerinClass
    };

var count = dataList.Count();

In both cases compare execution plan.