none
add columns to query of distinct records. RRS feed

  • Question

  • Hi,

    I am new to LINQToExcel, and using it to query a excel file to get disctinct record of one column.  I need to append empty columns andd then dispaly data in datagrid.  The empty columns can be used for user to enter data.  How do I do this?  Thanks very much for your help.

    Hugh

    Below is my code:

    public class Sample

    {

    public string Name { get; set; } }

    var excel = new ExcelQueryFactory() { FileName = TextBoxFilePath.Text };

    var sample = (from x in excel.Worksheet<Sample>("SampleFile")

    where x.Name != null

    select x.Name).Distinct();

    gridControl1.DataSource = sample.ToList();


    hugh

    Thursday, September 6, 2012 8:21 PM

Answers

  • Hi Fernando,

    I am sorry for late reply since I was away from the office for a few days.  I tried your code and it did not work.  The massage was the same "LingToExcel does not support distinct with more than one property is selected...." 

    I worked out a solution by querying distinct record and then convert it to a array and then add more columns using conventional LINQ.   Thanks for your help.

    Hugh


    hugh

    Wednesday, September 12, 2012 2:24 PM

All replies

  • Hi Hugh;

    I have modified the query to create a class called NameDTO but you can rename it to what every you like. It will create one of the class for each value being returned. the property called Data is the empty value to be placed into the data grid view. This should give you what you want.

    // The query has been modified in the select clause to hold a blank column
    // for user data.
    var sample = (from x in excel.Worksheet<Sample>("SampleFile")
                  where x.Name != null
                  select new NameDTO
                  {
                      Name = x.Name,
                      Data = String.Empty
                  }).Distinct();
    
    gridControl1.DataSource = sample.ToList();
    
    
    // The Linq query will automatically create one of these 
    // for each Name and Data to be returned.
    public class NameDTO
    {
        public string Name { get; set; }
        public string Data { get; set; }
    }

      


    Fernando (MCSD)

    If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".

    Friday, September 7, 2012 2:52 AM
  • Hi Fernando,

    Thanks very much for your help.  I tried similar thing before and it did not work since it is LinqToExcel. The Message is "LinqToExcel only provides support for the Disctinct(0 method when it's mapped to a class and a single property is selected.  ..."

    I tried "group by" it did not work for LinqToExcel either.  Thanks again for your help.

    Hugh


    hugh

    Friday, September 7, 2012 2:06 PM
  • Hi Hugh;

    Try it this way. First the Linq to Excel will execute then a Linq to Object query will put the data in the correct format.

    var sample = (from x2 in (from x in excel.Worksheet<Sample>("SampleFile")
                              where x.Name != null
                              select x.Name)
                  select new NameDTO
                  {
                      Name = x2,
                      Data = String.Empty
                  }).Distinct();

      


    Fernando (MCSD)

    If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".

    Friday, September 7, 2012 7:44 PM
  • Hi Fernando,

    I am sorry for late reply since I was away from the office for a few days.  I tried your code and it did not work.  The massage was the same "LingToExcel does not support distinct with more than one property is selected...." 

    I worked out a solution by querying distinct record and then convert it to a array and then add more columns using conventional LINQ.   Thanks for your help.

    Hugh


    hugh

    Wednesday, September 12, 2012 2:24 PM
  • Hi Hugh;

    That is strange because that is what my second query I posted exactly does. Please note the ( ) around the Linq to Excel query.

    (from x in excel.Worksheet<Sample>("SampleFile")
                              where x.Name != null
                              select x.Name)

    That part of the complete query gets done first just like any other C# code in ( ). Then the rest of the complete query gets executed on the results of the Linq to Excel after it has returned.

      

      


    Fernando (MCSD)

    If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".

    Thursday, September 13, 2012 3:29 AM