Linq Query with Outer Join & Group By return "Object reference not set to an instance of an object." but works perfectly in LINQPad

[AJ]

This has been driving me crazy and I now believe this is most likely a bug as I've never ran across it before.

The table ConsignmentAgreements has a field CANumber which is never null and is not a primary key. The second table, Orders, has a field PONumber which does allow null fields and is also not a primary key. I want to create a basic join that lists all the ConsignmentAgreements and the number of orders that have the CANumber as the PONumber. Very simple stuff. Wrong.

Here is my code:

var cas = (from ca in _ctx.ConsignmentAgreements
            join o in _ctx.Orders on ca.CANumber equals o.PONumber into j1
            from loj in j1.DefaultIfEmpty()
            group loj by new
                        {
                            ca.ConsignmentAgreementID,
                            ca.CANumber,
                            Location = ca.Location.Name,
                            ca.Descr
                        } into cag
            select new {
                            ConsignmentAgreementID = cag.Key.ConsignmentAgreementID,
                            CANumber = cag.Key.CANumber,
                            Location = cag.Key.Location,
                            Descr = cag.Key.Descr,
                            NumberOfOrders = cag != null ? cag.Count() : 0
                        }).ToList();

 Running that code gives me a "Object reference not set to an instance of an object." error.  Meanwhile, running this exact same query using LINQPad it immediately gives me the desired result.

However, if I code to:

var cas = (from ca in _ctx.ConsignmentAgreements.ToList()
            join o in _ctx.Orders.ToList() on ca.CANumber equals o.PONumber into j1
            from loj in j1.DefaultIfEmpty()
            group loj by new
                        {
                            ca.ConsignmentAgreementID,
                            ca.CANumber,
                            Location = ca.Location.Name,
                            ca.Descr
                        } into cag
            select new
            {
                ConsignmentAgreementID = cag.Key.ConsignmentAgreementID,
                CANumber = cag.Key.CANumber,
                Location = cag.Key.Location,
                Descr = cag.Key.Descr,
                NumberOfOrders = cag != null ? cag.Count() : 0
            }).ToList();

It works, but seems unnecessary. 

Can someone explain that one to me?

[Viktor Zhivkov]

Hi Andrew,

The query that you have posted contains an outer join between ConsignmentAgreements and Orders that OpenAccess has hard time to interpret correctly. We will look into this issue and do our best to fix it, but it will take some time.
We will investigate further how you can work around this limitation and I will post our finding to you tomorrow.
Please excuse us for the delay and the inconvenience caused.

As a reward for finding this issue I have added Telerik bonus points to you account.

All the best,
Viktor Zhivkov
the Telerik team

[Viktor Zhivkov]

Hi Andrew,

We have been trying several different approaches to work around this issue.
So far the one that you already have is the best candidate - in order to avoid the exception we have to do the outer join in memory.

If it happens that you do not require outer join, things could get easier. Please tell us a bit more about what are your intentions in this query and if there are any other important details (like Where filters or specific data model design).

If the in-memory work around is not appropriate for your solution please let us know so we can continue with the investigation.

Kind regards,
Viktor Zhivkov
the Telerik team

[AJ]

I do require an outer join, as some Consignment Agreements may not have any orders. Since this is used on a back-end application, it won't get too much action... so the work around that I posted previously would work. The other alternative solution that I found (and am currently using) is:

var cas = _ctx.ConsignmentAgreements
    .Select(x=>new
                    {
                        x.ConsignmentAgreementID,
                        x.CANumber,
                        Location = x.Location.Name,
                        x.Descr,
                        NumberOfOrders = _ctx.Orders.Count(o=>o.PONumber == x.CANumber)
                    })
    .OrderBy(x => x.Location).ThenBy(x => x.CANumber)
    .ToList();

Which gets the job done as well, although I'm not sure of the performance differences. I figured that if I needed to get "more" from the Orders table (besides just a count of records) the first method would be a better bet. Any insight you have would be great.

Either way, I really appreciate you guys taking time to try and figure this oddity out. Thank you for the great support!

 - Andrew

[Viktor Zhivkov]

Hello Andrew,

We have found the root of the problem and we have been able to do a fix that enables queries like yours to be handled properly.
Unfortunately the change of code has potential to break a lot of things and we will need some time to test it extensively for unwanted side effects.
We will ship this bug fix in the official 2012 Q3 release in the middle of October. 
Meanwhile you will have to use your work around code that you have posted.

Below is the query that we have executed successfully in our test environment, that should be very similar to yours:

01.public void OuterJoinedGroupedTest()
02.{
03.    // using Northwind data model
04.    var q = from o in dataContext.Orders
05.            join od in dataContext.OrderDetails on o.Id equals od.OrderID into g
06.            from j in g.DefaultIfEmpty()
07.            group j by new { o.ShipCity, o.ShipCountry, Company = o.Customer.CompanyName } into s
08.            select new { City = s.Key.ShipCity, Country = s.Key.ShipCountry, Company = s.Key.Company, Number = s != null ? s.Count() : 0 };
09.    var r = q.ToList();
10.
11.    //Asserts
12.}

If you have any other outer join or group join queries that cause you any trouble, please send them to us as soon as possible so we can analyze their behaviour and apply any fix needed before the release.

Greetings,
Viktor Zhivkov
the Telerik team

[Fawad]

Similar issue here.
This query  :

var lstSALockedDetails = (from cores in Database.Cores
join cls in Database.Clients
on cores.ClientID equals cls.ClientID
join staff in Database.Staffs on cores.StaffID equals staff.StaffID
where (staff.ASPRole == ASPRole.CC || staff.ASPRole == ASPRole.SE)
     && (cls.Type == ClientType.Client
    || cls.Type == ClientType.Lead
    || cls.Type == ClientType.HotLead)
&& cores.PanelType != PanelType.Prospect
orderby cores.Sequence descending
group new { cores, staff, cls } by new { cores.ClientID, cores.StaffID }
into grp
select new
{
 ClientId =  grp.Key.ClientID,
 LatestActionDate =  grp.Max(t => t.cores.ActionDate),
 FullName = grp.Select(name => name.staff.FullName).First(),
 LockerId =  grp.Select(name => name.staff.StaffID).First(),
 CompanyName = grp.Select(c => c.clients.CompanyName).First(),
 ClientType = grp.Select(c => c.clients.Type).First()
})
.Where(dr => dr.ClientId != null && dr.LatestActionDate.Date <= DateTime.Now.Date.AddDays(-2)).ToList();

Works perfectly in LinqPad but throws Null Reference Exception in ORM.

Bit frustrating.

Regards.

[Viktor Zhivkov]

Hello Fawad,

We will have to look deeper in order to find the cause of the failing query.
Please give us a few days to respond. I will contact you back with a work around (if there is any) or any details that we can share.

Thank you for your feedback and please excuse us for the inconvenience.

Greetings,
Viktor Zhivkov
the Telerik team

[Fawad]

Hi Viktor,

Don't worry, just updated and the latest release has fixed this problem, probably something in older release was causing it.

Regards.