none
LINQ Query Question. RRS feed

  • Question

  • Hello,

    I am reading an xml file into a DataSet. This Creates a number of DataTables against which I am running a LINQ query. I am using the following query. In the final Join statement I would like to use an AND clause to constrain the query.

     

    The AccountData Table has the following colunms

    AttributeName AttributeValue ItemDetail_ID


    There are several AttributeNames for each ItemDetail_ID, In other words there is a row foreach AttributeName. In the query below this line:

    join _itemCustomerNumber in (AccountData.AsEnumerable()) on _itemOut.Field<Int32>("ItemOut_ID") equals _itemCustomerNumber.Field<int>("ItemDetail_ID")

    will return all the rows that have the correct ItemOut_ID. This is actually multiple rows.  I only want the row has the AttributeName equal to "CustomerNumber"

    somthing like:


    on _itemOut.Field<Int32>("ItemOut_ID") equals _itemCustomerNumber.Field<int>("ItemDetail_ID") AND _itemCustomerNumber.Field<String>("AttributeName") = "CustomerNumber"


    I know this syntax is wrong. Can anyone help me with the correct syntax?

    Thanks

     

     

                   var itemsQuery = (from _itemOut in ItemOut.AsEnumerable()

                                      join _itemID in (ItemID.AsEnumerable()) on _itemOut.Field<Int32>("ItemOut_ID") equals _itemID.Field<Int32>("ItemOut_ID")
                                      join _itemDetail in (ItemDetail.AsEnumerable()) on _itemOut.Field<Int32>("ItemOut_ID") equals _itemDetail.Field<Int32>("ItemOut_ID")
                                      join _itemDescription in (Description.AsEnumerable()) on _itemOut.Field<Int32>("ItemOut_ID") equals _itemDescription.Field<Int32>("ItemDetail_ID")
                                      join _itemCustomerNumber in (AccountData.AsEnumerable()) on _itemOut.Field<Int32>("ItemOut_ID") equals _itemCustomerNumber.Field<int>("ItemDetail_ID")

                                      select new
                                      {
                                          _quantity = Convert.ToInt32(Convert.ToDouble(_itemOut.Field<string>("quantity"))),
                                          _productCode = _itemID.Field<string>("SupplierPartID"),
                                          _Description = _itemDescription.Field<string>("Description_Text"),
                                          _soldBy = _itemDetail.Field<string>("UnitOfMeasure"),
                                          _customerNumber = _itemCustomerNumber.Field<string>("Extrinsic_Text")
                                      });

     


                    foreach (var dr in itemsQuery)
                    {

      }

    Monday, October 24, 2011 6:44 PM

Answers

All replies