LINQ Query Not Returning one value that should be there

Question

In a block of code I have a Foreach that I use to run through and count specific pieces that may or may not exist in the database. Basically for each part on an Order, I go on to count the Product Groups those belong to and then the Division those Product Groups belong to. For that I use this LINQ query:

foreach (var OrderDtl_yRow in ( from ThisOrderDtl in Db.OrderDtl
                                                    join ThisProdGrup in Db.ProdGrup on 
                    ThisOrderDtl.ProdCode equals ThisProdGrup.ProdCode
                    where
                        ThisOrderDtl.Company == Session.CompanyID &&
                        ThisOrderDtl.OrderNum == 195792
                        select new
                        {
                            ProdCode = ThisOrderDtl.ProdCode,
                            Division = ThisProdGrup.Division_c,
                            OrderNum = ThisOrderDtl.OrderNum,
                            OrderLine = ThisOrderDtl.OrderLine

                        }))
                        { ....counting things... }

Currently I've got message boxes set up to return the values to me as the process is going. I get everything to return correctly except the Division, that always shows up as blank in the MessageBoxes (So NULL I'd assume). So my Counters for Division don't Increment.

If I take that out into LINQPad I'm unsure how to return results of a foreach, but I tried it with

if(OrderDtl_yRow.Division != null && OrderDtl_yRow.Division != "")
{i++;}
i.Dump();

and got 5 (There were 5 rows I expected so I'm at least pulling our something). Then I converted it to a simpler FirstOrDefault statement to test a single value like

var OrderDtl_yRow = ( from ThisOrderDtl in OrderDtl
                                                    join ThisProdGrup in ProdGrup on 
                    ThisOrderDtl.ProdCode equals ThisProdGrup.ProdCode
                    where
                        ThisOrderDtl.OrderNum == 195792 &&
                        ThisOrderDtl.OrderLine == 1
                        select new
                            {
                            ProdCode = ThisOrderDtl.ProdCode,
                            Division = ThisProdGrup.Division_c,
                            OrderNum = ThisOrderDtl.OrderNum,
                            OrderLine = ThisOrderDtl.OrderLine
                            }).FirstOrDefault();

Then if I do a OrderDtl_yRow.Dump() I get my result and sure enough, Division comes through. So all signs point to it being fine, yet I can't bring over the value where I actually need it to show up. Thoughts? Thanks!

P.S. For those familiar with Epicor ERP Division is a UD field, so it technically belongs to the table ProdGrup_UD, but in Epicor it recognized that as the table ProdGrup just fine, its only SQL that makes you join _UD to the parent table. I tried joining it anyways for funsies and it didn't like it because it knew the column was there already. So that should be fine.

UPDATE: Rookie Move, didn't upload the Division data into the testing environment, so nothing was there, then checked against Live data where it existed and scratched my head as to why it didn't match. But I learned something about LinqPad and Linq so it wasn't a useless exercise.

Answer 1

You need to play some more in Linqpad to see what is happening, Set the language to C# program, Press F4 and add references to Server\Bin\Epicor.System.dll and Server\Assemblies\Erp.Data.910100.dll and point the app.copnfig to your Server\web.config file. In the main block create yourself a Db context with var Db = new Erp.ErpContext();

Linqpad can display complex data structures so you needn't have done FirstOrDefault in your last example. for instance:

void Main()
{
    var Db = new Erp.ErpContext();

    var sessionCompany = "EPIC06";

    var x = (from hed in Db.OrderHed
             join dtl in Db.OrderDtl
                 on new { hed.Company, hed.OrderNum }
                 equals new { dtl.Company, dtl.OrderNum }
                 into dtlList
             where 
                hed.Company == sessionCompany
             select new { hed, dtlList })

             .Dump();
}

Also note that in SQL dbo.ProdGrup is an autogenerated view that joins the tables Erp.ProdGrup and Erp.ProdGrup_UD for you.