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

8 posts, 0 answers
  1. AJ
    AJ avatar
    33 posts
    Member since:
    Jun 2011

    Posted 08 Aug 2012 Link to this post

    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?
  2. Viktor Zhivkov
    Admin
    Viktor Zhivkov avatar
    291 posts

    Posted 13 Aug 2012 Link to this post

    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
    Follow @OpenAccessORM Twitter channel to be the first one to get the latest updates on new releases, tips and tricks and sneak peeks at our product labs!
  3. DevCraft banner
  4. Viktor Zhivkov
    Admin
    Viktor Zhivkov avatar
    291 posts

    Posted 14 Aug 2012 Link to this post

    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
    Follow @OpenAccessORM Twitter channel to be the first one to get the latest updates on new releases, tips and tricks and sneak peeks at our product labs!
  5. AJ
    AJ avatar
    33 posts
    Member since:
    Jun 2011

    Posted 14 Aug 2012 Link to this post

    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


  6. Viktor Zhivkov
    Admin
    Viktor Zhivkov avatar
    291 posts

    Posted 17 Aug 2012 Link to this post

    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
    Follow @OpenAccessORM Twitter channel to be the first one to get the latest updates on new releases, tips and tricks and sneak peeks at our product labs!
  7. Fawad
    Fawad avatar
    38 posts
    Member since:
    May 2009

    Posted 29 Oct 2012 Link to this post

    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.
  8. Viktor Zhivkov
    Admin
    Viktor Zhivkov avatar
    291 posts

    Posted 01 Nov 2012 Link to this post

    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
    Telerik OpenAccess ORM Meets ASP.NET Web API. Read more.
  9. Fawad
    Fawad avatar
    38 posts
    Member since:
    May 2009

    Posted 01 Nov 2012 Link to this post

    Hi Viktor,

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

    Regards.
Back to Top
DevCraft banner