none
LINQ outer join doesn't seem to work (C#) RRS feed

  • Question

  • I'm trying to find prices that are not on the webserver and add them from the Dynamics GP Server. But when I run this code, it appears to be trying to add records that are already in the web server database.

     Here is my code:

                 MySQLWholesaleEntities db2 = new MySQLWholesaleEntities();  //Web Server Prices
                db2.CommandTimeout = 600;
                web_galaxyEntities db1 = new web_galaxyEntities();  //GP Prices
                db1.CommandTimeout = 600;
                Console.WriteLine("Filling wholesale price table");
                var mysqlPrices = new List<webWholeSaleInventoryPrice>(db2.webWholeSaleInventoryPrices);
                Console.WriteLine("web prices count = " + mysqlPrices.Count().ToString());
                Console.WriteLine("filling GP price tables");
                var GpPrices = new List<gvaWholeSaleInventoryPrice>(db1.gvaWholeSaleInventoryPrices);
                Console.WriteLine("GP prices count = " + GpPrices.Count().ToString());
                //add prices
                var addquery = from gp_price in GpPrices
                            join  web_price in mysqlPrices
                            on new { gp_price.ITEMNMBR, gp_price.PRCLEVEL } equals new { web_price.ITEMNMBR, web_price.PRCLEVEL } into outer
                            from o in outer.DefaultIfEmpty()
                            where o == null
                            select new
                            {
                               gp_itemnmbr = gp_price.ITEMNMBR,
                                gp_price = gp_price.PRICE,
                                gp_prclevel = gp_price.PRCLEVEL
                            };
                Console.WriteLine("outer count = " + addquery.Count().ToString());
                
                foreach (var o in addquery)
                {
                    var wp = new webWholeSaleInventoryPrice();
                    wp.ITEMNMBR = o.gp_itemnmbr;
                    wp.MODIFDT = DateTime.Now;
                    wp.PRCLEVEL = o.gp_prclevel;
                    wp.PRICE = o.gp_price;
                    db2.webWholeSaleInventoryPrices.AddObject(wp);
                }

     And the output:

    Filling wholesale price table|
    web prices count = 3535519
    filling GP price tables
    GP prices count = 3553415
    outer count = 3231261

     

    The outer count should be 17,896 I believe...I don't know where it is coming up with 3,231,261 records.  I tried to find a group for LINQ, but I could not, so I'm going to try and post this here. 
    Thanks very much!

    Scott Emick


    Scott Emick
    Thursday, August 11, 2011 2:02 PM

All replies

  • Hi Scott;

    The query looks correct to me and should have worked. So my question is what are the data types of ITEMNMBR and PRCLEVEL? Is any of the two a string data type? The reason I am asking is that if the case is different between the two databases on a column being compared you would be getting this type of results. For example your join clause if ITEMNMBR in one database is for example abc123 and in the other ABC123 then the join will assume them to be different and so will show up in your result list.

     


    Fernando (MCSD)

    If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".
    Sunday, August 14, 2011 7:43 PM
  • The two fields that are being joined are varchar and the same length and case, but I wonder if any spaces might cause an issue.  I'm going to check that out and see if that is the issue.  Thanks for reply.
    Scott Emick
    Monday, August 15, 2011 12:53 PM
  • Hi Scott;

    If the spaces are not in the same position and or the number of spaces are not the same then this too will cause this issue.

    Please let me know the outcome.

     


    Fernando (MCSD)

    If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".
    Monday, August 15, 2011 1:32 PM
  • Any udpate? Would you mind letting us know how it goes now?

    Thanks,


    Larcolais Gong[MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Friday, August 19, 2011 4:10 AM