none
Linq to DataSet outer join question... RRS feed

  • Question

  •  

    var query = from fs in DSR.Tables["FuelSales"].AsEnumerable()
    join fsdept in DSR.Tables["Department"].AsEnumerable()
    on fs.Field<Int32?>("FuelSales_ID") equals fsdept.Field<Int32?>("FuelSales_ID")
    join fscat in DSR.Tables["Category"].AsEnumerable()
    on fsdept.Field<Int32>("Department_ID") equals fscat.Field<Int32>("Department_ID")
    join fsfitm in DSR.Tables["FuelItem"].AsEnumerable()
    on fscat.Field<Int32>("category_ID") equals fsfitm.Field<Int32>("category_ID") into fsitmTbl
    from fsit in fsitmTbl
    join cot in DSR.Tables["commodities"].AsEnumerable()
    on fsit.Field<String>("extID") equals cot.Field<String>("productid") into resTbl
    from lpq in resTbl

    select new
    {
    fuelExtID = lpq.Field<
    String>("extID"),
    retail = lpq.Field<
    Decimal>("retail"),
    retailAmt = lpq.Field<
    Decimal>("retailAmt"),
    closingPumpGal = lpq.Field<
    Decimal>("closingPumpGal"),
    closingPumpRetail = lpq.Field<Decimal>("ClosingPumpRetail"),
    commid = lpq.Field<String>("commodityid")?? " "

    };

     

    foreach (var x in query){
    Console.WriteLine("extID: {0} retail: {1} retailAmt: {2} closingPumpGal: {3} closingPumpRetail {4}",
    x.fuelExtID, x.retail, x.retailAmt, x.closingPumpGal, x.closingPumpRetail);
    }

    The above query is from a program which compiiles fine but when running it errors out with the following message:
    Unhandled Exception: System.ArgumentException: Column 'extID' does not belong to table commodities. However as one can see the "extID" column is from 'fsit" which gets this from "commodities"... Can someone help me to solve this??
    Thanks
    Sankar

      
    Thursday, June 9, 2011 3:51 PM

Answers

  • the line #s reported in at "foreach" and followed by "select new"

    I have just now fixed this problem by replacing the last line in the 'select new' section based on an example in the MSDN library article http://msdn.microsoft.com/en-us/library/bb397895.aspx

    commid = (lpq ==

    null) ? String.Empty : lpq.Field<String>("commodityid")

    However I am still curious as to how the first weird error message occurred 'extID' not in table "commoditied"?

    Thanks
    Sankar

    Thursday, June 9, 2011 5:51 PM

All replies

  • Change from lpq in resTbl  TO  from lpq in resTbl.DefaultIfEmpty()
    Thursday, June 9, 2011 4:31 PM
  • That change causes "System.ArgumentNullException: Value cannot be null" error..
    What happens with the "into " clause? what goes into the variable after the "into"?
    Thanks
    Sankar

    Thursday, June 9, 2011 4:51 PM
  • You also need DefaultIfEmpty() on the line from fsit in fsitmTbl if that is an outer join too.

     

    Where is the exception being thrown in the query or the foreach when you try and access the enumeration?

    Thursday, June 9, 2011 5:06 PM
  • the line #s reported in at "foreach" and followed by "select new"

    I have just now fixed this problem by replacing the last line in the 'select new' section based on an example in the MSDN library article http://msdn.microsoft.com/en-us/library/bb397895.aspx

    commid = (lpq ==

    null) ? String.Empty : lpq.Field<String>("commodityid")

    However I am still curious as to how the first weird error message occurred 'extID' not in table "commoditied"?

    Thanks
    Sankar

    Thursday, June 9, 2011 5:51 PM